[druid-user] Druid query failure

I didn’t get a response on druidforum.org, so reposting here

Version 0.20.1

Using wikipedia datasource as an example, testing with clause, union-all and joins combinations:

The following combining left join, union and with clause, and a groupby, it produces an error:
with v1 as (select w.* from wikipedia w left join (select ‘x’ as x) x on w.channel=x.x union all select * from wikipedia) select channel, count(*) from v1 group by 1

{
“error”: “Unknown exception”,
“errorMessage”: “Cannot build plan for query: with v1 as (select w.* from wikipedia w left join (select ‘x’ as x) x on w.channel=x.x union all select * from wikipedia) select channel, count(*) from v1 group by 1”,
“errorClass”: “org.apache.druid.java.util.common.ISE”,
“host”: null
}

However, if I removed the left join inside the with clause, it works
with v1 as (select w.* from wikipedia w union all select * from wikipedia) select channel, count(*) from v1 group by 1

Can someone point out what kind of error is org.apache.druid.java.util.common.ISE?

Hey,
You might be able to see the full stack trace in the log of your broker.

In any case, AFAIK, the query you’re trying to run does not meet the limitations of the UNION ALL in Druid, see https://druid.apache.org/docs/latest/querying/sql.html#union-all.

Thanks for your help. I hope Druid project will expand the support for union-all.

The inner query is kind of odd - does it run by itself? (Ie select w.* from wikipedia w left join (select ‘x’ as x) x on w.channel=x.x union all select * from wikipedia)
It may be the UNION ALL, but I think the LEFT JOIN might be a problem.

Are you trying to do something in particular that this is working towards? The query seems equivalent to
SELECT * FROM wikipedia WHERE channel = ‘x’ UNION ALL SELECT * FROM wikipedia

The left join doesn’t work for me, I’m not sure why yet. Since there’s no ‘x’ channel in my wikipedia, I tried
SELECT w.* FROM wikipedia w LEFT JOIN (SELECT ‘#ar.wikipedia’ AS x) x ON w.channel=x.x

and it didn’t work. (But SELECT * FROM wikipedia WHERE channel = ‘#ar.wikipedia’ did.)