Description
I'm 2.1'ing this for visibility but consider 2.0 backport if it's not a big change
Discussed in #12270
Originally posted by div1001 January 23, 2025
Describe the bug
Encountered a bug(?) when migrating from 1.4 to 2.0.
In 2.0 attempting to use expression (CASE, arithmetic, etc) in RETURNING results in AssertionError being raised, 1.4 works fine.
Explicitly adding .label() to the expression seems to fix the problem.
Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected
None
SQLAlchemy Version in Use
2.0.37
DBAPI (i.e. the database driver)
None
Database Vendor and Major Version
None
Python Version
3.10
Operating system
Linux
To Reproduce
from sqlalchemy import Integer, delete
from sqlalchemy.orm import (
DeclarativeBase,
Mapped,
mapped_column,
)
class Base(DeclarativeBase):
pass
class Table(Base):
__tablename__ = 'tbl'
id: Mapped[int] = mapped_column(Integer, primary_key=True)
stmt = delete(Table).returning(Table.id, Table.id * -1).cte()
print(str(stmt)) # DELETE FROM tbl RETURNING tbl.id, tbl.id * :id_1 AS anon_2
stmt_w_label = delete(Table).returning(Table.id, (Table.id * -1).label('smth')).cte()
print(str(stmt_w_label)) # DELETE FROM tbl RETURNING tbl.id, tbl.id * :id_1 AS smth
# works
print('with label:', stmt_w_label.c.id)
# fails with AssertionError on "assert key is not None"
print('without label:', stmt.c.id)Error
Traceback (most recent call last):
File "sqla_test.py", line 28, in <module>
print('without label:', complex_stmt.c.id)
File ".venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 1145, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/selectable.py", line 894, in c
self._populate_column_collection()
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/selectable.py", line 2149, in _populate_column_collection
self.element._generate_fromclause_column_proxies(self)
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/dml.py", line 423, in _generate_fromclause_column_proxies
fromclause._columns._populate_separate_keys(
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 1695, in _populate_separate_keys
self._collection[:] = collection = [
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 1695, in <listcomp>
self._collection[:] = collection = [
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/dml.py", line 424, in <genexpr>
col._make_proxy(fromclause)
File ".venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 1660, in _make_proxy
assert key is not None
AssertionError
Additional context:
The actual use case was something like this:
my_cte = delete(...).returning(..., case(...)).cte()
db.execute(
insert(...).from_select(..., select(my_cte).order_by(my_cte.c.id))
)
which failed when trying to get id of my_cte.c, explicitly adding a label to case() resolves the problem.