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

1 files changed, 9 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"