from sqlalchemy import literal
from sqlalchemy import select
from sqlalchemy import union_all
from sqlalchemy.orm import aliased
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
class Base(DeclarativeBase):
pass
class A(Base):
__tablename__ = "a"
i: Mapped[int] = mapped_column(primary_key=True)
j: Mapped[int] = mapped_column()
class B(Base):
__tablename__ = "b"
i: Mapped[int] = mapped_column(primary_key=True)
j: Mapped[int | None] = mapped_column()
# fails
a = aliased(A, select(A).where(A.i > A.j).cte("filtered_a"))
ac = a
# works
#a = select(A).where(A.i > A.j).cte("filtered_a")
#ac = a.c
a1 = select(ac.i, literal(1, literal_execute=True).label("j"))
b = select(B).join(a, ac.i == B.i).where(B.j.is_not(None))
query = union_all(a1, b)
print(query)
we’d like to release today but have been slowed down by some CI issues that we havent figured out yet
we release monthly so we are just about due though I try very hard not to release on fridays, as a general courtesy and also that I am not around later this afternoon if things go wrong.
this issue has a simple workaround which is to use the CTE directly without using aliased() (see test case at the top). There’s no compelling reason for the CTE to be inside of aliased() since it’s not the row returning construct in the query. if you have some structure where you are receiving an existing
aliased()you can access the CTE like this