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

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().