Merge "Use FETCH FIRST N ROWS / OFFSET for Oracle LIMIT/OFFSET" into main

3 files changed, 106 insertions, 44 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 65f37cfb5..e33ae4dbb 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1994,9 +1994,6 @@ class MSSQLCompiler(compiler.SQLCompiler):
             )
         )
 
-    def fetch_clause(self, cs, **kwargs):
-        return ""
-
     def limit_clause(self, cs, **kwargs):
         return ""
 
@@ -2028,21 +2025,13 @@ class MSSQLCompiler(compiler.SQLCompiler):
         if self.dialect._supports_offset_fetch and not self._use_top(select):
             self._check_can_use_fetch_limit(select)
 
-            text = ""
-
-            if select._offset_clause is not None:
-                offset_str = self.process(select._offset_clause, **kw)
-            else:
-                offset_str = "0"
-            text += "\n OFFSET %s ROWS" % offset_str
-
-            limit = self._get_limit_or_fetch(select)
+            return self.fetch_clause(
+                select,
+                fetch_clause=self._get_limit_or_fetch(select),
+                require_offset=True,
+                **kw,
+            )
 
-            if limit is not None:
-                text += "\n FETCH FIRST %s ROWS ONLY" % self.process(
-                    limit, **kw
-                )
-            return text
         else:
             return ""
 
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 9957417b4..c06da6ffe 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -260,23 +260,38 @@ impact of this change has been mitigated.
 LIMIT/OFFSET/FETCH Support
 --------------------------
 
-Methods like :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` currently
-use an emulated approach for LIMIT / OFFSET based on window functions, which
-involves creation of a subquery using ``ROW_NUMBER`` that is prone to
-performance issues as well as SQL construction issues for complex statements.
-However, this approach is supported by all Oracle versions.  See notes below.
-
-When using Oracle 12c and above, use the :meth:`_sql.Select.fetch` method
-instead; this will render the more modern
-``FETCH FIRST N ROW / OFFSET N ROWS`` syntax.
+Methods like :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` make
+use of ``FETCH FIRST N ROW / OFFSET N ROWS`` syntax assuming
+Oracle 12c or above, and assuming the SELECT statement is not embedded within
+a compound statement like UNION.  This syntax is also available directly by using
+the :meth:`_sql.Select.fetch` method.
+
+.. versionchanged:: 2.0  the Oracle dialect now uses
+   ``FETCH FIRST N ROW / OFFSET N ROWS`` for all
+   :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` usage including
+   within the ORM and legacy :class:`_orm.Query`.  To force the legacy
+   behavior using window functions, specify the ``enable_offset_fetch=False``
+   dialect parameter to :func:`_sa.create_engine`.
+
+The use of ``FETCH FIRST / OFFSET`` may be disabled on any Oracle version
+by passing ``enable_offset_fetch=False`` to :func:`_sa.create_engine`, which
+will force the use of "legacy" mode that makes use of window functions.
+This mode is also selected automatically when using a version of Oracle
+prior to 12c.
+
+When using legacy mode, or when a :class:`.Select` statement
+with limit/offset is embedded in a compound statement, an emulated approach for
+LIMIT / OFFSET based on window functions is used, which involves creation of a
+subquery using ``ROW_NUMBER`` that is prone to performance issues as well as
+SQL construction issues for complex statements. However, this approach is
+supported by all Oracle versions. See notes below.
 
 Notes on LIMIT / OFFSET emulation (when fetch() method cannot be used)
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
-If using :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset`,
-or with the ORM the :meth:`_orm.Query.limit` and :meth:`_orm.Query.offset` methods,
-and the :meth:`_sql.Select.fetch` method **cannot** be used instead, the following
-notes apply:
+If using :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset`, or with the
+ORM the :meth:`_orm.Query.limit` and :meth:`_orm.Query.offset` methods on an
+Oracle version prior to 12c, the following notes apply:
 
 * SQLAlchemy currently makes use of ROWNUM to achieve
   LIMIT/OFFSET; the exact methodology is taken from
@@ -296,10 +311,6 @@ notes apply:
 
           :ref:`change_4808`.
 
-* A future release may use ``FETCH FIRST N ROW / OFFSET N ROWS`` automatically
-  when :meth:`_sql.Select.limit`, :meth:`_sql.Select.offset`, :meth:`_orm.Query.limit`,
-  :meth:`_orm.Query.offset` are used.
-
 .. _oracle_returning:
 
 RETURNING Support
@@ -1001,6 +1012,33 @@ class OracleCompiler(compiler.SQLCompiler):
 
         return "RETURNING " + ", ".join(columns) + " INTO " + ", ".join(binds)
 
+    def _row_limit_clause(self, select, **kw):
+        """ORacle 12c supports OFFSET/FETCH operators
+        Use it instead subquery with row_number
+
+        """
+
+        if (
+            select._fetch_clause is not None
+            or not self.dialect._supports_offset_fetch
+        ):
+            return super()._row_limit_clause(
+                select, use_literal_execute_for_simple_int=True, **kw
+            )
+        else:
+            return self.fetch_clause(
+                select,
+                fetch_clause=self._get_limit_or_fetch(select),
+                use_literal_execute_for_simple_int=True,
+                **kw,
+            )
+
+    def _get_limit_or_fetch(self, select):
+        if select._fetch_clause is None:
+            return select._limit_clause
+        else:
+            return select._fetch_clause
+
     def translate_select_structure(self, select_stmt, **kwargs):
         select = select_stmt
 
@@ -1017,6 +1055,7 @@ class OracleCompiler(compiler.SQLCompiler):
             # if fetch is used this is not needed
             if (
                 select._has_row_limiting_clause
+                and not self.dialect._supports_offset_fetch
                 and select._fetch_clause is None
             ):
                 limit_clause = select._limit_clause
@@ -1375,6 +1414,8 @@ class OracleDialect(default.DefaultDialect):
     supports_alter = True
     max_identifier_length = 128
 
+    _supports_offset_fetch = True
+
     insert_returning = True
     update_returning = True
     delete_returning = True
@@ -1435,6 +1476,7 @@ class OracleDialect(default.DefaultDialect):
         use_binds_for_limits=None,
         use_nchar_for_unicode=False,
         exclude_tablespaces=("SYSTEM", "SYSAUX"),
+        enable_offset_fetch=True,
         **kwargs,
     ):
         default.DefaultDialect.__init__(self, **kwargs)
@@ -1442,6 +1484,9 @@ class OracleDialect(default.DefaultDialect):
         self.use_ansi = use_ansi
         self.optimize_limits = optimize_limits
         self.exclude_tablespaces = exclude_tablespaces
+        self.enable_offset_fetch = (
+            self._supports_offset_fetch
+        ) = enable_offset_fetch
 
     def initialize(self, connection):
         super(OracleDialect, self).initialize(connection)
@@ -1458,6 +1503,9 @@ class OracleDialect(default.DefaultDialect):
             self.use_ansi = False
 
         self.supports_identity_columns = self.server_version_info >= (12,)
+        self._supports_offset_fetch = (
+            self.enable_offset_fetch and self.server_version_info >= (12,)
+        )
 
     def _get_effective_compat_server_version_info(self, connection):
         # dialect does not need compat levels below 12.2, so don't query
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 87d031cc2..08b876e69 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -4266,19 +4266,44 @@ class SQLCompiler(Compiled):
             text += " OFFSET " + self.process(select._offset_clause, **kw)
         return text
 
-    def fetch_clause(self, select, **kw):
+    def fetch_clause(
+        self,
+        select,
+        fetch_clause=None,
+        require_offset=False,
+        use_literal_execute_for_simple_int=False,
+        **kw,
+    ):
+        if fetch_clause is None:
+            fetch_clause = select._fetch_clause
+            fetch_clause_options = select._fetch_clause_options
+        else:
+            fetch_clause_options = {"percent": False, "with_ties": False}
+
         text = ""
+
         if select._offset_clause is not None:
-            text += "\n OFFSET %s ROWS" % self.process(
-                select._offset_clause, **kw
-            )
-        if select._fetch_clause is not None:
+            offset_clause = select._offset_clause
+            if (
+                use_literal_execute_for_simple_int
+                and select._simple_int_clause(offset_clause)
+            ):
+                offset_clause = offset_clause.render_literal_execute()
+            offset_str = self.process(offset_clause, **kw)
+            text += "\n OFFSET %s ROWS" % offset_str
+        elif require_offset:
+            text += "\n OFFSET 0 ROWS"
+
+        if fetch_clause is not None:
+            if (
+                use_literal_execute_for_simple_int
+                and select._simple_int_clause(fetch_clause)
+            ):
+                fetch_clause = fetch_clause.render_literal_execute()
             text += "\n FETCH FIRST %s%s ROWS %s" % (
-                self.process(select._fetch_clause, **kw),
-                " PERCENT" if select._fetch_clause_options["percent"] else "",
-                "WITH TIES"
-                if select._fetch_clause_options["with_ties"]
-                else "ONLY",
+                self.process(fetch_clause, **kw),
+                " PERCENT" if fetch_clause_options["percent"] else "",
+                "WITH TIES" if fetch_clause_options["with_ties"] else "ONLY",
             )
         return text