Autogeneration of index with coalesce function produces invalid SQL

**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:
```py
...
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:
```SQL
CREATE UNIQUE INDEX uq_user_id_team_id ON dummy_user (id, coalesce(team_id, 0))
```

**Expected behavior**

The valid expected SQL instruction is:
```SQL
CREATE UNIQUE INDEX uq_user_id_team_id ON dummy_user (id, (coalesce(team_id, 0)))
```

**To Reproduce**
```py
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!**


Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Autogeneration of index with coalesce function produces invalid SQL #1492

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Uh oh!

Autogeneration of index with coalesce function produces invalid SQL #1492

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions