- Added a tuple_() construct, allows sets of expressions

to be compared to another set, typically with IN against
composite primary keys or similar.  Also accepts an
IN with multiple columns.   The "scalar select can
have only one column" error message is removed - will
rely upon the database to report problems with
col mismatch.

7 files changed, 134 insertions, 80 deletions

diff --git a/CHANGES b/CHANGES
index ce7a67dcb..f8ebfbb41 100644
--- a/CHANGES
+++ b/CHANGES
@@ -293,7 +293,15 @@ CHANGES
       (or a "bytes" type in Python 3), instead of the built-
       in "buffer" type.  This allows symmetric round trips
       of binary data. [ticket:1524]
-      
+    
+    - Added a tuple_() construct, allows sets of expressions
+      to be compared to another set, typically with IN against
+      composite primary keys or similar.  Also accepts an 
+      IN with multiple columns.   The "scalar select can
+      have only one column" error message is removed - will
+      rely upon the database to report problems with 
+      col mismatch.
+        
     - User-defined "default" and "onupdate" callables which 
       accept a context should now call upon 
       "context.current_parameters" to get at the dictionary
diff --git a/doc/build/reference/sqlalchemy/expressions.rst b/doc/build/reference/sqlalchemy/expressions.rst
index 28cc37f02..1c3c208b4 100644
--- a/doc/build/reference/sqlalchemy/expressions.rst
+++ b/doc/build/reference/sqlalchemy/expressions.rst
@@ -99,6 +99,8 @@ The expression package uses functions to construct SQL expressions.  The return
 
 .. autofunction:: text
 
+.. autofunction:: tuple_
+
 .. autofunction:: union
 
 .. autofunction:: union_all
diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py
index 8aa293a6c..dbc0c5dc9 100644
--- a/lib/sqlalchemy/__init__.py
+++ b/lib/sqlalchemy/__init__.py
@@ -42,6 +42,7 @@ from sqlalchemy.sql import (
     select,
     subquery,
     text,
+    tuple_,
     union,
     union_all,
     update,
diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py
index 0b347ca38..aa18eac17 100644
--- a/lib/sqlalchemy/sql/__init__.py
+++ b/lib/sqlalchemy/sql/__init__.py
@@ -46,6 +46,7 @@ from sqlalchemy.sql.expression import (
     subquery,
     table,
     text,
+    tuple_,
     union,
     union_all,
     update,
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index cf5d98d8f..6d74fec16 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -47,7 +47,7 @@ __all__ = [
     'modifier', 'collate',
     'insert', 'intersect', 'intersect_all', 'join', 'label', 'literal',
     'literal_column', 'not_', 'null', 'or_', 'outparam', 'outerjoin', 'select',
-    'subquery', 'table', 'text', 'union', 'union_all', 'update', ]
+    'subquery', 'table', 'text', 'tuple_', 'union', 'union_all', 'update', ]
 
 PARSE_AUTOCOMMIT = util._symbol('PARSE_AUTOCOMMIT')
 
@@ -662,6 +662,18 @@ def literal(value, type_=None):
     """
     return _BindParamClause(None, value, type_=type_, unique=True)
 
+def tuple_(*expr):
+    """Return a SQL tuple.   
+    
+    Main usage is to produce a composite IN construct::
+    
+        tuple_(table.c.col1, table.c.col2).in_(
+            [(1, 2), (5, 12), (10, 19)]
+        )
+    
+    """
+    return _Tuple(*expr)
+    
 def label(name, obj):
     """Return a :class:`_Label` object for the
     given :class:`ColumnElement`.
@@ -955,6 +967,13 @@ def _literal_as_binds(element, name=None, type_=None):
     else:
         return element
 
+def _type_from_args(args):
+    for a in args:
+        if not isinstance(a.type, sqltypes.NullType):
+            return a.type
+    else:
+        return sqltypes.NullType
+
 def _no_literals(element):
     if hasattr(element, '__clause_element__'):
         return element.__clause_element__()
@@ -1500,7 +1519,8 @@ class _CompareMixin(ColumnOperators):
             if not _is_literal(o):
                 if not isinstance( o, _CompareMixin):
                     raise exc.InvalidRequestError( 
-                        "in() function accepts either a list of non-selectable values, or a selectable: %r" % o)
+                        "in() function accepts either a list of non-selectable values, "
+                        "or a selectable: %r" % o)
             else:
                 o = self._bind_param(o)
             args.append(o)
@@ -2360,6 +2380,22 @@ class BooleanClauseList(ClauseList, ColumnElement):
     def _select_iterable(self):
         return (self, )
 
+class _Tuple(ClauseList, ColumnElement):
+    
+    def __init__(self, *clauses, **kw):
+        super(_Tuple, self).__init__(*clauses, **kw)
+        self.type = _type_from_args(clauses)
+
+    @property
+    def _select_iterable(self):
+        return (self, )
+
+    def _bind_param(self, obj):
+        return _Tuple(*[
+            _BindParamClause(None, o, type_=self.type, unique=True)
+            for o in obj
+        ]).self_group()
+    
 
 class _Case(ColumnElement):
     __visit_name__ = 'case'
@@ -3318,9 +3354,6 @@ class _ScalarSelect(_Grouping):
     def __init__(self, element):
         self.element = element
         cols = list(element.c)
-        if len(cols) != 1:
-            raise exc.InvalidRequestError("Scalar select can only be created "
-                    "from a Select object that has exactly one column expression.")
         self.type = cols[0].type
 
     @property
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index c6cb938d4..212f81ada 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -1,6 +1,6 @@
 from sqlalchemy import types as sqltypes
 from sqlalchemy.sql.expression import (
-    ClauseList, Function, _literal_as_binds, text
+    ClauseList, Function, _literal_as_binds, text, _type_from_args
     )
 from sqlalchemy.sql import operators
 from sqlalchemy.sql.visitors import VisitableType
@@ -102,9 +102,3 @@ class sysdate(AnsiFunction):
 class user(AnsiFunction):
     __return_type__ = sqltypes.String
 
-def _type_from_args(args):
-    for a in args:
-        if not isinstance(a.type, sqltypes.NullType):
-            return a.type
-    else:
-        return sqltypes.NullType
diff --git a/test/sql/test_select.py b/test/sql/test_select.py
index 28317db57..fd7fb9ceb 100644
--- a/test/sql/test_select.py
+++ b/test/sql/test_select.py
@@ -367,18 +367,12 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
 
     @testing.uses_deprecated('scalar option')
     def test_scalar_select(self):
-        try:
-            s = select([table1.c.myid, table1.c.name]).as_scalar()
-            assert False
-        except exc.InvalidRequestError, err:
-            assert str(err) == "Scalar select can only be created from a Select object that has exactly one column expression.", str(err)
-
-        try:
-            # generic function which will look at the type of expression
-            func.coalesce(select([table1.c.myid]))
-            assert False
-        except exc.InvalidRequestError, err:
-            assert str(err) == "Select objects don't have a type.  Call as_scalar() on this Select object to return a 'scalar' version of this Select.", str(err)
+        assert_raises_message(
+            exc.InvalidRequestError,
+            r"Select objects don't have a type\.  Call as_scalar\(\) "
+            "on this Select object to return a 'scalar' version of this Select\.",
+            func.coalesce, select([table1.c.myid])
+        )
 
         s = select([table1.c.myid], correlate=False).as_scalar()
         self.assert_compile(select([table1, s]), "SELECT mytable.myid, mytable.name, mytable.description, (SELECT mytable.myid FROM mytable) AS anon_1 FROM mytable")
@@ -1356,80 +1350,93 @@ EXISTS (select yay from foo where boo = lar)",
         
         assert [str(c) for c in s.c] == ["id", "hoho"]
 
+        
     @testing.emits_warning('.*empty sequence.*')
     def test_in(self):
-        self.assert_compile(select([table1], table1.c.myid.in_(['a'])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1)")
+        self.assert_compile(table1.c.myid.in_(['a']),
+        "mytable.myid IN (:myid_1)")
 
-        self.assert_compile(select([table1], ~table1.c.myid.in_(['a'])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid NOT IN (:myid_1)")
+        self.assert_compile(~table1.c.myid.in_(['a']),
+        "mytable.myid NOT IN (:myid_1)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(['a', 'b'])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1, :myid_2)")
+        self.assert_compile(table1.c.myid.in_(['a', 'b']),
+        "mytable.myid IN (:myid_1, :myid_2)")
 
-        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 (:myid_1, :myid_2)")
+        self.assert_compile(table1.c.myid.in_(iter(['a', 'b'])),
+        "mytable.myid IN (:myid_1, :myid_2)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_([literal('a')])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1)")
+        self.assert_compile(table1.c.myid.in_([literal('a')]),
+        "mytable.myid IN (:param_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 (:param_1, :myid_1)")
+        self.assert_compile(table1.c.myid.in_([literal('a'), 'b']),
+        "mytable.myid IN (:param_1, :myid_1)")
 
-        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 (:param_1, :param_2)")
+        self.assert_compile(table1.c.myid.in_([literal('a'), literal('b')]),
+        "mytable.myid IN (:param_1, :param_2)")
 
-        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 (:myid_1, :param_1)")
+        self.assert_compile(table1.c.myid.in_(['a', literal('b')]),
+        "mytable.myid IN (:myid_1, :param_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 (:param_1 + :param_2)")
+        self.assert_compile(table1.c.myid.in_([literal(1) + 'a']),
+        "mytable.myid IN (:param_1 + :param_2)")
 
-        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 (:param_1 || :param_2, :myid_1)")
+        self.assert_compile(table1.c.myid.in_([literal('a') +'a', 'b']),
+        "mytable.myid IN (:param_1 || :param_2, :myid_1)")
 
-        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 (:param_1 || :param_2, :param_3)")
+        self.assert_compile(table1.c.myid.in_([literal('a') + literal('a'), literal('b')]),
+        "mytable.myid IN (:param_1 || :param_2, :param_3)")
 
-        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 (:myid_1, :param_1 + :param_2)")
+        self.assert_compile(table1.c.myid.in_([1, literal(3) + 4]),
+        "mytable.myid IN (:myid_1, :param_1 + :param_2)")
 
-        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 (:param_1 < :param_2)")
+        self.assert_compile(table1.c.myid.in_([literal('a') < 'b']),
+        "mytable.myid IN (:param_1 < :param_2)")
 
-        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(table1.c.myid.in_([table1.c.myid]),
+        "mytable.myid IN (mytable.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 (:myid_1, mytable.myid)")
+        self.assert_compile(table1.c.myid.in_(['a', table1.c.myid]),
+        "mytable.myid IN (:myid_1, mytable.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 (:param_1, mytable.myid)")
+        self.assert_compile(table1.c.myid.in_([literal('a'), table1.c.myid]),
+        "mytable.myid IN (:param_1, mytable.myid)")
 
-        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 (:param_1, mytable.myid + :myid_1)")
+        self.assert_compile(table1.c.myid.in_([literal('a'), table1.c.myid +'a']),
+        "mytable.myid IN (:param_1, mytable.myid + :myid_1)")
 
-        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 (:param_1, :myid_1 + mytable.myid)")
+        self.assert_compile(table1.c.myid.in_([literal(1), 'a' + table1.c.myid]),
+        "mytable.myid IN (:param_1, :myid_1 + mytable.myid)")
 
-        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 (:myid_1, :myid_2, :myid_3)")
+        self.assert_compile(table1.c.myid.in_([1, 2, 3]),
+        "mytable.myid IN (:myid_1, :myid_2, :myid_3)")
 
-        self.assert_compile(select([table1], table1.c.myid.in_(select([table2.c.otherid]))),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (SELECT myothertable.otherid FROM myothertable)")
+        self.assert_compile(table1.c.myid.in_(select([table2.c.otherid])),
+        "mytable.myid IN (SELECT myothertable.otherid FROM myothertable)")
 
-        self.assert_compile(select([table1], ~table1.c.myid.in_(select([table2.c.otherid]))),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid NOT IN (SELECT myothertable.otherid FROM myothertable)")
+        self.assert_compile(~table1.c.myid.in_(select([table2.c.otherid])),
+        "mytable.myid NOT IN (SELECT myothertable.otherid FROM myothertable)")
+
+        # test empty in clause
+        self.assert_compile(table1.c.myid.in_([]),
+        "mytable.myid != mytable.myid")
+
+        self.assert_compile(
+            select([table1.c.myid.in_(select([table2.c.otherid]))]),
+            "SELECT mytable.myid IN (SELECT myothertable.otherid FROM myothertable) AS anon_1 FROM mytable"
+        )
+        self.assert_compile(
+            select([table1.c.myid.in_(select([table2.c.otherid]).as_scalar())]),
+            "SELECT mytable.myid IN (SELECT myothertable.otherid FROM myothertable) AS anon_1 FROM mytable"
+        )
 
-        self.assert_compile(select([table1], table1.c.myid.in_(
+        self.assert_compile(table1.c.myid.in_(
             union(
                   select([table1.c.myid], table1.c.myid == 5),
                   select([table1.c.myid], table1.c.myid == 12),
             )
-        )), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable \
-WHERE mytable.myid IN (\
-SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 \
-UNION SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_2)")
+        ), "mytable.myid IN ("\
+        "SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 "\
+        "UNION SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_2)")
 
         # test that putting a select in an IN clause does not blow away its ORDER BY clause
         self.assert_compile(
@@ -1444,19 +1451,27 @@ UNION SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_2)")
             "FROM myothertable ORDER BY myothertable.othername  LIMIT 10) ORDER BY mytable.myid"
         )
 
-        # test empty in clause
-        self.assert_compile(select([table1], table1.c.myid.in_([])),
-        "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != mytable.myid")
+    def test_tuple(self):
+        self.assert_compile(tuple_(table1.c.myid, table1.c.name).in_([(1, 'foo'), (5, 'bar')]),
+            "(mytable.myid, mytable.name) IN ((:param_1, :param_2), (:param_3, :param_4))"
+        )
 
         self.assert_compile(
-            select([table1.c.myid.in_(select([table2.c.otherid]))]),
-            "SELECT mytable.myid IN (SELECT myothertable.otherid FROM myothertable) AS anon_1 FROM mytable"
+            tuple_(table1.c.myid, table1.c.name).in_(
+                        [tuple_(table2.c.otherid, table2.c.othername)]
+                    ),
+            "(mytable.myid, mytable.name) IN (myothertable.otherid, myothertable.othername)"
         )
+        
         self.assert_compile(
-            select([table1.c.myid.in_(select([table2.c.otherid]).as_scalar())]),
-            "SELECT mytable.myid IN (SELECT myothertable.otherid FROM myothertable) AS anon_1 FROM mytable"
+            tuple_(table1.c.myid, table1.c.name).in_(
+                        select([table2.c.otherid, table2.c.othername])
+                    ),
+            "(mytable.myid, mytable.name) IN (SELECT "
+            "myothertable.otherid, myothertable.othername FROM myothertable)"
         )
-
+        
+        
     def test_cast(self):
         tbl = table('casttest',
                     column('id', Integer),