Nested SQL query throws error on latest version 0.11.0

[bendrecm]

For dataset se-druid, following query was working for 0.10.1 version but throws error on latest version 0.11.0

SELECT gender, country, count(distinct unique_users) as unique_users FROM \"se-druid\" GROUP BY gender, country HAVING gender <> '' and country in (SELECT country FROM \"se-druid\" WHERE country <> '' and country is not null GROUP BY country ORDER BY count(distinct unique_users) desc LIMIT 5)

Error:

{"error":"Unknown exception","errorMessage":"Cannot build plan for query: SELECT gender, country, count(distinct unique_users) as unique_users FROM \"se-druid{"query":"SELECT gender, country, count(distinct unique_users) as unique_users FROM \"se-druid\" GROUP BY gender, country HAVING gender <> '' and country in (SELECT country FROM \"se-druid\" WHERE country <> '' and country is not null GROUP BY country ORDER BY count(distinct unique_users) desc LIMIT 5)","errorClass":"io.druid.java.util.common.ISE","host":null}

How to reproduce ->

1. Run any nested query like above on calcite SQL adapter on 0.11.0 version. The nested query can be in where clause or having clause.

[piturriky]

Any updates? Same error in druid 0.12.1 in a nested query like:
"SELECT * FROM dd_5 WHERE (internal_id, __time) IN (SELECT internal_id, max(__time) FROM dd_5 GROUP BY internal_id ORDER BY max(__time))"

[jihoonson]

Hi @piturriky, I think your problem has fixed in #5788. #5788 is backported to 0.12.3, so please try out our next release once it becomes available!

@bendrecm I think we still have the problem for your query, but I think you can workaround it by moving the IN clause to WHERE rather than HAVING. Similarly, the query would work in 0.12.3.

[gianm]

Closing per #5353 (comment).