typing updates

The :meth:`_sql.ColumnOperators.in_` and
:meth:`_sql.ColumnOperators.not_in_` are typed to include
``Iterable[Any]`` rather than ``Sequence[Any]`` for more flexibility in
argument type.

The :func:`_sql.or_` and :func:`_sql.and_` from a typing perspective
require the first argument to be present, however these functions still
accept zero arguments which will emit a deprecation warning at runtime.
Typing is also added to support sending the fixed literal ``False`` for
:func:`_sql.or_` and ``True`` for :func:`_sql.and_` as the first argument
only, however the documentation now indicates sending the
:func:`_sql.false` and :func:`_sql.true` constructs in these cases as a
more explicit approach.

Fixed typing issue where iterating over a :class:`_orm.Query` object
was not correctly typed.

Fixes: #9122
Fixes: #9123
Fixes: #9125
Change-Id: I500e3e1b826717b3dd49afa1e682c3c8279c9226

3 files changed, 83 insertions, 37 deletions

diff --git a/lib/sqlalchemy/sql/_elements_constructors.py b/lib/sqlalchemy/sql/_elements_constructors.py
index d97ede868..9b9632273 100644
--- a/lib/sqlalchemy/sql/_elements_constructors.py
+++ b/lib/sqlalchemy/sql/_elements_constructors.py
@@ -16,6 +16,7 @@ from typing import Optional
 from typing import overload
 from typing import Sequence
 from typing import Tuple as typing_Tuple
+from typing import TYPE_CHECKING
 from typing import TypeVar
 from typing import Union
 
@@ -112,7 +113,10 @@ def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
     return CollectionAggregate._create_all(expr)
 
 
-def and_(*clauses: _ColumnExpressionArgument[bool]) -> ColumnElement[bool]:
+def and_(  # type: ignore[empty-body]
+    initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
+    *clauses: _ColumnExpressionArgument[bool],
+) -> ColumnElement[bool]:
     r"""Produce a conjunction of expressions joined by ``AND``.
 
     E.g.::
@@ -150,13 +154,15 @@ def and_(*clauses: _ColumnExpressionArgument[bool]) -> ColumnElement[bool]:
     argument in order to be valid; a :func:`.and_` construct with no
     arguments is ambiguous.   To produce an "empty" or dynamically
     generated :func:`.and_`  expression, from a given list of expressions,
-    a "default" element of ``True`` should be specified::
+    a "default" element of :func:`_sql.true` (or just ``True``) should be
+    specified::
 
-        criteria = and_(True, *expressions)
+        from sqlalchemy import true
+        criteria = and_(true(), *expressions)
 
     The above expression will compile to SQL as the expression ``true``
     or ``1 = 1``, depending on backend, if no other expressions are
-    present.  If expressions are present, then the ``True`` value is
+    present.  If expressions are present, then the :func:`_sql.true` value is
     ignored as it does not affect the outcome of an AND expression that
     has other elements.
 
@@ -170,7 +176,13 @@ def and_(*clauses: _ColumnExpressionArgument[bool]) -> ColumnElement[bool]:
         :func:`.or_`
 
     """
-    return BooleanClauseList.and_(*clauses)
+    ...
+
+
+if not TYPE_CHECKING:
+    # handle deprecated case which allows zero-arguments
+    def and_(*clauses):  # noqa: F811
+        return BooleanClauseList.and_(*clauses)
 
 
 def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
@@ -1251,7 +1263,10 @@ def nulls_last(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
     return UnaryExpression._create_nulls_last(column)
 
 
-def or_(*clauses: _ColumnExpressionArgument[bool]) -> ColumnElement[bool]:
+def or_(  # type: ignore[empty-body]
+    initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
+    *clauses: _ColumnExpressionArgument[bool],
+) -> ColumnElement[bool]:
     """Produce a conjunction of expressions joined by ``OR``.
 
     E.g.::
@@ -1279,13 +1294,15 @@ def or_(*clauses: _ColumnExpressionArgument[bool]) -> ColumnElement[bool]:
     argument in order to be valid; a :func:`.or_` construct with no
     arguments is ambiguous.   To produce an "empty" or dynamically
     generated :func:`.or_`  expression, from a given list of expressions,
-    a "default" element of ``False`` should be specified::
+    a "default" element of :func:`_sql.false` (or just ``False``) should be
+    specified::
 
-        or_criteria = or_(False, *expressions)
+        from sqlalchemy import false
+        or_criteria = or_(false(), *expressions)
 
     The above expression will compile to SQL as the expression ``false``
     or ``0 = 1``, depending on backend, if no other expressions are
-    present.  If expressions are present, then the ``False`` value is
+    present.  If expressions are present, then the :func:`_sql.false` value is
     ignored as it does not affect the outcome of an OR expression which
     has other elements.
 
@@ -1299,7 +1316,13 @@ def or_(*clauses: _ColumnExpressionArgument[bool]) -> ColumnElement[bool]:
         :func:`.and_`
 
     """
-    return BooleanClauseList.or_(*clauses)
+    ...
+
+
+if not TYPE_CHECKING:
+    # handle deprecated case which allows zero-arguments
+    def or_(*clauses):  # noqa: F811
+        return BooleanClauseList.or_(*clauses)
 
 
 def over(
diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py
index 9fe65b0cd..3b187e49d 100644
--- a/lib/sqlalchemy/sql/coercions.py
+++ b/lib/sqlalchemy/sql/coercions.py
@@ -311,7 +311,7 @@ def expect(
 
 @overload
 def expect(
-    role: Union[Type[roles.JoinTargetRole], Type[roles.OnClauseRole]],
+    role: Type[roles.JoinTargetRole],
     element: _JoinTargetProtocol,
     **kw: Any,
 ) -> _JoinTargetProtocol:
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 6d1949425..981f71964 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -863,7 +863,7 @@ class SQLCoreOperations(Generic[_T], ColumnOperators, TypingOnly):
         def in_(
             self,
             other: Union[
-                Sequence[Any], BindParameter[Any], roles.InElementRole
+                Iterable[Any], BindParameter[Any], roles.InElementRole
             ],
         ) -> BinaryExpression[bool]:
             ...
@@ -871,7 +871,7 @@ class SQLCoreOperations(Generic[_T], ColumnOperators, TypingOnly):
         def not_in(
             self,
             other: Union[
-                Sequence[Any], BindParameter[Any], roles.InElementRole
+                Iterable[Any], BindParameter[Any], roles.InElementRole
             ],
         ) -> BinaryExpression[bool]:
             ...
@@ -2944,16 +2944,39 @@ class BooleanClauseList(ExpressionClauseList[bool]):
         operator: OperatorType,
         continue_on: Any,
         skip_on: Any,
-        *clauses: _ColumnExpressionArgument[Any],
+        initial_clause: Any = _NoArg.NO_ARG,
+        *clauses: Any,
         **kw: Any,
     ) -> ColumnElement[Any]:
+
+        if initial_clause is _NoArg.NO_ARG:
+            # no elements period.  deprecated use case.  return an empty
+            # ClauseList construct that generates nothing unless it has
+            # elements added to it.
+            name = operator.__name__
+
+            util.warn_deprecated(
+                f"Invoking {name}() without arguments is deprecated, and "
+                f"will be disallowed in a future release.   For an empty "
+                f"""{name}() construct, use '{name}({
+                    'true()' if continue_on is True_._singleton else 'false()'
+                }, *args)' """
+                f"""or '{name}({
+                    'True' if continue_on is True_._singleton else 'False'
+                }, *args)'.""",
+                version="1.4",
+            )
+            return cls._construct_raw(operator)  # type: ignore[no-any-return]
+
         lcc, convert_clauses = cls._process_clauses_for_boolean(
             operator,
             continue_on,
             skip_on,
             [
                 coercions.expect(roles.WhereHavingRole, clause)
-                for clause in util.coerce_generator_arg(clauses)
+                for clause in util.coerce_generator_arg(
+                    (initial_clause,) + clauses
+                )
             ],
         )
 
@@ -2969,27 +2992,11 @@ class BooleanClauseList(ExpressionClauseList[bool]):
             )
 
             return cls._construct_raw(operator, flattened_clauses)  # type: ignore # noqa: E501
-        elif lcc == 1:
+        else:
+            assert lcc
             # just one element.  return it as a single boolean element,
             # not a list and discard the operator.
             return convert_clauses[0]  # type: ignore[no-any-return] # noqa: E501
-        else:
-            # no elements period.  deprecated use case.  return an empty
-            # ClauseList construct that generates nothing unless it has
-            # elements added to it.
-            util.warn_deprecated(
-                "Invoking %(name)s() without arguments is deprecated, and "
-                "will be disallowed in a future release.   For an empty "
-                "%(name)s() construct, use %(name)s(%(continue_on)s, *args)."
-                % {
-                    "name": operator.__name__,
-                    "continue_on": "True"
-                    if continue_on is True_._singleton
-                    else "False",
-                },
-                version="1.4",
-            )
-            return cls._construct_raw(operator)  # type: ignore[no-any-return] # noqa: E501
 
     @classmethod
     def _construct_for_whereclause(
@@ -3035,26 +3042,42 @@ class BooleanClauseList(ExpressionClauseList[bool]):
 
     @classmethod
     def and_(
-        cls, *clauses: _ColumnExpressionArgument[bool]
+        cls,
+        initial_clause: Union[
+            Literal[True], _ColumnExpressionArgument[bool], _NoArg
+        ] = _NoArg.NO_ARG,
+        *clauses: _ColumnExpressionArgument[bool],
     ) -> ColumnElement[bool]:
         r"""Produce a conjunction of expressions joined by ``AND``.
 
         See :func:`_sql.and_` for full documentation.
         """
         return cls._construct(
-            operators.and_, True_._singleton, False_._singleton, *clauses
+            operators.and_,
+            True_._singleton,
+            False_._singleton,
+            initial_clause,
+            *clauses,
         )
 
     @classmethod
     def or_(
-        cls, *clauses: _ColumnExpressionArgument[bool]
+        cls,
+        initial_clause: Union[
+            Literal[False], _ColumnExpressionArgument[bool], _NoArg
+        ] = _NoArg.NO_ARG,
+        *clauses: _ColumnExpressionArgument[bool],
     ) -> ColumnElement[bool]:
         """Produce a conjunction of expressions joined by ``OR``.
 
         See :func:`_sql.or_` for full documentation.
         """
         return cls._construct(
-            operators.or_, False_._singleton, True_._singleton, *clauses
+            operators.or_,
+            False_._singleton,
+            True_._singleton,
+            initial_clause,
+            *clauses,
         )
 
     @property