sql issue of 'IN' syntax in Avatica calcite

Question

I'm using Avatica calcite as a JDBC driver to query Druid DB. I found the 'IN' syntax CAN NOT followed by more than 19 elements. e.g

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)

this works, but this one errors:

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)

how can I use the 'IN' syntax with more than 19 elements ?

I tried this UNION ALL syntax but no luck..., it seems that druid/avatica not support this kind of syntax — rellocs wood, Sep 21 '17 at 03:03
Does `city_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19) OR city_id IN (20)` work? — melpomene, Sep 21 '17 at 05:28
@melpomene, Both (1-19) and (20) works, but (1-20) not work, it's the number of elements that matters — rellocs wood, Sep 21 '17 at 08:32
@rellocswood The suggestion was to just use an `OR` in your `WHERE` clause to combine multiple `IN` clauses. — Michael Mior, Sep 21 '17 at 11:07
Thanks @Michael ,I've used the OR as workground ,just want to know that will this IN issue be added to the fixing plan of Avatica ? — rellocs wood, Sep 22 '17 at 00:59

score 1 · Accepted Answer · answered Sep 25 '17 at 22:56

The reason this doesn't work is described in https://github.com/druid-io/druid/issues/4203. It should be fixed in Druid SQL after Calcite 1.14 is released, which will let us customize its behavior a bit more.

Until then, try the workaround suggested by @melpomene, which should work:

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)
  OR city_id IN (20,21,22)

sql issue of 'IN' syntax in Avatica calcite

1 Answers1