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)