Merge "Fix MSSQL / Oracle limit/offset regressions"

2 files changed, 22 insertions, 32 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 1be3965b8..bfa1cf08c 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1784,7 +1784,7 @@ class MSSQLCompiler(compiler.SQLCompiler):
     def limit_clause(self, cs, **kwargs):
         return ""
 
-    def _check_can_use_fetch_like(self, select):
+    def _check_can_use_fetch_limit(self, select):
         # to use ROW_NUMBER(), an ORDER BY is required.
         # OFFSET are FETCH are options of the ORDER BY clause
         if not select._order_by_clause.clauses:
@@ -1810,7 +1810,7 @@ class MSSQLCompiler(compiler.SQLCompiler):
         """
 
         if self.dialect._supports_offset_fetch and not self._use_top(select):
-            self._check_can_use_fetch_like(select)
+            self._check_can_use_fetch_limit(select)
 
             text = ""
 
@@ -1850,7 +1850,7 @@ class MSSQLCompiler(compiler.SQLCompiler):
             and not self._use_top(select)
             and not getattr(select, "_mssql_visit", None)
         ):
-            self._check_can_use_fetch_like(select)
+            self._check_can_use_fetch_limit(select)
 
             _order_by_clauses = [
                 sql_util.unwrap_label_reference(elem)
@@ -2031,7 +2031,10 @@ class MSSQLCompiler(compiler.SQLCompiler):
         if (
             self.is_subquery()
             and not select._limit
-            and (not select._offset or not self.dialect._supports_offset_fetch)
+            and (
+                select._offset is None
+                or not self.dialect._supports_offset_fetch
+            )
         ):
             # avoid processing the order by clause if we won't end up
             # using it, because we don't want all the bind params tacked
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 88b101811..46fcbbbe1 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -561,7 +561,6 @@ from ...types import CHAR
 from ...types import CLOB
 from ...types import FLOAT
 from ...types import INTEGER
-from ...types import Integer
 from ...types import NCHAR
 from ...types import NVARCHAR
 from ...types import TIMESTAMP
@@ -1092,6 +1091,13 @@ class OracleCompiler(compiler.SQLCompiler):
             ):
                 limit_clause = select._limit_clause
                 offset_clause = select._offset_clause
+
+                if select._simple_int_clause(limit_clause):
+                    limit_clause = limit_clause._render_literal_execute()
+
+                if select._simple_int_clause(offset_clause):
+                    offset_clause = offset_clause._render_literal_execute()
+
                 # currently using form at:
                 # https://blogs.oracle.com/oraclemagazine/\
                 # on-rownum-and-limiting-results
@@ -1120,22 +1126,17 @@ class OracleCompiler(compiler.SQLCompiler):
                         is not None
                     ]
                 )
+
                 if (
                     limit_clause is not None
                     and self.dialect.optimize_limits
                     and select._simple_int_clause(limit_clause)
                 ):
-                    param = sql.bindparam(
-                        "_ora_frow",
-                        select._limit,
-                        type_=Integer,
-                        literal_execute=True,
-                        unique=True,
-                    )
                     limitselect = limitselect.prefix_with(
                         expression.text(
-                            "/*+ FIRST_ROWS(:_ora_frow) */"
-                        ).bindparams(param)
+                            "/*+ FIRST_ROWS(%s) */"
+                            % self.process(limit_clause, **kwargs)
+                        )
                     )
 
                 limitselect._oracle_visit = True
@@ -1155,19 +1156,14 @@ class OracleCompiler(compiler.SQLCompiler):
                         offset_clause is None
                         or select._simple_int_clause(offset_clause)
                     ):
-                        max_row = select._limit
+                        max_row = limit_clause
 
                         if offset_clause is not None:
-                            max_row += select._offset
-                        max_row = sql.bindparam(
-                            None,
-                            max_row,
-                            type_=Integer,
-                            literal_execute=True,
-                            unique=True,
-                        )
+                            max_row = max_row + offset_clause
+
                     else:
                         max_row = limit_clause
+
                         if offset_clause is not None:
                             max_row = max_row + offset_clause
                     limitselect = limitselect.where(
@@ -1214,15 +1210,6 @@ class OracleCompiler(compiler.SQLCompiler):
                             adapter.traverse(elem) for elem in for_update.of
                         ]
 
-                    if select._simple_int_clause(offset_clause):
-                        offset_clause = sql.bindparam(
-                            None,
-                            select._offset,
-                            Integer,
-                            literal_execute=True,
-                            unique=True,
-                        )
-
                     offsetselect = offsetselect.where(
                         sql.literal_column("ora_rn") > offset_clause
                     )