Index on a table containing columns with no sort order fails while reflecting the table

[mmahmed]

Environment details

• Programming language: Python
• OS: debian (can be reproduce on any OS)
• Language runtime version: Python 3.11.7
• Package version: sqlalchemy-spanner==1.7.0, SQLAlchemy==1.4.49

Steps to reproduce

1. Install Packages:

 pip install SQLAlchemy==1.4.49 sqlalchemy-spanner==1.7.0

2. Setup Engine:

from sqlalchemy import create_engine

# Replace the following with your actual database connection string
DATABASE_URL = "spanner+spanner:///projects/YOUR_PROJECT_ID/instances/YOUR_INSTANCE_ID/databases/YOUR_DATABASE_ID"
engine = create_engine(DATABASE_URL)

3. Setup Database:

# SQL for creating the table
create_table_sql = """
CREATE TABLE clicks (
  id INT64 NOT NULL,
  date DATE NOT NULL,
  url_hash STRING(32) NOT NULL,
  url STRING(MAX) NOT NULL,
) PRIMARY KEY(id, date DESC, url_hash);
"""

# SQL for creating the index
create_index_sql = """
CREATE INDEX idx_on_clicks ON clicks(id, date DESC, url_hash) STORING (url);
"""

# Execute the SQL commands
with engine.connect() as connection:
    connection.execute(create_table_sql)
    connection.execute(create_index_sql)

4. Reflect Database:

from sqlalchemy import MetaData, inspect

inspector = inspect(engine)
meta = MetaData(bind=engine)
MetaData.reflect(meta, views=True)

5. Exception from Step 4:

  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 614, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 689, in _init
    self._autoload(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 724, in _autoload
    conn_insp.reflect_table(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 807, in reflect_table
    self._reflect_indexes(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 1071, in _reflect_indexes
    if k in c_sorting:
       ^^^^^^^^^^^^^^
TypeError: argument of type 'NoneType' is not iterable

Note: SQLAlchemy-2.0.30 also has the same issue.

[mmahmed]

Hi @harshachinta - Can you please check this issue and the attached PR. I have updated the issue with detailed steps to reproduce. Please let me know if you need any information.

[mmahmed]

Hi @ankiaga - Can you please check this issue and the attached PR. Please let me know if you need any information.

[mmahmed]

Hi @olavloite - Can you please check this issue and the attached PR. Please let me know if you need any information.