CASE WHEN statement using Calcite in Druid

[chaitany...@zeotap.com]

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

[Gian Merlino]

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)

Gian

*DISCLAIMER - This email message and any attachments to it are confidential and intended solely for the addressee(s). If you are not the intended recipient, please delete the message and notify the sender immediately. Also, you are notified that any unauthorised disclosure, use, copying, or storage of this message or its attachments is strictly prohibited.

--
You received this message because you are subscribed to the Google Groups "Druid User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+unsubscribe@googlegroups.com.
To post to this group, send email to druid...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/1b32a7c7-f208-4526-a72e-d2d880af27e5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[chaitany...@zeotap.com]

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

On Monday, February 5, 2018 at 12:51:57 AM UTC+5:30, Gian Merlino wrote:

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)

Gian

On Fri, Feb 2, 2018 at 4:30 AM, <chaitany...@zeotap.com> wrote:

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

*DISCLAIMER - This email message and any attachments to it are confidential and intended solely for the addressee(s). If you are not the intended recipient, please delete the message and notify the sender immediately. Also, you are notified that any unauthorised disclosure, use, copying, or storage of this message or its attachments is strictly prohibited.

--
You received this message because you are subscribed to the Google Groups "Druid User" group.

To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+...@googlegroups.com.

[Gian Merlino]

Ah, yep, it was recently added.

Gian

To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+unsubscribe@googlegroups.com.

To post to this group, send email to druid...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/12a2f365-20b2-4b8a-a29b-dd66de1aa397%40googlegroups.com.