Add support of empty list in exanding of bindparam

Added new logic to the "expanding IN" bound parameter feature whereby if
the given list is empty, a special "empty set" expression that is specific
to different backends is generated, thus allowing IN expressions to be
fully dynamic including empty IN expressions.

Fixes: #4271
Change-Id: Icc3c73bbd6005206b9d06baaeb14a097af5edd36
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/432

8 files changed, 127 insertions, 11 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index c10b75c02..ec267880c 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1532,6 +1532,9 @@ class MSSQLCompiler(compiler.SQLCompiler):
                                  fromhints=from_hints, **kw)
             for t in [from_table] + extra_froms)
 
+    def visit_empty_set_expr(self, type_):
+        return 'SELECT 1 WHERE 1!=1'
+
 
 class MSSQLStrictCompiler(MSSQLCompiler):
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 3aede4a05..2bb5b5beb 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1114,6 +1114,9 @@ class MySQLCompiler(compiler.SQLCompiler):
                                  fromhints=from_hints, **kw)
             for t in [from_table] + extra_froms)
 
+    def visit_empty_set_expr(self, type_):
+        return 'SELECT 1 FROM (SELECT 1) as _empty_set WHERE 1!=1'
+
 
 class MySQLDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kw):
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 356c2a2bf..76ae1ced6 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -909,6 +909,9 @@ class OracleCompiler(compiler.SQLCompiler):
     def limit_clause(self, select, **kw):
         return ""
 
+    def visit_empty_set_expr(self, type_):
+        return 'SELECT 1 FROM DUAL WHERE 1!=1'
+
     def for_update_clause(self, select, **kw):
         if self.is_subquery():
             return ""
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index c9270943b..ad15cb0ea 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1485,6 +1485,15 @@ class PGCompiler(compiler.SQLCompiler):
                 if escape else ''
             )
 
+    def visit_empty_set_expr(self, type_, **kw):
+        # cast the empty set to the type we are comparing against.  if
+        # we are comparing against the null type, pick an arbitrary
+        # datatype for the empty set
+        if type_._isnull:
+            type_ = INTEGER()
+        return 'SELECT CAST(NULL AS %s) WHERE 1!=1' % \
+               self.dialect.type_compiler.process(type_, **kw)
+
     def render_literal_value(self, value, type_):
         value = super(PGCompiler, self).render_literal_value(value, type_)
 
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index cc17b6c9b..345ad901e 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -870,6 +870,9 @@ class SQLiteCompiler(compiler.SQLCompiler):
             self.process(binary.left, **kw),
             self.process(binary.right, **kw))
 
+    def visit_empty_set_expr(self, type_):
+        return 'SELECT 1 FROM (SELECT 1) WHERE 1!=1'
+
 
 class SQLiteDDLCompiler(compiler.DDLCompiler):
 
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 915812a4f..aa524cd51 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -734,10 +734,12 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
             if parameter.expanding:
                 values = compiled_params.pop(name)
                 if not values:
-                    raise exc.InvalidRequestError(
-                        "'expanding' parameters can't be used with an "
-                        "empty list"
+                    to_update = []
+                    replacement_expressions[name] = (
+                        self.compiled.visit_empty_set_expr(
+                            type_=parameter.type)
                     )
+
                 elif isinstance(values[0], (tuple, list)):
                     to_update = [
                         ("%s_%s_%s" % (name, i, j), value)
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 529e28ba6..57da43217 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1058,6 +1058,12 @@ class SQLCompiler(Compiled):
                 self._emit_empty_in_warning()
             return self.process(binary.left == binary.left)
 
+    def visit_empty_set_expr(self, type_):
+        raise NotImplementedError(
+            "Dialect '%s' does not support empty set expression." %
+            self.dialect.name
+        )
+
     def visit_binary(self, binary, override_operator=None,
                      eager_grouping=False, **kw):
 
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index 05b9162de..78b34f496 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -1,7 +1,9 @@
+from sqlalchemy.dialects.mssql.base import MSDialect
+from sqlalchemy.dialects.oracle.base import OracleDialect
 from .. import fixtures, config
-from ..assertions import eq_
+from ..assertions import eq_, in_
 
-from sqlalchemy import util
+from sqlalchemy import util, case, null, true, false, or_
 from sqlalchemy import Integer, String, select, func, bindparam, union, tuple_
 from sqlalchemy import testing
 from sqlalchemy import literal_column
@@ -363,17 +365,18 @@ class ExpandingBoundInTest(fixtures.TablesTest):
         Table("some_table", metadata,
               Column('id', Integer, primary_key=True),
               Column('x', Integer),
-              Column('y', Integer))
+              Column('y', Integer),
+              Column('z', String(50)))
 
     @classmethod
     def insert_data(cls):
         config.db.execute(
             cls.tables.some_table.insert(),
             [
-                {"id": 1, "x": 1, "y": 2},
-                {"id": 2, "x": 2, "y": 3},
-                {"id": 3, "x": 3, "y": 4},
-                {"id": 4, "x": 4, "y": 5},
+                {"id": 1, "x": 1, "y": 2, "z": "z1"},
+                {"id": 2, "x": 2, "y": 3, "z": "z2"},
+                {"id": 3, "x": 3, "y": 4, "z": "z3"},
+                {"id": 4, "x": 4, "y": 5, "z": "z4"},
             ]
         )
 
@@ -383,6 +386,22 @@ class ExpandingBoundInTest(fixtures.TablesTest):
             result
         )
 
+    def test_multiple_empty_sets(self):
+        # test that any anonymous aliasing used by the dialect
+        # is fine with duplicates
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.x.in_(bindparam('q', expanding=True))).where(
+            table.c.y.in_(bindparam('p', expanding=True))
+        ).order_by(table.c.id)
+
+        self._assert_result(
+            stmt,
+            [],
+            params={"q": [], "p": []},
+        )
+
     def test_bound_in_scalar(self):
         table = self.tables.some_table
 
@@ -400,7 +419,8 @@ class ExpandingBoundInTest(fixtures.TablesTest):
         table = self.tables.some_table
 
         stmt = select([table.c.id]).where(
-            tuple_(table.c.x, table.c.y).in_(bindparam('q', expanding=True))).order_by(table.c.id)
+            tuple_(table.c.x, table.c.y).in_(
+                bindparam('q', expanding=True))).order_by(table.c.id)
 
         self._assert_result(
             stmt,
@@ -408,6 +428,73 @@ class ExpandingBoundInTest(fixtures.TablesTest):
             params={"q": [(2, 3), (3, 4), (4, 5)]},
         )
 
+    def test_empty_set_against_integer(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.x.in_(bindparam('q', expanding=True))).order_by(table.c.id)
+
+        self._assert_result(
+            stmt,
+            [],
+            params={"q": []},
+        )
+
+    def test_empty_set_against_integer_negation(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.x.notin_(bindparam('q', expanding=True))
+        ).order_by(table.c.id)
+
+        self._assert_result(
+            stmt,
+            [(1, ), (2, ), (3, ), (4, )],
+            params={"q": []},
+        )
+
+    def test_empty_set_against_string(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.z.in_(bindparam('q', expanding=True))).order_by(table.c.id)
+
+        self._assert_result(
+            stmt,
+            [],
+            params={"q": []},
+        )
+
+    def test_empty_set_against_string_negation(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.z.notin_(bindparam('q', expanding=True))
+        ).order_by(table.c.id)
+
+        self._assert_result(
+            stmt,
+            [(1, ), (2, ), (3, ), (4, )],
+            params={"q": []},
+        )
+
+    def test_null_in_empty_set_is_false(self):
+        stmt = select([
+            case(
+                [
+                    (
+                        null().in_(bindparam('foo', value=(), expanding=True)),
+                        true()
+                    )
+                ],
+                else_=false()
+            )
+        ])
+        in_(
+            config.db.execute(stmt).fetchone()[0],
+            (False, 0)
+        )
+
 
 class LikeFunctionsTest(fixtures.TablesTest):
     __backend__ = True