repair any_() / all_() "implicit flip" behavior for None

Fixed an inconsistency in the any_() / all_() functions / methods where the
special behavior these functions have of "flipping" the expression such
that the "ANY" / "ALL" expression is always on the right side would not
function if the comparison were against the None value, that is,
"column.any_() == None" should produce the same SQL expression as "null()
== column.any_()". Added more docs to clarify this as well, plus mentions
that any_() / all_() generally supersede the ARRAY version "any()" /
"all()".

Fixes: #7140
Change-Id: Ia5d55414ba40eb3fbda3598931fdd24c9b4a4411

7 files changed, 172 insertions, 74 deletions

diff --git a/doc/build/changelog/unreleased_14/7140.rst b/doc/build/changelog/unreleased_14/7140.rst
new file mode 100644
index 000000000..2c87a9450
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/7140.rst
@@ -0,0 +1,12 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 7140
+
+    Fixed an inconsistency in the any_() / all_() functions / methods where the
+    special behavior these functions have of "flipping" the expression such
+    that the "ANY" / "ALL" expression is always on the right side would not
+    function if the comparison were against the None value, that is,
+    "column.any_() == None" should produce the same SQL expression as "null()
+    == column.any_()". Added more docs to clarify this as well, plus mentions
+    that any_() / all_() generally supersede the ARRAY version "any()" /
+    "all()".
diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py
index 3cb60d3f3..9659d31b9 100644
--- a/lib/sqlalchemy/dialects/postgresql/array.py
+++ b/lib/sqlalchemy/dialects/postgresql/array.py
@@ -16,13 +16,8 @@ from ...sql import roles
 
 
 def Any(other, arrexpr, operator=operators.eq):
-    """A synonym for the :meth:`.ARRAY.Comparator.any` method.
-
-    This method is legacy and is here for backwards-compatibility.
-
-    .. seealso::
-
-        :func:`_expression.any_`
+    """A synonym for the ARRAY-level :meth:`.ARRAY.Comparator.any` method.
+    See that method for details.
 
     """
 
@@ -30,13 +25,8 @@ def Any(other, arrexpr, operator=operators.eq):
 
 
 def All(other, arrexpr, operator=operators.eq):
-    """A synonym for the :meth:`.ARRAY.Comparator.all` method.
-
-    This method is legacy and is here for backwards-compatibility.
-
-    .. seealso::
-
-        :func:`_expression.all_`
+    """A synonym for the ARRAY-level :meth:`.ARRAY.Comparator.all` method.
+    See that method for details.
 
     """
 
diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py
index 557095d04..036a96e9f 100644
--- a/lib/sqlalchemy/sql/default_comparator.py
+++ b/lib/sqlalchemy/sql/default_comparator.py
@@ -34,6 +34,7 @@ def _boolean_compare(
     negate=None,
     reverse=False,
     _python_is_types=(util.NoneType, bool),
+    _any_all_expr=False,
     result_type=None,
     **kwargs
 ):
@@ -42,7 +43,6 @@ def _boolean_compare(
         result_type = type_api.BOOLEANTYPE
 
     if isinstance(obj, _python_is_types + (Null, True_, False_)):
-
         # allow x ==/!= True/False to be treated as a literal.
         # this comes out to "== / != true/false" or "1/0" if those
         # constants aren't supported and works on all platforms
@@ -69,8 +69,12 @@ def _boolean_compare(
                 negate=negate,
                 modifiers=kwargs,
             )
+        elif _any_all_expr:
+            obj = coercions.expect(
+                roles.ConstExprRole, element=obj, operator=op, expr=expr
+            )
         else:
-            # all other None/True/False uses IS, IS NOT
+            # all other None uses IS, IS NOT
             if op in (operators.eq, operators.is_):
                 return BinaryExpression(
                     expr,
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 04565443d..8f02527b9 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -3658,26 +3658,52 @@ class CollectionAggregate(UnaryExpression):
     def _create_any(cls, expr):
         """Produce an ANY expression.
 
-        This may apply to an array type for some dialects (e.g. postgresql),
-        or to a subquery for others (e.g. mysql).  e.g.::
+        For dialects such as that of PostgreSQL, this operator applies
+        to usage of the :class:`_types.ARRAY` datatype, for that of
+        MySQL, it may apply to a subquery.  e.g.::
 
-            # postgresql '5 = ANY (somearray)'
+            # renders on PostgreSQL:
+            # '5 = ANY (somearray)'
             expr = 5 == any_(mytable.c.somearray)
 
-            # mysql '5 = ANY (SELECT value FROM table)'
+            # renders on MySQL:
+            # '5 = ANY (SELECT value FROM table)'
             expr = 5 == any_(select(table.c.value))
 
-        The operator is more conveniently available from any
-        :class:`_sql.ColumnElement` object that makes use of the
-        :class:`_types.ARRAY` datatype::
+        Comparison to NULL may work using ``None`` or :func:`_sql.null`::
 
-            expr = mytable.c.somearray.any(5)
+            None == any_(mytable.c.somearray)
+
+        The any_() / all_() operators also feature a special "operand flipping"
+        behavior such that if any_() / all_() are used on the left side of a
+        comparison using a standalone operator such as ``==``, ``!=``, etc.
+        (not including operator methods such as
+        :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
+
+            # would render '5 = ANY (column)`
+            any_(mytable.c.column) == 5
+
+        Or with ``None``, which note will not perform
+        the usual step of rendering "IS" as is normally the case for NULL::
+
+            # would render 'NULL = ANY(somearray)'
+            any_(mytable.c.somearray) == None
+
+        .. versionchanged:: 1.4.26  repaired the use of any_() / all_()
+           comparing to NULL on the right side to be flipped to the left.
+
+        The column-level :meth:`_sql.ColumnElement.any_` method (not to be
+        confused with :class:`_types.ARRAY` level
+        :meth:`_types.ARRAY.Comparator.any`) is shorthand for
+        ``any_(col)``::
+
+            5 = mytable.c.somearray.any_()
 
         .. seealso::
 
-            :func:`_expression.all_`
+            :meth:`_sql.ColumnOperators.any_`
 
-            :meth:`_types.ARRAY.any`
+            :func:`_expression.all_`
 
         """
 
@@ -3695,29 +3721,54 @@ class CollectionAggregate(UnaryExpression):
     def _create_all(cls, expr):
         """Produce an ALL expression.
 
-        This may apply to an array type for some dialects (e.g. postgresql),
-        or to a subquery for others (e.g. mysql).  e.g.::
+        For dialects such as that of PostgreSQL, this operator applies
+        to usage of the :class:`_types.ARRAY` datatype, for that of
+        MySQL, it may apply to a subquery.  e.g.::
 
-            # postgresql '5 = ALL (somearray)'
+            # renders on PostgreSQL:
+            # '5 = ALL (somearray)'
             expr = 5 == all_(mytable.c.somearray)
 
-            # mysql '5 = ALL (SELECT value FROM table)'
+            # renders on MySQL:
+            # '5 = ALL (SELECT value FROM table)'
             expr = 5 == all_(select(table.c.value))
 
-        The operator is more conveniently available from any
-        :class:`_sql.ColumnElement` object that makes use of the
-        :class:`_types.ARRAY` datatype::
+        Comparison to NULL may work using ``None``::
+
+            None == all_(mytable.c.somearray)
 
-            expr = mytable.c.somearray.all(5)
+        The any_() / all_() operators also feature a special "operand flipping"
+        behavior such that if any_() / all_() are used on the left side of a
+        comparison using a standalone operator such as ``==``, ``!=``, etc.
+        (not including operator methods such as
+        :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
+
+            # would render '5 = ALL (column)`
+            all_(mytable.c.column) == 5
+
+        Or with ``None``, which note will not perform
+        the usual step of rendering "IS" as is normally the case for NULL::
+
+            # would render 'NULL = ALL(somearray)'
+            all_(mytable.c.somearray) == None
+
+        .. versionchanged:: 1.4.26  repaired the use of any_() / all_()
+           comparing to NULL on the right side to be flipped to the left.
+
+        The column-level :meth:`_sql.ColumnElement.all_` method (not to be
+        confused with :class:`_types.ARRAY` level
+        :meth:`_types.ARRAY.Comparator.all`) is shorthand for
+        ``all_(col)``::
+
+            5 == mytable.c.somearray.all_()
 
         .. seealso::
 
-            :func:`_expression.any_`
+            :meth:`_sql.ColumnOperators.all_`
 
-            :meth:`_types.ARRAY.Comparator.all`
+            :func:`_expression.any_`
 
         """
-
         expr = coercions.expect(roles.ExpressionElementRole, expr)
         expr = expr.self_group()
         return CollectionAggregate(
@@ -3735,7 +3786,7 @@ class CollectionAggregate(UnaryExpression):
             raise exc.ArgumentError(
                 "Only comparison operators may be used with ANY/ALL"
             )
-        kwargs["reverse"] = True
+        kwargs["reverse"] = kwargs["_any_all_expr"] = True
         return self.comparator.operate(operators.mirror(op), *other, **kwargs)
 
     def reverse_operate(self, op, other, **kwargs):
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 771de1e47..695e086b8 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -1161,24 +1161,16 @@ class ColumnOperators(Operators):
         return self.operate(distinct_op)
 
     def any_(self):
-        """Produce a :func:`_expression.any_` clause against the
+        """Produce an :func:`_expression.any_` clause against the
         parent object.
 
-        This operator is only appropriate against a scalar subquery
-        object, or for some backends an column expression that is
-        against the ARRAY type, e.g.::
+        See the documentation for :func:`_sql.any_` for examples.
 
-            # postgresql '5 = ANY (somearray)'
-            expr = 5 == mytable.c.somearray.any_()
-
-            # mysql '5 = ANY (SELECT value FROM table)'
-            expr = 5 == select(table.c.value).scalar_subquery().any_()
-
-        .. seealso::
-
-            :func:`_expression.any_` - standalone version
-
-            :func:`_expression.all_` - ALL operator
+        .. note:: be sure to not confuse the newer
+            :meth:`_sql.ColumnOperators.any_` method with its older
+            :class:`_types.ARRAY`-specific counterpart, the
+            :meth:`_types.ARRAY.Comparator.any` method, which a different
+            calling syntax and usage pattern.
 
         .. versionadded:: 1.1
 
@@ -1186,24 +1178,17 @@ class ColumnOperators(Operators):
         return self.operate(any_op)
 
     def all_(self):
-        """Produce a :func:`_expression.all_` clause against the
+        """Produce an :func:`_expression.all_` clause against the
         parent object.
 
-        This operator is only appropriate against a scalar subquery
-        object, or for some backends an column expression that is
-        against the ARRAY type, e.g.::
-
-            # postgresql '5 = ALL (somearray)'
-            expr = 5 == mytable.c.somearray.all_()
-
-            # mysql '5 = ALL (SELECT value FROM table)'
-            expr = 5 == select(table.c.value).scalar_subquery().all_()
-
-        .. seealso::
+        See the documentation for :func:`_sql.all_` for examples.
 
-            :func:`_expression.all_` - standalone version
+        .. note:: be sure to not confuse the newer
+            :meth:`_sql.ColumnOperators.all_` method with its older
+            :class:`_types.ARRAY`-specific counterpart, the
+            :meth:`_types.ARRAY.Comparator.all` method, which a different
+            calling syntax and usage pattern.
 
-            :func:`_expression.any_` - ANY operator
 
         .. versionadded:: 1.1
 
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 0341c931d..ae589d648 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -2801,10 +2801,14 @@ class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine):
         def any(self, other, operator=None):
             """Return ``other operator ANY (array)`` clause.
 
-            Argument places are switched, because ANY requires array
-            expression to be on the right hand-side.
+            .. note:: This method is an :class:`_types.ARRAY` - specific
+                construct that is now superseded by the :func:`_sql.any_`
+                function, which features a different calling style. The
+                :func:`_sql.any_` function is also mirrored at the method level
+                via the :meth:`_sql.ColumnOperators.any_` method.
 
-            E.g.::
+            Usage of array-specific :meth:`_types.ARRAY.Comparator.any`
+            is as follows::
 
                 from sqlalchemy.sql import operators
 
@@ -2841,10 +2845,14 @@ class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine):
         def all(self, other, operator=None):
             """Return ``other operator ALL (array)`` clause.
 
-            Argument places are switched, because ALL requires array
-            expression to be on the right hand-side.
+            .. note:: This method is an :class:`_types.ARRAY` - specific
+                construct that is now superseded by the :func:`_sql.any_`
+                function, which features a different calling style. The
+                :func:`_sql.any_` function is also mirrored at the method level
+                via the :meth:`_sql.ColumnOperators.any_` method.
 
-            E.g.::
+            Usage of array-specific :meth:`_types.ARRAY.Comparator.all`
+            is as follows::
 
                 from sqlalchemy.sql import operators
 
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index b2ba7ae73..79aa4d794 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -76,7 +76,11 @@ class LoopOperate(operators.ColumnOperators):
         return op
 
 
-class DefaultColumnComparatorTest(fixtures.TestBase):
+class DefaultColumnComparatorTest(
+    testing.AssertsCompiledSQL, fixtures.TestBase
+):
+    dialect = "default_enhanced"
+
     @testing.combinations((operators.desc_op, desc), (operators.asc_op, asc))
     def test_scalar(self, operator, compare_to):
         left = column("left")
@@ -159,6 +163,32 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
         loop = LoopOperate()
         is_(operator(loop, *arg), operator)
 
+    def test_null_true_false_is_sanity_checks(self):
+
+        d = default.DefaultDialect()
+        d.supports_native_boolean = True
+
+        self.assert_compile(
+            column("q") == None,
+            "q IS NULL",
+        )
+        self.assert_compile(
+            column("q") == null(),
+            "q IS NULL",
+        )
+        # IS coercion only occurs from left to right (just discovered this)
+        self.assert_compile(
+            null() == column("q"),
+            "NULL = q",
+        )
+        self.assert_compile(column("q") == true(), "q = true", dialect=d)
+        self.assert_compile(true() == column("q"), "true = q", dialect=d)
+        self.assert_compile(column("q") == True, "q = true", dialect=d)
+
+        # this comes out reversed; no choice, column.__eq__() is called
+        # and we don't get to know it's "reverse"
+        self.assert_compile(True == column("q"), "q = true", dialect=d)
+
     def test_no_getitem(self):
         assert_raises_message(
             NotImplementedError,
@@ -3392,6 +3422,24 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         )
         return t
 
+    @testing.combinations(
+        lambda col: any_(col) == None,
+        lambda col: col.any_() == None,
+        lambda col: any_(col) == null(),
+        lambda col: col.any_() == null(),
+        lambda col: null() == any_(col),
+        lambda col: null() == col.any_(),
+        lambda col: None == any_(col),
+        lambda col: None == col.any_(),
+        argnames="expr",
+    )
+    @testing.combinations("int", "array", argnames="datatype")
+    def test_any_generic_null(self, datatype, expr, t_fixture):
+
+        col = t_fixture.c.data if datatype == "int" else t_fixture.c.arrval
+
+        self.assert_compile(expr(col), "NULL = ANY (tab1.%s)" % col.name)
+
     def test_any_array(self, t_fixture):
         t = t_fixture