sqlalchemy: ORM alias on CTE doesnt separate out things correctly

Discussed in https://github.com/sqlalchemy/sqlalchemy/discussions/11164

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)

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Reactions: 1
  • Comments: 15 (7 by maintainers)

Commits related to this issue

Most upvoted comments

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

a = aliased(A, select(A).where(A.i > A.j).cte("filtered_a"))

# this is the CTE on the inside
a = inspect(a).selectable