Merge "Add "empty in" strategies; default to "static""

5 files changed, 92 insertions, 17 deletions

diff --git a/lib/sqlalchemy/engine/__init__.py b/lib/sqlalchemy/engine/__init__.py
index 2a6c68d66..bd8b7e68a 100644
--- a/lib/sqlalchemy/engine/__init__.py
+++ b/lib/sqlalchemy/engine/__init__.py
@@ -192,6 +192,24 @@ def create_engine(*args, **kwargs):
         :ref:`dbengine_logging` for information on how to configure logging
         directly.
 
+    :param empty_in_strategy:  The SQL compilation strategy to use when
+        rendering an IN or NOT IN expression for :meth:`.ColumnOperators.in_`
+        where the right-hand side
+        is an empty set.   This is a string value that may be one of
+        ``static``, ``dynamic``, or ``dynamic_warn``.   The ``static``
+        strategy is the default, and an IN comparison to an empty set
+        will generate a simple false expression "1 != 1".   The ``dynamic``
+        strategy behaves like that of SQLAlchemy 1.1 and earlier, emitting
+        a false expression of the form "expr != expr", which has the effect
+        of evaluting to NULL in the case of a null expression.
+        ``dynamic_warn`` is the same as ``dynamic``, however also emits a
+        warning when an empty set is encountered; this because the "dynamic"
+        comparison is typically poorly performing on most databases.
+
+        .. versionadded:: 1.2  Added the ``empty_in_strategy`` setting and
+           additionally defaulted the behavior for empty-set IN comparisons
+           to a static boolean expression.
+
     :param encoding: Defaults to ``utf-8``.  This is the string
         encoding used by SQLAlchemy for string encode/decode
         operations which occur within SQLAlchemy, **outside of
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 18c3276f8..b8c2d2845 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -178,6 +178,7 @@ class DefaultDialect(interfaces.Dialect):
                  supports_right_nested_joins=None,
                  case_sensitive=True,
                  supports_native_boolean=None,
+                 empty_in_strategy='static',
                  label_length=None, **kwargs):
 
         if not getattr(self, 'ported_sqla_06', True):
@@ -207,6 +208,17 @@ 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'")
+
         if label_length and label_length > self.max_identifier_length:
             raise exc.ArgumentError(
                 "Label length of %d is greater than this dialect's"
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index bfa22c206..a1d5a879d 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1003,6 +1003,30 @@ class SQLCompiler(Compiled):
         return "NOT %s" % self.visit_binary(
             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_binary(self, binary, override_operator=None,
                      eager_grouping=False, **kw):
 
diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py
index 7498bbe5d..d409ebacc 100644
--- a/lib/sqlalchemy/sql/default_comparator.py
+++ b/lib/sqlalchemy/sql/default_comparator.py
@@ -146,23 +146,14 @@ def _in_impl(expr, op, seq_or_selectable, negate_op, **kw):
         else:
             o = expr._bind_param(op, o)
         args.append(o)
-    if len(args) == 0:
 
-        # Special case handling for empty IN's, behave like
-        # comparison against zero row selectable.  We use != to
-        # build the contradiction as it handles NULL values
-        # appropriately, i.e. "not (x IN ())" should not return NULL
-        # values for x.
-
-        util.warn('The IN-predicate on "%s" was invoked with an '
-                  'empty sequence. This results in a '
-                  'contradiction, which nonetheless can be '
-                  'expensive to evaluate.  Consider alternative '
-                  'strategies for improved performance.' % expr)
-        if op is operators.in_op:
-            return expr != expr
-        else:
-            return expr == expr
+    if len(args) == 0:
+        op, negate_op = (
+            operators.empty_in_op,
+            operators.empty_notin_op) if op is operators.in_op \
+            else (
+                operators.empty_notin_op,
+                operators.empty_in_op)
 
     return _boolean_compare(expr, op,
                             ClauseList(*args).self_group(against=op),
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 1690d546b..01bee62cf 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -458,6 +458,17 @@ class ColumnOperators(Operators):
         "other" may be a tuple/list of column expressions,
         or a :func:`~.expression.select` construct.
 
+        In the case that ``other`` is an empty sequence, the compiler
+        produces an "empty in" expression.   This defaults to the
+        expression "1 != 1" to produce false in all cases.  The
+        :paramref:`.create_engine.empty_in_strategy` may be used to
+        alter this behavior.
+
+        .. versionchanged:: 1.2  The :meth:`.ColumnOperators.in_` and
+           :meth:`.ColumnOperators.notin_` operators
+           now produce a "static" expression for an empty IN sequence
+           by default.
+
         """
         return self.operate(in_op, other)
 
@@ -467,7 +478,16 @@ class ColumnOperators(Operators):
         This is equivalent to using negation with
         :meth:`.ColumnOperators.in_`, i.e. ``~x.in_(y)``.
 
-        .. versionadded:: 0.8
+        In the case that ``other`` is an empty sequence, the compiler
+        produces an "empty not in" expression.   This defaults to the
+        expression "1 = 1" to produce true in all cases.  The
+        :paramref:`.create_engine.empty_in_strategy` may be used to
+        alter this behavior.
+
+        .. versionchanged:: 1.2  The :meth:`.ColumnOperators.in_` and
+           :meth:`.ColumnOperators.notin_` operators
+           now produce a "static" expression for an empty IN sequence
+           by default.
 
         .. seealso::
 
@@ -957,6 +977,14 @@ def comma_op(a, b):
     raise NotImplementedError()
 
 
+def empty_in_op(a, b):
+    raise NotImplementedError()
+
+
+def empty_notin_op(a, b):
+    raise NotImplementedError()
+
+
 def concat_op(a, b):
     return a.concat(b)
 
@@ -1073,6 +1101,8 @@ _PRECEDENCE = {
     ne: 5,
     is_distinct_from: 5,
     isnot_distinct_from: 5,
+    empty_in_op: 5,
+    empty_notin_op: 5,
     gt: 5,
     lt: 5,
     ge: 5,