assign correct typing to deferred(), query_property()

Fixed typing for :func:`_orm.deferred` and :func:`_orm.query_expression`
to work correctly with 2.0 style mappings.

Fixes: #9536
Change-Id: Idb88c0a11b7896093234b3cc31595f64b552cba0

3 files changed, 26 insertions, 5 deletions

diff --git a/doc/build/changelog/unreleased_20/9536.rst b/doc/build/changelog/unreleased_20/9536.rst
new file mode 100644
index 000000000..0e807c73a
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/9536.rst
@@ -0,0 +1,6 @@
+.. change::
+    :tags: bug, typing
+    :tickets: 9536
+
+    Fixed typing for :func:`_orm.deferred` and :func:`_orm.query_expression`
+    to work correctly with 2.0 style mappings.
diff --git a/lib/sqlalchemy/orm/_orm_constructors.py b/lib/sqlalchemy/orm/_orm_constructors.py
index 9f9330cea..11ed80d8c 100644
--- a/lib/sqlalchemy/orm/_orm_constructors.py
+++ b/lib/sqlalchemy/orm/_orm_constructors.py
@@ -24,7 +24,6 @@ from ._typing import _O
 from .descriptor_props import Composite
 from .descriptor_props import Synonym
 from .interfaces import _AttributeOptions
-from .properties import ColumnProperty
 from .properties import MappedColumn
 from .properties import MappedSQLExpression
 from .query import AliasOption
@@ -1971,7 +1970,7 @@ def deferred(
     expire_on_flush: bool = True,
     info: Optional[_InfoType] = None,
     doc: Optional[str] = None,
-) -> ColumnProperty[_T]:
+) -> MappedSQLExpression[_T]:
     r"""Indicate a column-based mapped attribute that by default will
     not load unless accessed.
 
@@ -1997,7 +1996,7 @@ def deferred(
         :ref:`orm_queryguide_deferred_imperative`
 
     """
-    return ColumnProperty(
+    return MappedSQLExpression(
         column,
         *additional_columns,
         attribute_options=_AttributeOptions(
@@ -2021,7 +2020,7 @@ def query_expression(
     expire_on_flush: bool = True,
     info: Optional[_InfoType] = None,
     doc: Optional[str] = None,
-) -> ColumnProperty[_T]:
+) -> MappedSQLExpression[_T]:
     """Indicate an attribute that populates from a query-time SQL expression.
 
     :param default_expr: Optional SQL expression object that will be used in
@@ -2034,7 +2033,7 @@ def query_expression(
         :ref:`orm_queryguide_with_expression` - background and usage examples
 
     """
-    prop = ColumnProperty(
+    prop = MappedSQLExpression(
         default_expr,
         attribute_options=_AttributeOptions(
             _NoArg.NO_ARG,
diff --git a/test/ext/mypy/plain_files/orm_config_constructs.py b/test/ext/mypy/plain_files/orm_config_constructs.py
index 008e16f24..4583cdaba 100644
--- a/test/ext/mypy/plain_files/orm_config_constructs.py
+++ b/test/ext/mypy/plain_files/orm_config_constructs.py
@@ -1,6 +1,10 @@
+from sqlalchemy import String
+from sqlalchemy.orm import column_property
 from sqlalchemy.orm import DeclarativeBase
+from sqlalchemy.orm import deferred
 from sqlalchemy.orm import Mapped
 from sqlalchemy.orm import mapped_column
+from sqlalchemy.orm import query_expression
 from sqlalchemy.orm import validates
 
 
@@ -18,3 +22,15 @@ class User(Base):
     def validate_name(self, name: str) -> str:
         """test #8577"""
         return name + "hi"
+
+    # test #9536
+    _password: Mapped[str] = mapped_column("Password", String)
+    password1: Mapped[str] = column_property(
+        _password.collate("SQL_Latin1_General_CP1_CS_AS"), deferred=True
+    )
+    password2: Mapped[str] = deferred(
+        _password.collate("SQL_Latin1_General_CP1_CS_AS")
+    )
+    password3: Mapped[str] = query_expression(
+        _password.collate("SQL_Latin1_General_CP1_CS_AS")
+    )