add common base class for all SQL col expression objects

Added a new type :class:`.SQLColumnExpression` which may be indicated in
user code to represent any SQL column oriented expression, including both
those based on :class:`.ColumnElement` as well as on ORM
:class:`.QueryableAttribute`. This type is a real class, not an alias, so
can also be used as the foundation for other objects.

Fixes: #8847
Change-Id: I3161bdff1c9f447793fce87864e1774a90cd4146

13 files changed, 180 insertions, 8 deletions

diff --git a/doc/build/changelog/unreleased_20/8847.rst b/doc/build/changelog/unreleased_20/8847.rst
new file mode 100644
index 000000000..b3842ac65
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/8847.rst
@@ -0,0 +1,11 @@
+.. change::
+    :tags: usecase, typing
+    :tickets: 8847
+
+    Added a new type :class:`.SQLColumnExpression` which may be indicated in
+    user code to represent any SQL column oriented expression, including both
+    those based on :class:`.ColumnElement` as well as on ORM
+    :class:`.QueryableAttribute`. This type is a real class, not an alias, so
+    can also be used as the foundation for other objects.  An additional
+    ORM-specific subclass :class:`.SQLORMExpression` is also included.
+
diff --git a/doc/build/core/sqlelement.rst b/doc/build/core/sqlelement.rst
index 499f26571..be780adb0 100644
--- a/doc/build/core/sqlelement.rst
+++ b/doc/build/core/sqlelement.rst
@@ -170,6 +170,8 @@ The classes here are generated using the constructors listed at
 .. autoclass:: Over
    :members:
 
+.. autoclass:: SQLColumnExpression
+
 .. autoclass:: TextClause
    :members:
 
diff --git a/doc/build/orm/internals.rst b/doc/build/orm/internals.rst
index f0ace43a6..9bb7e83a4 100644
--- a/doc/build/orm/internals.rst
+++ b/doc/build/orm/internals.rst
@@ -79,6 +79,8 @@ sections, are listed here.
 .. autoclass:: RelationshipProperty
   :members:
 
+.. autoclass:: SQLORMExpression
+
 .. autoclass:: Synonym
 
 .. autoclass:: SynonymProperty
diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py
index 55ce29310..ccc3a446d 100644
--- a/lib/sqlalchemy/__init__.py
+++ b/lib/sqlalchemy/__init__.py
@@ -179,6 +179,7 @@ from .sql.expression import Select as Select
 from .sql.expression import select as select
 from .sql.expression import Selectable as Selectable
 from .sql.expression import SelectBase as SelectBase
+from .sql.expression import SQLColumnExpression as SQLColumnExpression
 from .sql.expression import StatementLambdaElement as StatementLambdaElement
 from .sql.expression import Subquery as Subquery
 from .sql.expression import table as table
diff --git a/lib/sqlalchemy/orm/__init__.py b/lib/sqlalchemy/orm/__init__.py
index 5e2161515..96acce2ff 100644
--- a/lib/sqlalchemy/orm/__init__.py
+++ b/lib/sqlalchemy/orm/__init__.py
@@ -49,6 +49,7 @@ from .base import Mapped as Mapped
 from .base import NotExtension as NotExtension
 from .base import ORMDescriptor as ORMDescriptor
 from .base import PassiveFlag as PassiveFlag
+from .base import SQLORMExpression as SQLORMExpression
 from .base import WriteOnlyMapped as WriteOnlyMapped
 from .context import FromStatement as FromStatement
 from .context import QueryContext as QueryContext
diff --git a/lib/sqlalchemy/orm/attributes.py b/lib/sqlalchemy/orm/attributes.py
index 2c77111c1..89beedc47 100644
--- a/lib/sqlalchemy/orm/attributes.py
+++ b/lib/sqlalchemy/orm/attributes.py
@@ -70,6 +70,7 @@ from .base import PASSIVE_RETURN_NO_VALUE
 from .base import PassiveFlag
 from .base import RELATED_OBJECT_OK  # noqa
 from .base import SQL_OK  # noqa
+from .base import SQLORMExpression
 from .base import state_str
 from .. import event
 from .. import exc
@@ -131,8 +132,8 @@ SelfQueryableAttribute = TypeVar(
 
 @inspection._self_inspects
 class QueryableAttribute(
-    roles.ExpressionElementRole[_T],
     _DeclarativeMapped[_T],
+    SQLORMExpression[_T],
     interfaces.InspectionAttr,
     interfaces.PropComparator[_T],
     roles.JoinTargetRole,
diff --git a/lib/sqlalchemy/orm/base.py b/lib/sqlalchemy/orm/base.py
index b46c78799..032364ff4 100644
--- a/lib/sqlalchemy/orm/base.py
+++ b/lib/sqlalchemy/orm/base.py
@@ -31,7 +31,7 @@ from ._typing import insp_is_mapper
 from .. import exc as sa_exc
 from .. import inspection
 from .. import util
-from ..sql import roles
+from ..sql.elements import SQLColumnExpression
 from ..sql.elements import SQLCoreOperations
 from ..util import FastIntFlag
 from ..util.langhelpers import TypingOnly
@@ -52,6 +52,7 @@ if typing.TYPE_CHECKING:
     from ..sql._typing import _ColumnExpressionArgument
     from ..sql._typing import _InfoType
     from ..sql.elements import ColumnElement
+    from ..sql.operators import OperatorType
 
 _T = TypeVar("_T", bound=Any)
 
@@ -740,9 +741,31 @@ class _MappedAnnotationBase(Generic[_T], TypingOnly):
     __slots__ = ()
 
 
+class SQLORMExpression(
+    SQLORMOperations[_T], SQLColumnExpression[_T], TypingOnly
+):
+    """A type that may be used to indicate any ORM-level attribute or
+    object that acts in place of one, in the context of SQL expression
+    construction.
+
+    :class:`.SQLORMExpression` extends from the Core
+    :class:`.SQLColumnExpression` to add additional SQL methods that are ORM
+    specific, such as :meth:`.PropComparator.of_type`, and is part of the bases
+    for :class:`.InstrumentedAttribute`. It may be used in :pep:`484` typing to
+    indicate arguments or return values that should behave as ORM-level
+    attribute expressions.
+
+    .. versionadded:: 2.0.0b4
+
+
+    """
+
+    __slots__ = ()
+
+
 class Mapped(
+    SQLORMExpression[_T],
     ORMDescriptor[_T],
-    roles.TypedColumnsClauseRole[_T],
     _MappedAnnotationBase[_T],
 ):
     """Represent an ORM mapped attribute on a mapped class.
@@ -830,6 +853,22 @@ class _DeclarativeMapped(Mapped[_T], _MappedAttribute[_T]):
 
     __slots__ = ()
 
+    # MappedSQLExpression, Relationship, Composite etc. dont actually do
+    # SQL expression behavior.  yet there is code that compares them with
+    # __eq__(), __ne__(), etc.   Since #8847 made Mapped even more full
+    # featured including ColumnOperators, we need to have those methods
+    # be no-ops for these objects, so return NotImplemented to fall back
+    # to normal comparison behavior.
+    def operate(self, op: OperatorType, *other: Any, **kwargs: Any) -> Any:
+        return NotImplemented
+
+    __sa_operate__ = operate
+
+    def reverse_operate(
+        self, op: OperatorType, other: Any, **kwargs: Any
+    ) -> Any:
+        return NotImplemented
+
 
 class DynamicMapped(_MappedAnnotationBase[_T]):
     """Represent the ORM mapped attribute type for a "dynamic" relationship.
diff --git a/lib/sqlalchemy/orm/interfaces.py b/lib/sqlalchemy/orm/interfaces.py
index e61e82126..ff003f654 100644
--- a/lib/sqlalchemy/orm/interfaces.py
+++ b/lib/sqlalchemy/orm/interfaces.py
@@ -26,6 +26,7 @@ from typing import Callable
 from typing import cast
 from typing import ClassVar
 from typing import Dict
+from typing import Generic
 from typing import Iterator
 from typing import List
 from typing import NamedTuple
@@ -65,6 +66,7 @@ from ..sql import visitors
 from ..sql.base import _NoArg
 from ..sql.base import ExecutableOption
 from ..sql.cache_key import HasCacheKey
+from ..sql.operators import ColumnOperators
 from ..sql.schema import Column
 from ..sql.type_api import TypeEngine
 from ..util.typing import RODescriptorReference
@@ -595,7 +597,7 @@ class MapperProperty(
 
 
 @inspection._self_inspects
-class PropComparator(SQLORMOperations[_T]):
+class PropComparator(SQLORMOperations[_T], Generic[_T], ColumnOperators):
     r"""Defines SQL operations for ORM mapped attributes.
 
     SQLAlchemy allows for operators to
@@ -676,7 +678,6 @@ class PropComparator(SQLORMOperations[_T]):
         :attr:`.TypeEngine.comparator_factory`
 
     """
-
     __slots__ = "prop", "_parententity", "_adapt_to_entity"
 
     __visit_name__ = "orm_prop_comparator"
diff --git a/lib/sqlalchemy/orm/properties.py b/lib/sqlalchemy/orm/properties.py
index c1da267f4..785a1a098 100644
--- a/lib/sqlalchemy/orm/properties.py
+++ b/lib/sqlalchemy/orm/properties.py
@@ -47,7 +47,6 @@ from ..sql import coercions
 from ..sql import roles
 from ..sql import sqltypes
 from ..sql.base import _NoArg
-from ..sql.elements import SQLCoreOperations
 from ..sql.roles import DDLConstraintColumnRole
 from ..sql.schema import Column
 from ..sql.schema import SchemaConst
@@ -500,7 +499,6 @@ class MappedSQLExpression(ColumnProperty[_T], _DeclarativeMapped[_T]):
 
 class MappedColumn(
     DDLConstraintColumnRole,
-    SQLCoreOperations[_T],
     _IntrospectsAnnotations,
     _MapsColumns[_T],
     _DeclarativeMapped[_T],
diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py
index 5702d6c25..8dae9c3f5 100644
--- a/lib/sqlalchemy/sql/__init__.py
+++ b/lib/sqlalchemy/sql/__init__.py
@@ -82,6 +82,7 @@ from .expression import Select as Select
 from .expression import select as select
 from .expression import Selectable as Selectable
 from .expression import SelectLabelStyle as SelectLabelStyle
+from .expression import SQLColumnExpression as SQLColumnExpression
 from .expression import StatementLambdaElement as StatementLambdaElement
 from .expression import Subquery as Subquery
 from .expression import table as table
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index d9a1a9358..914d2b326 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -1115,6 +1115,26 @@ class SQLCoreOperations(Generic[_T], ColumnOperators, TypingOnly):
             ...
 
 
+class SQLColumnExpression(
+    SQLCoreOperations[_T], roles.ExpressionElementRole[_T], TypingOnly
+):
+    """A type that may be used to indicate any SQL column element or object
+    that acts in place of one.
+
+    :class:`.SQLColumnExpression` is a base of
+    :class:`.ColumnElement`, as well as within the bases of ORM elements
+    such as :class:`.InstrumentedAttribute`, and may be used in :pep:`484`
+    typing to indicate arguments or return values that should behave
+    as column expressions.
+
+    .. versionadded:: 2.0.0b4
+
+
+    """
+
+    __slots__ = ()
+
+
 _SQO = SQLCoreOperations
 
 SelfColumnElement = TypeVar("SelfColumnElement", bound="ColumnElement[Any]")
@@ -1131,7 +1151,7 @@ class ColumnElement(
     roles.DMLColumnRole,
     roles.DDLConstraintColumnRole,
     roles.DDLExpressionRole,
-    SQLCoreOperations[_T],
+    SQLColumnExpression[_T],
     DQLDMLClauseElement,
 ):
     """Represent a column-oriented SQL expression suitable for usage in the
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index d08bbf4eb..2498bfb37 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -95,6 +95,7 @@ from .elements import quoted_name as quoted_name
 from .elements import ReleaseSavepointClause as ReleaseSavepointClause
 from .elements import RollbackToSavepointClause as RollbackToSavepointClause
 from .elements import SavepointClause as SavepointClause
+from .elements import SQLColumnExpression as SQLColumnExpression
 from .elements import TextClause as TextClause
 from .elements import True_ as True_
 from .elements import Tuple as Tuple
diff --git a/test/ext/mypy/plain_files/common_sql_element.py b/test/ext/mypy/plain_files/common_sql_element.py
new file mode 100644
index 000000000..af36c85ee
--- /dev/null
+++ b/test/ext/mypy/plain_files/common_sql_element.py
@@ -0,0 +1,94 @@
+"""tests for #8847
+
+we want to assert that SQLColumnExpression can be used to represent
+all SQL expressions generically, across Core and ORM, without using
+unions.
+
+"""
+
+
+from __future__ import annotations
+
+from sqlalchemy import Column
+from sqlalchemy import Integer
+from sqlalchemy import MetaData
+from sqlalchemy import select
+from sqlalchemy import SQLColumnExpression
+from sqlalchemy import String
+from sqlalchemy import Table
+from sqlalchemy.orm import DeclarativeBase
+from sqlalchemy.orm import Mapped
+from sqlalchemy.orm import mapped_column
+
+
+class Base(DeclarativeBase):
+    pass
+
+
+class User(Base):
+    __tablename__ = "a"
+
+    id: Mapped[int] = mapped_column(primary_key=True)
+    email: Mapped[str]
+
+
+user_table = Table(
+    "user_table", MetaData(), Column("id", Integer), Column("email", String)
+)
+
+
+def receives_str_col_expr(expr: SQLColumnExpression[str]) -> None:
+    pass
+
+
+def receives_bool_col_expr(expr: SQLColumnExpression[bool]) -> None:
+    pass
+
+
+def orm_expr(email: str) -> SQLColumnExpression[bool]:
+    return User.email == email
+
+
+def core_expr(email: str) -> SQLColumnExpression[bool]:
+    email_col: Column[str] = user_table.c.email
+    return email_col == email
+
+
+e1 = orm_expr("hi")
+
+# EXPECTED_TYPE: SQLColumnExpression[bool]
+reveal_type(e1)
+
+stmt = select(e1)
+
+# EXPECTED_TYPE: Select[Tuple[bool]]
+reveal_type(stmt)
+
+stmt = stmt.where(e1)
+
+
+e2 = core_expr("hi")
+
+# EXPECTED_TYPE: SQLColumnExpression[bool]
+reveal_type(e2)
+
+stmt = select(e2)
+
+# EXPECTED_TYPE: Select[Tuple[bool]]
+reveal_type(stmt)
+
+stmt = stmt.where(e2)
+
+
+receives_str_col_expr(User.email)
+receives_str_col_expr(User.email + "some expr")
+receives_str_col_expr(User.email.label("x"))
+receives_str_col_expr(User.email.label("x"))
+
+receives_bool_col_expr(e1)
+receives_bool_col_expr(e1.label("x"))
+receives_bool_col_expr(User.email == "x")
+
+receives_bool_col_expr(e2)
+receives_bool_col_expr(e2.label("x"))
+receives_bool_col_expr(user_table.c.email == "x")