Fix MSSQL / Oracle limit/offset regressions

Fixed a regression in MSSQL 2012+ that prevented the order clause
to be rendered when ``offset=0`` is used in a subquery.

Fixed critical regression where the Oracle compiler would not maintain the
correct parameter values in the LIMIT/OFFSET for a select due to a caching
issue.

Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Fixes: #6163
Fixes: #6173
Change-Id: Ieb12354271d09ad935d684ee0db4fa0128837215

1 files changed, 7 insertions, 4 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