- The exists() construct won't "export" its contained list

of elements as FROM clauses, allowing them to be used more
effectively in the columns clause of a SELECT.

- and_() and or_() now generate a ColumnElement, allowing
boolean expressions as result columns, i.e.
select([and_(1, 0)]).  [ticket:798]

2 files changed, 49 insertions, 8 deletions

diff --git a/test/sql/query.py b/test/sql/query.py
index fa247a7b2..6ca2a2542 100644
--- a/test/sql/query.py
+++ b/test/sql/query.py
@@ -200,6 +200,26 @@ class QueryTest(TestBase):
         self.assert_(not (rp != equal))
         self.assert_(not (equal != equal))
 
+    def test_or_and_as_columns(self):
+        if testing.against('sqlite'):
+            true, false = 1, 0
+        else:
+            true, false = literal_column('true'), literal_column('false')
+        
+        self.assertEquals(testing.db.execute(select([and_(true, false)])).scalar(), False)
+        self.assertEquals(testing.db.execute(select([and_(true, true)])).scalar(), True)
+        self.assertEquals(testing.db.execute(select([or_(true, false)])).scalar(), True)
+        self.assertEquals(testing.db.execute(select([or_(false, false)])).scalar(), False)
+        self.assertEquals(testing.db.execute(select([not_(or_(false, false))])).scalar(), True)
+
+        row = testing.db.execute(select([or_(false, false).label("x"), and_(true, false).label("y")])).fetchone()
+        assert row.x == False
+        assert row.y == False
+
+        row = testing.db.execute(select([or_(true, false).label("x"), and_(true, false).label("y")])).fetchone()
+        assert row.x == True
+        assert row.y == False
+        
     def test_fetchmany(self):
         users.insert().execute(user_id = 7, user_name = 'jack')
         users.insert().execute(user_id = 8, user_name = 'ed')
diff --git a/test/sql/select.py b/test/sql/select.py
index dd082fb8d..18f4b91dd 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -170,24 +170,36 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
         self.assert_compile(select([table1, exists([1], from_obj=table2).label('foo')]), "SELECT mytable.myid, mytable.name, mytable.description, EXISTS (SELECT 1 FROM myothertable) AS foo FROM mytable", params={})
 
         self.assert_compile(
-          table1.select(exists([1], table2.c.otherid == table1.c.myid).correlate(table1)),
-          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT 1 FROM myothertable WHERE myothertable.otherid = mytable.myid)"
+          table1.select(exists().where(table2.c.otherid == table1.c.myid).correlate(table1)),
+          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT * FROM myothertable WHERE myothertable.otherid = mytable.myid)"
         )
 
         self.assert_compile(
-          table1.select(exists([1]).where(table2.c.otherid == table1.c.myid).correlate(table1)),
-          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT 1 FROM myothertable WHERE myothertable.otherid = mytable.myid)"
+          table1.select(exists().where(table2.c.otherid == table1.c.myid).correlate(table1)),
+          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT * FROM myothertable WHERE myothertable.otherid = mytable.myid)"
         )
 
         self.assert_compile(
-          table1.select(exists([1]).where(table2.c.otherid == table1.c.myid).correlate(table1)).replace_selectable(table2, table2.alias()),
-          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT 1 FROM myothertable AS myothertable_1 WHERE myothertable_1.otherid = mytable.myid)"
+          table1.select(exists().where(table2.c.otherid == table1.c.myid).correlate(table1)).replace_selectable(table2, table2.alias()),
+          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE EXISTS (SELECT * FROM myothertable AS myothertable_1 WHERE myothertable_1.otherid = mytable.myid)"
         )
 
         self.assert_compile(
-          table1.select(exists([1]).where(table2.c.otherid == table1.c.myid).correlate(table1)).select_from(table1.join(table2, table1.c.myid==table2.c.otherid)).replace_selectable(table2, table2.alias()),
-          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable JOIN myothertable AS myothertable_1 ON mytable.myid = myothertable_1.otherid WHERE EXISTS (SELECT 1 FROM myothertable AS myothertable_1 WHERE myothertable_1.otherid = mytable.myid)"
+          table1.select(exists().where(table2.c.otherid == table1.c.myid).correlate(table1)).select_from(table1.join(table2, table1.c.myid==table2.c.otherid)).replace_selectable(table2, table2.alias()),
+          "SELECT mytable.myid, mytable.name, mytable.description FROM mytable JOIN myothertable AS myothertable_1 ON mytable.myid = myothertable_1.otherid WHERE EXISTS (SELECT * FROM myothertable AS myothertable_1 WHERE myothertable_1.otherid = mytable.myid)"
         )
+        
+        self.assert_compile(
+            select([
+                or_(
+                    exists().where(table2.c.otherid=='foo'),
+                    exists().where(table2.c.otherid=='bar')
+                )
+            ]),
+            "SELECT ((EXISTS (SELECT * FROM myothertable WHERE myothertable.otherid = :otherid_1)) "\
+            "OR (EXISTS (SELECT * FROM myothertable WHERE myothertable.otherid = :otherid_2))) AS anon_1"
+        )
+        
 
     def test_where_subquery(self):
         s = select([addresses.c.street], addresses.c.user_id==users.c.user_id, correlate=True).alias('s')
@@ -330,6 +342,15 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
     
 
     def test_conjunctions(self):
+        a, b, c = 'a', 'b', 'c'
+        x = and_(a, b, c)
+        assert isinstance(x.type,  Boolean)
+        assert str(x) == 'a AND b AND c'
+        self.assert_compile(
+            select([x.label('foo')]),
+            'SELECT (a AND b AND c) AS foo'
+        )
+        
         self.assert_compile(
             and_(table1.c.myid == 12, table1.c.name=='asdf', table2.c.othername == 'foo', "sysdate() = today()"),
             "mytable.myid = :myid_1 AND mytable.name = :name_1 "\