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

[laughingman7743]

Summary

The SQLAlchemy compliance suite (tests/sqlalchemy/test_suite.py) deletes test classes and skips individual methods.
pyathena/sqlalchemy/requirements.py also declares requirements as unsupported.
Some exclusions are genuine Athena limitations, while others can be implemented or narrowed by the dialect.

This umbrella issue tracks the audit.
Each remaining workstream is a sub-issue intended for a separate implementation session, worktree, and draft pull request.

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

A. Likely supportable

• SimpleUpdateDeleteTest — enabled by Enable SQLAlchemy update and delete compliance tests #750.
• RowCountTest and non-returning DML results — tracked by SQLAlchemy compliance: complete DML result and rowcount coverage #754; resolved by Support executemany row counts and restore DML compliance coverage #768.
• array_type requirement — tracked by SQLAlchemy compliance: enable native ARRAY type support #755; resolved by Support SQLAlchemy ARRAY partial UPDATE and slice resizing #776.
• datetime_literals requirement — tracked with timestamp precision by SQLAlchemy compliance: support datetime literals and timestamp microseconds #756; resolved by Support SQLAlchemy datetime literals and microsecond timestamps #818.
• TrueDivTest.test_truediv_integer / test_truediv_integer_bound — enabled by Fix SQLAlchemy true division semantics #753.
• HasTableTest.test_has_table_cache — tracked with reflection behavior by SQLAlchemy compliance: restore reflection and identifier coverage #757; resolved by Restore SQLAlchemy reflection and identifier coverage #771.

B. Needs verification against Athena engine v3

• CTETest — tracked by SQLAlchemy compliance: restore CTE test coverage #758; resolved by Restore SQLAlchemy CTE compliance coverage #772.
• ComponentReflectionTest / ComponentReflectionTestExtra — tracked by SQLAlchemy compliance: restore reflection and identifier coverage #757; resolved by Restore SQLAlchemy reflection and identifier coverage #771.
• DateTimeMicrosecondsTest / TimestampMicrosecondsTest and timestamp_microseconds — tracked by SQLAlchemy compliance: support datetime literals and timestamp microseconds #756; resolved by Support SQLAlchemy datetime literals and microsecond timestamps #818.
• BinaryTest — tracked by SQLAlchemy compliance: restore binary type coverage #759; resolved by Restore SQLAlchemy binary compliance and preserve CSV binary NULLs #770.
• DifficultParametersTest — tracked by SQLAlchemy compliance: audit parameter and SQL rendering edge cases #760; resolved by Re-enable SQLAlchemy parameter and SQL rendering compliance tests #825.
• QuotedNameArgumentTest — tracked by SQLAlchemy compliance: restore reflection and identifier coverage #757; resolved by Restore SQLAlchemy reflection and identifier coverage #771.
• LongNameBlowoutTest — tracked by SQLAlchemy compliance: restore reflection and identifier coverage #757; resolved by Restore SQLAlchemy reflection and identifier coverage #771.
• InsertBehaviorTest.test_no_results_for_non_returning_insert — tracked by SQLAlchemy compliance: complete DML result and rowcount coverage #754; resolved by Support executemany row counts and restore DML compliance coverage #768.
• TrueDivTest.test_truediv_numeric / test_truediv_float — enabled by Fix SQLAlchemy true division semantics #753.
• FetchLimitOffsetTest.test_limit_render_multiple_times — tracked by SQLAlchemy compliance: audit parameter and SQL rendering edge cases #760; resolved by Re-enable SQLAlchemy parameter and SQL rendering compliance tests #825.
• IntegerTest.test_huge_int — enabled by SQLAlchemy compliance: audit numeric range and float precision #826.
• StringTest.test_dont_truncate_rightside — tracked by SQLAlchemy compliance: audit parameter and SQL rendering edge cases #760; resolved by Re-enable SQLAlchemy parameter and SQL rendering compliance tests #825.
• precision_generic_float_type requirement — kept closed by SQLAlchemy compliance: audit numeric range and float precision #826 with a confirmed reason: generic Float maps to 32-bit Athena REAL.
• update_where_target_in_subquery requirement — tracked by SQLAlchemy compliance: verify Iceberg UPDATE with target subqueries #762; resolved by Enable Iceberg UPDATE target subquery compliance #769.

C. Genuinely unsupported by Athena

No implementation is planned for the exclusions below unless Athena adds the required capability.

• PK, FK, unique, and 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, and HasIndexTest.
• Identity and autoincrement: autoincrement_insert, IdentityAutoincrementTest, and InsertBehaviorTest.test_insert_from_select_autoinc(_no_rows).
• Types Athena lacks: TimeTest, TimeMicrosecondsTest, UuidTest, and uuid_data_type.
• Syntax Athena lacks: DistinctOnTest and expression-based LIMIT or OFFSET cases.
• Temporary tables: temporary_tables and temp_table_reflection.

Test constraints

• Run only the affected test nodes locally.
• Run Athena tests with -n 1 because the development account has limited query concurrency.
• Prefer compile-only or unit tests when they prove the behavior without Athena.
• Leave complete SQLAlchemy and Python-version matrices to CI.

References

• Test suite exclusions: tests/sqlalchemy/test_suite.py
• Requirements: pyathena/sqlalchemy/requirements.py
• Dialect: pyathena/sqlalchemy/base.py and pyathena/sqlalchemy/compiler.py
• Suite configuration: setup.cfg and tests/sqlalchemy/conftest.py

[laughingman7743]

All sub-issues (#754, #755, #756, #757, #758, #759, #760, #761, #762) are closed as completed, and every checklist item above now links the pull request that resolved it. Section C (genuine Athena limitations) records exclusions that are not planned.