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.

4 files changed, 16 insertions, 4 deletions

diff --git a/CHANGES b/CHANGES
index 60716cc3c..12e14b7f1 100644
--- a/CHANGES
+++ b/CHANGES
@@ -33,6 +33,11 @@ CHANGES
     - Removed "default_order_by()" method on all FromClause
       objects.
 
+    - 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.
+
 0.5.0rc2
 ========
 - orm
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index b721e5884..cb2bcd6d6 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -1383,8 +1383,8 @@ class _CompareMixin(ColumnOperators):
             args.append(o)
 
         if len(args) == 0:
-            # Special case handling for empty IN's
-            return _Grouping(case([(self.__eq__(None), text('NULL'))], else_=text('0')).__eq__(text('1')))
+            # Special case handling for empty IN's, behave like comparison against zero row selectable
+            return self != self
 
         return self.__compare(op, ClauseList(*args).self_group(against=op), negate=negate_op)
 
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]))]),