From aba308868544b21bafa0b3435701ddc908654b0a Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 28 Apr 2021 18:31:51 -0400 Subject: Use non-subquery form for empty IN Revised the "EMPTY IN" expression to no longer rely upon using a subquery, as this was causing some compatibility and performance problems. The new approach for selected databases takes advantage of using a NULL-returning IN expression combined with the usual "1 != 1" or "1 = 1" expression appended by AND or OR. The expression is now the default for all backends other than SQLite, which still had some compatibility issues regarding tuple "IN" for older SQLite versions. Third party dialects can still override how the "empty set" expression renders by implementing a new compiler method ``def visit_empty_set_op_expr(self, type_, expand_op)``, which takes precedence over the existing ``def visit_empty_set_expr(self, element_types)`` which remains in place. Fixes: #6258 Fixes: #6397 Change-Id: I2df09eb00d2ad3b57039ae48128fdf94641b5e59 --- lib/sqlalchemy/sql/coercions.py | 11 +++------ lib/sqlalchemy/sql/compiler.py | 50 +++++++++++++++++++++++++++++++++++++---- lib/sqlalchemy/sql/elements.py | 10 +++++++++ 3 files changed, 59 insertions(+), 12 deletions(-) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py index b7aba9d74..820fc1bf1 100644 --- a/lib/sqlalchemy/sql/coercions.py +++ b/lib/sqlalchemy/sql/coercions.py @@ -561,14 +561,9 @@ class InElementImpl(RoleImpl): return element.self_group(against=operator) elif isinstance(element, elements.BindParameter): - if not element.expanding: - # coercing to expanding at the moment to work with the - # lambda system. not sure if this is the right approach. - # is there a valid use case to send a single non-expanding - # param to IN? check for ARRAY type? - element = element._clone(maintain_key=True) - element.expanding = True - + # previously we were adding expanding flags here but + # we now do this in the compiler where we have more context + # see compiler.py -> _render_in_expr_w_bindparam return element else: return element diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 6168248ff..e9e05b7e9 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1903,6 +1903,45 @@ class SQLCompiler(Compiled): binary, override_operator=operators.match_op ) + def visit_in_op_binary(self, binary, operator, **kw): + 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) + + def _render_in_expr_w_bindparam(self, binary, operator, **kw): + opstring = OPERATORS[operator] + + if isinstance(binary.right, elements.BindParameter): + if not binary.right.expanding or not binary.right.expand_op: + # note that by cloning here, we rely upon the + # _cache_key_bind_match dictionary to resolve + # clones of bindparam() objects to the ones that are + # present in our cache key. + binary.right = binary.right._clone(maintain_key=True) + binary.right.expanding = True + binary.right.expand_op = operator + + return self._generate_generic_binary(binary, opstring, **kw) + + def visit_empty_set_op_expr(self, type_, expand_op): + if expand_op is operators.not_in_op: + if len(type_) > 1: + return "(%s)) OR (1 = 1" % ( + ", ".join("NULL" for element in type_) + ) + else: + return "NULL) OR (1 = 1" + elif expand_op is operators.in_op: + if len(type_) > 1: + return "(%s)) AND (1 != 1" % ( + ", ".join("NULL" for element in type_) + ) + else: + return "NULL) AND (1 != 1" + else: + return self.visit_empty_set_expr(type_) + def visit_empty_set_expr(self, element_types): raise NotImplementedError( "Dialect '%s' does not support empty set expression." @@ -1959,12 +1998,12 @@ class SQLCompiler(Compiled): to_update = [] if parameter.type._is_tuple_type: - replacement_expression = self.visit_empty_set_expr( - parameter.type.types + replacement_expression = 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)): @@ -3900,6 +3939,9 @@ 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/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 696f3b249..e27b97802 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -1411,7 +1411,17 @@ class BindParameter(roles.InElementRole, ColumnElement): self.callable = callable_ self.isoutparam = isoutparam self.required = required + + # indicate an "expanding" parameter; the compiler sets this + # automatically in the compiler _render_in_expr_w_bindparam method + # for an IN expression self.expanding = expanding + + # this is another hint to help w/ expanding and is typically + # set in the compiler _render_in_expr_w_bindparam method for an + # IN expression + self.expand_op = None + self.literal_execute = literal_execute if _is_crud: self._is_crud = True -- cgit v1.2.1