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.