I see, thanks Julian for the confirmation. For the issue 1), I created the KYLIN-2756 to fix it in future release.
2017-07-25 13:48 GMT+08:00 Julian Hyde <[email protected]>: > 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 史少锋 > > -- Best regards, Shaofeng Shi 史少锋
