Add new "expanding" feature to bindparam()

Added a new kind of :func:`.bindparam` called "expanding".  This is
for use in ``IN`` expressions where the list of elements is rendered
into individual bound parameters at statement execution time, rather
than at statement compilation time.  This allows both a single bound
parameter name to be linked to an IN expression of multiple elements,
as well as allows query caching to be used with IN expressions.  The
new feature allows the related features of "select in" loading and
"polymorphic in" loading to make use of the baked query extension
to reduce call overhead.   This feature should be considered to be
**experimental** for 1.2.

Fixes: #3953
Change-Id: Ie708414a3ab9c0af29998a2c7f239ff7633b1f6e

3 files changed, 87 insertions, 5 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 8b19b8931..05893d748 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2174,6 +2174,18 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "myothertable.otherid, myothertable.othername FROM myothertable)"
         )
 
+    def test_expanding_parameter(self):
+        self.assert_compile(
+            tuple_(table1.c.myid, table1.c.name).in_(
+                bindparam('foo', expanding=True)),
+            "(mytable.myid, mytable.name) IN ([EXPANDING_foo])"
+        )
+
+        self.assert_compile(
+            table1.c.myid.in_(bindparam('foo', expanding=True)),
+            "mytable.myid IN ([EXPANDING_foo])"
+        )
+
     def test_cast(self):
         tbl = table('casttest',
                     column('id', Integer),
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 217af4337..ac05d3a81 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -184,7 +184,7 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
         foo = ClauseList()
         assert_raises_message(
             exc.InvalidRequestError,
-            r"in_\(\) accepts either a list of expressions or a selectable:",
+            r"in_\(\) accepts either a list of expressions, a selectable",
             left.in_, [foo]
         )
 
@@ -193,7 +193,7 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
         right = column('right')
         assert_raises_message(
             exc.InvalidRequestError,
-            r"in_\(\) accepts either a list of expressions or a selectable:",
+            r"in_\(\) accepts either a list of expressions, a selectable",
             left.in_, right
         )
 
@@ -210,7 +210,7 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
         right = column('right', HasGetitem)
         assert_raises_message(
             exc.InvalidRequestError,
-            r"in_\(\) accepts either a list of expressions or a selectable:",
+            r"in_\(\) accepts either a list of expressions, a selectable",
             left.in_, right
         )
 
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index d90cb0476..28300855f 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -6,7 +6,7 @@ from sqlalchemy import (
     exc, sql, func, select, String, Integer, MetaData, and_, ForeignKey,
     union, intersect, except_, union_all, VARCHAR, INT, text,
     bindparam, literal, not_, literal_column, desc, asc,
-    TypeDecorator, or_, cast)
+    TypeDecorator, or_, cast, tuple_)
 from sqlalchemy.engine import default
 from sqlalchemy.testing.schema import Table, Column
 
@@ -405,7 +405,6 @@ class QueryTest(fixtures.TestBase):
                     use_labels=labels),
                 [(3, 'a'), (2, 'b'), (1, None)])
 
-    @testing.emits_warning('.*empty sequence.*')
     def test_in_filtering(self):
         """test the behavior of the in_() function."""
 
@@ -431,6 +430,77 @@ class QueryTest(fixtures.TestBase):
         # Null values are not outside any set
         assert len(r) == 0
 
+    def test_expanding_in(self):
+        testing.db.execute(
+            users.insert(),
+            [
+                dict(user_id=7, user_name='jack'),
+                dict(user_id=8, user_name='fred'),
+                dict(user_id=9, user_name=None)
+            ]
+        )
+
+        with testing.db.connect() as conn:
+            stmt = select([users]).where(
+                users.c.user_name.in_(bindparam('uname', expanding=True))
+            ).order_by(users.c.user_id)
+
+            eq_(
+                conn.execute(stmt, {"uname": ['jack']}).fetchall(),
+                [(7, 'jack')]
+            )
+
+            eq_(
+                conn.execute(stmt, {"uname": ['jack', 'fred']}).fetchall(),
+                [(7, 'jack'), (8, 'fred')]
+            )
+
+            assert_raises_message(
+                exc.StatementError,
+                "'expanding' parameters can't be used with an empty list",
+                conn.execute,
+                stmt, {"uname": []}
+            )
+
+            assert_raises_message(
+                exc.StatementError,
+                "'expanding' parameters can't be used with executemany()",
+                conn.execute,
+                users.update().where(
+                    users.c.user_name.in_(bindparam('uname', expanding=True))
+                ), [{"uname": ['fred']}, {"uname": ['ed']}]
+            )
+
+    @testing.requires.tuple_in
+    def test_expanding_in_composite(self):
+        testing.db.execute(
+            users.insert(),
+            [
+                dict(user_id=7, user_name='jack'),
+                dict(user_id=8, user_name='fred'),
+                dict(user_id=9, user_name=None)
+            ]
+        )
+
+        with testing.db.connect() as conn:
+            stmt = select([users]).where(
+                tuple_(
+                    users.c.user_id,
+                    users.c.user_name
+                ).in_(bindparam('uname', expanding=True))
+            ).order_by(users.c.user_id)
+
+            eq_(
+                conn.execute(stmt, {"uname": [(7, 'jack')]}).fetchall(),
+                [(7, 'jack')]
+            )
+
+            eq_(
+                conn.execute(stmt, {"uname": [(7, 'jack'), (8, 'fred')]}).fetchall(),
+                [(7, 'jack'), (8, 'fred')]
+            )
+
+
     @testing.fails_on('firebird', "uses sql-92 rules")
     @testing.fails_on('sybase', "uses sql-92 rules")
     @testing.fails_if(