0

I'm using Avatica as a JDBC driver to query a Druid DB but I found it doesn't support MYSQL-like paging syntax:

SELECT * FROM tableA limit 4, 5

It only supports syntax like

SELECT * FROM tableA limit 2

How do I write the paging SQL with Avatica?

reference: http://calcite.apache.org/docs/reference.html

Michael Mior
  • 28,107
  • 9
  • 89
  • 113
rellocs wood
  • 1,381
  • 4
  • 21
  • 36

1 Answers1

2

For LIMIT 4, 5 you can use OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY. However, the next version of Calcite (1.14.0), which will be released within the next couple weeks, will support the MySQL style of limits you described.

Michael Mior
  • 28,107
  • 9
  • 89
  • 113
  • In addition to the standard SQL syntax Michael mentioned, Calcite also supports PostgreSQL syntax, `LIMIT 5 OFFSET 4`. Take your pick! – Julian Hyde Sep 21 '17 at 00:16
  • Thanks @Michael but I found another sql issue that the number of elements after 'IN' syntax is limited ,it return error once exceed 19. For example, sql "SELECT * FROM ds1 WHERE city_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)" works, but sql "SELECT * FROM ds1 WHERE city_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19, 20)" will return error, – rellocs wood Sep 21 '17 at 00:37
  • Thanks @Julian , the PostgreSql syntax looks more simple, is it also supported by Calcite 1.14.0 + ? – rellocs wood Sep 21 '17 at 00:41
  • @rellocswood Yes, that is also supported. If you have a separate problem with `IN`, you should ask a new question. – Michael Mior Sep 21 '17 at 02:13
  • See https://calcite.apache.org/docs/reference.html for syntax. ORDER BY is under "query:". – Julian Hyde Sep 22 '17 at 04:52
  • I can't reproduce the "IN" problem. If you have a reproducible test case please log a bug at https://issues.apache.org/jira/projects/CALCITE and include the error stack. – Julian Hyde Sep 22 '17 at 18:23