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

3 files changed, 108 insertions, 32 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index e65db2a36..8b19b8931 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1386,7 +1386,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=mysql.dialect()
         )
 
-    @testing.emits_warning('.*empty sequence.*')
     def test_render_binds_as_literal(self):
         """test a compiler that renders binds inline into
         SQL in the columns clause."""
@@ -1423,7 +1422,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
 
         self.assert_compile(
             select([literal("foo").in_([])]),
-            "SELECT 'foo' != 'foo' AS anon_1",
+            "SELECT 1 != 1 AS anon_1",
             dialect=dialect
         )
 
@@ -1440,13 +1439,16 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=dialect
         )
 
+        empty_in_dialect = default.DefaultDialect(empty_in_strategy='dynamic')
+        empty_in_dialect.statement_compiler = Compiler
+
         assert_raises_message(
             exc.CompileError,
             "Bind parameter 'foo' without a "
             "renderable value not allowed here.",
             bindparam("foo").in_(
                 []).compile,
-            dialect=dialect)
+            dialect=empty_in_dialect)
 
     def test_literal(self):
 
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 0bdebab58..0e0a8b29c 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -1638,6 +1638,11 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         column('othername', String)
     )
 
+    def _dialect(self, empty_in_strategy="static"):
+        return default.DefaultDialect(
+            empty_in_strategy=empty_in_strategy
+        )
+
     def test_in_1(self):
         self.assert_compile(self.table1.c.myid.in_(['a']),
                             "mytable.myid IN (:myid_1)")
@@ -1751,11 +1756,6 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             "FROM myothertable)"
         )
 
-    @testing.emits_warning('.*empty sequence.*')
-    def test_in_23(self):
-        self.assert_compile(self.table1.c.myid.in_([]),
-                            "mytable.myid != mytable.myid")
-
     def test_in_24(self):
         self.assert_compile(
             select([self.table1.c.myid.in_(select([self.table2.c.otherid]))]),
@@ -1812,15 +1812,53 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             "mytable.myid IN (NULL)"
         )
 
-    @testing.emits_warning('.*empty sequence.*')
-    def test_in_29(self):
+    def test_empty_in_dynamic_1(self):
+        self.assert_compile(self.table1.c.myid.in_([]),
+                            "mytable.myid != mytable.myid",
+                            dialect=self._dialect("dynamic"))
+
+    def test_empty_in_dynamic_2(self):
+        self.assert_compile(self.table1.c.myid.notin_([]),
+                            "mytable.myid = mytable.myid",
+                            dialect=self._dialect("dynamic"))
+
+    def test_empty_in_dynamic_3(self):
+        self.assert_compile(~self.table1.c.myid.in_([]),
+                            "mytable.myid = mytable.myid",
+                            dialect=self._dialect("dynamic"))
+
+    def test_empty_in_dynamic_warn_1(self):
+        with testing.expect_warnings(
+                "The IN-predicate was invoked with an empty sequence."):
+            self.assert_compile(self.table1.c.myid.in_([]),
+                                "mytable.myid != mytable.myid",
+                                dialect=self._dialect("dynamic_warn"))
+
+    def test_empty_in_dynamic_warn_2(self):
+        with testing.expect_warnings(
+                "The IN-predicate was invoked with an empty sequence."):
+            self.assert_compile(self.table1.c.myid.notin_([]),
+                                "mytable.myid = mytable.myid",
+                                dialect=self._dialect("dynamic_warn"))
+
+    def test_empty_in_dynamic_warn_3(self):
+        with testing.expect_warnings(
+                "The IN-predicate was invoked with an empty sequence."):
+            self.assert_compile(~self.table1.c.myid.in_([]),
+                                "mytable.myid = mytable.myid",
+                                dialect=self._dialect("dynamic_warn"))
+
+    def test_empty_in_static_1(self):
+        self.assert_compile(self.table1.c.myid.in_([]),
+                            "1 != 1")
+
+    def test_empty_in_static_2(self):
         self.assert_compile(self.table1.c.myid.notin_([]),
-                            "mytable.myid = mytable.myid")
+                            "1 = 1")
 
-    @testing.emits_warning('.*empty sequence.*')
-    def test_in_30(self):
+    def test_empty_in_static_3(self):
         self.assert_compile(~self.table1.c.myid.in_([]),
-                            "mytable.myid = mytable.myid")
+                            "1 = 1")
 
 
 class MathOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index bc9a176f1..d90cb0476 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -420,8 +420,7 @@ class QueryTest(fixtures.TestBase):
 
         s = users.select(not_(users.c.user_name.in_([])))
         r = s.execute().fetchall()
-        # All usernames with a value are outside an empty set
-        assert len(r) == 2
+        assert len(r) == 3
 
         s = users.select(users.c.user_name.in_(['jack', 'fred']))
         r = s.execute().fetchall()
@@ -432,7 +431,6 @@ class QueryTest(fixtures.TestBase):
         # Null values are not outside any set
         assert len(r) == 0
 
-    @testing.emits_warning('.*empty sequence.*')
     @testing.fails_on('firebird', "uses sql-92 rules")
     @testing.fails_on('sybase', "uses sql-92 rules")
     @testing.fails_if(
@@ -456,7 +454,7 @@ class QueryTest(fixtures.TestBase):
         r = s.execute(search_key='john').fetchall()
         assert len(r) == 3
         r = s.execute(search_key=None).fetchall()
-        assert len(r) == 0
+        assert len(r) == 3
 
     @testing.emits_warning('.*empty sequence.*')
     def test_literal_in(self):
@@ -470,28 +468,66 @@ class QueryTest(fixtures.TestBase):
         r = s.execute().fetchall()
         assert len(r) == 3
 
-    @testing.emits_warning('.*empty sequence.*')
     @testing.requires.boolean_col_expressions
-    def test_in_filtering_advanced(self):
+    def test_empty_in_filtering_static(self):
         """test the behavior of the in_() function when
         comparing against an empty collection, specifically
         that a proper boolean value is generated.
 
         """
 
-        users.insert().execute(user_id=7, user_name='jack')
-        users.insert().execute(user_id=8, user_name='fred')
-        users.insert().execute(user_id=9, user_name=None)
+        with testing.db.connect() as conn:
+            conn.execute(
+                users.insert(),
+                [
+                    {'user_id': 7, 'user_name': 'jack'},
+                    {'user_id': 8, 'user_name': 'ed'},
+                    {'user_id': 9, 'user_name': None}
+                ]
+            )
 
-        s = users.select(users.c.user_name.in_([]) == True)  # noqa
-        r = s.execute().fetchall()
-        assert len(r) == 0
-        s = users.select(users.c.user_name.in_([]) == False)  # noqa
-        r = s.execute().fetchall()
-        assert len(r) == 2
-        s = users.select(users.c.user_name.in_([]) == None)  # noqa
-        r = s.execute().fetchall()
-        assert len(r) == 1
+            s = users.select(users.c.user_name.in_([]) == True)  # noqa
+            r = conn.execute(s).fetchall()
+            assert len(r) == 0
+            s = users.select(users.c.user_name.in_([]) == False)  # noqa
+            r = conn.execute(s).fetchall()
+            assert len(r) == 3
+            s = users.select(users.c.user_name.in_([]) == None)  # noqa
+            r = conn.execute(s).fetchall()
+            assert len(r) == 0
+
+    @testing.requires.boolean_col_expressions
+    def test_empty_in_filtering_dynamic(self):
+        """test the behavior of the in_() function when
+        comparing against an empty collection, specifically
+        that a proper boolean value is generated.
+
+        """
+
+        engine = engines.testing_engine(
+            options={"empty_in_strategy": "dynamic"})
+
+        with engine.connect() as conn:
+            users.create(engine, checkfirst=True)
+
+            conn.execute(
+                users.insert(),
+                [
+                    {'user_id': 7, 'user_name': 'jack'},
+                    {'user_id': 8, 'user_name': 'ed'},
+                    {'user_id': 9, 'user_name': None}
+                ]
+            )
+
+            s = users.select(users.c.user_name.in_([]) == True)  # noqa
+            r = conn.execute(s).fetchall()
+            assert len(r) == 0
+            s = users.select(users.c.user_name.in_([]) == False)  # noqa
+            r = conn.execute(s).fetchall()
+            assert len(r) == 2
+            s = users.select(users.c.user_name.in_([]) == None)  # noqa
+            r = conn.execute(s).fetchall()
+            assert len(r) == 1
 
 
 class RequiredBindTest(fixtures.TablesTest):