- limit/offset no longer uses ROW NUMBER OVER to limit rows,

      and instead uses subqueries in conjunction with a special
      Oracle optimization comment.  Allows LIMIT/OFFSET to work
      in conjunction with DISTINCT. [ticket:536]

2 files changed, 89 insertions, 40 deletions

diff --git a/test/dialect/oracle.py b/test/dialect/oracle.py
index dcc191cdc..1a7a060b5 100644
--- a/test/dialect/oracle.py
+++ b/test/dialect/oracle.py
@@ -60,8 +60,9 @@ class CompileTest(TestBase, AssertsCompiledSQL):
         
         s = select([t]).limit(10).offset(20)
 
-        self.assert_compile(s, "SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2, "
-            "ROW_NUMBER() OVER (ORDER BY sometable.rowid) AS ora_rn FROM sometable) WHERE ora_rn>20 AND ora_rn<=30"
+        self.assert_compile(s, "SELECT col1, col2 FROM (SELECT /*+ FIRST_ROWS(10) */ col1, col2, ROWNUM AS ora_rn "
+            "FROM (SELECT  sometable.col1 AS col1, sometable.col2 AS col2 "
+            "FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1"
         )
 
         # assert that despite the subquery, the columns from the table,
@@ -71,17 +72,16 @@ class CompileTest(TestBase, AssertsCompiledSQL):
         
         s = select([s.c.col1, s.c.col2])
 
-        self.assert_compile(s, "SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2, ROW_NUMBER() OVER (ORDER BY sometable.rowid) AS ora_rn FROM sometable) WHERE ora_rn>20 AND ora_rn<=30)")
+        self.assert_compile(s, "SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT /*+ FIRST_ROWS(10) */ col1, col2, ROWNUM AS ora_rn FROM (SELECT  sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1)")
 
         # testing this twice to ensure oracle doesn't modify the original statement
-        self.assert_compile(s, "SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2, ROW_NUMBER() OVER (ORDER BY sometable.rowid) AS ora_rn FROM sometable) WHERE ora_rn>20 AND ora_rn<=30)")
+        self.assert_compile(s, "SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT /*+ FIRST_ROWS(10) */ col1, col2, ROWNUM AS ora_rn FROM (SELECT  sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1)")
 
         s = select([t]).limit(10).offset(20).order_by(t.c.col2)
 
-        self.assert_compile(s, "SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
-            "sometable.col2 AS col2, ROW_NUMBER() OVER (ORDER BY sometable.col2) AS ora_rn FROM sometable) WHERE ora_rn>20 AND ora_rn<=30")
+        self.assert_compile(s, "SELECT col1, col2 FROM (SELECT /*+ FIRST_ROWS(10) */ col1, col2, ROWNUM "
+            "AS ora_rn FROM (SELECT  sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable "
+            "ORDER BY sometable.col2) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1")
 
     def test_outer_join(self):
         table1 = table('mytable',
@@ -128,13 +128,18 @@ AND mytable.myid = myothertable.otherid(+)",
         self.assert_compile(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid = myothertable.otherid AND mytable.myid = myothertable.otherid", dialect=oracle.dialect(use_ansi=False))
 
         query = table1.join(table2, table1.c.myid==table2.c.otherid).outerjoin(table3, table3.c.userid==table2.c.otherid)
-        self.assert_compile(query.select().order_by(table1.oid_column).limit(10).offset(5), "SELECT myid, name, description, otherid, othername, userid, \
-otherstuff FROM (SELECT mytable.myid AS myid, mytable.name AS name, \
-mytable.description AS description, myothertable.otherid AS otherid, \
-myothertable.othername AS othername, thirdtable.userid AS userid, \
-thirdtable.otherstuff AS otherstuff, ROW_NUMBER() OVER (ORDER BY mytable.rowid) AS ora_rn \
-FROM mytable, myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND mytable.myid = myothertable.otherid) \
-WHERE ora_rn>5 AND ora_rn<=15", dialect=oracle.dialect(use_ansi=False))
+
+        self.assert_compile(query.select().order_by(table1.oid_column).limit(10).offset(5), 
+        
+            "SELECT myid, name, description, otherid, othername, userid, "
+            "otherstuff FROM (SELECT /*+ FIRST_ROWS(10) */ myid, name, description, "
+            "otherid, othername, userid, otherstuff, ROWNUM AS ora_rn FROM (SELECT  "
+            "mytable.myid AS myid, mytable.name AS name, mytable.description AS description, "
+            "myothertable.otherid AS otherid, myothertable.othername AS othername, "
+            "thirdtable.userid AS userid, thirdtable.otherstuff AS otherstuff FROM mytable, "
+            "myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND "
+            "mytable.myid = myothertable.otherid ORDER BY mytable.rowid) WHERE "
+            "ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1", dialect=oracle.dialect(use_ansi=False))
 
     def test_alias_outer_join(self):
         address_types = table('address_types',
diff --git a/test/sql/query.py b/test/sql/query.py
index 6bed07a9b..fa247a7b2 100644
--- a/test/sql/query.py
+++ b/test/sql/query.py
@@ -317,31 +317,7 @@ class QueryTest(TestBase):
 
         print repr(users.select().execute().fetchall())
 
-    def test_select_limit(self):
-        users.insert().execute(user_id=1, user_name='john')
-        users.insert().execute(user_id=2, user_name='jack')
-        users.insert().execute(user_id=3, user_name='ed')
-        users.insert().execute(user_id=4, user_name='wendy')
-        users.insert().execute(user_id=5, user_name='laura')
-        users.insert().execute(user_id=6, user_name='ralph')
-        users.insert().execute(user_id=7, user_name='fido')
-        r = users.select(limit=3, order_by=[users.c.user_id]).execute().fetchall()
-        self.assert_(r == [(1, 'john'), (2, 'jack'), (3, 'ed')], repr(r))
 
-    @testing.crashes('mssql', 'FIXME: guessing')
-    @testing.fails_on('maxdb')
-    def test_select_limit_offset(self):
-        users.insert().execute(user_id=1, user_name='john')
-        users.insert().execute(user_id=2, user_name='jack')
-        users.insert().execute(user_id=3, user_name='ed')
-        users.insert().execute(user_id=4, user_name='wendy')
-        users.insert().execute(user_id=5, user_name='laura')
-        users.insert().execute(user_id=6, user_name='ralph')
-        users.insert().execute(user_id=7, user_name='fido')
-        r = users.select(limit=3, offset=2, order_by=[users.c.user_id]).execute().fetchall()
-        self.assert_(r==[(3, 'ed'), (4, 'wendy'), (5, 'laura')])
-        r = users.select(offset=5, order_by=[users.c.user_id]).execute().fetchall()
-        self.assert_(r==[(6, 'ralph'), (7, 'fido')])
 
     @testing.exclude('mysql', '<', (5, 0, 37), 'database bug')
     def test_scalar_select(self):
@@ -556,7 +532,7 @@ class QueryTest(TestBase):
         finally:
             shadowed.drop(checkfirst=True)
 
-    @testing.fails_on('firebird', 'maxdb')
+    @testing.fails_on('firebird', 'maxdb', 'oracle')
     def test_in_filtering(self):
         """test the behavior of the in_() function."""
 
@@ -608,6 +584,74 @@ class QueryTest(TestBase):
         assert len(r) == 1
 
 
+class LimitTest(TestBase):
+
+    def setUpAll(self):
+        global users, addresses, metadata
+        metadata = MetaData(testing.db)
+        users = Table('query_users', metadata,
+            Column('user_id', INT, primary_key = True),
+            Column('user_name', VARCHAR(20)),
+        )
+        addresses = Table('query_addresses', metadata,
+            Column('address_id', Integer, primary_key=True),
+            Column('user_id', Integer, ForeignKey('query_users.user_id')),
+            Column('address', String(30)))
+        metadata.create_all()
+        self._data()
+        
+    def _data(self):
+        users.insert().execute(user_id=1, user_name='john')
+        addresses.insert().execute(address_id=1, user_id=1, address='addr1')
+        users.insert().execute(user_id=2, user_name='jack')
+        addresses.insert().execute(address_id=2, user_id=2, address='addr1')
+        users.insert().execute(user_id=3, user_name='ed')
+        addresses.insert().execute(address_id=3, user_id=3, address='addr2')
+        users.insert().execute(user_id=4, user_name='wendy')
+        addresses.insert().execute(address_id=4, user_id=4, address='addr3')
+        users.insert().execute(user_id=5, user_name='laura')
+        addresses.insert().execute(address_id=5, user_id=5, address='addr4')
+        users.insert().execute(user_id=6, user_name='ralph')
+        addresses.insert().execute(address_id=6, user_id=6, address='addr5')
+        users.insert().execute(user_id=7, user_name='fido')
+        addresses.insert().execute(address_id=7, user_id=7, address='addr5')
+        
+    def tearDownAll(self):
+        metadata.drop_all()
+
+    def test_select_limit(self):
+        r = users.select(limit=3, order_by=[users.c.user_id]).execute().fetchall()
+        self.assert_(r == [(1, 'john'), (2, 'jack'), (3, 'ed')], repr(r))
+
+    @testing.crashes('mssql', 'FIXME: guessing')
+    @testing.fails_on('maxdb')
+    def test_select_limit_offset(self):
+        r = users.select(limit=3, offset=2, order_by=[users.c.user_id]).execute().fetchall()
+        self.assert_(r==[(3, 'ed'), (4, 'wendy'), (5, 'laura')])
+        r = users.select(offset=5, order_by=[users.c.user_id]).execute().fetchall()
+        self.assert_(r==[(6, 'ralph'), (7, 'fido')])
+
+    def test_select_distinct_limit(self):
+        """Test the interaction between limit and distinct"""
+
+        r = sorted([x[0] for x in select([addresses.c.address]).distinct().limit(3).execute().fetchall()])
+        self.assert_(len(r) == 3, repr(r))
+        self.assert_(r[0] != r[1] and r[1] != r[2], repr(r))
+
+    def test_select_distinct_offset(self):
+        """Test the interaction between limit and offset"""
+
+        r = sorted([x[0] for x in select([addresses.c.address]).distinct().offset(1).execute().fetchall()])
+        self.assert_(len(r) == 4, repr(r))
+        self.assert_(r[0] != r[1] and r[1] != r[2] and r[2] != [3], repr(r))
+
+    def test_select_distinct_limit_offset(self):
+        """Test the interaction between limit and limit/offset"""
+
+        r = select([addresses.c.address]).order_by(addresses.c.address).distinct().offset(2).limit(3).execute().fetchall()
+        self.assert_(len(r) == 3, repr(r))
+        self.assert_(r[0] != r[1] and r[1] != r[2], repr(r))
+
 class CompoundTest(TestBase):
     """test compound statements like UNION, INTERSECT, particularly their ability to nest on
     different databases."""