search

googleapis / python-spanner-sqlalchemy

Apache License 2.0
38 stars 28 forks source link

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

Open mmahmed opened 2 weeks ago

mmahmed commented 2 weeks ago

Environment details

Steps to reproduce

  1. Install Packages:
 pip install SQLAlchemy==1.4.49 sqlalchemy-spanner==1.7.0
  1. 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)
  1. 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)
  1. Reflect Database:
from sqlalchemy import MetaData, inspect

inspector = inspect(engine)
meta = MetaData(bind=engine)
MetaData.reflect(meta, views=True)
  1. 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 commented 2 weeks ago

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 commented 2 weeks ago

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