[CALCITE-1786] Implement Oracle <12g support of limit/offset query syntax

• Type: Task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.11.0
• Component/s: jdbc-adapter
• Assignee: Unassigned
• Reporter: Sergiy Simonov
• Created: 11/May/17 13:46
• Updated: 28/Feb/24 06:03

Description

Currently Calcite supports only two kinds of limit/offset SQL query syntax:

If in DB dialect supportsOffsetFetch == false, syntax is

LIMIT 20 OFFSET 10

If supportsOffsetFetch == true, syntax is

OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY

Oracle dialect uses the later syntax and it works perfectly fine for Oracle 12g
But Oracle before 12g version does not support this syntax, as well as LIMIT/OFFSET syntax.

Oracle query for older Oracle version should look like:

SELECT * FROM (original query)
WHERE ROWNUM > 10 AND ROWNUM <= 20

Issue Links

• relates to: CALCITE-5723 Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch