From 23e3420fc93342dc66c6845863716bfb6d648a22 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Sat, 26 Aug 2006 14:42:18 +0000
Subject: [PATCH] added limit/offset to union queries

---
 CHANGES                   |  1 +
 lib/sqlalchemy/ansisql.py |  9 ++++++---
 lib/sqlalchemy/sql.py     |  2 ++
 test/sql/select.py        | 13 +++++++++++++
 4 files changed, 22 insertions(+), 3 deletions(-)

diff --git a/CHANGES b/CHANGES
index db4d15054c..405610760b 100644
--- a/CHANGES
+++ b/CHANGES
@@ -36,6 +36,7 @@ flag for use with table reflection to help with quoting rules
 - unit tests updated to run without any pysqlite installed; pool
 test uses a mock DBAPI
 - urls support escaped characters in passwords [ticket:281]
+- added limit/offset to UNION queries
 
 0.2.7
 - quoting facilities set up so that database-specific quoting can be
diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py
index f77e855e44..da6f4bf832 100644
--- a/lib/sqlalchemy/ansisql.py
+++ b/lib/sqlalchemy/ansisql.py
@@ -244,6 +244,7 @@ class ANSICompiler(sql.Compiled):
         order_by = self.get_str(cs.order_by_clause)
         if order_by:
             text += " ORDER BY " + order_by
+        text += self.visit_select_postclauses(cs)
         if cs.parens:
             self.strings[cs] = "(" + text + ")"
         else:
@@ -409,12 +410,14 @@ class ANSICompiler(sql.Compiled):
         return (select.limit or select.offset) and self.limit_clause(select) or ""
 
     def limit_clause(self, select):
+        text = ""
         if select.limit is not None:
-            return  " \n LIMIT " + str(select.limit)
+            text +=  " \n LIMIT " + str(select.limit)
         if select.offset is not None:
             if select.limit is None:
-                return " \n LIMIT -1"
-            return " OFFSET " + str(select.offset)
+                text += " \n LIMIT -1"
+            text += " OFFSET " + str(select.offset)
+        return text
 
     def visit_table(self, table):
         self.froms[table] = self.preparer.format_table(table)
diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py
index 2aa1342ca0..596e0e8eef 100644
--- a/lib/sqlalchemy/sql.py
+++ b/lib/sqlalchemy/sql.py
@@ -1347,6 +1347,8 @@ class CompoundSelect(SelectBaseMixin, FromClause):
         self.correlate = kwargs.pop('correlate', False)
         self.for_update = kwargs.pop('for_update', False)
         self.nowait = kwargs.pop('nowait', False)
+        self.limit = kwargs.get('limit', None)
+        self.offset = kwargs.get('offset', None)
         for s in self.selects:
             s.group_by(None)
             s.order_by(None)
diff --git a/test/sql/select.py b/test/sql/select.py
index 57b123fc28..e43d30c54e 100644
--- a/test/sql/select.py
+++ b/test/sql/select.py
@@ -461,6 +461,19 @@ FROM myothertable UNION SELECT thirdtable.userid, thirdtable.otherstuff FROM thi
             )
             assert u.corresponding_column(table2.c.otherid) is u.c.otherid
             
+            self.runtest(
+                union(
+                    select([table1]),
+                    select([table2]),
+                    order_by=['myid'],
+                    offset=10,
+                    limit=5
+                )
+            ,    "SELECT mytable.myid, mytable.name, mytable.description \
+FROM mytable UNION SELECT myothertable.otherid, myothertable.othername \
+FROM myothertable ORDER BY myid \
+ LIMIT 5 OFFSET 10"
+            )
             
     def testouterjoin(self):
         # test an outer join.  the oracle module should take the ON clause of the join and
-- 
2.47.2