Druid query failures

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?

Sorry we missed this. Our notifications weren’t working.

I this got handled in the Google Group… no support for UNION_ALL quite yet.

Hi I am trying to run the query:

select name, elem_id, sum(sessions) as sessions, sum(packets) as packets, sum(bytes) as bytes, sum(“count”) as “count” from (

( select col1 as name, col2 as elem_id, count(col_3) as sessions, sum(col_4) as packets, sum(col_5) as bytes, sum(col_6) as “count” from data where “__time” BETWEEN TIMESTAMP ‘2022-08-25 10:00:00’ AND TIMESTAMP ‘2022-08-25 14:00:00’ AND serialnum = ‘test’ group by serialnum, app_name, app_id order by bytes desc limit 100) UNION ALL

(

( select col1 as name, col2 as elem_id, count(col_3) as sessions, sum(col_4) as packets, sum(col_5) as bytes, sum(col_6) as “count” from data_no_rollup_serialint where “__time” BETWEEN TIMESTAMP ‘2022-08-31 10:00:00’ AND TIMESTAMP ‘2022-08-31 14:00:00’ AND col7= ‘test’ group by col7, col1 , col8 order by bytes desc limit 100)

) c group by name, elem_id order by bytes desc limit 10

But getting the error:
Possible error: SQL requires union between inputs that are not simple table scans and involve a filter or aliasing. Or column types of tables being unioned are not of same type.

Is there any way to rewrite this query so it works or any other workaround for a query like this? Thanks

Hi Karan,

Sorry that we haven’t gotten back to you sooner with a solution. It would be easier to understand how to make that query work or possible workarounds if we had a better idea of what you were trying to do. Do you have 10-15 mins on Tuesday (9/6) to go over this so we can quickly get to a solution that works for you?

Hi @karandhodi,
I tried converting your SQL pattern using the wikipedia data and faced similar struggles.
I was able to produce a result by just using the inner selects with a UNION ALL, but I was unable to then apply another level of aggregation the way you did. Seems like a SQL parser bug.

Please create an issue at: Issues · apache/druid · GitHub

Thanks,

Sergio