Exception when compiling queries

[gusostow]

Versions

sqlalchemy                1.4.39                   pypi_0    pypi
sqlalchemy-sybase         1.0.6                    pypi_0    pypi
python                    3.10.5          h582c2e5_0_cpython conda-forge

I noticed an issue with query compilation since this commit sqlalchemy/sqlalchemy@60e7034. It persists with and without sqlalchemy-sybase installed.

When certain variables are used more than once in a select query, then the query will fail to compile. See my reproducing example:

import contextlib
from datetime import datetime

import db  # in-house code for getting sybase meta

import sqlalchemy as sa
from sqlalchemy import select


#SOURCE = "sqlite"  # works in sqlite
SOURCE = "sybase"  # broken for sybase

NAME = "table"


@contextlib.contextmanager
def TempTable(meta, *args):
    table = sa.Table(NAME, meta, *args, schema="tempdb.", quote_schema=False)
    table.create()
    try:
        yield table
    finally:
        table.drop()


@contextlib.contextmanager
def get_meta(source):
    cols = (
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("date", sa.Date),
    )
    if source == "sqlite":
        engine = sa.create_engine("sqlite:///:memory:")

        meta = sa.MetaData(bind=engine)
        table = sa.Table(
            NAME,
            meta,
            *cols,
        )
        meta.create_all(engine)
        yield table.name, meta
    elif source == "sybase":
        
        # FIXME: replace with method to get sybase meta
        meta = db.get_meta("sybase", "prod")

        with TempTable(
            meta,
            *cols
        ) as table:
            yield f"{table.schema}.{table.name}", meta

    else:
        raise


MIN_DATE_SQL = "1970-01-01"

with get_meta(SOURCE) as (tablename, meta):
    table = meta.tables[tablename]

    # Issue is because this variable is used twice in query
    # if it is redefined for use in the WHERE then the exception goes away
    date = sa.sql.func.coalesce(table.c.date, MIN_DATE_SQL)

    query = (
        select(
            [
                date,
            ]
        )
        .select_from(table)
        .where(datetime(2021, 1, 1) < date)
        .limit(1)
    )

    print(len(list(query.bind.execute(query.compile(query.bind)))))

Traceback (most recent call last):
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('The SQL contains 3 parameter markers, but 2 parameters were supplied', 'HY000')

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

Traceback (most recent call last):
  File "/home/aostow/projects/sa14/mre.py", line 78, in <module>
    print(len(list(query.bind.execute(query.compile(query.bind)))))
  File "<string>", line 2, in execute
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 402, in warned
    return fn(*args, **kwargs)
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3176, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 463, in _execute_on_connection
    return connection._execute_compiled(
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1551, in _execute_compiled
    ret = self._execute_context(
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/PREFIX/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('The SQL contains 3 parameter markers, but 2 parameters were supplied', 'HY000')
[SQL: SELECT coalesce(tempdb..[table].[date], '1970-0101') AS coalesce_1 
FROM tempdb..[table] 
WHERE coalesce(tempdb..[table].[date], ?) > ? ROWS LIMIT ?]
[parameters: (datetime.datetime(2021, 1, 1, 0, 0), 1)]
(Background on this error at: https://sqlalche.me/e/14/f405)

My reproducing example includes ability to run the same operation against sqlite which does not fail.

[gordthompson]

#11