Deliver straight BinaryExpr w/ no negate for any() / all()

Adjusted the :meth:`_types.ARRAY.Comparator.any` and
:meth:`_types.ARRAY.Comparator.all` methods to implement a straight "NOT"
operation for negation, rather than negating the comparison operator.

Fixes: #5518
Change-Id: I87ee9278c321aafe51a679fcfcbb5fbb11307fda

4 files changed, 56 insertions, 2 deletions

diff --git a/doc/build/changelog/unreleased_13/5518.rst b/doc/build/changelog/unreleased_13/5518.rst
new file mode 100644
index 000000000..1cd2a259f
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/5518.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: bug, postgresql
+    :tickets: 5518
+
+    Adjusted the :meth:`_types.ARRAY.Comparator.any` and
+    :meth:`_types.ARRAY.Comparator.all` methods to implement a straight "NOT"
+    operation for negation, rather than negating the comparison operator.
\ No newline at end of file
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 14ddedaec..fd85d6d30 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -2699,9 +2699,13 @@ class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine):
             """
             elements = util.preloaded.sql_elements
             operator = operator if operator else operators.eq
-            return operator(
+
+            # send plain BinaryExpression so that negate remains at None,
+            # leading to NOT expr for negation.
+            return elements.BinaryExpression(
                 coercions.expect(roles.ExpressionElementRole, other),
                 elements.CollectionAggregate._create_any(self.expr),
+                operator,
             )
 
         @util.preload_module("sqlalchemy.sql.elements")
@@ -2735,9 +2739,13 @@ class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine):
             """
             elements = util.preloaded.sql_elements
             operator = operator if operator else operators.eq
-            return operator(
+
+            # send plain BinaryExpression so that negate remains at None,
+            # leading to NOT expr for negation.
+            return elements.BinaryExpression(
                 coercions.expect(roles.ExpressionElementRole, other),
                 elements.CollectionAggregate._create_all(self.expr),
+                operator,
             )
 
     comparator_factory = Comparator
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 20732067d..ce285007f 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1234,6 +1234,27 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "%(param_1)s = ANY (x)",
             checkparams={"param_1": 4},
         )
+
+        self.assert_compile(
+            c.any(5), "%(param_1)s = ANY (x)", checkparams={"param_1": 5},
+        )
+
+        self.assert_compile(
+            ~c.any(5),
+            "NOT (%(param_1)s = ANY (x))",
+            checkparams={"param_1": 5},
+        )
+
+        self.assert_compile(
+            c.all(5), "%(param_1)s = ALL (x)", checkparams={"param_1": 5},
+        )
+
+        self.assert_compile(
+            ~c.all(5),
+            "NOT (%(param_1)s = ALL (x))",
+            checkparams={"param_1": 5},
+        )
+
         self.assert_compile(
             c.any(5, operator=operators.ne),
             "%(param_1)s != ANY (x)",
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index e5835a749..fcf40ebbd 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -3045,6 +3045,15 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             checkparams={"param_1": 5},
         )
 
+    def test_any_array_comparator_negate_accessor(self, t_fixture):
+        t = t_fixture
+
+        self.assert_compile(
+            ~t.c.arrval.any(5, operator.gt),
+            "NOT (:param_1 > ANY (tab1.arrval))",
+            checkparams={"param_1": 5},
+        )
+
     def test_all_array_comparator_accessor(self, t_fixture):
         t = t_fixture
 
@@ -3054,6 +3063,15 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             checkparams={"param_1": 5},
         )
 
+    def test_all_array_comparator_negate_accessor(self, t_fixture):
+        t = t_fixture
+
+        self.assert_compile(
+            ~t.c.arrval.all(5, operator.gt),
+            "NOT (:param_1 > ALL (tab1.arrval))",
+            checkparams={"param_1": 5},
+        )
+
     def test_any_array_expression(self, t_fixture):
         t = t_fixture