[
https://issues.apache.org/jira/browse/CALCITE-1786?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16778459#comment-16778459
]
Haisheng Yuan commented on CALCITE-1786:
----------------------------------------
ROW > n (n > 0) will always return false, per document
[https://docs.oracle.com/cd/B14117_01/server.101/b10759/pseudocolumns008.htm]
I tried on Oracle 11gR2, select * from foo where ROWNUM > 1, no rows are
returned.
But we can implement it in another way:
{{SELECT * from ( select *, rownum r from foo ) where r > 10 and r <= 30}}
> 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
> Components: jdbc-adapter
> Affects Versions: 1.11.0
> Reporter: Sergiy Simonov
> Priority: Major
>
> 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 Oracle version should look like:
> {code}
> SELECT * FROM (original query)
> WHERE ROWNUM > 10 AND ROWNUM <= 20
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
