improve return type for QueryableAttribute.and_()

Fixed typing issue where :meth:`_orm.PropComparator.and_` expressions would
not be correctly typed inside of loader options such as
:func:`_orm.selectinload`.

Fixes: #9669
Change-Id: I874cb22c004e0a24f2b7f530fda542de2c4c6d3b

1 files changed, 38 insertions, 0 deletions

diff --git a/test/ext/mypy/plain_files/orm_querying.py b/test/ext/mypy/plain_files/orm_querying.py
new file mode 100644
index 000000000..6bde850aa
--- /dev/null
+++ b/test/ext/mypy/plain_files/orm_querying.py
@@ -0,0 +1,38 @@
+from __future__ import annotations
+
+from sqlalchemy import ForeignKey
+from sqlalchemy import select
+from sqlalchemy.orm import aliased
+from sqlalchemy.orm import DeclarativeBase
+from sqlalchemy.orm import Mapped
+from sqlalchemy.orm import mapped_column
+from sqlalchemy.orm import relationship
+from sqlalchemy.orm import selectinload
+
+
+class Base(DeclarativeBase):
+    pass
+
+
+class A(Base):
+    __tablename__ = "a"
+
+    id: Mapped[int] = mapped_column(primary_key=True)
+    data: Mapped[str]
+    bs: Mapped[list[B]] = relationship("B")
+
+
+class B(Base):
+    __tablename__ = "b"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    a_id: Mapped[int] = mapped_column(ForeignKey("a.id"))
+    data: Mapped[str]
+
+
+def test_9669_and() -> None:
+    select(A).options(selectinload(A.bs.and_(B.data == "some data")))
+
+
+def test_9669_of_type() -> None:
+    ba = aliased(B)
+    select(A).options(selectinload(A.bs.of_type(ba)))