Parenthesize for empty not in

Fixed regression caused by the "empty in" change just made in
:ticket:`6397` 1.4.12 where the expression needs to be parenthesized for
the "not in" use case, otherwise the condition will interfere with the
other filtering criteria.

also amends StrSQLCompiler to use the newer "empty IN" style for
its compilation process.

Fixes: #6428
Change-Id: I182a552fc0d3065a9e38c0f4ece2deb143735c36

2 files changed, 29 insertions, 7 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 57ffdf86b..437a11a60 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1908,7 +1908,9 @@ class SQLCompiler(Compiled):
         return self._render_in_expr_w_bindparam(binary, operator, **kw)
 
     def visit_not_in_op_binary(self, binary, operator, **kw):
-        return self._render_in_expr_w_bindparam(binary, operator, **kw)
+        return "(%s)" % self._render_in_expr_w_bindparam(
+            binary, operator, **kw
+        )
 
     def _render_in_expr_w_bindparam(self, binary, operator, **kw):
         opstring = OPERATORS[operator]
@@ -1957,10 +1959,13 @@ class SQLCompiler(Compiled):
             if parameter.type._is_tuple_type:
                 replacement_expression = (
                     "VALUES " if self.dialect.tuple_in_values else ""
-                ) + self.visit_empty_set_expr(parameter.type.types)
+                ) + self.visit_empty_set_op_expr(
+                    parameter.type.types, parameter.expand_op
+                )
+
             else:
-                replacement_expression = self.visit_empty_set_expr(
-                    [parameter.type]
+                replacement_expression = self.visit_empty_set_op_expr(
+                    [parameter.type], parameter.expand_op
                 )
 
         elif isinstance(values[0], (tuple, list)):
@@ -3939,9 +3944,6 @@ class StrSQLCompiler(SQLCompiler):
             for t in extra_froms
         )
 
-    def visit_empty_set_op_expr(self, type_, expand_op):
-        return self.visit_empty_set_expr(type_)
-
     def visit_empty_set_expr(self, type_):
         return "SELECT 1 WHERE 1!=1"
 
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index 8133c2105..1605033a0 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -1110,6 +1110,26 @@ class ExpandingBoundInTest(fixtures.TablesTest):
         )
         self._assert_result(stmt, [(2,), (3,), (4,)])
 
+    def test_nonempty_in_plus_empty_notin(self):
+        table = self.tables.some_table
+        stmt = (
+            select(table.c.id)
+            .where(table.c.x.in_([2, 3]))
+            .where(table.c.id.not_in([]))
+            .order_by(table.c.id)
+        )
+        self._assert_result(stmt, [(2,), (3,)])
+
+    def test_empty_in_plus_notempty_notin(self):
+        table = self.tables.some_table
+        stmt = (
+            select(table.c.id)
+            .where(table.c.x.in_([]))
+            .where(table.c.id.not_in([2, 3]))
+            .order_by(table.c.id)
+        )
+        self._assert_result(stmt, [])
+
     @testing.requires.tuple_in
     def test_bound_in_two_tuple_bindparam(self):
         table = self.tables.some_table