Use FETCH FIRST N ROWS / OFFSET for Oracle LIMIT/OFFSET

Oracle will now use FETCH FIRST N ROWS / OFFSET syntax for limit/offset
support by default for Oracle 12c and above. This syntax was already
available when :meth:`_sql.Select.fetch` were used directly, it's now
implied for :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` as
well.

I'm currently setting this up so that the new syntax renders
in Oracle using POSTCOMPILE binds.    I really have no indication
if Oracle's SQL optimizer would be better with params
here, so that it can cache the SQL plan, or if it expects
hardcoded numbers for these. Since we had reports that the previous
ROWNUM thing really needed hardcoded ints, let's guess
for now that hardcoded ints would be preferable.  it can be turned
off with a single boolean if users report that they'd prefer
real bound values.

Fixes: #8221
Change-Id: I812ec24ffc947199866947b666d6ec6e6a690f22

1 files changed, 35 insertions, 10 deletions

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