Slightly changed behavior of IN operator for comparing to empty collections. Now results in inequality comparison against self. More portable, but breaks with stored procedures that aren't pure functions.

2 files changed, 9 insertions, 2 deletions

diff --git a/test/sql/query.py b/test/sql/query.py
index c5b9b5d35..4d52c2764 100644
--- a/test/sql/query.py
+++ b/test/sql/query.py
@@ -550,7 +550,6 @@ class QueryTest(TestBase):
         finally:
             shadowed.drop(checkfirst=True)
 
-    @testing.fails_on('firebird', 'maxdb', 'oracle')
     def test_in_filtering(self):
         """test the behavior of the in_() function."""
 
@@ -591,6 +590,14 @@ class QueryTest(TestBase):
         r = s.execute(search_key=None).fetchall()
         assert len(r) == 0
 
+    @testing.fails_on('firebird', 'maxdb', 'oracle')
+    def test_in_filtering_advanced(self):
+        """test the behavior of the in_() function when comparing against an empty collection."""
+
+        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)
+
         s = users.select(users.c.user_name.in_([]) == True)
         r = s.execute().fetchall()
         assert len(r) == 0
diff --git a/test/sql/select.py b/test/sql/select.py
index 52aa151d9..e959a7992 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -1185,7 +1185,7 @@ UNION SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_2)")
 
         # test empty in clause
         self.assert_compile(select([table1], table1.c.myid.in_([])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE (CASE WHEN (mytable.myid IS NULL) THEN NULL ELSE 0 END = 1)")
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != mytable.myid")
 
         self.assert_compile(
             select([table1.c.myid.in_(select([table2.c.otherid]))]),