Nested varchar in ARRAY<ROW<...>> is deserialized as int in DictCursor results

[kimhanse]

Summary

PyAthena appears to deserialize nested varchar values inside complex types (array(row(...))) as Python int when the string is numeric-looking.

In Athena, the type is clearly varchar, but PyAthena returns int in the parsed Python object.

Environment

• PyAthena: 3.29.3
• Python: 3.12
• Cursor: pyathena.cursor.DictCursor
• Workgroup: Athena (Presto/Trino engine in AWS Athena)

Repro

#!/usr/bin/env python3
from pyathena import connect
from pyathena.cursor import DictCursor
connection = connect(
    work_group="integrations_workgroup",
    cursor_class=DictCursor,
)
cursor = connection.cursor()
cursor.execute(
    """
    WITH t AS (
        SELECT *
        FROM (
            VALUES (
                ARRAY [
                    CAST(ROW('1234') AS ROW(string varchar)),
                    CAST(ROW('value') AS ROW(string varchar))
                ]
            )
        ) AS v(field)
    )
    SELECT
        field,
        json_format(CAST(field AS JSON)) AS field_json
    FROM t
    """
)
result = cursor.fetchall()
print(f"Result: {result}")
print(f"Expected string here: {result[0]['field'][0]['string']}")

Result is

Result: [{'field': [{'string': 1234}, {'string': 'value'}], 'field_json': '[{"string":"1234"},{"string":"value"}]'}]
Expected string here: 1234

The types of the result are lost and it seem that pyathena assumes type based on the values.

Not sure if there is a way to get the expected types from Athena, in my real case they are stored in the table definition, the WITH...SELECT above is just written up as an example for this report.

[laughingman7743]

Thank you for the detailed report.

Root Cause

This is caused by a limitation in the AWS Athena GetQueryResults API. The API's ColumnInfo.Type field only returns the base type name (e.g., "array", "map", "row") — it does not return the full type signature like array(row(string varchar)).

This means PyAthena has no information about the types of nested elements inside complex types. As a result, the converter falls back to heuristic type inference: if a value looks like a number, it's converted to int; if it looks like a float, it becomes float; otherwise it stays a str. This is what causes '1234' (varchar) to become 1234 (int).

This limitation is documented by other Athena client libraries as well:

• Uber's athenadriver: "the type information is stored in ColumnInfo's Type, which is only a type name of the data type, not containing any type metadata"
• The Athena Guide: "the result metadata only specifies if a column is an array or map, not the types of the elements, keys, or values"

Workaround

You can use CAST(... AS JSON) to preserve type information. JSON format properly distinguishes between strings ("1234") and numbers (1234), and PyAthena uses json.loads() for JSON columns which handles this correctly:

cursor.execute("""
    WITH t AS (
        SELECT *
        FROM (
            VALUES (
                ARRAY [
                    CAST(ROW('1234') AS ROW(string varchar)),
                    CAST(ROW('value') AS ROW(string varchar))
                ]
            )
        ) AS v(field)
    )
    SELECT
        CAST(field AS JSON) AS field
    FROM t
""")
result = cursor.fetchall()
# field values will be properly typed: {'string': '1234'}, {'string': 'value'}

Planned Improvements

We are considering the following improvements to address this issue:

• result_set_type_hints parameter: Allow users to provide full type signatures for columns (e.g., result_set_type_hints={"field": "array(row(string varchar))"}), enabling accurate type conversion for nested complex types without requiring SQL changes.
• Safer default behavior: Change the default behavior for nested types (when no type hints are provided) to return values as strings rather than guessing, as silent data corruption is worse than returning unconverted strings.