pep-484 - SQL column operations

note we are taking out the
ColumnOperartors[SQLCoreOperations] thing; not really clear
why that was needed and at the moment it seems I was likely
confused.

Change-Id: I834b75f9b44f91b97e29f2e1a7b1029bd910e0a1

1 files changed, 90 insertions, 75 deletions

diff --git a/lib/sqlalchemy/sql/_elements_constructors.py b/lib/sqlalchemy/sql/_elements_constructors.py
index 9d15cdcc3..770fbe40c 100644
--- a/lib/sqlalchemy/sql/_elements_constructors.py
+++ b/lib/sqlalchemy/sql/_elements_constructors.py
@@ -9,17 +9,19 @@ from __future__ import annotations
 
 import typing
 from typing import Any
-from typing import cast as _typing_cast
+from typing import Callable
+from typing import Iterable
+from typing import Mapping
 from typing import Optional
 from typing import overload
-from typing import Type
+from typing import Sequence
+from typing import Tuple as typing_Tuple
 from typing import TypeVar
 from typing import Union
 
 from . import coercions
-from . import operators
 from . import roles
-from .base import NO_ARG
+from .base import _NoArg
 from .coercions import _document_text_coercion
 from .elements import BindParameter
 from .elements import BooleanClauseList
@@ -35,18 +37,20 @@ from .elements import FunctionFilter
 from .elements import Label
 from .elements import Null
 from .elements import Over
-from .elements import SQLCoreOperations
 from .elements import TextClause
 from .elements import True_
 from .elements import Tuple
 from .elements import TypeCoerce
 from .elements import UnaryExpression
 from .elements import WithinGroup
+from .functions import FunctionElement
+from ..util.typing import Literal
 
 if typing.TYPE_CHECKING:
-    from elements import BinaryExpression
-
     from . import sqltypes
+    from ._typing import _ColumnExpression
+    from ._typing import _TypeEngineArgument
+    from .elements import BinaryExpression
     from .functions import FunctionElement
     from .selectable import FromClause
     from .type_api import TypeEngine
@@ -54,7 +58,7 @@ if typing.TYPE_CHECKING:
 _T = TypeVar("_T")
 
 
-def all_(expr):
+def all_(expr: _ColumnExpression[_T]) -> CollectionAggregate[_T]:
     """Produce an ALL expression.
 
     For dialects such as that of PostgreSQL, this operator applies
@@ -108,7 +112,7 @@ def all_(expr):
     return CollectionAggregate._create_all(expr)
 
 
-def and_(*clauses):
+def and_(*clauses: _ColumnExpression[bool]) -> BooleanClauseList:
     r"""Produce a conjunction of expressions joined by ``AND``.
 
     E.g.::
@@ -169,7 +173,7 @@ def and_(*clauses):
     return BooleanClauseList.and_(*clauses)
 
 
-def any_(expr):
+def any_(expr: _ColumnExpression[_T]) -> CollectionAggregate[_T]:
     """Produce an ANY expression.
 
     For dialects such as that of PostgreSQL, this operator applies
@@ -223,7 +227,7 @@ def any_(expr):
     return CollectionAggregate._create_any(expr)
 
 
-def asc(column):
+def asc(column: _ColumnExpression[_T]) -> UnaryExpression[_T]:
     """Produce an ascending ``ORDER BY`` clause element.
 
     e.g.::
@@ -261,7 +265,9 @@ def asc(column):
     return UnaryExpression._create_asc(column)
 
 
-def collate(expression, collation):
+def collate(
+    expression: _ColumnExpression[str], collation: str
+) -> BinaryExpression[str]:
     """Return the clause ``expression COLLATE collation``.
 
     e.g.::
@@ -282,7 +288,12 @@ def collate(expression, collation):
     return CollationClause._create_collation_expression(expression, collation)
 
 
-def between(expr, lower_bound, upper_bound, symmetric=False):
+def between(
+    expr: _ColumnExpression[_T],
+    lower_bound: Any,
+    upper_bound: Any,
+    symmetric: bool = False,
+) -> BinaryExpression[bool]:
     """Produce a ``BETWEEN`` predicate clause.
 
     E.g.::
@@ -338,7 +349,9 @@ def between(expr, lower_bound, upper_bound, symmetric=False):
     return expr.between(lower_bound, upper_bound, symmetric=symmetric)
 
 
-def outparam(key, type_=None):
+def outparam(
+    key: str, type_: Optional[TypeEngine[_T]] = None
+) -> BindParameter[_T]:
     """Create an 'OUT' parameter for usage in functions (stored procedures),
     for databases which support them.
 
@@ -352,16 +365,16 @@ def outparam(key, type_=None):
 
 
 @overload
-def not_(clause: "BinaryExpression[_T]") -> "BinaryExpression[_T]":
+def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]:
     ...
 
 
 @overload
-def not_(clause: "ColumnElement[_T]") -> "UnaryExpression[_T]":
+def not_(clause: _ColumnExpression[_T]) -> ColumnElement[_T]:
     ...
 
 
-def not_(clause: "ColumnElement[_T]") -> "ColumnElement[_T]":
+def not_(clause: _ColumnExpression[_T]) -> ColumnElement[_T]:
     """Return a negation of the given clause, i.e. ``NOT(clause)``.
 
     The ``~`` operator is also overloaded on all
@@ -370,29 +383,21 @@ def not_(clause: "ColumnElement[_T]") -> "ColumnElement[_T]":
 
     """
 
-    return operators.inv(
-        _typing_cast(
-            "ColumnElement[_T]",
-            coercions.expect(roles.ExpressionElementRole, clause),
-        )
-    )
+    return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
 
 
 def bindparam(
-    key,
-    value=NO_ARG,
-    type_: Optional[Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"]] = None,
-    unique=False,
-    required=NO_ARG,
-    quote=None,
-    callable_=None,
-    expanding=False,
-    isoutparam=False,
-    literal_execute=False,
-    _compared_to_operator=None,
-    _compared_to_type=None,
-    _is_crud=False,
-) -> "BindParameter[_T]":
+    key: str,
+    value: Any = _NoArg.NO_ARG,
+    type_: Optional[TypeEngine[_T]] = None,
+    unique: bool = False,
+    required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
+    quote: Optional[bool] = None,
+    callable_: Optional[Callable[[], Any]] = None,
+    expanding: bool = False,
+    isoutparam: bool = False,
+    literal_execute: bool = False,
+) -> BindParameter[_T]:
     r"""Produce a "bound expression".
 
     The return value is an instance of :class:`.BindParameter`; this
@@ -636,13 +641,16 @@ def bindparam(
         expanding,
         isoutparam,
         literal_execute,
-        _compared_to_operator,
-        _compared_to_type,
-        _is_crud,
     )
 
 
-def case(*whens, value=None, else_=None) -> "Case[Any]":
+def case(
+    *whens: Union[
+        typing_Tuple[_ColumnExpression[bool], Any], Mapping[Any, Any]
+    ],
+    value: Optional[Any] = None,
+    else_: Optional[Any] = None,
+) -> Case[Any]:
     r"""Produce a ``CASE`` expression.
 
     The ``CASE`` construct in SQL is a conditional object that
@@ -767,9 +775,9 @@ def case(*whens, value=None, else_=None) -> "Case[Any]":
 
 
 def cast(
-    expression: ColumnElement,
-    type_: Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"],
-) -> "Cast[_T]":
+    expression: _ColumnExpression[Any],
+    type_: _TypeEngineArgument[_T],
+) -> Cast[_T]:
     r"""Produce a ``CAST`` expression.
 
     :func:`.cast` returns an instance of :class:`.Cast`.
@@ -826,10 +834,10 @@ def cast(
 
 def column(
     text: str,
-    type_: Optional[Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"]] = None,
+    type_: Optional[_TypeEngineArgument[_T]] = None,
     is_literal: bool = False,
-    _selectable: Optional["FromClause"] = None,
-) -> "ColumnClause[_T]":
+    _selectable: Optional[FromClause] = None,
+) -> ColumnClause[_T]:
     """Produce a :class:`.ColumnClause` object.
 
     The :class:`.ColumnClause` is a lightweight analogue to the
@@ -921,12 +929,10 @@ def column(
         :ref:`sqlexpression_literal_column`
 
     """
-    self = ColumnClause.__new__(ColumnClause)
-    self.__init__(text, type_, is_literal, _selectable)
-    return self
+    return ColumnClause(text, type_, is_literal, _selectable)
 
 
-def desc(column):
+def desc(column: _ColumnExpression[_T]) -> UnaryExpression[_T]:
     """Produce a descending ``ORDER BY`` clause element.
 
     e.g.::
@@ -965,7 +971,7 @@ def desc(column):
     return UnaryExpression._create_desc(column)
 
 
-def distinct(expr):
+def distinct(expr: _ColumnExpression[_T]) -> UnaryExpression[_T]:
     """Produce an column-expression-level unary ``DISTINCT`` clause.
 
     This applies the ``DISTINCT`` keyword to an individual column
@@ -1004,7 +1010,7 @@ def distinct(expr):
     return UnaryExpression._create_distinct(expr)
 
 
-def extract(field: str, expr: ColumnElement) -> "Extract[sqltypes.Integer]":
+def extract(field: str, expr: _ColumnExpression[Any]) -> Extract:
     """Return a :class:`.Extract` construct.
 
     This is typically available as :func:`.extract`
@@ -1045,7 +1051,7 @@ def extract(field: str, expr: ColumnElement) -> "Extract[sqltypes.Integer]":
     return Extract(field, expr)
 
 
-def false():
+def false() -> False_:
     """Return a :class:`.False_` construct.
 
     E.g.::
@@ -1083,7 +1089,9 @@ def false():
     return False_._instance()
 
 
-def funcfilter(func, *criterion) -> "FunctionFilter":
+def funcfilter(
+    func: FunctionElement[_T], *criterion: _ColumnExpression[bool]
+) -> FunctionFilter[_T]:
     """Produce a :class:`.FunctionFilter` object against a function.
 
     Used against aggregate and window functions,
@@ -1114,8 +1122,8 @@ def funcfilter(func, *criterion) -> "FunctionFilter":
 
 def label(
     name: str,
-    element: ColumnElement[_T],
-    type_: Optional[Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"]] = None,
+    element: _ColumnExpression[_T],
+    type_: Optional[_TypeEngineArgument[_T]] = None,
 ) -> "Label[_T]":
     """Return a :class:`Label` object for the
     given :class:`_expression.ColumnElement`.
@@ -1135,13 +1143,13 @@ def label(
     return Label(name, element, type_)
 
 
-def null():
+def null() -> Null:
     """Return a constant :class:`.Null` construct."""
 
     return Null._instance()
 
 
-def nulls_first(column):
+def nulls_first(column: _ColumnExpression[_T]) -> UnaryExpression[_T]:
     """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
 
     :func:`.nulls_first` is intended to modify the expression produced
@@ -1185,7 +1193,7 @@ def nulls_first(column):
     return UnaryExpression._create_nulls_first(column)
 
 
-def nulls_last(column):
+def nulls_last(column: _ColumnExpression[_T]) -> UnaryExpression[_T]:
     """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
 
     :func:`.nulls_last` is intended to modify the expression produced
@@ -1229,7 +1237,7 @@ def nulls_last(column):
     return UnaryExpression._create_nulls_last(column)
 
 
-def or_(*clauses: SQLCoreOperations) -> BooleanClauseList:
+def or_(*clauses: _ColumnExpression[bool]) -> BooleanClauseList:
     """Produce a conjunction of expressions joined by ``OR``.
 
     E.g.::
@@ -1281,12 +1289,16 @@ def or_(*clauses: SQLCoreOperations) -> BooleanClauseList:
 
 
 def over(
-    element: "FunctionElement[_T]",
-    partition_by=None,
-    order_by=None,
-    range_=None,
-    rows=None,
-) -> "Over[_T]":
+    element: FunctionElement[_T],
+    partition_by: Optional[
+        Union[Iterable[_ColumnExpression[Any]], _ColumnExpression[Any]]
+    ] = None,
+    order_by: Optional[
+        Union[Iterable[_ColumnExpression[Any]], _ColumnExpression[Any]]
+    ] = None,
+    range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
+    rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
+) -> Over[_T]:
     r"""Produce an :class:`.Over` object against a function.
 
     Used against aggregate or so-called "window" functions,
@@ -1373,7 +1385,7 @@ def over(
 
 
 @_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
-def text(text):
+def text(text: str) -> TextClause:
     r"""Construct a new :class:`_expression.TextClause` clause,
     representing
     a textual SQL string directly.
@@ -1451,7 +1463,7 @@ def text(text):
     return TextClause(text)
 
 
-def true():
+def true() -> True_:
     """Return a constant :class:`.True_` construct.
 
     E.g.::
@@ -1489,7 +1501,10 @@ def true():
     return True_._instance()
 
 
-def tuple_(*clauses: roles.ExpressionElementRole, types=None) -> "Tuple":
+def tuple_(
+    *clauses: _ColumnExpression[Any],
+    types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
+) -> Tuple:
     """Return a :class:`.Tuple`.
 
     Main usage is to produce a composite IN construct using
@@ -1516,9 +1531,9 @@ def tuple_(*clauses: roles.ExpressionElementRole, types=None) -> "Tuple":
 
 
 def type_coerce(
-    expression: "ColumnElement",
-    type_: Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"],
-) -> "TypeCoerce[_T]":
+    expression: _ColumnExpression[Any],
+    type_: _TypeEngineArgument[_T],
+) -> TypeCoerce[_T]:
     r"""Associate a SQL expression with a particular type, without rendering
     ``CAST``.
 
@@ -1597,8 +1612,8 @@ def type_coerce(
 
 
 def within_group(
-    element: "FunctionElement[_T]", *order_by: roles.OrderByRole
-) -> "WithinGroup[_T]":
+    element: FunctionElement[_T], *order_by: _ColumnExpression[Any]
+) -> WithinGroup[_T]:
     r"""Produce a :class:`.WithinGroup` object against a function.
 
     Used against so-called "ordered set aggregate" and "hypothetical