You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Sergiy Simonov (JIRA)" <ji...@apache.org> on 2017/05/11 15:39:04 UTC
[jira] [Updated] (CALCITE-1786) Implement Oracle <12g support of
limit/offset query syntax
[ https://issues.apache.org/jira/browse/CALCITE-1786?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sergiy Simonov updated CALCITE-1786:
------------------------------------
Description:
Currently Calcite supports only two kinds of limit/offset SQL query syntax:
If in DB dialect supportsOffsetFetch == false, syntax is
{code}LIMIT 20 OFFSET 10{code}
If supportsOffsetFetch == true, syntax is
{code}OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY{code}
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 version should look like:
{code}
SELECT * FROM (original query) WHERE ROWNUM > 10 AND ROWNUM <= 20
{code}
was:
Currently Calcite supports only two kinds of limit/offset SQL query syntax:
If DB dialect does not supportsOffsetFetch, syntax is
{code}LIMIT 20 OFFSET 10{code}
If supportsOffsetFetch = true, syntax is
{code}OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY{code}
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 version should look like:
{code}
SELECT * FROM (original query) WHERE ROWNUM > 10 AND ROWNUM <= 20
{code}
> Implement Oracle <12g support of limit/offset query syntax
> ----------------------------------------------------------
>
> Key: CALCITE-1786
> URL: https://issues.apache.org/jira/browse/CALCITE-1786
> Project: Calcite
> Issue Type: Task
> Affects Versions: 1.11.0
> Reporter: Sergiy Simonov
> Assignee: Julian Hyde
> Fix For: next
>
>
> Currently Calcite supports only two kinds of limit/offset SQL query syntax:
> If in DB dialect supportsOffsetFetch == false, syntax is
> {code}LIMIT 20 OFFSET 10{code}
> If supportsOffsetFetch == true, syntax is
> {code}OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY{code}
> 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 version should look like:
> {code}
> SELECT * FROM (original query) WHERE ROWNUM > 10 AND ROWNUM <= 20
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)