- union(), intersect(), except() and other "compound" types

of statements have more consistent behavior w.r.t.
parenthesizing.   Each compound element embedded within
another will now be grouped with parenthesis - previously,
the first compound element in the list would not be grouped,
as SQLite doesn't like a statement to start with
parenthesis.   However, Postgresql in particular has
precedence rules regarding INTERSECT, and it is
more consistent for parenthesis to be applied equally
to all sub-elements.   So now, the workaround for SQLite
is also what the workaround for PG was previously -
when nesting compound elements, the first one usually needs
".alias().select()" called on it to wrap it inside
of a subquery.  [ticket:1665]

1 files changed, 69 insertions, 8 deletions

diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index 953dcab7f..2500cde60 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -1116,6 +1116,7 @@ class CompoundTest(TestBase):
     @testing.crashes('oracle', 'FIXME: unknown, verify not fails_on')
     @testing.crashes('sybase', 'FIXME: unknown, verify not fails_on')
     @testing.fails_on('mysql', 'FIXME: unknown')
+    @testing.fails_on('sqlite', "Can't handle this style of nesting")
     def test_except_style1(self):
         e = except_(union(
             select([t1.c.col3, t1.c.col4]),
@@ -1126,7 +1127,7 @@ class CompoundTest(TestBase):
         wanted = [('aaa', 'aaa'), ('aaa', 'ccc'), ('bbb', 'aaa'),
                   ('bbb', 'bbb'), ('ccc', 'bbb'), ('ccc', 'ccc')]
 
-        found = self._fetchall_sorted(e.alias('bar').select().execute())
+        found = self._fetchall_sorted(e.alias().select().execute())
         eq_(found, wanted)
 
     @testing.crashes('firebird', 'Does not support except')
@@ -1134,11 +1135,14 @@ class CompoundTest(TestBase):
     @testing.crashes('sybase', 'FIXME: unknown, verify not fails_on')
     @testing.fails_on('mysql', 'FIXME: unknown')
     def test_except_style2(self):
+        # same as style1, but add alias().select() to the except_().
+        # sqlite can handle it now.
+        
         e = except_(union(
             select([t1.c.col3, t1.c.col4]),
             select([t2.c.col3, t2.c.col4]),
             select([t3.c.col3, t3.c.col4]),
-        ).alias('foo').select(), select([t2.c.col3, t2.c.col4]))
+        ).alias().select(), select([t2.c.col3, t2.c.col4]))
 
         wanted = [('aaa', 'aaa'), ('aaa', 'ccc'), ('bbb', 'aaa'),
                   ('bbb', 'bbb'), ('ccc', 'bbb'), ('ccc', 'ccc')]
@@ -1146,14 +1150,14 @@ class CompoundTest(TestBase):
         found1 = self._fetchall_sorted(e.execute())
         eq_(found1, wanted)
 
-        found2 = self._fetchall_sorted(e.alias('bar').select().execute())
+        found2 = self._fetchall_sorted(e.alias().select().execute())
         eq_(found2, wanted)
 
     @testing.crashes('firebird', 'Does not support except')
     @testing.crashes('oracle', 'FIXME: unknown, verify not fails_on')
     @testing.crashes('sybase', 'FIXME: unknown, verify not fails_on')
     @testing.fails_on('mysql', 'FIXME: unknown')
-    @testing.fails_on('sqlite', 'FIXME: unknown')
+    @testing.fails_on('sqlite', "Can't handle this style of nesting")
     def test_except_style3(self):
         # aaa, bbb, ccc - (aaa, bbb, ccc - (ccc)) = ccc
         e = except_(
@@ -1167,16 +1171,73 @@ class CompoundTest(TestBase):
         eq_(e.alias('foo').select().execute().fetchall(),
                           [('ccc',)])
 
+    @testing.crashes('firebird', 'Does not support except')
+    @testing.crashes('oracle', 'FIXME: unknown, verify not fails_on')
+    @testing.crashes('sybase', 'FIXME: unknown, verify not fails_on')
+    @testing.fails_on('mysql', 'FIXME: unknown')
+    def test_except_style4(self):
+        # aaa, bbb, ccc - (aaa, bbb, ccc - (ccc)) = ccc
+        e = except_(
+            select([t1.c.col3]), # aaa, bbb, ccc
+            except_(
+                select([t2.c.col3]), # aaa, bbb, ccc
+                select([t3.c.col3], t3.c.col3 == 'ccc'), #ccc
+            ).alias().select()
+        )
+        
+        eq_(e.execute().fetchall(), [('ccc',)])
+        eq_(
+            e.alias().select().execute().fetchall(),
+            [('ccc',)]
+        )
+
+    @testing.crashes('firebird', 'Does not support intersect')
+    @testing.fails_on('mysql', 'FIXME: unknown')
+    @testing.fails_on('sqlite', "sqlite can't handle leading parenthesis")
+    def test_intersect_unions(self):
+        u = intersect(
+            union(
+                select([t1.c.col3, t1.c.col4]),
+                select([t3.c.col3, t3.c.col4]),
+            ),
+            union(
+                select([t2.c.col3, t2.c.col4]),
+                select([t3.c.col3, t3.c.col4]),
+            ).alias().select()
+        )
+        wanted = [('aaa', 'ccc'), ('bbb', 'aaa'), ('ccc', 'bbb')]
+        found = self._fetchall_sorted(u.execute())
+
+        eq_(found, wanted)
+
+    @testing.crashes('firebird', 'Does not support intersect')
+    @testing.fails_on('mysql', 'FIXME: unknown')
+    def test_intersect_unions_2(self):
+        u = intersect(
+            union(
+                select([t1.c.col3, t1.c.col4]),
+                select([t3.c.col3, t3.c.col4]),
+            ).alias().select(),
+            union(
+                select([t2.c.col3, t2.c.col4]),
+                select([t3.c.col3, t3.c.col4]),
+            ).alias().select()
+        )
+        wanted = [('aaa', 'ccc'), ('bbb', 'aaa'), ('ccc', 'bbb')]
+        found = self._fetchall_sorted(u.execute())
+
+        eq_(found, wanted)
+    
     @testing.crashes('firebird', 'Does not support intersect')
     @testing.fails_on('mysql', 'FIXME: unknown')
-    def test_composite(self):
+    def test_intersect(self):
         u = intersect(
             select([t2.c.col3, t2.c.col4]),
             union(
                 select([t1.c.col3, t1.c.col4]),
                 select([t2.c.col3, t2.c.col4]),
                 select([t3.c.col3, t3.c.col4]),
-            ).alias('foo').select()
+            ).alias().select()
         )
         wanted = [('aaa', 'bbb'), ('bbb', 'ccc'), ('ccc', 'aaa')]
         found = self._fetchall_sorted(u.execute())
@@ -1192,8 +1253,8 @@ class CompoundTest(TestBase):
                 select([t1.c.col3, t1.c.col4]),
                 select([t2.c.col3, t2.c.col4]),
                 select([t3.c.col3, t3.c.col4]),
-            ).alias('foo').select()
-        ).alias('bar')
+            ).alias().select()
+        ).alias()
 
         wanted = [('aaa', 'bbb'), ('bbb', 'ccc'), ('ccc', 'aaa')]
         found = self._fetchall_sorted(ua.select().execute())