Autogeneration of index with coalesce function produces invalid SQL

[AurelienJoy]

Describe the bug

When we declare an index using a coalesce function, the SQL migration instruction produced is invalid because it is missing some parenthesis around the coalesce function.

Example of the invalid generated alembic instruction:

...
op.create_index('uq_user_id_team_id', 'dummy_user', ['id', sa.text('coalesce(team_id, 0)')], unique=True)
...

And the invalid SQL instruction produced:

CREATE UNIQUE INDEX uq_user_id_team_id ON dummy_user (id, coalesce(team_id, 0))

Expected behavior

The valid expected SQL instruction is:

CREATE UNIQUE INDEX uq_user_id_team_id ON dummy_user (id, (coalesce(team_id, 0)))

To Reproduce

from typing import Optional

from sqlalchemy import Index, Integer, String, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "dummy_user"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    team_id: Mapped[Optional[int]] = mapped_column(Integer)

    __table_args__ = (
        Index(
            "uq_user_id_team_id",
            "id",
            func.coalesce(team_id, 0),
            unique=True,
        ),
    )

Auto-generate:

alembic revision --autogenerate -m "Init user"

Error

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 6dfa085b3de9 -> dd8fa63bfe84, Init user
Traceback (most recent call last):
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/.virtualenvs/global/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/.virtualenvs/global/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/.virtualenvs/global/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'team_id, 0))' at line 1")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/.virtualenvs/global/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/config.py", line 641, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/config.py", line 631, in main
    self.run_cmd(cfg, options)
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/config.py", line 608, in run_cmd
    fn(
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/command.py", line 403, in upgrade
    script.run_env()
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/script/base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 850, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "migrations/env.py", line 101, in <module>
    run_migrations_online()
  File "migrations/env.py", line 95, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/runtime/environment.py", line 948, in run_migrations
    self.get_context().run_migrations(**kw)
  File ".virtualenvs/global/lib/python3.9/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
    step.migration_fn(**kw)
  File "/projects/global/prvcore/alembic/migrations/versions/dd8fa63bfe84_init_user.py", line 31, in upgrade
    op.create_index('uq_user_id_team_id', 'dummy_user', ['id', sa.text('coalesce(team_id, 0)')], unique=True)
  File "<string>", line 8, in create_index
  File "<string>", line 3, in create_index
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/operations/ops.py", line 999, in create_index
    return operations.invoke(op)
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/operations/base.py", line 445, in invoke
    return fn(self, operation)
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/operations/toimpl.py", line 108, in create_index
    operations.impl.create_index(idx, **kw)
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/ddl/impl.py", line 395, in create_index
    self._exec(schema.CreateIndex(index, **kw))
  File "/.virtualenvs/global/lib/python3.9/site-packages/alembic/ddl/impl.py", line 207, in _exec
    return conn.execute(construct, multiparams)
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    ret = self._execute_context(
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/.virtualenvs/global/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/.virtualenvs/global/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/.virtualenvs/global/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/.virtualenvs/global/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.ProgrammingError: (MySQLdb.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'team_id, 0))' at line 1")
[SQL: CREATE UNIQUE INDEX uq_user_id_team_id ON dummy_user (id, coalesce(team_id, 0))]
(Background on this error at: https://sqlalche.me/e/20/f405)

Versions.

• OS: MacOS 14.4.1
• Python: 3.9
• Alembic: 1.13.1
• SQLAlchemy: 2.0.30
• Database: mysql 8.0.32
• DBAPI: /

Additional context

Also when doing an alembic check after the index creation, it prints a warning:

WARNI [alembic.ddl.impl] Generating approximate signature for index Index('uq_user_id_team_id', Column('id', Integer(), table=<dummy_user>, primary_key=True, nullable=False), <sqlalchemy.sql.functions.coalesce at 0x122caeb80; coalesce>, unique=True). The dialect implementation should either skip expression indexes or provide a custom implementation.

Is it expected ? How can I fix it ?

Have a nice day!

[CaselIT]

Hi,

thanks for reporting. Here the issue seems to be when compiling the statement, not autogenerate

[CaselIT]

So the create index is managed by sqlalchemy.

@zzzeek what do you think is best here? Modify what is rendered by autogenerate, so render sa.text('(coalesce(team_id, 0))'), or modify the create index in sqlalchemy to add () to text elements?

[zzzeek]

I feel like this cant be the only case where some artificial parenthesis need to be added, nothing else in mysql / pg / sql server dialects?

indeed, take a look at

alembic/alembic/ddl/postgresql.py

Lines 427 to 448 in b8e2c35

	def render_ddl_sql_expr(
	self,
	expr: ClauseElement,
	is_server_default: bool = False,
	is_index: bool = False,
	**kw: Any,
	) -> str:
	"""Render a SQL expression that is typically a server default,
	index expression, etc.
	"""
	# apply self_group to index expressions;
	# see https://github.com/sqlalchemy/sqlalchemy/blob/
	# 82fa95cfce070fab401d020c6e6e4a6a96cc2578/
	# lib/sqlalchemy/dialects/postgresql/base.py#L2261
	if is_index and not isinstance(expr, ColumnClause):
	expr = expr.self_group()
	return super().render_ddl_sql_expr(
	expr, is_server_default=is_server_default, is_index=is_index, **kw
	)

. likely the same exact code to add to mysql.py

[CaselIT]

Good catch. I forgot to look for self_group