Druid: Select from union subquery fails due to column order

[Green-Angry-Bird]

Description

A very unhelpful Unknown exception (org.apache.druid.java.util.common.ISE): Cannot build plan for query is returned when the projection of a subquery containing a union has a different columnar order.

Sample query:

SELECT airline, purchase_timestamp FROM (
    SELECT 
        "__time" as purchase_timestamp,
        "Airline" as airline
    FROM "book" 
    UNION ALL
    SELECT 
        "__time" as purchase_timestamp,
        "Airline" as airline
    FROM  "avail"
)

The above query fails since airline, purchase_timestamp is in a different order than the subquery. There is a corresponding error in the broker log (truncated for brevity):

org.apache.druid.sql.http.SqlResource - Failed to handle query: [...]
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: [...]
Suppressed: org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=BINDABLE, sort=[]. All the inputs have relevant nodes, however the cost is still infinite [...]

The following query works (projection is in the SAME order as union subquery):

SELECT purchase_timestamp, airline FROM (
    SELECT 
        "__time" as purchase_timestamp,
        "Airline" as airline
    FROM "book" 
    UNION ALL
    SELECT 
        "__time" as purchase_timestamp,
        "Airline" as airline
    FROM  "avail"
)

The following query also works (projection is in a different order but subquery is NOT a union):

SELECT airline, purchase_timestamp FROM (
    SELECT 
        "__time" as purchase_timestamp,
        "Airline" as airline
    FROM "book"
)

Affected Version

We are running 0.18.0

[gianm]

I think this is a consequence of the fact that today, "UNION ALL" must appear at the very outer layer of the query: https://druid.apache.org/docs/latest/querying/sql.html#union-all

Technically even the second query you posted shouldn't work. But it probably works because the outer query is eliminated at some point early in the planning process (since it doesn't do anything).

I think when we add support for "UNION ALL" appearing at more points within the query structure, then this issue will be solved. The weird error message will also go away at that point.

[borasy]

Hi @gianm,

Is there any plan to support Union in a subquery and then GroupBy similar to below query?

SELECT type, sum(cnt)
FROM (
    SELECT  
        type,
        COUNT(*) AS cnt 
    FROM    
        tbl_a 
    GROUP BY type
    UNION ALL   
    SELECT  
        type,
        COUNT(*) AS cnt 
    FROM    
        tbl_b 
    GROUP BY type
)
GROUP BY type

Thanks,

[gianm]

@borasy To do that query, I think we'd want to extend the native "union" datasource (https://druid.apache.org/docs/latest/querying/datasource.html#union) to be able to accept "table" or "query" children instead of just "table". Then, the SQL layer could plan your query into a native "union" datasource on top of "query" children.

[borasy]

I'm not super clear on how to do that at the moment. For now, I will just request separate queries from Druid into Pandas' dataframes and do more transformation there.
Thanks anyway,