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, 51 insertions, 135 deletions

diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 9eacf0527..7016adcf4 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -192,7 +192,16 @@ class DefaultDialect(interfaces.Dialect):
             "and corresponding dialect-level parameters are deprecated, "
             "and will be removed in a future release.  Modern DBAPIs support "
             "Python Unicode natively and this parameter is unnecessary.",
-        )
+        ),
+        empty_in_strategy=(
+            "1.4",
+            "The :paramref:`.create_engine.empty_in_strategy` keyword is "
+            "deprecated, and no longer has any effect.  All IN expressions "
+            "are now rendered using "
+            'the "expanding parameter" strategy which renders a set of bound'
+            'expressions, or an "empty set" SELECT, at statement execution'
+            "time.",
+        ),
     )
     def __init__(
         self,
@@ -203,7 +212,6 @@ class DefaultDialect(interfaces.Dialect):
         implicit_returning=None,
         case_sensitive=True,
         supports_native_boolean=None,
-        empty_in_strategy="static",
         max_identifier_length=None,
         label_length=None,
         **kwargs
@@ -235,18 +243,6 @@ class DefaultDialect(interfaces.Dialect):
             self.supports_native_boolean = supports_native_boolean
         self.case_sensitive = case_sensitive
 
-        self.empty_in_strategy = empty_in_strategy
-        if empty_in_strategy == "static":
-            self._use_static_in = True
-        elif empty_in_strategy in ("dynamic", "dynamic_warn"):
-            self._use_static_in = False
-            self._warn_on_empty_in = empty_in_strategy == "dynamic_warn"
-        else:
-            raise exc.ArgumentError(
-                "empty_in_strategy may be 'static', "
-                "'dynamic', or 'dynamic_warn'"
-            )
-
         self._user_defined_max_identifier_length = max_identifier_length
         if self._user_defined_max_identifier_length:
             self.max_identifier_length = (
@@ -732,19 +728,18 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
             compiled._loose_column_name_matching,
         )
 
-        self.unicode_statement = util.text_type(compiled)
-        if not dialect.supports_unicode_statements:
-            self.statement = self.unicode_statement.encode(
-                self.dialect.encoding
-            )
-        else:
-            self.statement = self.unicode_statement
-
         self.isinsert = compiled.isinsert
         self.isupdate = compiled.isupdate
         self.isdelete = compiled.isdelete
         self.is_text = compiled.isplaintext
 
+        if self.isinsert or self.isupdate or self.isdelete:
+            self.is_crud = True
+            self._is_explicit_returning = bool(compiled.statement._returning)
+            self._is_implicit_returning = bool(
+                compiled.returning and not compiled.statement._returning
+            )
+
         if not parameters:
             self.compiled_parameters = [compiled.construct_params()]
         else:
@@ -755,14 +750,11 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
 
             self.executemany = len(parameters) > 1
 
-        self.cursor = self.create_cursor()
+        # this must occur before create_cursor() since the statement
+        # has to be regexed in some cases for server side cursor
+        self.unicode_statement = util.text_type(compiled)
 
-        if self.isinsert or self.isupdate or self.isdelete:
-            self.is_crud = True
-            self._is_explicit_returning = bool(compiled.statement._returning)
-            self._is_implicit_returning = bool(
-                compiled.returning and not compiled.statement._returning
-            )
+        self.cursor = self.create_cursor()
 
         if self.compiled.insert_prefetch or self.compiled.update_prefetch:
             if self.executemany:
@@ -772,15 +764,38 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
 
         processors = compiled._bind_processors
 
-        if compiled.literal_execute_params:
-            # copy processors for this case as they will be mutated
-            processors = dict(processors)
-            positiontup = self._literal_execute_parameters(
-                compiled, processors
+        if compiled.literal_execute_params or compiled.post_compile_params:
+            if self.executemany:
+                raise exc.InvalidRequestError(
+                    "'literal_execute' or 'expanding' parameters can't be "
+                    "used with executemany()"
+                )
+
+            expanded_state = compiled._process_parameters_for_postcompile(
+                self.compiled_parameters[0]
             )
+
+            # re-assign self.unicode_statement
+            self.unicode_statement = expanded_state.statement
+
+            # used by set_input_sizes() which is needed for Oracle
+            self._expanded_parameters = expanded_state.parameter_expansion
+
+            processors = dict(processors)
+            processors.update(expanded_state.processors)
+            positiontup = expanded_state.positiontup
         elif compiled.positional:
             positiontup = self.compiled.positiontup
 
+        # final self.unicode_statement is now assigned, encode if needed
+        # by dialect
+        if not dialect.supports_unicode_statements:
+            self.statement = self.unicode_statement.encode(
+                self.dialect.encoding
+            )
+        else:
+            self.statement = self.unicode_statement
+
         # Convert the dictionary of bind parameter values
         # into a dict or list to be sent to the DBAPI's
         # execute() or executemany() method.
@@ -825,105 +840,6 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
 
         return self
 
-    def _literal_execute_parameters(self, compiled, processors):
-        """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 self.executemany:
-            raise exc.InvalidRequestError(
-                "'literal_execute' or 'expanding' parameters can't be "
-                "used with executemany()"
-            )
-
-        if compiled.positional and compiled._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."
-            )
-
-        self._expanded_parameters = {}
-
-        compiled_params = self.compiled_parameters[0]
-        if compiled.positional:
-            positiontup = []
-        else:
-            positiontup = None
-
-        replacement_expressions = {}
-        to_update_sets = {}
-
-        for name in (
-            compiled.positiontup
-            if compiled.positional
-            else compiled.bind_names.values()
-        ):
-            parameter = compiled.binds[name]
-            if parameter in compiled.literal_execute_params:
-
-                if not parameter.expanding:
-                    value = compiled_params.pop(name)
-                    replacement_expressions[
-                        name
-                    ] = compiled.render_literal_bindparam(
-                        parameter, render_literal_value=value
-                    )
-                    continue
-
-                if name in replacement_expressions:
-                    to_update = to_update_sets[name]
-                else:
-                    # we are removing the parameter from compiled_params
-                    # 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 = compiled_params.pop(name)
-
-                    leep = compiled._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:
-                    compiled_params.update(to_update)
-
-                    processors.update(
-                        (key, processors[name])
-                        for key, value in to_update
-                        if name in processors
-                    )
-                    if compiled.positional:
-                        positiontup.extend(name for name, value in to_update)
-                    self._expanded_parameters[name] = [
-                        expand_key for expand_key, value in to_update
-                    ]
-            elif compiled.positional:
-                positiontup.append(name)
-
-        def process_expanding(m):
-            return replacement_expressions[m.group(1)]
-
-        self.statement = re.sub(
-            r"\[POSTCOMPILE_(\S+)\]", process_expanding, self.statement
-        )
-        return positiontup
-
     @classmethod
     def _init_statement(
         cls, dialect, connection, dbapi_connection, statement, parameters
@@ -1084,8 +1000,8 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
                                 self.compiled.statement, expression.TextClause
                             )
                         )
-                        and self.statement
-                        and SERVER_SIDE_CURSOR_RE.match(self.statement)
+                        and self.unicode_statement
+                        and SERVER_SIDE_CURSOR_RE.match(self.unicode_statement)
                     )
                 )
             )