SQLAlchemy compliance suite: audit skipped tests and re-enable supportable ones

[laughingman7743]

Summary

The SQLAlchemy compliance suite (tests/sqlalchemy/test_suite.py) deletes 19 test classes and skips 16 methods, and pyathena/sqlalchemy/requirements.py declares 21 requirements as unsupported. Some of these are genuine Athena limitations and should stay skipped, but others are likely supportable by the dialect and should be tracked and implemented.

This is an umbrella issue modeled on #722: items are classified below; individual items should be split into child issues/PRs as they are picked up.

Important context: the compliance suite runs against Iceberg tables — tests/sqlalchemy/conftest.py appends tblproperties='table_type'='ICEBERG' to the dburi. Iceberg tables support row-level UPDATE/DELETE/MERGE, so Iceberg DML is in scope for the suite.

A. Likely supportable — implement

• SimpleUpdateDeleteTest / RowCountTest (deleted, test_suite.py:23-24) — Iceberg tables support row-level UPDATE/DELETE. The blocker is likely DML rowcount reporting (Athena does not return affected-row counts), not the DML itself. Investigate re-enabling SimpleUpdateDeleteTest and gating only the rowcount assertions (sane_rowcount requirement).
• array_type requirement (requirements.py:10) — Athena has native array<T>, but the dialect maps array → String (pyathena/sqlalchemy/base.py:77). Implement real ARRAY type support and open the requirement.
• datetime_literals requirement (requirements.py:82) — Trino/Athena supports TIMESTAMP '...' / DATE '...' literals. Likely a literal-rendering gap in the compiler rather than an engine limitation.
• TrueDivTest.test_truediv_integer / test_truediv_integer_bound (skipped, test_suite.py:53-59) — int/int → int is Trino behavior, but SQLAlchemy truediv semantics can be satisfied by casting operands to DOUBLE in the compiler (other dialects do this).
• HasTableTest.test_has_table_cache (skipped, test_suite.py:32) — "No cache is used when creating tables." This is a dialect-side caching behavior mismatch and should be fixable in the dialect.

B. Needs verification — re-test against Athena engine v3 (Trino)

• CTETest (deleted, test_suite.py:14) — inconsistent with ctes = supported() (requirements.py:104). Basic WITH works on Athena. Re-enable the test class and gate only the unsupported parts (e.g., recursive CTEs, CTE-with-DML), or resolve the contradiction the other way.
• ComponentReflectionTest / ComponentReflectionTestExtra (deleted, test_suite.py:11-12) — deleted wholesale, but column, comment, and view reflection already work (base.py implements get_view_names, get_view_definition, get_table_comment, per-column comments). Re-enable and let requirement gating (PK/FK/index reflection already unsupported) skip only the genuinely unsupported sub-tests.
• DateTimeMicrosecondsTest / TimestampMicrosecondsTest (deleted, test_suite.py:15,27) and timestamp_microseconds requirement (requirements.py:86) — Athena engine v3 (Trino) supports sub-second timestamp precision. Verify and re-enable if possible.
• BinaryTest (deleted, test_suite.py:10) — Athena has VARBINARY; likely a binary bind/literal encoding gap in the dialect rather than an engine limitation.
• DifficultParametersTest (deleted, test_suite.py:16) — odd bind-parameter names; paramstyle/quoting concern, likely dialect-fixable.
• QuotedNameArgumentTest (deleted, test_suite.py:22) — quoted names passed to reflection APIs; likely dialect-fixable.
• LongNameBlowoutTest (deleted, test_suite.py:21) — verify against Athena/Glue identifier length limits.
• InsertBehaviorTest.test_no_results_for_non_returning_insert (skipped TODO, test_suite.py:46)
• TrueDivTest.test_truediv_numeric / test_truediv_float (skipped TODO, test_suite.py:61-69) — float precision/rounding (AssertionError: 2.299999908606215 != 2.3).
• FetchLimitOffsetTest.test_limit_render_multiple_times (skipped TODO, test_suite.py:93)
• IntegerTest.test_huge_int (skipped TODO, test_suite.py:100) — values beyond BIGINT range; may be a genuine limitation, verify.
• StringTest.test_dont_truncate_rightside (skipped TODO, test_suite.py:107)
• precision_generic_float_type requirement (requirements.py:90) — float precision artifact ({15.7563820, 15.7563830} != {15.7563827}); verify whether REAL/DOUBLE mapping can satisfy it.
• update_where_target_in_subquery requirement (requirements.py:74) — UPDATE ... WHERE col IN (subquery) on Iceberg; Trino support varies by version, verify on engine v3.

C. Genuinely unsupported by Athena — keep skipped (for the record)

No action needed; listed to document why they stay excluded.

• PK/FK/unique/index constraints and their reflection: foreign_keys, on_update_cascade, self_referential_foreign_keys, foreign_key_ddl, primary_key_constraint_reflection, foreign_key_constraint_reflection, index_reflection, indexes_with_ascdesc, reflect_indexes_with_ascdesc, unique_constraint_reflection, duplicate_key_raises_integrity_error, recursive_fk_cascade, CompositeKeyReflectionTest, JoinTest, HasIndexTest
• Identity/autoincrement: autoincrement_insert, IdentityAutoincrementTest, InsertBehaviorTest.test_insert_from_select_autoinc(_no_rows)
• Types Athena lacks: TimeTest / TimeMicrosecondsTest (no standalone TIME type), UuidTest / uuid_data_type (no native UUID type)
• Syntax Athena lacks: DistinctOnTest (DISTINCT ON is PostgreSQL-only), expression-in-LIMIT/OFFSET skips in FetchLimitOffsetTest (Trino accepts constants only)
• Temp tables: temporary_tables, temp_table_reflection (no session-scoped temporary tables; CTAS is the substitute)

References

• Test suite exclusions: tests/sqlalchemy/test_suite.py
• Requirements: pyathena/sqlalchemy/requirements.py
• Dialect: pyathena/sqlalchemy/base.py, pyathena/sqlalchemy/compiler.py
• Suite configuration: setup.cfg ([sqla_testing]), tests/sqlalchemy/conftest.py