CASE WHEN statement using Calcite in Druid

Hi,

We have a datasource named se-druid which has some columns including ‘gender’. We have enabled sql avatica using

druid.sql.enable = true

druid.sql.avatica.enable = true

Simple queries like following are working fine -

{“query”:“SELECT gender FROM “se-druid” limit 20”}

but i get error when running following query -

{“query”:“SELECT case when gender=‘Male’ then ‘M’ else ‘F’ END FROM “se-druid” limit 20”}

Although the syntax looks correct and this is supported on calcite as per http://druid.io/docs/0.11.0/querying/sql.html

Error - {“error”:“Unknown exception”,“errorMessage”:“Cannot build plan for query: SELECT case when gender=‘Male’ then ‘M’ else ‘F’ END FROM “se-druid” limit 20”,“errorClass”:“io.druid.java.util.common.ISE”,“host”:null}

Any help will be appreciated.

Thanks

Chaitanya

Hi Chaitanya,

I just tried a similar query and it works for me. If you check broker.log do you see a fuller exception stack trace? Maybe there’s some clues there. Also, what type is the “gender” column? (As reported by INFORMATION_SCHEMA.COLUMNS)

Looks like this is not supported in older version of Druid (0.10.1)

When i upgraded to 0.11.0, it started working. Thanks

-Chaitanya

Ah, yep, it was recently added.