change the in_ API to accept a sequence or a selectable [ticket:750]

2 files changed, 57 insertions, 42 deletions

diff --git a/test/sql/query.py b/test/sql/query.py
index 77e1421a5..ba29d6a8f 100644
--- a/test/sql/query.py
+++ b/test/sql/query.py
@@ -597,46 +597,46 @@ class QueryTest(PersistTest):
         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_())
+        s = users.select(users.c.user_name.in_([]))
         r = s.execute().fetchall()
         # No username is in empty set
         assert len(r) == 0
         
-        s = users.select(not_(users.c.user_name.in_()))
+        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
         
-        s = users.select(users.c.user_name.in_('jack','fred'))
+        s = users.select(users.c.user_name.in_(['jack','fred']))
         r = s.execute().fetchall()
         assert len(r) == 2
         
-        s = users.select(not_(users.c.user_name.in_('jack','fred')))
+        s = users.select(not_(users.c.user_name.in_(['jack','fred'])))
         r = s.execute().fetchall()
         # Null values are not outside any set
         assert len(r) == 0
         
         u = bindparam('search_key')
         
-        s = users.select(u.in_())
+        s = users.select(u.in_([]))
         r = s.execute(search_key='john').fetchall()
         assert len(r) == 0
         r = s.execute(search_key=None).fetchall()
         assert len(r) == 0
         
-        s = users.select(not_(u.in_()))
+        s = users.select(not_(u.in_([])))
         r = s.execute(search_key='john').fetchall()
         assert len(r) == 3
         r = s.execute(search_key=None).fetchall()
         assert len(r) == 0
         
-        s = users.select(users.c.user_name.in_() == True)
+        s = users.select(users.c.user_name.in_([]) == True)
         r = s.execute().fetchall()
         assert len(r) == 0
-        s = users.select(users.c.user_name.in_() == False)
+        s = users.select(users.c.user_name.in_([]) == False)
         r = s.execute().fetchall()
         assert len(r) == 2
-        s = users.select(users.c.user_name.in_() == None)
+        s = users.select(users.c.user_name.in_([]) == None)
         r = s.execute().fetchall()
         assert len(r) == 1
 
@@ -690,9 +690,9 @@ class CompoundTest(PersistTest):
     def test_union(self):
         (s1, s2) = (
             select([t1.c.col3.label('col3'), t1.c.col4.label('col4')],
-                   t1.c.col2.in_("t1col2r1", "t1col2r2")),
+                   t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
             select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
-                   t2.c.col2.in_("t2col2r2", "t2col2r3"))
+                   t2.c.col2.in_(["t2col2r2", "t2col2r3"]))
         )        
         u = union(s1, s2)
 
@@ -707,9 +707,9 @@ class CompoundTest(PersistTest):
     def test_union_ordered(self):
         (s1, s2) = (
             select([t1.c.col3.label('col3'), t1.c.col4.label('col4')],
-                   t1.c.col2.in_("t1col2r1", "t1col2r2")),
+                   t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
             select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
-                   t2.c.col2.in_("t2col2r2", "t2col2r3"))
+                   t2.c.col2.in_(["t2col2r2", "t2col2r3"]))
         )        
         u = union(s1, s2, order_by=['col3', 'col4'])
 
@@ -720,9 +720,9 @@ class CompoundTest(PersistTest):
     def test_union_ordered_alias(self):
         (s1, s2) = (
             select([t1.c.col3.label('col3'), t1.c.col4.label('col4')],
-                   t1.c.col2.in_("t1col2r1", "t1col2r2")),
+                   t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
             select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
-                   t2.c.col2.in_("t2col2r2", "t2col2r3"))
+                   t2.c.col2.in_(["t2col2r2", "t2col2r3"]))
         )        
         u = union(s1, s2, order_by=['col3', 'col4'])
 
diff --git a/test/sql/select.py b/test/sql/select.py
index 8096b0c97..5c8b570d7 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -946,58 +946,61 @@ EXISTS (select yay from foo where boo = lar)",
         assert [str(c) for c in s.c] == ["id", "hoho"]
         
     def testin(self):
-        self.assert_compile(select([table1], table1.c.myid.in_('a')),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid")
+        self.assert_compile(select([table1], table1.c.myid.in_(['a'])),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid)")
 
-        self.assert_compile(select([table1], ~table1.c.myid.in_('a')),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != :mytable_myid")
+        self.assert_compile(select([table1], ~table1.c.myid.in_(['a'])),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid NOT IN (:mytable_myid)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_('a', 'b')),
+        self.assert_compile(select([table1], table1.c.myid.in_(['a', 'b'])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal('a'))),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :literal")
+        self.assert_compile(select([table1], table1.c.myid.in_(iter(['a', 'b']))),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1)")
+
+        self.assert_compile(select([table1], table1.c.myid.in_([literal('a')])),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal('a'), 'b')),
+        self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), 'b'])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :mytable_myid)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal('a'), literal('b'))),
+        self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), literal('b')])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :literal_1)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_('a', literal('b'))),
+        self.assert_compile(select([table1], table1.c.myid.in_(['a', literal('b')])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :literal)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal(1) + 'a')),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :literal + :literal_1")
+        self.assert_compile(select([table1], table1.c.myid.in_([literal(1) + 'a'])),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal + :literal_1)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal('a') +'a', 'b')),
+        self.assert_compile(select([table1], table1.c.myid.in_([literal('a') +'a', 'b'])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal || :literal_1, :mytable_myid)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal('a') + literal('a'), literal('b'))),
+        self.assert_compile(select([table1], table1.c.myid.in_([literal('a') + literal('a'), literal('b')])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal || :literal_1, :literal_2)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(1, literal(3) + 4)),
+        self.assert_compile(select([table1], table1.c.myid.in_([1, literal(3) + 4])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :literal + :literal_1)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal('a') < 'b')),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = (:literal < :literal_1)")
+        self.assert_compile(select([table1], table1.c.myid.in_([literal('a') < 'b'])),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal < :literal_1)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(table1.c.myid)),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = mytable.myid")
+        self.assert_compile(select([table1], table1.c.myid.in_([table1.c.myid])),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (mytable.myid)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_('a', table1.c.myid)),
+        self.assert_compile(select([table1], table1.c.myid.in_(['a', table1.c.myid])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, mytable.myid)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal('a'), table1.c.myid)),
+        self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), table1.c.myid])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, mytable.myid)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal('a'), table1.c.myid +'a')),
+        self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), table1.c.myid +'a'])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, mytable.myid + :mytable_myid)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(literal(1), 'a' + table1.c.myid)),
+        self.assert_compile(select([table1], table1.c.myid.in_([literal(1), 'a' + table1.c.myid])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :mytable_myid + mytable.myid)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(1, 2, 3)),
+        self.assert_compile(select([table1], table1.c.myid.in_([1, 2, 3])),
         "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1, :mytable_myid_2)")
 
         self.assert_compile(select([table1], table1.c.myid.in_(select([table2.c.otherid]))),
@@ -1028,9 +1031,21 @@ UNION SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE
         )
         
         # test empty in clause
-        self.assert_compile(select([table1], table1.c.myid.in_()),
+        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)")
         
+    def test_in_deprecated_api(self):
+        self.assert_compile(select([table1], table1.c.myid.in_('abc')),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid)")
+        
+        self.assert_compile(select([table1], table1.c.myid.in_(1)),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid)")
+        
+        self.assert_compile(select([table1], table1.c.myid.in_(1,2)),
+        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1)")
+        
+        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)")
     
     def testcast(self):
         tbl = table('casttest',
@@ -1081,9 +1096,9 @@ UNION SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE
             "SELECT op.field FROM op WHERE (op.field + :op_field) * :literal")
         self.assert_compile(table.select((table.c.field * 5) + 6),
             "SELECT op.field FROM op WHERE op.field * :op_field + :literal")
-        self.assert_compile(table.select(5 + table.c.field.in_(5,6)),
+        self.assert_compile(table.select(5 + table.c.field.in_([5,6])),
             "SELECT op.field FROM op WHERE :literal + (op.field IN (:op_field, :op_field_1))")
-        self.assert_compile(table.select((5 + table.c.field).in_(5,6)),
+        self.assert_compile(table.select((5 + table.c.field).in_([5,6])),
             "SELECT op.field FROM op WHERE :op_field + op.field IN (:literal, :literal_1)")
         self.assert_compile(table.select(not_(and_(table.c.field == 5, table.c.field == 7))),
             "SELECT op.field FROM op WHERE NOT (op.field = :op_field AND op.field = :op_field_1)")