From 756501b65a9c88790c1947d7c39956bfc374b8e8 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Tue, 18 Apr 2023 09:42:50 -0400
Subject: [PATCH] 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
---
 doc/build/changelog/unreleased_20/9669.rst |  7 ++++
 lib/sqlalchemy/orm/attributes.py           |  2 +-
 test/ext/mypy/plain_files/orm_querying.py  | 38 ++++++++++++++++++++++
 3 files changed, 46 insertions(+), 1 deletion(-)
 create mode 100644 doc/build/changelog/unreleased_20/9669.rst
 create mode 100644 test/ext/mypy/plain_files/orm_querying.py

diff --git a/doc/build/changelog/unreleased_20/9669.rst b/doc/build/changelog/unreleased_20/9669.rst
new file mode 100644
index 0000000000..1ab9b071cb
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/9669.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: bug, typing
+    :tickets: 9669
+
+    Fixed typing issue where :meth:`_orm.PropComparator.and_` expressions would
+    not be correctly typed inside of loader options such as
+    :func:`_orm.selectinload`.
diff --git a/lib/sqlalchemy/orm/attributes.py b/lib/sqlalchemy/orm/attributes.py
index 3a60eda4f2..69ddd33889 100644
--- a/lib/sqlalchemy/orm/attributes.py
+++ b/lib/sqlalchemy/orm/attributes.py
@@ -405,7 +405,7 @@ class QueryableAttribute(
 
     def and_(
         self, *clauses: _ColumnExpressionArgument[bool]
-    ) -> interfaces.PropComparator[bool]:
+    ) -> QueryableAttribute[bool]:
         if TYPE_CHECKING:
             assert isinstance(self.comparator, RelationshipProperty.Comparator)
 
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 0000000000..6bde850aae
--- /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)))
-- 
2.47.3