Use expanding IN for all literal value IN expressions

The "expanding IN" feature, which generates IN expressions at query
execution time which are based on the particular parameters associated with
the statement execution, is now used for all IN expressions made against
lists of literal values.   This allows IN expressions to be fully cacheable
independently of the list of values being passed, and also includes support
for empty lists. For any scenario where the IN expression contains
non-literal SQL expressions, the old behavior of pre-rendering for each
position in the IN is maintained. The change also completes support for
expanding IN with tuples, where previously type-specific bind processors
weren't taking effect.

As part of this change, a more explicit separation between
"literal execute" and "post compile" bound parameters is being made;
as the "ansi bind rules" feature is rendering bound parameters
inline, as we now support "postcompile" generically, these should
be used here, however we have to render literal values at
execution time even for "expanding" parameters.  new test fixtures
etc. are added to assert everything goes to the right place.

Fixes: #4645
Change-Id: Iaa2b7bfbfaaf5b80799ee17c9b8507293cba6ed1

1 files changed, 189 insertions, 33 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 807b01c24..75ccad3fd 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -23,6 +23,7 @@ To generate user-defined SQL strings, see
 
 """
 
+import collections
 import contextlib
 import itertools
 import re
@@ -257,6 +258,18 @@ RM_OBJECTS = 2
 RM_TYPE = 3
 
 
+ExpandedState = collections.namedtuple(
+    "ExpandedState",
+    [
+        "statement",
+        "additional_parameters",
+        "processors",
+        "positiontup",
+        "parameter_expansion",
+    ],
+)
+
+
 class Compiled(object):
 
     """Represent a compiled SQL or DDL expression.
@@ -525,6 +538,12 @@ class SQLCompiler(Compiled):
 
     """
 
+    _render_postcompile = False
+    """
+    whether to render out POSTCOMPILE params during the compile phase.
+
+    """
+
     insert_single_values_expr = None
     """When an INSERT is compiled with a single set of parameters inside
     a VALUES expression, the string is assigned here, where it can be
@@ -535,6 +554,16 @@ class SQLCompiler(Compiled):
     """
 
     literal_execute_params = frozenset()
+    """bindparameter objects that are rendered as literal values at statement
+    execution time.
+
+    """
+
+    post_compile_params = frozenset()
+    """bindparameter objects that are rendered as bound parameter placeholders
+    at statement execution time.
+
+    """
 
     insert_prefetch = update_prefetch = ()
 
@@ -610,6 +639,9 @@ class SQLCompiler(Compiled):
         if self.positional and self._numeric_binds:
             self._apply_numbered_params()
 
+        if self._render_postcompile:
+            self._process_parameters_for_postcompile(_populate_self=True)
+
     @property
     def prefetch(self):
         return list(self.insert_prefetch + self.update_prefetch)
@@ -665,7 +697,12 @@ class SQLCompiler(Compiled):
             for key, value in (
                 (
                     self.bind_names[bindparam],
-                    bindparam.type._cached_bind_processor(self.dialect),
+                    bindparam.type._cached_bind_processor(self.dialect)
+                    if not bindparam._expanding_in_types
+                    else tuple(
+                        elem_type._cached_bind_processor(self.dialect)
+                        for elem_type in bindparam._expanding_in_types
+                    ),
                 )
                 for bindparam in self.bind_names
             )
@@ -741,6 +778,141 @@ class SQLCompiler(Compiled):
         compiled object, for those values that are present."""
         return self.construct_params(_check=False)
 
+    def _process_parameters_for_postcompile(
+        self, parameters=None, _populate_self=False
+    ):
+        """handle special post compile parameters.
+
+        These include:
+
+        * "expanding" parameters -typically IN tuples that are rendered
+          on a per-parameter basis for an otherwise fixed SQL statement string.
+
+        * literal_binds compiled with the literal_execute flag.  Used for
+          things like SQL Server "TOP N" where the driver does not accommodate
+          N as a bound parameter.
+
+        """
+
+        if parameters is None:
+            parameters = self.construct_params()
+
+        expanded_parameters = {}
+        if self.positional:
+            positiontup = []
+        else:
+            positiontup = None
+
+        processors = self._bind_processors
+
+        new_processors = {}
+
+        if self.positional and self._numeric_binds:
+            # I'm not familiar with any DBAPI that uses 'numeric'.
+            # strategy would likely be to make use of numbers greater than
+            # the highest number present; then for expanding parameters,
+            # append them to the end of the parameter list.   that way
+            # we avoid having to renumber all the existing parameters.
+            raise NotImplementedError(
+                "'post-compile' bind parameters are not supported with "
+                "the 'numeric' paramstyle at this time."
+            )
+
+        replacement_expressions = {}
+        to_update_sets = {}
+
+        for name in (
+            self.positiontup if self.positional else self.bind_names.values()
+        ):
+            parameter = self.binds[name]
+            if parameter in self.literal_execute_params:
+                value = parameters.pop(name)
+                replacement_expressions[name] = self.render_literal_bindparam(
+                    parameter, render_literal_value=value
+                )
+                continue
+
+            if parameter in self.post_compile_params:
+                if name in replacement_expressions:
+                    to_update = to_update_sets[name]
+                else:
+                    # we are removing the parameter from parameters
+                    # because it is a list value, which is not expected by
+                    # TypeEngine objects that would otherwise be asked to
+                    # process it. the single name is being replaced with
+                    # individual numbered parameters for each value in the
+                    # param.
+                    values = parameters.pop(name)
+
+                    leep = self._literal_execute_expanding_parameter
+                    to_update, replacement_expr = leep(name, parameter, values)
+
+                    to_update_sets[name] = to_update
+                    replacement_expressions[name] = replacement_expr
+
+                if not parameter.literal_execute:
+                    parameters.update(to_update)
+                    if parameter._expanding_in_types:
+                        new_processors.update(
+                            (
+                                "%s_%s_%s" % (name, i, j),
+                                processors[name][j - 1],
+                            )
+                            for i, tuple_element in enumerate(values, 1)
+                            for j, value in enumerate(tuple_element, 1)
+                            if name in processors
+                            and processors[name][j - 1] is not None
+                        )
+                    else:
+                        new_processors.update(
+                            (key, processors[name])
+                            for key, value in to_update
+                            if name in processors
+                        )
+                    if self.positional:
+                        positiontup.extend(name for name, value in to_update)
+                    expanded_parameters[name] = [
+                        expand_key for expand_key, value in to_update
+                    ]
+            elif self.positional:
+                positiontup.append(name)
+
+        def process_expanding(m):
+            return replacement_expressions[m.group(1)]
+
+        statement = re.sub(
+            r"\[POSTCOMPILE_(\S+)\]", process_expanding, self.string
+        )
+
+        expanded_state = ExpandedState(
+            statement,
+            parameters,
+            new_processors,
+            positiontup,
+            expanded_parameters,
+        )
+
+        if _populate_self:
+            # this is for the "render_postcompile" flag, which is not
+            # otherwise used internally and is for end-user debugging and
+            # special use cases.
+            self.string = expanded_state.statement
+            self._bind_processors.update(expanded_state.processors)
+            self.positiontup = expanded_state.positiontup
+            self.post_compile_params = frozenset()
+            for key in expanded_state.parameter_expansion:
+                bind = self.binds.pop(key)
+                self.bind_names.pop(bind)
+                for value, expanded_key in zip(
+                    bind.value, expanded_state.parameter_expansion[key]
+                ):
+                    self.binds[expanded_key] = new_param = bind._with_value(
+                        value
+                    )
+                    self.bind_names[new_param] = expanded_key
+
+        return expanded_state
+
     @util.dependencies("sqlalchemy.engine.result")
     def _create_result_map(self, result):
         """utility method used for unit tests only."""
@@ -1291,31 +1463,6 @@ class SQLCompiler(Compiled):
             binary, override_operator=operators.match_op
         )
 
-    def _emit_empty_in_warning(self):
-        util.warn(
-            "The IN-predicate was invoked with an "
-            "empty sequence. This results in a "
-            "contradiction, which nonetheless can be "
-            "expensive to evaluate.  Consider alternative "
-            "strategies for improved performance."
-        )
-
-    def visit_empty_in_op_binary(self, binary, operator, **kw):
-        if self.dialect._use_static_in:
-            return "1 != 1"
-        else:
-            if self.dialect._warn_on_empty_in:
-                self._emit_empty_in_warning()
-            return self.process(binary.left != binary.left)
-
-    def visit_empty_notin_op_binary(self, binary, operator, **kw):
-        if self.dialect._use_static_in:
-            return "1 = 1"
-        else:
-            if self.dialect._warn_on_empty_in:
-                self._emit_empty_in_warning()
-            return self.process(binary.left == binary.left)
-
     def visit_empty_set_expr(self, element_types):
         raise NotImplementedError(
             "Dialect '%s' does not support empty set expression."
@@ -1407,7 +1554,7 @@ class SQLCompiler(Compiled):
             and isinstance(binary.left, elements.BindParameter)
             and isinstance(binary.right, elements.BindParameter)
         ):
-            kw["literal_binds"] = True
+            kw["literal_execute"] = True
 
         operator_ = override_operator or binary.operator
         disp = self._get_operator_dispatch(operator_, "binary", None)
@@ -1588,6 +1735,7 @@ class SQLCompiler(Compiled):
         literal_binds=False,
         skip_bind_expression=False,
         literal_execute=False,
+        render_postcompile=False,
         **kwargs
     ):
 
@@ -1605,17 +1753,16 @@ class SQLCompiler(Compiled):
                 )
 
         if not literal_binds:
-            post_compile = (
+            literal_execute = (
                 literal_execute
                 or bindparam.literal_execute
-                or bindparam.expanding
+                or (within_columns_clause and self.ansi_bind_rules)
             )
+            post_compile = literal_execute or bindparam.expanding
         else:
             post_compile = False
 
-        if not literal_execute and (
-            literal_binds or (within_columns_clause and self.ansi_bind_rules)
-        ):
+        if not literal_execute and (literal_binds):
             ret = self.render_literal_bindparam(
                 bindparam, within_columns_clause=True, **kwargs
             )
@@ -1650,7 +1797,13 @@ class SQLCompiler(Compiled):
         self.binds[bindparam.key] = self.binds[name] = bindparam
 
         if post_compile:
-            self.literal_execute_params |= {bindparam}
+            if render_postcompile:
+                self._render_postcompile = True
+
+            if literal_execute:
+                self.literal_execute_params |= {bindparam}
+            else:
+                self.post_compile_params |= {bindparam}
 
         ret = self.bindparam_string(
             name,
@@ -2897,6 +3050,9 @@ class StrSQLCompiler(SQLCompiler):
             for t in extra_froms
         )
 
+    def visit_empty_set_expr(self, type_):
+        return "SELECT 1 WHERE 1!=1"
+
 
 class DDLCompiler(Compiled):
     @util.memoized_property