- An alias() of a select() will convert to a "scalar subquery"

when used in an unambiguously scalar context, i.e. it's used
in a comparison operation.  This applies to
the ORM when using query.subquery() as well.

4 files changed, 44 insertions, 4 deletions

diff --git a/CHANGES b/CHANGES
index 3e1c6f9ec..786b9c15f 100644
--- a/CHANGES
+++ b/CHANGES
@@ -93,6 +93,11 @@ CHANGES
        with_polymorphic(), or using from_self().
         
 - sql
+    - An alias() of a select() will convert to a "scalar subquery"
+      when used in an unambiguously scalar context, i.e. it's used 
+      in a comparison operation.  This applies to
+      the ORM when using query.subquery() as well.
+      
     - Fixed missing _label attribute on Function object, others
       when used in a select() with use_labels (such as when used
       in an ORM column_property()).  [ticket:1302]
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 903b3052d..f94e849e5 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -1527,7 +1527,7 @@ class _CompareMixin(ColumnOperators):
             return other.__clause_element__()
         elif not isinstance(other, ClauseElement):
             return self._bind_param(other)
-        elif isinstance(other, _SelectBaseMixin):
+        elif isinstance(other, (_SelectBaseMixin, Alias)):
             return other.as_scalar()
         else:
             return other
@@ -1769,7 +1769,21 @@ class FromClause(Selectable):
         return Join(self, right, onclause, True)
 
     def alias(self, name=None):
-        """return an alias of this ``FromClause`` against another ``FromClause``."""
+        """return an alias of this ``FromClause``.
+        
+        For table objects, this has the effect of the table being rendered
+        as ``tablename AS aliasname`` in a SELECT statement.  
+        For select objects, the effect is that of creating a named
+        subquery, i.e. ``(select ...) AS aliasname``.
+        The ``alias()`` method is the general way to create
+        a "subquery" out of an existing SELECT.
+        
+        The ``name`` parameter is optional, and if left blank an 
+        "anonymous" name will be generated at compile time, guaranteed
+        to be unique against other anonymous constructs used in the
+        same statement.
+        
+        """
 
         return Alias(self, name)
 
@@ -2600,6 +2614,12 @@ class Alias(FromClause):
     def description(self):
         return self.name.encode('ascii', 'backslashreplace')
 
+    def as_scalar(self):
+        try:
+            return self.element.as_scalar()
+        except AttributeError:
+            raise AttributeError("Element %s does not support 'as_scalar()'" % self.element)
+        
     def is_derived_from(self, fromclause):
         if fromclause in self._cloned_set:
             return True
diff --git a/test/orm/query.py b/test/orm/query.py
index d5ed96d4b..1d13a6c45 100644
--- a/test/orm/query.py
+++ b/test/orm/query.py
@@ -525,7 +525,16 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL):
         l = list(session.query(User).instances(s.execute(emailad = 'jack@bean.com')))
         eq_([User(id=7)], l)
 
-
+    def test_scalar_subquery(self):
+        session = create_session()
+        
+        q = session.query(User.id).filter(User.id==7).subquery()
+        
+        q = session.query(User).filter(User.id==q)
+        
+        eq_(User(id=7), q.one())
+        
+        
     def test_in(self):
         session = create_session()
         s = session.query(User.id).join(User.addresses).group_by(User.id).having(func.count(Address.id) > 2)
@@ -1742,7 +1751,7 @@ class MixedEntitiesTest(QueryTest):
         q2 = q.group_by([User.name.like('%j%')]).order_by(desc(User.name.like('%j%'))).values(User.name.like('%j%'), func.count(User.name.like('%j%')))
         self.assertEquals(list(q2), [(True, 1), (False, 3)])
 
-    def test_scalar_subquery(self):
+    def test_correlated_subquery(self):
         """test that a subquery constructed from ORM attributes doesn't leak out 
         those entities to the outermost query.
         
diff --git a/test/sql/select.py b/test/sql/select.py
index aeb53bf19..5d6d6dcaa 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -307,6 +307,12 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
         s = select([table1.c.myid]).correlate(None).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")
 
+        # test that aliases use as_scalar() when used in an explicitly scalar context
+        s = select([table1.c.myid]).alias()
+        self.assert_compile(select([table1.c.myid]).where(table1.c.myid==s), "SELECT mytable.myid FROM mytable WHERE mytable.myid = (SELECT mytable.myid FROM mytable)")
+        self.assert_compile(select([table1.c.myid]).where(s > table1.c.myid), "SELECT mytable.myid FROM mytable WHERE mytable.myid < (SELECT mytable.myid FROM mytable)")
+
+
         s = select([table1.c.myid]).as_scalar()
         self.assert_compile(select([table2, s]), "SELECT myothertable.otherid, myothertable.othername, (SELECT mytable.myid FROM mytable) AS anon_1 FROM myothertable")