Merge "Fix MSSQL / Oracle limit/offset regressions"

3 files changed, 51 insertions, 30 deletions

diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 759833eca..582205ea1 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -440,7 +440,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "foo.myid = mytable.myid",
         )
 
-    def test_noorderby_insubquery_offset_oldstyle(self):
+    @testing.combinations(10, 0)
+    def test_noorderby_insubquery_offset_oldstyle(self, offset):
         """test "no ORDER BY in subqueries unless TOP / LIMIT / OFFSET"
         present"""
 
@@ -454,7 +455,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         q = (
             select(table1.c.myid)
             .order_by(table1.c.myid)
-            .offset(10)
+            .offset(offset)
             .alias("foo")
         )
         crit = q.c.myid == table1.c.myid
@@ -467,7 +468,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "foo.myid = mytable.myid",
         )
 
-    def test_noorderby_insubquery_offset_newstyle(self, dialect_2012):
+    @testing.combinations(10, 0, argnames="offset")
+    def test_noorderby_insubquery_offset_newstyle(self, dialect_2012, offset):
         """test "no ORDER BY in subqueries unless TOP / LIMIT / OFFSET"
         present"""
 
@@ -481,7 +483,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         q = (
             select(table1.c.myid)
             .order_by(table1.c.myid)
-            .offset(10)
+            .offset(offset)
             .alias("foo")
         )
         crit = q.c.myid == table1.c.myid
diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py
index c8e0ff7ef..5e9f46e1a 100644
--- a/test/dialect/oracle/test_compiler.py
+++ b/test/dialect/oracle/test_compiler.py
@@ -163,9 +163,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "anon_2.col2 AS col2, ROWNUM AS ora_rn FROM (SELECT "
             "sometable.col1 AS col1, sometable.col2 AS "
             "col2 FROM sometable) anon_2 WHERE ROWNUM <= "
-            "[POSTCOMPILE_param_1]) anon_1 WHERE ora_rn > "
+            "[POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) anon_1 "
+            "WHERE ora_rn > "
             "[POSTCOMPILE_param_2]",
-            checkparams={"param_1": 30, "param_2": 20},
+            checkparams={"param_1": 10, "param_2": 20},
         )
 
         c = s.compile(dialect=oracle.OracleDialect())
@@ -179,14 +180,15 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(
             s,
             "SELECT anon_1.col1, anon_1.col2 FROM "
-            "(SELECT /*+ FIRST_ROWS([POSTCOMPILE_ora_frow_1]) */ "
+            "(SELECT /*+ FIRST_ROWS([POSTCOMPILE_param_1]) */ "
             "anon_2.col1 AS col1, "
             "anon_2.col2 AS col2, ROWNUM AS ora_rn FROM (SELECT "
             "sometable.col1 AS col1, sometable.col2 AS "
             "col2 FROM sometable) anon_2 WHERE ROWNUM <= "
-            "[POSTCOMPILE_param_1]) anon_1 WHERE ora_rn > "
+            "[POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) anon_1 "
+            "WHERE ora_rn > "
             "[POSTCOMPILE_param_2]",
-            checkparams={"ora_frow_1": 10, "param_1": 30, "param_2": 20},
+            checkparams={"param_1": 10, "param_2": 20},
             dialect=oracle.OracleDialect(optimize_limits=True),
         )
 
@@ -204,9 +206,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, "
             "sometable.col2 AS col2 FROM sometable) anon_3 "
-            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_2 "
+            "WHERE ROWNUM <= [POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) "
+            "anon_2 "
             "WHERE ora_rn > [POSTCOMPILE_param_2]) anon_1",
-            checkparams={"param_1": 30, "param_2": 20},
+            checkparams={"param_1": 10, "param_2": 20},
         )
 
         self.assert_compile(
@@ -218,7 +221,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, "
             "sometable.col2 AS col2 FROM sometable) anon_3 "
-            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_2 "
+            "WHERE ROWNUM <= [POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) "
+            "anon_2 "
             "WHERE ora_rn > [POSTCOMPILE_param_2]) anon_1",
         )
         c = s2.compile(dialect=oracle.OracleDialect())
@@ -237,9 +241,9 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "sometable.col1 AS col1, sometable.col2 AS "
             "col2 FROM sometable ORDER BY "
             "sometable.col2) anon_2 WHERE ROWNUM <= "
-            "[POSTCOMPILE_param_1]) anon_1 "
+            "[POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) anon_1 "
             "WHERE ora_rn > [POSTCOMPILE_param_2]",
-            checkparams={"param_1": 30, "param_2": 20},
+            checkparams={"param_1": 10, "param_2": 20},
         )
         c = s.compile(dialect=oracle.OracleDialect())
         eq_(len(c._result_columns), 2)
@@ -265,13 +269,13 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         s = select(t).with_for_update().limit(10).order_by(t.c.col2)
         self.assert_compile(
             s,
-            "SELECT /*+ FIRST_ROWS([POSTCOMPILE_ora_frow_1]) */ "
+            "SELECT /*+ FIRST_ROWS([POSTCOMPILE_param_1]) */ "
             "anon_1.col1, anon_1.col2 FROM (SELECT "
             "sometable.col1 AS col1, sometable.col2 AS "
             "col2 FROM sometable ORDER BY "
             "sometable.col2) anon_1 WHERE ROWNUM <= [POSTCOMPILE_param_1] "
             "FOR UPDATE",
-            checkparams={"param_1": 10, "ora_frow_1": 10},
+            checkparams={"param_1": 10},
             dialect=oracle.OracleDialect(optimize_limits=True),
         )
 
@@ -287,10 +291,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "sometable.col1 AS col1, sometable.col2 AS "
             "col2 FROM sometable ORDER BY "
             "sometable.col2) anon_2 WHERE ROWNUM <= "
-            "[POSTCOMPILE_param_1]) anon_1 "
+            "[POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) anon_1 "
             "WHERE ora_rn > [POSTCOMPILE_param_2] FOR "
             "UPDATE",
-            checkparams={"param_1": 30, "param_2": 20},
+            checkparams={"param_1": 10, "param_2": 20},
         )
 
     def test_limit_six(self):
@@ -308,7 +312,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "col1, anon_2.col2 AS col2, ROWNUM AS ora_rn FROM "
             "(SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
             "FROM sometable ORDER BY sometable.col2) anon_2 WHERE "
-            "ROWNUM <= :param_1 + :param_2 + :param_3) anon_1 "
+            "ROWNUM <= [POSTCOMPILE_param_1] + :param_2 + :param_3) anon_1 "
             "WHERE ora_rn > :param_2 + :param_3",
             checkparams={"param_1": 10, "param_2": 10, "param_3": 20},
         )
@@ -512,10 +516,11 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "ROWNUM AS ora_rn "
             "FROM (SELECT mytable.myid AS myid, mytable.name AS name "
             "FROM mytable WHERE mytable.myid = :myid_1) anon_2 "
-            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_1 "
+            "WHERE ROWNUM <= [POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) "
+            "anon_1 "
             "WHERE ora_rn > [POSTCOMPILE_param_2] "
             "FOR UPDATE OF anon_1.name NOWAIT",
-            checkparams={"param_1": 60, "param_2": 50, "myid_1": 7},
+            checkparams={"param_1": 10, "param_2": 50, "myid_1": 7},
         )
 
     def test_for_update_of_w_limit_offset_adaption_col_unpresent(self):
@@ -531,10 +536,11 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "ROWNUM AS ora_rn, anon_2.name AS name "
             "FROM (SELECT mytable.myid AS myid, mytable.name AS name "
             "FROM mytable WHERE mytable.myid = :myid_1) anon_2 "
-            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_1 "
+            "WHERE "
+            "ROWNUM <= [POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) anon_1 "
             "WHERE ora_rn > [POSTCOMPILE_param_2] "
             "FOR UPDATE OF anon_1.name NOWAIT",
-            checkparams={"param_1": 60, "param_2": 50, "myid_1": 7},
+            checkparams={"param_1": 10, "param_2": 50, "myid_1": 7},
         )
 
     def test_for_update_of_w_limit_offset_adaption_partial_col_unpresent(self):
@@ -552,10 +558,11 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "mytable.bar AS bar, "
             "mytable.foo AS foo FROM mytable "
             "WHERE mytable.myid = :myid_1) anon_2 "
-            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_1 "
+            "WHERE ROWNUM <= [POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) "
+            "anon_1 "
             "WHERE ora_rn > [POSTCOMPILE_param_2] "
             "FOR UPDATE OF anon_1.foo, anon_1.bar NOWAIT",
-            checkparams={"param_1": 60, "param_2": 50, "myid_1": 7},
+            checkparams={"param_1": 10, "param_2": 50, "myid_1": 7},
         )
 
     def test_limit_preserves_typing_information(self):
@@ -616,7 +623,8 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "anon_2.col1 AS col1, anon_2.col2 AS col2, ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
             "FROM sometable) anon_2 "
-            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_1 "
+            "WHERE ROWNUM <= [POSTCOMPILE_param_1] + "
+            "[POSTCOMPILE_param_2]) anon_1 "
             "WHERE ora_rn > [POSTCOMPILE_param_2]",
             dialect=dialect,
         )
@@ -672,10 +680,11 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "ROWNUM AS ora_rn "
             "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
             "FROM sometable) anon_2 "
-            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_1 "
+            "WHERE ROWNUM <= [POSTCOMPILE_param_1] + "
+            "[POSTCOMPILE_param_2]) anon_1 "
             "WHERE ora_rn > [POSTCOMPILE_param_2]",
             dialect=dialect,
-            checkparams={"param_1": 20, "param_2": 10},
+            checkparams={"param_1": 10, "param_2": 10},
         )
 
     def test_long_labels_legacy_ident_length(self):
@@ -875,9 +884,10 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "thirdtable.userid(+) = "
             "myothertable.otherid AND mytable.myid = "
             "myothertable.otherid ORDER BY mytable.name) anon_2 "
-            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_1 "
+            "WHERE ROWNUM <= [POSTCOMPILE_param_1] + [POSTCOMPILE_param_2]) "
+            "anon_1 "
             "WHERE ora_rn > [POSTCOMPILE_param_2]",
-            checkparams={"param_1": 15, "param_2": 5},
+            checkparams={"param_1": 10, "param_2": 5},
             dialect=oracle.dialect(use_ansi=False),
         )
 
diff --git a/test/dialect/oracle/test_dialect.py b/test/dialect/oracle/test_dialect.py
index cce33d4a2..554e5f18b 100644
--- a/test/dialect/oracle/test_dialect.py
+++ b/test/dialect/oracle/test_dialect.py
@@ -33,6 +33,7 @@ from sqlalchemy.testing import mock
 from sqlalchemy.testing.mock import Mock
 from sqlalchemy.testing.schema import Column
 from sqlalchemy.testing.schema import Table
+from sqlalchemy.testing.suite import test_select
 from sqlalchemy.util import u
 from sqlalchemy.util import ue
 
@@ -1054,3 +1055,11 @@ END;
         fn = func.three_pairs().table_valued("string1", "string2")
         result = connection.execute(select(fn.c.string1, fn.c.string2)).all()
         eq_(result, [("a", "b"), ("c", "d"), ("e", "f")])
+
+
+class OptimizedFetchLimitOffsetTest(test_select.FetchLimitOffsetTest):
+    __only_on__ = "oracle"
+
+    @classmethod
+    def setup_bind(cls):
+        return engines.testing_engine(options={"optimize_limits": True})