No typed path to .scalars() when exec() gets a compound select from .union()

[Pedrexus]

Description

Combining two selects with .union() and running them through session.exec() works at runtime, but the result's .scalars() doesn't survive type checking:

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str


class Villain(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str


engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Hero(name="Deadpond"))
    session.add(Villain(name="Chaos"))
    session.commit()
    statement = select(Hero.name).union(select(Villain.name))
    result = session.exec(statement)
    names = result.scalars().all()  # runs fine, rejected by type checkers
    print(names)  # ['Chaos', 'Deadpond']

At runtime .union() returns a SQLAlchemy CompoundSelect, and exec() hands back the plain SQLAlchemy Result (a ChunkedIteratorResult here), which has .scalars(). On the typed surface though:

• pyright 1.1.407 rejects the exec() call itself: No overloads for "exec" match the provided arguments — CompoundSelect[tuple[str]] is not SQLModel's Select/SelectOfScalar, so only the UpdateBase overload is left and it doesn't fit either.
• ty 0.0.59 resolves exec() to TupleResult[Unknown] and then rejects the attribute: Object of type TupleResult[Unknown] has no attribute scalars. TupleResult is SQLAlchemy's typing-only class that whitelists a subset of Result methods, and scalars() isn't among them.

So any compound select (union, intersect, except) currently has no way to reach .scalars() without a cast or an ignore.

Versions: sqlmodel 0.0.39, SQLAlchemy 2.1.0b3, Python 3.14.

A fix could be an exec() overload accepting CompoundSelect (or Executable) that returns Result[Any], similar to what #909 did for update/delete statements, or a tuple-result annotation that declares scalars().

[apoorva-01]

The reason exec() blows up here is that its overloads only cover Select, SelectOfScalar, and UpdateBase. A .union() (or intersect/except) hands you a SQLAlchemy CompoundSelect, which matches none of them, so pyright falls back to the UpdateBase overload and ty ends up with TupleResult that has no scalars().

The typed way out is session.execute() instead of exec(). It takes any Executable, CompoundSelect included, and returns Result[Any], so .scalars().all() checks clean with no cast:

statement = select(Hero.name).union(select(Villain.name))
names = session.execute(statement).scalars().all()

One heads-up: SQLModel puts @deprecated on execute() to nudge people toward exec(), so a PEP 702 aware checker will show a soft deprecation hint on the call. It's not going anywhere though, it's the documented passthrough to plain SQLAlchemy for exactly the statements exec() doesn't model. If you'd rather stay on exec() you're back to a cast, which is the thing you're trying to dodge.

[LuShadowX]

session.execute() does type-check cleanly, so it works as a workaround — but it's a method SQLModel explicitly discourages, which I think supports your overload proposal rather than closing it out.

Checked with pyright 1.1.411, sqlmodel 0.0.39:

statement = select(Hero.name).union(select(Villain.name))
session.exec(statement).scalars().all()
#   error: No overloads for "exec" match the provided arguments
#   error: Argument of type "CompoundSelect[tuple[str]]" cannot be assigned to
#          parameter "statement" of type "UpdateBase" in function "exec"

session.execute(statement).scalars().all()
#   0 errors — clean

Both return ['Chaos', 'Deadpond'] at runtime. For completeness, exec(statement).all() gives [('Chaos',), ('Deadpond',)], tuples rather than scalars — but that line doesn't type-check either, since the rejection is on the exec() call itself, not on .scalars().

Two things worth adding:

The failure isn't caused by the SQLAlchemy 2.1 prerelease. I reproduced both errors on SQLAlchemy 2.0.51, so it's purely the shape of exec()'s overloads. Relatedly, sqlmodel 0.0.39 declares SQLAlchemy<2.1.0,>=2.0.14, so your 2.1.0b3 is outside the supported range — uv refuses that combination outright. Unrelated to this bug, but worth knowing before you chase anything else.

execute() is marked deprecated in SQLModel. From sqlmodel/orm/session.py:

@deprecated(
    """
    ...
    """,
    category=None,
)
def execute(
    self,
    statement: _Executable,
    ...
) -> Result[Any]:
    """
    🚨 You probably want to use `session.exec()` instead of `session.execute()`.
    ...
    """

category=None means no runtime warning and pyright doesn't flag it, so it stays quiet — but the documented intent is that execute() isn't the path users should be on. So the current state is: the only type-clean way to run a compound select is the method the library tells you not to use.

That seems like a reasonable case for the exec() overload you sketched. CompoundSelect is arguably the narrower, safer option over Executable — it covers union/intersect/except without widening exec() to accept anything executable, and Result[Any] matches what exec() already hands back at runtime for these statements.