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.
Versions
I noticed an issue with query compilation since this commit https://github.com/sqlalchemy/sqlalchemy/commit/60e7034a7423955cd89d5624f8769d3804ca6d82. 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:
My reproducing example includes ability to run the same operation against sqlite which does not fail.