add Mapped to _ORMColCollectionElement

Fixed issue where using the :paramref:`_orm.relationship.remote_side`
and similar parameters, passing an annotated declarative object typed as
:class:`_orm.Mapped`, would not be accepted by the type checker.

Fixes: #9150
Change-Id: I5770c17ee4ad8c54661354da9582ec3c4706ffcc

3 files changed, 33 insertions, 1 deletions

diff --git a/doc/build/changelog/unreleased_20/more_typing.rst b/doc/build/changelog/unreleased_20/more_typing.rst
index 62cd04e8b..f7b1ba258 100644
--- a/doc/build/changelog/unreleased_20/more_typing.rst
+++ b/doc/build/changelog/unreleased_20/more_typing.rst
@@ -37,3 +37,11 @@
     as a context manager were not preserved, indicating :class:`_engine.Result`
     in all cases rather than the specific :class:`_engine.Result` sub-type.
     Pull request courtesy Martin Baláž.
+
+.. change::
+    :tags: typing, bug
+    :tickets: 9150
+
+    Fixed issue where using the :paramref:`_orm.relationship.remote_side`
+    and similar parameters, passing an annotated declarative object typed as
+    :class:`_orm.Mapped`, would not be accepted by the type checker.
diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index 66d3a6035..05fa17a96 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -175,7 +175,7 @@ _ORMOrderByArgument = Union[
 ORMBackrefArgument = Union[str, Tuple[str, Dict[str, Any]]]
 
 _ORMColCollectionElement = Union[
-    ColumnClause[Any], _HasClauseElement, roles.DMLColumnRole
+    ColumnClause[Any], _HasClauseElement, roles.DMLColumnRole, "Mapped[Any]"
 ]
 _ORMColCollectionArgument = Union[
     str,
diff --git a/test/ext/mypy/plain_files/experimental_relationship.py b/test/ext/mypy/plain_files/experimental_relationship.py
index a8d81426e..7acec89e1 100644
--- a/test/ext/mypy/plain_files/experimental_relationship.py
+++ b/test/ext/mypy/plain_files/experimental_relationship.py
@@ -1,6 +1,8 @@
 """this suite experiments with other kinds of relationship syntaxes.
 
 """
+from __future__ import annotations
+
 import typing
 from typing import List
 from typing import Optional
@@ -48,6 +50,28 @@ class Address(Base):
     user_style_two: Mapped["User"] = relationship()
 
 
+class SelfReferential(Base):
+    """test for #9150"""
+
+    __tablename__ = "MyTable"
+
+    idx: Mapped[int] = mapped_column(Integer, primary_key=True)
+    mytable_id: Mapped[int] = mapped_column(ForeignKey("MyTable.idx"))
+
+    not_anno = mapped_column(Integer)
+
+    selfref_1: Mapped[Optional[SelfReferential]] = relationship(
+        remote_side=idx
+    )
+    selfref_2: Mapped[Optional[SelfReferential]] = relationship(
+        foreign_keys=mytable_id
+    )
+
+    selfref_3: Mapped[Optional[SelfReferential]] = relationship(
+        remote_side=not_anno
+    )
+
+
 if typing.TYPE_CHECKING:
     # EXPECTED_RE_TYPE: sqlalchemy.*.InstrumentedAttribute\[Union\[builtins.str, None\]\]
     reveal_type(User.extra)