Autogenerate fails when index with expression is removed

[rafales]

Describe the bug

How the index looks like:

    __table_args__ = (
        sa.Index(
            "foobar",
            "network_id",
            sa.func.lower(contract_address),
            unique=True,
        ),
    )

When removing the above index and trying to run autogenerate I get the following exception:

  File "/project/.venv/lib/python3.11/site-packages/alembic/command.py", line 236, in revision
    scripts = [script for script in revision_context.generate_scripts()]
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/command.py", line 236, in <listcomp>
    scripts = [script for script in revision_context.generate_scripts()]
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/autogenerate/api.py", line 605, in generate_scripts
    yield self._to_script(generated_revision)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/autogenerate/api.py", line 505, in _to_script
    render._render_python_into_templatevars(
  File "/project/.venv/lib/python3.11/site-packages/alembic/autogenerate/render.py", line 85, in _render_python_into_templatevars
    _render_cmd_body(downgrade_ops, autogen_context)
  File "/project/.venv/lib/python3.11/site-packages/alembic/autogenerate/render.py", line 107, in _render_cmd_body
    lines = render_op(autogen_context, op)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/autogenerate/render.py", line 125, in render_op
    lines = util.to_list(renderer(autogen_context, op))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/autogenerate/render.py", line 156, in _render_modify_table
    t_lines = render_op(autogen_context, t_op)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/autogenerate/render.py", line 125, in render_op
    lines = util.to_list(renderer(autogen_context, op))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/autogenerate/render.py", line 276, in _add_index
    index = op.to_index()
            ^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/operations/ops.py", line 898, in to_index
    idx = schema_obj.index(
          ^^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/alembic/operations/schemaobj.py", line 252, in index
    idx = sa_schema.Index(
          ^^^^^^^^^^^^^^^^
  File "/project/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 5002, in __init__
    ColumnCollectionMixin.__init__(
  File "/project/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 3966, in __init__
    self._check_attach()
  File "/project/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 4009, in _check_attach
    raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Column(s) 'collection.lower(contract_address::text)' are not part of table 'collection'.

Here's a debugger session diving into what's wrong:

ipdb> vars(op)
{'index_name': 'foobar', 'table_name': 'collection', 'columns': [Column('network_id', VARCHAR(), table=<collection>, nullable=False), <alembic.util.sqla_compat._textual_index_element object at 0x115ddd190>], 'schema': None, 'unique': False, 'kw': {}}
ipdb> op.columns[1]
<alembic.util.sqla_compat._textual_index_element object at 0x115ddd190>
ipdb> str(op.columns[1])
'lower(contract_address::text)'

It seems that the problem lies with the fact that sqlalchemy.sql.schema.Index doesn't work nicely with alembic.util.sqla_compat._textual_index_element.

Versions.

• OS: MacOS 13.3
• Python: 3.11.2
• Alembic: 1.10.2
• SQLAlchemy: 2.0.8
• Database: PostgreSQL 15.2
• DBAPI: psycopg 2.9.5

[CaselIT]

Hi,

Thanks for reporting. I thought we added tests for this, but maybe there is something that's not covered. I'll try looking at it.

[CaselIT]

I thought we added tests for this, but maybe there is something that's not covered.

That was indeed the case, the issue was in the rendering part, not on detection of the indexes.

[sqla-tester]

CaselIT has proposed a fix for this issue in the main branch:

Fix issues in autogenerate of function index removal https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/4546

[CaselIT]

@rafales thanks for reporting and for testing this new feature. Let us know if you find other issues

@zzzeek I think we should publish 1.10.3 once this is merged, since currently autogenerate of the removal of a function index is broken

[zzzeek]

sounds good

[CaselIT]

@zzzeek if you aren't releasing already maybe we can wait for #1213?
law of twos strikes again :)

[zzzeek]

yeah i mentioned if you have a 1213 fix we will wait