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.
• array_type requirement — tracked by SQLAlchemy compliance: enable native ARRAY type support #755.
• datetime_literals requirement — tracked with timestamp precision by SQLAlchemy compliance: support datetime literals and timestamp microseconds #756.
• 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.

B. Needs verification against Athena engine v3

• CTETest — tracked by SQLAlchemy compliance: restore CTE test coverage #758.
• ComponentReflectionTest / ComponentReflectionTestExtra — tracked by SQLAlchemy compliance: restore reflection and identifier coverage #757.
• DateTimeMicrosecondsTest / TimestampMicrosecondsTest and timestamp_microseconds — tracked by SQLAlchemy compliance: support datetime literals and timestamp microseconds #756.
• BinaryTest — tracked by SQLAlchemy compliance: restore binary type coverage #759.
• DifficultParametersTest — tracked by SQLAlchemy compliance: audit parameter and SQL rendering edge cases #760.
• QuotedNameArgumentTest — tracked by SQLAlchemy compliance: restore reflection and identifier coverage #757.
• LongNameBlowoutTest — tracked by SQLAlchemy compliance: restore reflection and identifier coverage #757.
• InsertBehaviorTest.test_no_results_for_non_returning_insert — tracked by SQLAlchemy compliance: complete DML result and rowcount coverage #754.
• 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.
• IntegerTest.test_huge_int — tracked by SQLAlchemy compliance: audit numeric range and float precision #761.
• StringTest.test_dont_truncate_rightside — tracked by SQLAlchemy compliance: audit parameter and SQL rendering edge cases #760.
• precision_generic_float_type requirement — tracked by SQLAlchemy compliance: audit numeric range and float precision #761.
• update_where_target_in_subquery requirement — tracked by SQLAlchemy compliance: verify Iceberg UPDATE with target subqueries #762.

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