Compliance Test Failures in SQLAlchemy Spanner Dialect #805
Description
Observed Behavior:
The compliance_test_20 test suite for the SQLAlchemy Spanner dialect is failing on Python 3.13 and 3.14. The failures occur in tests related to schema reflection, specifically with identifiers that are quoted, contain dots, or have mixed case.
Environment:
- Python: 3.13 and 3.14
- SQLAlchemy: 2.0.45 (as per test logs)
- Spanner Emulator: latest
Failure Categories:
- Teardown Errors: Difficulty dropping schema elements created during tests, suggesting they weren't named or registered as expected.
google.api_core.exceptions.NotFound: 404 fk.with.dots is not a constraint in user_orders
google.api_core.exceptions.NotFound: 404 Index not found: ix.with.dots
google.api_core.exceptions.FailedPrecondition: 400 Cannot drop table test_table with indices: IX_test_data.
- Reflection Errors for Quoted/Special Names: The dialect fails to correctly return foreign keys, indexes, and unique constraints when their names include quotes, dots, or mixed case.
- Foreign Keys:
AssertionError: 0 != 1
In test_get_foreign_keys_quoted_name[FK_users_id]
AssertionError: 'FK_users_id' != 'fk.with.dots'
In test_get_foreign_keys_quoted_name[fk.with.dots]
AssertionError: 'FK_users_id' != 'fk_users_id'
In test_get_foreign_keys_quoted_name[fk_users_id]
- Indexes:
AssertionError: Expected IX_test_data in []
In test_get_indexes_quoted_name[IX_test_data]
AssertionError: Expected ix.with.dots in ['IX_test_data']
In test_get_indexes_quoted_name[ix.with.dots]
AssertionError: Expected mixedCaseIndex in ['IX_test_data']
In test_get_indexes_quoted_name[mixedCaseIndex]
- Unique Constraints:
AssertionError: 0 != 1
In test_get_unique_constraints_quoted_name[UQ_email]
- Incomplete Schema Reflection: Tests retrieving multiple schema objects show discrepancies in the returned sets.
AssertionError: {'dingalings', 'email_addresses', 'test_table', 'user_orders', 'users',
'users_ref'} != {'dingalings', 'email_addresses', 'users'}
In test_get_table_names[False-_exclusions_01-None-_exclusions_10]
Suspected Cause:
The core issue seems to be the dialect's handling of quoted identifiers and those with special characters or casing within the schema reflection queries against the Spanner
INFORMATION_SCHEMA. The dialect might not be correctly quoting or escaping these names in the queries, or not properly processing the results.
Next Steps:
- Investigate the SQL generated by the dialect for reflection methods.
- Verify how quoted identifiers are handled in the dialect's parsing logic.
- Test against a real Cloud Spanner instance to rule out emulator-specific behavior.