Merge "Initial ORM typing layout" into main

1 files changed, 288 insertions, 271 deletions

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 705a89889..65f345fb3 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -50,6 +50,7 @@ from .visitors import Traversible
 from .. import exc
 from .. import inspection
 from .. import util
+from ..util.langhelpers import TypingOnly
 
 if typing.TYPE_CHECKING:
     from decimal import Decimal
@@ -572,262 +573,8 @@ class CompilerColumnElement(
     __slots__ = ()
 
 
-class ColumnElement(
-    roles.ColumnArgumentOrKeyRole,
-    roles.StatementOptionRole,
-    roles.WhereHavingRole,
-    roles.BinaryElementRole,
-    roles.OrderByRole,
-    roles.ColumnsClauseRole,
-    roles.LimitOffsetRole,
-    roles.DMLColumnRole,
-    roles.DDLConstraintColumnRole,
-    roles.DDLExpressionRole,
-    operators.ColumnOperators["ColumnElement"],
-    ClauseElement,
-    Generic[_T],
-):
-    """Represent a column-oriented SQL expression suitable for usage in the
-    "columns" clause, WHERE clause etc. of a statement.
-
-    While the most familiar kind of :class:`_expression.ColumnElement` is the
-    :class:`_schema.Column` object, :class:`_expression.ColumnElement`
-    serves as the basis
-    for any unit that may be present in a SQL expression, including
-    the expressions themselves, SQL functions, bound parameters,
-    literal expressions, keywords such as ``NULL``, etc.
-    :class:`_expression.ColumnElement`
-    is the ultimate base class for all such elements.
-
-    A wide variety of SQLAlchemy Core functions work at the SQL expression
-    level, and are intended to accept instances of
-    :class:`_expression.ColumnElement` as
-    arguments.  These functions will typically document that they accept a
-    "SQL expression" as an argument.  What this means in terms of SQLAlchemy
-    usually refers to an input which is either already in the form of a
-    :class:`_expression.ColumnElement` object,
-    or a value which can be **coerced** into
-    one.  The coercion rules followed by most, but not all, SQLAlchemy Core
-    functions with regards to SQL expressions are as follows:
-
-        * a literal Python value, such as a string, integer or floating
-          point value, boolean, datetime, ``Decimal`` object, or virtually
-          any other Python object, will be coerced into a "literal bound
-          value".  This generally means that a :func:`.bindparam` will be
-          produced featuring the given value embedded into the construct; the
-          resulting :class:`.BindParameter` object is an instance of
-          :class:`_expression.ColumnElement`.
-          The Python value will ultimately be sent
-          to the DBAPI at execution time as a parameterized argument to the
-          ``execute()`` or ``executemany()`` methods, after SQLAlchemy
-          type-specific converters (e.g. those provided by any associated
-          :class:`.TypeEngine` objects) are applied to the value.
-
-        * any special object value, typically ORM-level constructs, which
-          feature an accessor called ``__clause_element__()``.  The Core
-          expression system looks for this method when an object of otherwise
-          unknown type is passed to a function that is looking to coerce the
-          argument into a :class:`_expression.ColumnElement` and sometimes a
-          :class:`_expression.SelectBase` expression.
-          It is used within the ORM to
-          convert from ORM-specific objects like mapped classes and
-          mapped attributes into Core expression objects.
-
-        * The Python ``None`` value is typically interpreted as ``NULL``,
-          which in SQLAlchemy Core produces an instance of :func:`.null`.
-
-    A :class:`_expression.ColumnElement` provides the ability to generate new
-    :class:`_expression.ColumnElement`
-    objects using Python expressions.  This means that Python operators
-    such as ``==``, ``!=`` and ``<`` are overloaded to mimic SQL operations,
-    and allow the instantiation of further :class:`_expression.ColumnElement`
-    instances
-    which are composed from other, more fundamental
-    :class:`_expression.ColumnElement`
-    objects.  For example, two :class:`.ColumnClause` objects can be added
-    together with the addition operator ``+`` to produce
-    a :class:`.BinaryExpression`.
-    Both :class:`.ColumnClause` and :class:`.BinaryExpression` are subclasses
-    of :class:`_expression.ColumnElement`::
-
-        >>> from sqlalchemy.sql import column
-        >>> column('a') + column('b')
-        <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
-        >>> print(column('a') + column('b'))
-        a + b
-
-    .. seealso::
-
-        :class:`_schema.Column`
-
-        :func:`_expression.column`
-
-    """
-
-    __visit_name__ = "column_element"
-
-    primary_key = False
-    foreign_keys = []
-    _proxies = ()
-
-    _tq_label = None
-    """The named label that can be used to target
-    this column in a result set in a "table qualified" context.
-
-    This label is almost always the label used when
-    rendering <expr> AS <label> in a SELECT statement when using
-    the LABEL_STYLE_TABLENAME_PLUS_COL label style, which is what the legacy
-    ORM ``Query`` object uses as well.
-
-    For a regular Column bound to a Table, this is typically the label
-    <tablename>_<columnname>.  For other constructs, different rules
-    may apply, such as anonymized labels and others.
-
-    .. versionchanged:: 1.4.21 renamed from ``._label``
-
-    """
-
-    key = None
-    """The 'key' that in some circumstances refers to this object in a
-    Python namespace.
-
-    This typically refers to the "key" of the column as present in the
-    ``.c`` collection of a selectable, e.g. ``sometable.c["somekey"]`` would
-    return a :class:`_schema.Column` with a ``.key`` of "somekey".
-
-    """
-
-    @HasMemoized.memoized_attribute
-    def _tq_key_label(self):
-        """A label-based version of 'key' that in some circumstances refers
-        to this object in a Python namespace.
-
-
-        _tq_key_label comes into play when a select() statement is constructed
-        with apply_labels(); in this case, all Column objects in the ``.c``
-        collection are rendered as <tablename>_<columnname> in SQL; this is
-        essentially the value of ._label. But to locate those columns in the
-        ``.c`` collection, the name is along the lines of <tablename>_<key>;
-        that's the typical value of .key_label.
-
-        .. versionchanged:: 1.4.21 renamed from ``._key_label``
-
-        """
-        return self._proxy_key
-
-    @property
-    def _key_label(self):
-        """legacy; renamed to _tq_key_label"""
-        return self._tq_key_label
-
-    @property
-    def _label(self):
-        """legacy; renamed to _tq_label"""
-        return self._tq_label
-
-    @property
-    def _non_anon_label(self):
-        """the 'name' that naturally applies this element when rendered in
-        SQL.
-
-        Concretely, this is the "name" of a column or a label in a
-        SELECT statement; ``<columnname>`` and ``<labelname>`` below::
-
-            SELECT <columnmame> FROM table
-
-            SELECT column AS <labelname> FROM table
-
-        Above, the two names noted will be what's present in the DBAPI
-        ``cursor.description`` as the names.
-
-        If this attribute returns ``None``, it means that the SQL element as
-        written does not have a 100% fully predictable "name" that would appear
-        in the ``cursor.description``. Examples include SQL functions, CAST
-        functions, etc. While such things do return names in
-        ``cursor.description``, they are only predictable on a
-        database-specific basis; e.g. an expression like ``MAX(table.col)`` may
-        appear as the string ``max`` on one database (like PostgreSQL) or may
-        appear as the whole expression ``max(table.col)`` on SQLite.
-
-        The default implementation looks for a ``.name`` attribute on the
-        object, as has been the precedent established in SQLAlchemy for many
-        years.  An exception is made on the ``FunctionElement`` subclass
-        so that the return value is always ``None``.
-
-        .. versionadded:: 1.4.21
-
-
-
-        """
-        return getattr(self, "name", None)
-
-    _render_label_in_columns_clause = True
-    """A flag used by select._columns_plus_names that helps to determine
-    we are actually going to render in terms of "SELECT <col> AS <label>".
-    This flag can be returned as False for some Column objects that want
-    to be rendered as simple "SELECT <col>"; typically columns that don't have
-    any parent table and are named the same as what the label would be
-    in any case.
-
-    """
-
-    _allow_label_resolve = True
-    """A flag that can be flipped to prevent a column from being resolvable
-    by string label name.
-
-    The joined eager loader strategy in the ORM uses this, for example.
-
-    """
-
-    _is_implicitly_boolean = False
-
-    _alt_names = ()
-
-    def self_group(self, against=None):
-        if (
-            against in (operators.and_, operators.or_, operators._asbool)
-            and self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity
-        ):
-            return AsBoolean(self, operators.is_true, operators.is_false)
-        elif against in (operators.any_op, operators.all_op):
-            return Grouping(self)
-        else:
-            return self
-
-    def _negate(self):
-        if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity:
-            return AsBoolean(self, operators.is_false, operators.is_true)
-        else:
-            return super(ColumnElement, self)._negate()
-
-    @util.memoized_property
-    def type(self) -> "TypeEngine[_T]":
-        return type_api.NULLTYPE
-
-    @HasMemoized.memoized_attribute
-    def comparator(self) -> "TypeEngine.Comparator[_T]":
-        try:
-            comparator_factory = self.type.comparator_factory
-        except AttributeError as err:
-            raise TypeError(
-                "Object %r associated with '.type' attribute "
-                "is not a TypeEngine class or object" % self.type
-            ) from err
-        else:
-            return comparator_factory(self)
-
-    def __getattr__(self, key):
-        try:
-            return getattr(self.comparator, key)
-        except AttributeError as err:
-            raise AttributeError(
-                "Neither %r object nor %r object has an attribute %r"
-                % (
-                    type(self).__name__,
-                    type(self.comparator).__name__,
-                    key,
-                )
-            ) from err
+class SQLCoreOperations(Generic[_T], TypingOnly):
+    __slots__ = ()
 
     # annotations for comparison methods
     # these are from operators->Operators / ColumnOperators,
@@ -894,7 +641,9 @@ class ColumnElement(
             ...
 
         @overload
-        def concat(self, other: Any) -> "BinaryExpression[_ST]":
+        def concat(
+            self: "SQLCoreOperations[_ST]", other: Any
+        ) -> "BinaryExpression[_ST]":
             ...
 
         @overload
@@ -986,7 +735,7 @@ class ColumnElement(
         ) -> "BinaryExpression[bool]":
             ...
 
-        def distinct(self: "ColumnElement[_T]") -> "UnaryExpression[_T]":
+        def distinct(self: "SQLCoreOperations[_T]") -> "UnaryExpression[_T]":
             ...
 
         def any_(self) -> "CollectionAggregate":
@@ -996,22 +745,28 @@ class ColumnElement(
             ...
 
         # numeric overloads.  These need more tweaking
+        # in particular they all need to have a variant for Optiona[_T]
+        # because Optional only applies to the data side, not the expression
+        # side
 
         @overload
         def __add__(
-            self: "ColumnElement[_NT]", other: "Union[ColumnElement[_NT], _NT]"
+            self: "Union[_SQO[_NT], _SQO[Optional[_NT]]]",
+            other: "Union[_SQO[Optional[_NT]], _SQO[_NT], _NT]",
         ) -> "BinaryExpression[_NT]":
             ...
 
         @overload
         def __add__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "Union[_SQO[_NT], _SQO[Optional[_NT]]]",
+            other: Any,
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
         @overload
         def __add__(
-            self: "ColumnElement[_ST]", other: Any
+            self: "Union[_SQO[_ST], _SQO[Optional[_ST]]]",
+            other: Any,
         ) -> "BinaryExpression[_ST]":
             ...
 
@@ -1031,7 +786,8 @@ class ColumnElement(
 
         @overload
         def __sub__(
-            self: "ColumnElement[_NT]", other: "Union[ColumnElement[_NT], _NT]"
+            self: "SQLCoreOperations[_NT]",
+            other: "Union[SQLCoreOperations[_NT], _NT]",
         ) -> "BinaryExpression[_NT]":
             ...
 
@@ -1044,7 +800,7 @@ class ColumnElement(
 
         @overload
         def __rsub__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1057,7 +813,7 @@ class ColumnElement(
 
         @overload
         def __mul__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1070,7 +826,7 @@ class ColumnElement(
 
         @overload
         def __rmul__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1083,7 +839,7 @@ class ColumnElement(
 
         @overload
         def __mod__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1096,7 +852,7 @@ class ColumnElement(
 
         @overload
         def __rmod__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1109,7 +865,7 @@ class ColumnElement(
 
         @overload
         def __truediv__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1122,7 +878,7 @@ class ColumnElement(
 
         @overload
         def __rtruediv__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1135,7 +891,7 @@ class ColumnElement(
 
         @overload
         def __floordiv__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1148,7 +904,7 @@ class ColumnElement(
 
         @overload
         def __rfloordiv__(
-            self: "ColumnElement[_NT]", other: Any
+            self: "SQLCoreOperations[_NT]", other: Any
         ) -> "BinaryExpression[_NUMERIC]":
             ...
 
@@ -1159,6 +915,267 @@ class ColumnElement(
         def __rfloordiv__(self, other: Any) -> "BinaryExpression":
             ...
 
+
+_SQO = SQLCoreOperations
+
+
+class ColumnElement(
+    roles.ColumnArgumentOrKeyRole,
+    roles.StatementOptionRole,
+    roles.WhereHavingRole,
+    roles.BinaryElementRole,
+    roles.OrderByRole,
+    roles.ColumnsClauseRole,
+    roles.LimitOffsetRole,
+    roles.DMLColumnRole,
+    roles.DDLConstraintColumnRole,
+    roles.DDLExpressionRole,
+    SQLCoreOperations[_T],
+    operators.ColumnOperators[SQLCoreOperations],
+    ClauseElement,
+):
+    """Represent a column-oriented SQL expression suitable for usage in the
+    "columns" clause, WHERE clause etc. of a statement.
+
+    While the most familiar kind of :class:`_expression.ColumnElement` is the
+    :class:`_schema.Column` object, :class:`_expression.ColumnElement`
+    serves as the basis
+    for any unit that may be present in a SQL expression, including
+    the expressions themselves, SQL functions, bound parameters,
+    literal expressions, keywords such as ``NULL``, etc.
+    :class:`_expression.ColumnElement`
+    is the ultimate base class for all such elements.
+
+    A wide variety of SQLAlchemy Core functions work at the SQL expression
+    level, and are intended to accept instances of
+    :class:`_expression.ColumnElement` as
+    arguments.  These functions will typically document that they accept a
+    "SQL expression" as an argument.  What this means in terms of SQLAlchemy
+    usually refers to an input which is either already in the form of a
+    :class:`_expression.ColumnElement` object,
+    or a value which can be **coerced** into
+    one.  The coercion rules followed by most, but not all, SQLAlchemy Core
+    functions with regards to SQL expressions are as follows:
+
+        * a literal Python value, such as a string, integer or floating
+          point value, boolean, datetime, ``Decimal`` object, or virtually
+          any other Python object, will be coerced into a "literal bound
+          value".  This generally means that a :func:`.bindparam` will be
+          produced featuring the given value embedded into the construct; the
+          resulting :class:`.BindParameter` object is an instance of
+          :class:`_expression.ColumnElement`.
+          The Python value will ultimately be sent
+          to the DBAPI at execution time as a parameterized argument to the
+          ``execute()`` or ``executemany()`` methods, after SQLAlchemy
+          type-specific converters (e.g. those provided by any associated
+          :class:`.TypeEngine` objects) are applied to the value.
+
+        * any special object value, typically ORM-level constructs, which
+          feature an accessor called ``__clause_element__()``.  The Core
+          expression system looks for this method when an object of otherwise
+          unknown type is passed to a function that is looking to coerce the
+          argument into a :class:`_expression.ColumnElement` and sometimes a
+          :class:`_expression.SelectBase` expression.
+          It is used within the ORM to
+          convert from ORM-specific objects like mapped classes and
+          mapped attributes into Core expression objects.
+
+        * The Python ``None`` value is typically interpreted as ``NULL``,
+          which in SQLAlchemy Core produces an instance of :func:`.null`.
+
+    A :class:`_expression.ColumnElement` provides the ability to generate new
+    :class:`_expression.ColumnElement`
+    objects using Python expressions.  This means that Python operators
+    such as ``==``, ``!=`` and ``<`` are overloaded to mimic SQL operations,
+    and allow the instantiation of further :class:`_expression.ColumnElement`
+    instances
+    which are composed from other, more fundamental
+    :class:`_expression.ColumnElement`
+    objects.  For example, two :class:`.ColumnClause` objects can be added
+    together with the addition operator ``+`` to produce
+    a :class:`.BinaryExpression`.
+    Both :class:`.ColumnClause` and :class:`.BinaryExpression` are subclasses
+    of :class:`_expression.ColumnElement`::
+
+        >>> from sqlalchemy.sql import column
+        >>> column('a') + column('b')
+        <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
+        >>> print(column('a') + column('b'))
+        a + b
+
+    .. seealso::
+
+        :class:`_schema.Column`
+
+        :func:`_expression.column`
+
+    """
+
+    __visit_name__ = "column_element"
+
+    primary_key = False
+    foreign_keys = []
+    _proxies = ()
+
+    _tq_label = None
+    """The named label that can be used to target
+    this column in a result set in a "table qualified" context.
+
+    This label is almost always the label used when
+    rendering <expr> AS <label> in a SELECT statement when using
+    the LABEL_STYLE_TABLENAME_PLUS_COL label style, which is what the legacy
+    ORM ``Query`` object uses as well.
+
+    For a regular Column bound to a Table, this is typically the label
+    <tablename>_<columnname>.  For other constructs, different rules
+    may apply, such as anonymized labels and others.
+
+    .. versionchanged:: 1.4.21 renamed from ``._label``
+
+    """
+
+    key = None
+    """The 'key' that in some circumstances refers to this object in a
+    Python namespace.
+
+    This typically refers to the "key" of the column as present in the
+    ``.c`` collection of a selectable, e.g. ``sometable.c["somekey"]`` would
+    return a :class:`_schema.Column` with a ``.key`` of "somekey".
+
+    """
+
+    @HasMemoized.memoized_attribute
+    def _tq_key_label(self):
+        """A label-based version of 'key' that in some circumstances refers
+        to this object in a Python namespace.
+
+
+        _tq_key_label comes into play when a select() statement is constructed
+        with apply_labels(); in this case, all Column objects in the ``.c``
+        collection are rendered as <tablename>_<columnname> in SQL; this is
+        essentially the value of ._label. But to locate those columns in the
+        ``.c`` collection, the name is along the lines of <tablename>_<key>;
+        that's the typical value of .key_label.
+
+        .. versionchanged:: 1.4.21 renamed from ``._key_label``
+
+        """
+        return self._proxy_key
+
+    @property
+    def _key_label(self):
+        """legacy; renamed to _tq_key_label"""
+        return self._tq_key_label
+
+    @property
+    def _label(self):
+        """legacy; renamed to _tq_label"""
+        return self._tq_label
+
+    @property
+    def _non_anon_label(self):
+        """the 'name' that naturally applies this element when rendered in
+        SQL.
+
+        Concretely, this is the "name" of a column or a label in a
+        SELECT statement; ``<columnname>`` and ``<labelname>`` below::
+
+            SELECT <columnmame> FROM table
+
+            SELECT column AS <labelname> FROM table
+
+        Above, the two names noted will be what's present in the DBAPI
+        ``cursor.description`` as the names.
+
+        If this attribute returns ``None``, it means that the SQL element as
+        written does not have a 100% fully predictable "name" that would appear
+        in the ``cursor.description``. Examples include SQL functions, CAST
+        functions, etc. While such things do return names in
+        ``cursor.description``, they are only predictable on a
+        database-specific basis; e.g. an expression like ``MAX(table.col)`` may
+        appear as the string ``max`` on one database (like PostgreSQL) or may
+        appear as the whole expression ``max(table.col)`` on SQLite.
+
+        The default implementation looks for a ``.name`` attribute on the
+        object, as has been the precedent established in SQLAlchemy for many
+        years.  An exception is made on the ``FunctionElement`` subclass
+        so that the return value is always ``None``.
+
+        .. versionadded:: 1.4.21
+
+
+
+        """
+        return getattr(self, "name", None)
+
+    _render_label_in_columns_clause = True
+    """A flag used by select._columns_plus_names that helps to determine
+    we are actually going to render in terms of "SELECT <col> AS <label>".
+    This flag can be returned as False for some Column objects that want
+    to be rendered as simple "SELECT <col>"; typically columns that don't have
+    any parent table and are named the same as what the label would be
+    in any case.
+
+    """
+
+    _allow_label_resolve = True
+    """A flag that can be flipped to prevent a column from being resolvable
+    by string label name.
+
+    The joined eager loader strategy in the ORM uses this, for example.
+
+    """
+
+    _is_implicitly_boolean = False
+
+    _alt_names = ()
+
+    def self_group(self, against=None):
+        if (
+            against in (operators.and_, operators.or_, operators._asbool)
+            and self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity
+        ):
+            return AsBoolean(self, operators.is_true, operators.is_false)
+        elif against in (operators.any_op, operators.all_op):
+            return Grouping(self)
+        else:
+            return self
+
+    def _negate(self):
+        if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity:
+            return AsBoolean(self, operators.is_false, operators.is_true)
+        else:
+            return super(ColumnElement, self)._negate()
+
+    @util.memoized_property
+    def type(self) -> "TypeEngine[_T]":
+        return type_api.NULLTYPE
+
+    @HasMemoized.memoized_attribute
+    def comparator(self) -> "TypeEngine.Comparator[_T]":
+        try:
+            comparator_factory = self.type.comparator_factory
+        except AttributeError as err:
+            raise TypeError(
+                "Object %r associated with '.type' attribute "
+                "is not a TypeEngine class or object" % self.type
+            ) from err
+        else:
+            return comparator_factory(self)
+
+    def __getattr__(self, key):
+        try:
+            return getattr(self.comparator, key)
+        except AttributeError as err:
+            raise AttributeError(
+                "Neither %r object nor %r object has an attribute %r"
+                % (
+                    type(self).__name__,
+                    type(self.comparator).__name__,
+                    key,
+                )
+            ) from err
+
     def operate(
         self,
         op: operators.OperatorType,