Green-Angry-Bird opened a new issue #10222:
URL: https://github.com/apache/druid/issues/10222
### Description
Adding a where clause to a projection of a subquery containing a union
breaks the query planner.
Sample query:
```
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"
)
WHERE purchase_timestamp >= '2020-07-28' AND purchase_timestamp <
'2020-07-29'
```
This query works without the WHERE clause:
```
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"
)
```
And the WHERE clause works on a projection of a subquery that does NOT
contain a union:
```
SELECT purchase_timestamp, airline
FROM (
SELECT
"__time" as purchase_timestamp,
"Airline" as airline
FROM "book"
)
WHERE purchase_timestamp >= '2020-07-28' AND purchase_timestamp <
'2020-07-29'
```
This definitely looks related to https://github.com/apache/druid/issues/10197
### Affected Version
Found in 0.18.0
### Related Logs
Broker error 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: convention=DRUID,
sort=[].
Missing conversion is LogicalSort[convention: NONE -> DRUID]
There is 1 empty subset: rel#165819:Subset#6.DRUID.[], the relevant part of
the original plan is as follows
165817:LogicalSort(fetch=[10000])
165815:LogicalFilter(subset=[rel#165816:Subset#5.NONE.[]],
condition=[AND(>=($0, 2020-07-28 00:00:00), <($0, 2020-07-29 00:00:00))])
165813:LogicalUnion(subset=[rel#165814:Subset#4.NONE.[]], all=[true])
165808:LogicalProject(subset=[rel#165809:Subset#1.NONE.[]],
purchase_timestamp=[$28], airline=[$0])
165765:LogicalTableScan(subset=[rel#165807:Subset#0.NONE.[]],
table=[[druid, book]])
165811:LogicalProject(subset=[rel#165812:Subset#3.NONE.[]],
purchase_timestamp=[$33], airline=[$1])
165767:LogicalTableScan(subset=[rel#165810:Subset#2.NONE.[]],
table=[[druid, avail]])
[...]
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.
Root: rel#165884:Subset#13.BINDABLE.[]
Original rel:
LogicalSort(subset=[rel#165819:Subset#6.DRUID.[]], fetch=[10000]): rowcount
= 50.0, cumulative cost = {50.0 rows, 1564.8092021712584 cpu, 0.0 io}, id =
165817
LogicalFilter(subset=[rel#165816:Subset#5.NONE.[]], condition=[AND(>=($0,
2020-07-28 00:00:00), <($0, 2020-07-29 00:00:00))]): rowcount = 50.0,
cumulative cost = {50.0 rows, 200.0 cpu, 0.0 io}, id = 165815
LogicalUnion(subset=[rel#165814:Subset#4.NONE.[]], all=[true]): rowcount
= 200.0, cumulative cost = {200.0 rows, 200.0 cpu, 0.0 io}, id = 165813
LogicalProject(subset=[rel#165809:Subset#1.NONE.[]],
purchase_timestamp=[$28], airline=[$0]): rowcount = 100.0, cumulative cost =
{100.0 rows, 200.0 cpu, 0.0 io}, id = 165808
LogicalTableScan(subset=[rel#165807:Subset#0.NONE.[]],
table=[[druid, book]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0
cpu, 0.0 io}, id = 165765
LogicalProject(subset=[rel#165812:Subset#3.NONE.[]],
purchase_timestamp=[$33], airline=[$1]): rowcount = 100.0, cumulative cost =
{100.0 rows, 200.0 cpu, 0.0 io}, id = 165811
LogicalTableScan(subset=[rel#165810:Subset#2.NONE.[]],
table=[[druid, avail]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0
cpu, 0.0 io}, id = 165767
[...]
at
org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:587)
~[calcite-core-1.21.0.jar:1.21.0]
at
org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:304)
~[calcite-core-1.21.0.jar:1.21.0]
at
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:647)
~[calcite-core-1.21.0.jar:1.21.0]
at
org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:327)
~[calcite-core-1.21.0.jar:1.21.0]
at
org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:346)
~[calcite-core-1.21.0.jar:1.21.0]
at
org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:357)
~[calcite-core-1.21.0.jar:1.21.0]
at
org.apache.druid.sql.calcite.planner.DruidPlanner.planWithBindableConvention(DruidPlanner.java:245)
~[druid-sql-0.18.0.jar:0.18.0]
at
org.apache.druid.sql.calcite.planner.DruidPlanner.plan(DruidPlanner.java:144)
~[druid-sql-0.18.0.jar:0.18.0]
at
org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:168)
~[druid-sql-0.18.0.jar:0.18.0]
at
org.apache.druid.sql.SqlLifecycle.plan(SqlLifecycle.java:179)
~[druid-sql-0.18.0.jar:0.18.0]
at
org.apache.druid.sql.SqlLifecycle.planAndAuthorize(SqlLifecycle.java:240)
~[druid-sql-0.18.0.jar:0.18.0]
at
org.apache.druid.sql.http.SqlResource.doPost(SqlResource.java:94)
[druid-sql-0.18.0.jar:0.18.0]
[...]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
