- union() and union_all() will not whack

any order_by() that has been applied to the
select()s inside.  If you union() a
select() with order_by() (presumably to support
LIMIT/OFFSET), you should also call self_group()
on it to apply parenthesis.

3 files changed, 18 insertions, 2 deletions

diff --git a/CHANGES b/CHANGES
index 4ec043e97..be545de64 100644
--- a/CHANGES
+++ b/CHANGES
@@ -111,6 +111,13 @@ CHANGES
     - the "passive" flag on session.is_modified()
       is correctly propagated to the attribute manager.
 
+    - union() and union_all() will not whack 
+      any order_by() that has been applied to the 
+      select()s inside.  If you union() a 
+      select() with order_by() (presumably to support
+      LIMIT/OFFSET), you should also call self_group() 
+      on it to apply parenthesis.
+      
 - documentation
     - Tickets [ticket:1200] [ticket:1149].
       
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 3d66ff21b..0bc7e6a49 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -3033,8 +3033,7 @@ class CompoundSelect(_SelectBaseMixin, FromClause):
                         "have identical numbers of columns; select #%d has %d columns, select #%d has %d" %
                         (1, len(self.selects[0].c), n+1, len(s.c))
                 )
-            if s._order_by_clause:
-                s = s.order_by(None)
+
             # unions group from left to right, so don't group first select
             if n:
                 self.selects.append(s.self_group(self))
diff --git a/test/sql/select.py b/test/sql/select.py
index 91585e37e..352eed42c 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -982,6 +982,16 @@ UNION SELECT mytable.myid FROM mytable"
         s = union(s, s)
         self.assert_compile(s, "SELECT foo, bar UNION SELECT foo, bar UNION (SELECT foo, bar UNION SELECT foo, bar)")
         
+        s = select([column('foo'), column('bar')])
+        # ORDER BY's even though not supported by all DB's, are rendered if requested
+        self.assert_compile(union(s.order_by("foo"), s.order_by("bar")), 
+            "SELECT foo, bar ORDER BY foo UNION SELECT foo, bar ORDER BY bar"
+        )
+        # self_group() is honored
+        self.assert_compile(union(s.order_by("foo").self_group(), s.order_by("bar").limit(10).self_group()), 
+            "(SELECT foo, bar ORDER BY foo) UNION (SELECT foo, bar ORDER BY bar  LIMIT 10)"
+        )
+        
 
     @testing.uses_deprecated()
     def test_binds(self):