Nested query giving error on 0.11.0 but works on 0.10.1

Hi,

I have a dataset named se-druid. I am using calcite adapter to run sql queries on druid.

When am running following nested 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)”

On Druid version 0.10.1, its working perfectly fine and returns me the result.

But on Druid version 0.11.0, i get following 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}

Am i missing something or is this a bug ?

Thanks
Chaitanya

Could you raise this as a bug in Github please? The URL is: https://github.com/druid-io/druid/issues/new

There were some changes in 0.11.0 to the semijoin rule that handles this kind of subquery, so it might have something to do with that.

Hi Gian,

I have created an issue. However, we do this functionality for our production setup. In absence of nested queries, we will have to take the logic at application level by firing multiple queries.

Do you have any alternatives ?

Thanks

Chaitanya

I think if you can, doing the logic at the application level is a good workaround right now. We’ll be looking into the bug in the meantime. Sorry for the inconvenience. Btw: if you are interested in contributing, even a failing unit test case (in CalciteQueryTest) would be helpful!