You are correct. Calcite requires “ONLY" after "FETCH … ROWS”. I have logged https://issues.apache.org/jira/browse/CALCITE-1901 <https://issues.apache.org/jira/browse/CALCITE-1901> to fix https://calcite.apache.org/docs/reference.html <https://calcite.apache.org/docs/reference.html>.
In your example, the 100 will be ignored. Calcite allows both “LIMIT … OFFSET” (to comply with PostgreSQL) and “OFFSET … FETCH” (to comply with the standard). But if you specify both LIMIT and FETCH it will ignore the LIMIT. Julian > On Jul 21, 2017, at 8:11 PM, ShaoFeng Shi <[email protected]> wrote: > > The following SQL works in my side: > > select * from KYLIN_SALES LIMIT 100 > OFFSET 1 ROWS FETCH FIRST 10 ROWS ONLY > > Here are two tricky things: > > 1) In Kylin's web GUI, it will automatically append a "LIMIT" after each > SQL if "LIMIT" wasn't appeared (this is to avoid too much data returned to > the web client); But in Calcite's grammer, "LIMIT" should appear before > "OFFSET"; so to bypass it I add the LIMIT here. If you send the query from > JDBC/ODBC or Rest API, there should be no such issue. > > 2) Calcite's document misses the "ONLY" keyword after "ROWS". > > @Julian, can you confirm the issue 2)? thanks! > > > > 2017-07-21 17:59 GMT+08:00 [email protected] <[email protected]>: > >> Hi, >> >> Kylin can support paged query? This sql can't work >> >> >> select * from KYLIN_SALES OFFSET 100 ROWS FETCH FIRST 10 ROWS >> >> >> >> Calcite doc: >> >> query: >> values | WITH withItem [ , withItem ]* query | { select | >> selectWithoutFrom | query UNION [ ALL | DISTINCT ] query | query EXCEPT [ >> ALL | DISTINCT ] query | query MINUS [ ALL | DISTINCT ] query | query >> INTERSECT [ ALL | DISTINCT ] query } [ ORDER BY orderItem [, orderItem ]* ] >> [ LIMIT { count | ALL } ] [ OFFSET start { ROW | ROWS } ] [ FETCH { FIRST | >> NEXT } [ count ] { ROW | ROWS } ] >> >> >> >> [email protected] >> > > > > -- > Best regards, > > Shaofeng Shi 史少锋
