SQLAlchemy type compiler emits invalid DDL for empty STRUCT, JSON, and CLOB columns

[laughingman7743]

Problem

AthenaTypeCompiler renders column DDL that Athena rejects, and it silently substitutes placeholder types instead of raising. #855 / PR #870 fixes STRUCT rendering for columns that declare their fields. The following cases remain.

1. Empty AthenaStruct() renders ROW() (pyathena/sqlalchemy/compiler.py:202). Athena has no empty STRUCT/ROW type. Both struct<> in DDL and CAST(NULL AS ROW()) in DML fail to parse. An empty AthenaStruct() arises from reflection as well as from user code. A top-level struct<...> column is reflected as AthenaStruct() with its fields discarded, and a top-level map<...> column is reflected as String (pyathena/sqlalchemy/base.py:89-91, :728). Fields are parsed only for types nested inside an ARRAY (base.py:693). So compiling CREATE TABLE from a reflected table emits ROW(), which Athena rejects.
2. Unexpected types fall back silently. visit_struct, visit_map, and visit_array return ROW(), MAP<STRING, STRING>, and ARRAY<STRING> when the type is not the expected class (compiler.py:202, :225, :235). They should raise CompileError, as visit_TIME does.
3. JSON columns render JSON in DDL (compiler.py:181). Athena's CREATE TABLE does not accept a JSON column type. CAST(... AS JSON) in DML is valid and must keep working.
4. CLOB / NCLOB render BINARY in DDL (compiler.py:144, :147), but CAST renders them as VARCHAR. These are character types and should be STRING in DDL.
5. The INT special cases are not needed for validity. get_column_specification replaces exact Integer/INTEGER/INT column types with INT (compiler.py:1154), and visit_INTEGER switches to INT through the _athena_array_ddl flag (compiler.py:123). Athena's DDL parser also accepts INTEGER, including inside MAP<...>. A subclass of Integer or a TypeDecorator over Integer already renders INTEGER in column DDL. These special cases only make the spelling consistent, so any cleanup can keep or drop them without affecting validity.
6. The class docstring says "FLOAT maps to REAL in CAST expressions" (compiler.py:85). CAST rendering is done by AthenaStatementCompiler.visit_cast / _complex_dml_type, not by this class.

The type compiler does not know whether it is rendering DDL (Hive) or DML (Trino) type syntax. Nested context is carried only by the _athena_array_ddl flag that visit_array sets. DML casts of complex types already bypass it through _complex_dml_type (compiler.py:718). In practice, the type compiler renders DDL.

Reproduction

from sqlalchemy import Column, MetaData, Table, types
from sqlalchemy.schema import CreateTable

from pyathena.sqlalchemy.base import AthenaDialect
from pyathena.sqlalchemy.types import AthenaStruct

table = Table(
    "t",
    MetaData(),
    Column("s", AthenaStruct()),
    Column("j", types.JSON),
    Column("c", types.CLOB),
    awsathena_location="s3://bucket/path/",
)
print(CreateTable(table).compile(dialect=AthenaDialect()))
# s ROW(), j JSON, c BINARY

Athena results were checked with StartQueryExecution. The DDL statements target a nonexistent database, so a statement that parses fails only with Database does not exist, and no table is created.

Statement	Result
SELECT CAST(NULL AS ROW())	mismatched input ')'. Expecting: <identifier>, <type>
CREATE EXTERNAL TABLE db.t (a struct<>) ...	ParseException ... mismatched input '<>' expecting < near 'struct' in struct type
CREATE EXTERNAL TABLE db.t (a JSON) ...	ParseException ... cannot recognize input near 'JSON' ')' 'STORED' in column type
CREATE EXTERNAL TABLE db.t (a INTEGER) ...	Parses (Database does not exist)
CREATE EXTERNAL TABLE db.t (a MAP<STRING, INTEGER>) ...	Parses (Database does not exist)

Environment

• PyAthena master a86a180
• Python 3.13.1, SQLAlchemy 2.0.46
• awsathena+rest dialect, Athena engine version 3

Proposed fix (optional)

• Treat AthenaTypeCompiler as the DDL (Hive) type compiler, and keep DML type rendering in AthenaStatementCompiler.
• Raise CompileError for an empty AthenaStruct and for unexpected types in visit_struct / visit_map / visit_array. Decide separately whether reflection should parse the fields of top-level struct<...> and map<...> columns. That would change reflected column types, so it is a behavior change.
• Render CLOB / NCLOB as STRING in DDL, and decide how a JSON column should render in DDL (for example STRING, or CompileError).
• Decide whether to keep the INT special cases.
• Update the tests that currently expect ROW() (tests/pyathena/sqlalchemy/test_compiler.py:92, :121, and the empty-struct tests added by PR fix: render Hive STRUCT syntax in table column DDL #870).

The behavior changes above need release notes. This should start after PR #870 is merged.