Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.18.0
Description
1) Empty subsets are the typical offenders for CannotPlan, so identify them and print before printing the planner dump
2) Print Graphviz-compatible output as well so the output is easier to understand
It would probably make sense to expose leafmost empty subsets via Set<RelSubset> CannotPlanExeption#getDeadSubsets()
Attachments
Attachments
- values_vs_subset_placement.png
- 80 kB
- Vladimir Sitnikov
- subsets.png
- 173 kB
- Vladimir Sitnikov
- running_rule.png
- 218 kB
- Vladimir Sitnikov
- converters.png
- 222 kB
- Vladimir Sitnikov
Issue Links
- is blocked by
-
CALCITE-2845 Avatica duplicates exception messages
- Reopened
- links to
Activity
In the above sample, the exception means original node DrillJoinRel(condition=[true], joinType=[inner]) is not implementable.
Which makes sense since Drill can't perform cartesian joins.
In fact, the true cause of the exception is "there's not enough rules" to identify that the join is not a cartesian one (e.g. push Filters above into the join).
Note: the exception above was produced when MultiJoin-related optimizations were disabled in Drill.
Another example: I've disabled EnumerableAggregateRule, and JdbcTest#testWithInsideWhereExists
select "deptno" from "hr"."emps" where exists ( with dept2 as (select * from "hr"."depts" where "depts"."deptno" >= "emps"."deptno") select 1 from dept2 where "deptno" <= "emps"."deptno")
The exception is as follows:
rel#169:Subset#10.ENUMERABLE.[]is not implementable. It means there's not enough rules to produce the node with desired properties There are 4 empty subsets: Leaf 0: rel#259:Subset#4.ENUMERABLE.[] 145:LogicalAggregate(group=[{0}]) 139:LogicalProject(subset=[rel#140:Subset#1.NONE.[]], deptno=[$1]) 82:EnumerableTableScan(subset=[rel#138:Subset#0.ENUMERABLE.[]], table=[[hr, emps]]) Leaf 1: rel#256:Subset#1.ENUMERABLE.[0] 139:LogicalProject(deptno=[$1]) 82:EnumerableTableScan(subset=[rel#138:Subset#0.ENUMERABLE.[]], table=[[hr, emps]]) Leaf 2: rel#255:Subset#8.ENUMERABLE.[0] 153:LogicalAggregate(group=[{0}]) 151:LogicalProject(subset=[rel#152:Subset#7.NONE.[]], deptno0=[$1]) 149:LogicalFilter(subset=[rel#150:Subset#6.NONE.[]], condition=[<=($0, $1)]) 147:LogicalJoin(subset=[rel#148:Subset#5.NONE.[]], condition=[>=($0, $1)], joinType=[inner]) 142:LogicalProject(subset=[rel#143:Subset#3.NONE.[]], deptno=[$0]) 84:EnumerableTableScan(subset=[rel#141:Subset#2.ENUMERABLE.[]], table=[[hr, depts]]) 145:LogicalAggregate(subset=[rel#146:Subset#4.NONE.[]], group=[{0}]) 139:LogicalProject(subset=[rel#140:Subset#1.NONE.[]], deptno=[$1]) 82:EnumerableTableScan(subset=[rel#138:Subset#0.ENUMERABLE.[]], table=[[hr, emps]]) Leaf 3: rel#273:Subset#8.ENUMERABLE.[] 153:LogicalAggregate(group=[{0}]) 151:LogicalProject(subset=[rel#152:Subset#7.NONE.[]], deptno0=[$1]) 149:LogicalFilter(subset=[rel#150:Subset#6.NONE.[]], condition=[<=($0, $1)]) 147:LogicalJoin(subset=[rel#148:Subset#5.NONE.[]], condition=[>=($0, $1)], joinType=[inner]) 142:LogicalProject(subset=[rel#143:Subset#3.NONE.[]], deptno=[$0]) 84:EnumerableTableScan(subset=[rel#141:Subset#2.ENUMERABLE.[]], table=[[hr, depts]]) 145:LogicalAggregate(subset=[rel#146:Subset#4.NONE.[]], group=[{0}]) 139:LogicalProject(subset=[rel#140:Subset#1.NONE.[]], deptno=[$1]) 82:EnumerableTableScan(subset=[rel#138:Subset#0.ENUMERABLE.[]], table=[[hr, emps]]) Root: rel#169:Subset#10.ENUMERABLE.[] Original rel: LogicalProject(deptno=[$0]): rowcount = 375.0, cumulative cost = {2385.0 rows, 1728.0 cpu, 0.0 io}, id = 137 LogicalJoin(condition=[=($0, $1)], joinType=[inner]): rowcount = 375.0, cumulative cost = {2010.0 rows, 1353.0 cpu, 0.0 io}, id = 136 LogicalProject(deptno=[$1]): rowcount = 100.0, cumulative cost = {200.0 rows, 201.0 cpu, 0.0 io}, id = 127 EnumerableTableScan(table=[[hr, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 82 LogicalAggregate(group=[{0}]): rowcount = 25.0, cumulative cost = {1435.0 rows, 1152.0 cpu, 0.0 io}, id = 135 LogicalProject(deptno0=[$1]): rowcount = 250.0, cumulative cost = {1410.0 rows, 1152.0 cpu, 0.0 io}, id = 134 LogicalFilter(condition=[<=($0, $1)]): rowcount = 250.0, cumulative cost = {1160.0 rows, 902.0 cpu, 0.0 io}, id = 133 LogicalJoin(condition=[>=($0, $1)], joinType=[inner]): rowcount = 500.0, cumulative cost = {910.0 rows, 402.0 cpu, 0.0 io}, id = 132 LogicalProject(deptno=[$0]): rowcount = 100.0, cumulative cost = {200.0 rows, 201.0 cpu, 0.0 io}, id = 128 EnumerableTableScan(table=[[hr, depts]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 84 LogicalAggregate(group=[{0}]): rowcount = 10.0, cumulative cost = {210.0 rows, 201.0 cpu, 0.0 io}, id = 131 LogicalProject(deptno=[$1]): rowcount = 100.0, cumulative cost = {200.0 rows, 201.0 cpu, 0.0 io}, id = 130 EnumerableTableScan(table=[[hr, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 82 Sets: Set#0, type: RecordType(JavaType(int) empid, JavaType(int) deptno, JavaType(class java.lang.String) name, JavaType(float) salary, JavaType(class java.lang.Integer) commission) rel#138:Subset#0.ENUMERABLE.[], best=rel#82, importance=0.6561 rel#82:EnumerableTableScan.ENUMERABLE.[](table=[hr, emps]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io} Set#1, type: RecordType(JavaType(int) deptno) rel#140:Subset#1.NONE.[], best=null, importance=0.7290000000000001 rel#139:LogicalProject.NONE.[](input=RelSubset#138,deptno=$1), rowcount=100.0, cumulative cost={inf} rel#175:Subset#1.ENUMERABLE.[], best=rel#242, importance=0.6561 rel#242:EnumerableProject.ENUMERABLE.[](input=RelSubset#138,deptno=$1), rowcount=100.0, cumulative cost={200.0 rows, 201.0 cpu, 0.0 io} rel#256:Subset#1.ENUMERABLE.[0], best=null, importance=0.6561 Set#2, type: RecordType(JavaType(int) deptno, JavaType(class java.lang.String) name, RecordType(JavaType(int) empid, JavaType(int) deptno, JavaType(class java.lang.String) name, JavaType(float) salary, JavaType(class java.lang.Integer) commission) ARRAY employees, RecordType(JavaType(int) x, JavaType(int) y) location) rel#141:Subset#2.ENUMERABLE.[], best=rel#84, importance=0.4304672100000001 rel#84:EnumerableTableScan.ENUMERABLE.[](table=[hr, depts]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io} Set#3, type: RecordType(JavaType(int) deptno) rel#143:Subset#3.NONE.[], best=null, importance=0.4782969000000001 rel#142:LogicalProject.NONE.[](input=RelSubset#141,deptno=$0), rowcount=100.0, cumulative cost={inf} rel#241:Subset#3.ENUMERABLE.[], best=rel#240, importance=0.4304672100000001 rel#240:EnumerableProject.ENUMERABLE.[](input=RelSubset#141,deptno=$0), rowcount=100.0, cumulative cost={200.0 rows, 201.0 cpu, 0.0 io} Set#4, type: RecordType(JavaType(int) deptno) rel#146:Subset#4.NONE.[], best=null, importance=0.4782969000000001 rel#145:LogicalAggregate.NONE.[](input=RelSubset#140,group={0}), rowcount=10.0, cumulative cost={inf} rel#235:LogicalAggregate.NONE.[](input=RelSubset#138,group={1}), rowcount=10.0, cumulative cost={inf} rel#259:Subset#4.ENUMERABLE.[], best=null, importance=0.4304672100000001 Set#5, type: RecordType(JavaType(int) deptno, JavaType(int) deptno0) rel#148:Subset#5.NONE.[], best=null, importance=0.531441 rel#147:LogicalJoin.NONE.[](left=RelSubset#143,right=RelSubset#146,condition=>=($0, $1),joinType=inner), rowcount=500.0, cumulative cost={inf} rel#205:LogicalProject.NONE.[](input=RelSubset#204,deptno=$1,deptno0=$0), rowcount=500.0, cumulative cost={inf} rel#244:Subset#5.ENUMERABLE.[], best=null, importance=0.4304672100000001 rel#248:EnumerableProject.ENUMERABLE.[](input=RelSubset#246,deptno=$1,deptno0=$0), rowcount=500.0, cumulative cost={inf} rel#278:EnumerableFilter.ENUMERABLE.[](input=RelSubset#262,condition=>=($0, $1)), rowcount=500.0, cumulative cost={inf} Set#6, type: RecordType(JavaType(int) deptno, JavaType(int) deptno0) rel#150:Subset#6.NONE.[], best=null, importance=0.5904900000000001 rel#149:LogicalFilter.NONE.[](input=RelSubset#148,condition=<=($0, $1)), rowcount=250.0, cumulative cost={inf} rel#209:LogicalProject.NONE.[](input=RelSubset#208,deptno=$1,deptno0=$0), rowcount=250.0, cumulative cost={inf} rel#220:LogicalJoin.NONE.[](left=RelSubset#143,right=RelSubset#146,condition=AND(>=($0, $1), <=($0, $1)),joinType=inner), rowcount=250.0, cumulative cost={inf} rel#309:LogicalFilter.NONE.[](input=RelSubset#262,condition=AND(>=($0, $1), <=($0, $1))), rowcount=250.0, cumulative cost={inf} rel#188:Subset#6.ENUMERABLE.[], best=null, importance=0.5904900000000001 rel#247:EnumerableProject.ENUMERABLE.[](input=RelSubset#232,deptno=$1,deptno0=$0), rowcount=250.0, cumulative cost={inf} rel#263:EnumerableFilter.ENUMERABLE.[](input=RelSubset#262,condition=AND(>=($0, $1), <=($0, $1))), rowcount=250.0, cumulative cost={inf} rel#280:EnumerableFilter.ENUMERABLE.[](input=RelSubset#244,condition=<=($0, $1)), rowcount=250.0, cumulative cost={inf} Set#7, type: RecordType(JavaType(int) deptno0) rel#152:Subset#7.NONE.[], best=null, importance=0.6561 rel#151:LogicalProject.NONE.[](input=RelSubset#150,deptno0=$1), rowcount=250.0, cumulative cost={inf} rel#210:LogicalProject.NONE.[](input=RelSubset#208,deptno0=$0), rowcount=250.0, cumulative cost={inf} rel#281:LogicalProject.NONE.[](input=RelSubset#188,deptno0=$1), rowcount=250.0, cumulative cost={inf} rel#282:LogicalProject.NONE.[](input=RelSubset#232,deptno0=$0), rowcount=250.0, cumulative cost={inf} rel#176:Subset#7.ENUMERABLE.[], best=null, importance=0.9 rel#189:EnumerableProject.ENUMERABLE.[](input=RelSubset#188,deptno0=$1), rowcount=250.0, cumulative cost={inf} rel#239:EnumerableProject.ENUMERABLE.[](input=RelSubset#232,deptno0=$0), rowcount=250.0, cumulative cost={inf} Set#8, type: RecordType(JavaType(int) deptno0) rel#154:Subset#8.NONE.[], best=null, importance=0.7290000000000001 rel#153:LogicalAggregate.NONE.[](input=RelSubset#152,group={0}), rowcount=25.0, cumulative cost={inf} rel#195:LogicalAggregate.NONE.[](input=RelSubset#150,group={1}), rowcount=25.0, cumulative cost={inf} rel#229:LogicalAggregate.NONE.[](input=RelSubset#208,group={0}), rowcount=25.0, cumulative cost={inf} rel#283:LogicalAggregate.NONE.[](input=RelSubset#232,group={0}), rowcount=25.0, cumulative cost={inf} rel#290:LogicalAggregate.NONE.[](input=RelSubset#188,group={1}), rowcount=25.0, cumulative cost={inf} rel#255:Subset#8.ENUMERABLE.[0], best=null, importance=0.6561 rel#273:Subset#8.ENUMERABLE.[], best=null, importance=0.6561 Set#9, type: RecordType(JavaType(int) deptno, JavaType(int) deptno0) rel#156:Subset#9.NONE.[], best=null, importance=0.81 rel#155:LogicalJoin.NONE.[](left=RelSubset#140,right=RelSubset#154,condition==($0, $1),joinType=inner), rowcount=375.0, cumulative cost={inf} rel#184:LogicalProject.NONE.[](input=RelSubset#183,deptno=$1,deptno0=$0), rowcount=375.0, cumulative cost={inf} rel#171:Subset#9.ENUMERABLE.[], best=null, importance=0.81 rel#252:EnumerableProject.ENUMERABLE.[](input=RelSubset#186,deptno=$1,deptno0=$0), rowcount=375.0, cumulative cost={inf} rel#258:EnumerableMergeJoin.ENUMERABLE.[[0], [1]](left=RelSubset#256,right=RelSubset#255,condition==($0, $1),joinType=inner), rowcount=375.0, cumulative cost={inf} rel#275:EnumerableJoin.ENUMERABLE.[](left=RelSubset#175,right=RelSubset#273,condition==($0, $1),joinType=inner), rowcount=375.0, cumulative cost={inf} Set#10, type: RecordType(JavaType(int) deptno) rel#158:Subset#10.NONE.[], best=null, importance=0.9 rel#157:LogicalProject.NONE.[](input=RelSubset#156,deptno=$0), rowcount=375.0, cumulative cost={inf} rel#174:SemiJoin.NONE.[](left=RelSubset#140,right=RelSubset#152,condition==($0, $1),joinType=inner), rowcount=100.0, cumulative cost={inf} rel#185:LogicalProject.NONE.[](input=RelSubset#183,deptno=$1), rowcount=375.0, cumulative cost={inf} rel#197:SemiJoin.NONE.[](left=RelSubset#140,right=RelSubset#150,condition==($0, $2),joinType=inner), rowcount=100.0, cumulative cost={inf} rel#231:SemiJoin.NONE.[](left=RelSubset#140,right=RelSubset#208,condition==($0, $1),joinType=inner), rowcount=100.0, cumulative cost={inf} rel#250:LogicalProject.NONE.[](input=RelSubset#171,deptno=$0), rowcount=375.0, cumulative cost={inf} rel#253:LogicalProject.NONE.[](input=RelSubset#186,deptno=$1), rowcount=375.0, cumulative cost={inf} rel#285:SemiJoin.NONE.[](left=RelSubset#140,right=RelSubset#232,condition==($0, $1),joinType=inner), rowcount=100.0, cumulative cost={inf} rel#292:SemiJoin.NONE.[](left=RelSubset#140,right=RelSubset#188,condition==($0, $2),joinType=inner), rowcount=100.0, cumulative cost={inf} rel#169:Subset#10.ENUMERABLE.[], best=null, importance=1.0 rel#170:AbstractConverter.ENUMERABLE.[](input=RelSubset#158,convention=ENUMERABLE,sort=[]), rowcount=375.0, cumulative cost={inf} rel#172:EnumerableProject.ENUMERABLE.[](input=RelSubset#171,deptno=$0), rowcount=375.0, cumulative cost={inf} rel#177:EnumerableSemiJoin.ENUMERABLE.[](left=RelSubset#175,right=RelSubset#176,condition==($0, $1),joinType=inner), rowcount=100.0, cumulative cost={inf} rel#187:EnumerableProject.ENUMERABLE.[](input=RelSubset#186,deptno=$1), rowcount=375.0, cumulative cost={inf} rel#198:EnumerableSemiJoin.ENUMERABLE.[](left=RelSubset#175,right=RelSubset#188,condition==($0, $2),joinType=inner), rowcount=100.0, cumulative cost={inf} rel#233:EnumerableSemiJoin.ENUMERABLE.[](left=RelSubset#175,right=RelSubset#232,condition==($0, $1),joinType=inner), rowcount=100.0, cumulative cost={inf} Set#11, type: RecordType(JavaType(int) deptno0, JavaType(int) deptno) rel#183:Subset#11.NONE.[], best=null, importance=0.7290000000000001 rel#181:LogicalJoin.NONE.[](left=RelSubset#154,right=RelSubset#140,condition==($1, $0),joinType=inner), rowcount=375.0, cumulative cost={inf} rel#193:LogicalProject.NONE.[](input=RelSubset#156,deptno0=$1,deptno=$0), rowcount=375.0, cumulative cost={inf} rel#186:Subset#11.ENUMERABLE.[], best=null, importance=0.7290000000000001 rel#249:EnumerableProject.ENUMERABLE.[](input=RelSubset#171,deptno0=$1,deptno=$0), rowcount=375.0, cumulative cost={inf} rel#257:EnumerableMergeJoin.ENUMERABLE.[[0], [1]](left=RelSubset#255,right=RelSubset#256,condition==($0, $1),joinType=inner), rowcount=375.0, cumulative cost={inf} rel#274:EnumerableJoin.ENUMERABLE.[](left=RelSubset#273,right=RelSubset#175,condition==($0, $1),joinType=inner), rowcount=375.0, cumulative cost={inf} Set#12, type: RecordType(JavaType(int) deptno, JavaType(int) deptno0) rel#204:Subset#12.NONE.[], best=null, importance=0.4782969000000001 rel#202:LogicalJoin.NONE.[](left=RelSubset#146,right=RelSubset#143,condition=>=($1, $0),joinType=inner), rowcount=500.0, cumulative cost={inf} rel#214:LogicalProject.NONE.[](input=RelSubset#148,deptno=$1,deptno0=$0), rowcount=500.0, cumulative cost={inf} rel#246:Subset#12.ENUMERABLE.[], best=null, importance=0.4782969000000001 rel#245:EnumerableProject.ENUMERABLE.[](input=RelSubset#244,deptno=$1,deptno0=$0), rowcount=500.0, cumulative cost={inf} rel#272:EnumerableFilter.ENUMERABLE.[](input=RelSubset#268,condition=>=($1, $0)), rowcount=500.0, cumulative cost={inf} Set#13, type: RecordType(JavaType(int) deptno, JavaType(int) deptno0) rel#208:Subset#13.NONE.[], best=null, importance=0.531441 rel#206:LogicalFilter.NONE.[](input=RelSubset#204,condition=<=($1, $0)), rowcount=250.0, cumulative cost={inf} rel#217:LogicalProject.NONE.[](input=RelSubset#150,deptno=$1,deptno0=$0), rowcount=250.0, cumulative cost={inf} rel#219:LogicalJoin.NONE.[](left=RelSubset#146,right=RelSubset#143,condition=AND(>=($1, $0), <=($1, $0)),joinType=inner), rowcount=250.0, cumulative cost={inf} rel#311:LogicalFilter.NONE.[](input=RelSubset#268,condition=AND(>=($1, $0), <=($1, $0))), rowcount=250.0, cumulative cost={inf} rel#232:Subset#13.ENUMERABLE.[], best=null, importance=0.531441 rel#243:EnumerableProject.ENUMERABLE.[](input=RelSubset#188,deptno=$1,deptno0=$0), rowcount=250.0, cumulative cost={inf} rel#269:EnumerableFilter.ENUMERABLE.[](input=RelSubset#268,condition=AND(>=($1, $0), <=($1, $0))), rowcount=250.0, cumulative cost={inf} rel#279:EnumerableFilter.ENUMERABLE.[](input=RelSubset#246,condition=<=($1, $0)), rowcount=250.0, cumulative cost={inf} Set#14, type: RecordType(JavaType(int) deptno, JavaType(int) deptno0) rel#262:Subset#14.ENUMERABLE.[], best=null, importance=0.531441 rel#260:EnumerableJoin.ENUMERABLE.[](left=RelSubset#241,right=RelSubset#259,condition=true,joinType=inner), rowcount=1000.0, cumulative cost={inf} Set#15, type: RecordType(JavaType(int) deptno, JavaType(int) deptno0) rel#268:Subset#15.ENUMERABLE.[], best=null, importance=0.4782969000000001 rel#266:EnumerableJoin.ENUMERABLE.[](left=RelSubset#259,right=RelSubset#241,condition=true,joinType=inner), rowcount=1000.0, cumulative cost={inf} Graphviz: digraph G { root [style=filled,label="Root"]; subgraph cluster0{ label="Set 0 RecordType(JavaType(int) empid, JavaType(int) deptno, JavaType(class java.lang.String) name, JavaType(float) salary, JavaType(class java.lang.Integer) commission)"; rel82 [label="rel#82:EnumerableTableScan(table=[hr, emps])\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box] subset138 [label="rel#138:Subset#0.ENUMERABLE.[]"] } subgraph cluster1{ label="Set 1 RecordType(JavaType(int) deptno)"; rel139 [label="rel#139:LogicalProject(input=RelSubset#138,deptno=$1)\nrows=100.0, cost={inf}",shape=box] rel242 [label="rel#242:EnumerableProject(input=RelSubset#138,deptno=$1)\nrows=100.0, cost={200.0 rows, 201.0 cpu, 0.0 io}",color=blue,shape=box] subset140 [label="rel#140:Subset#1.NONE.[]"] subset175 [label="rel#175:Subset#1.ENUMERABLE.[]"] subset256 [label="rel#256:Subset#1.ENUMERABLE.[0]",color=red] subset175 -> subset256; } subgraph cluster2{ label="Set 2 RecordType(JavaType(int) deptno, JavaType(class java.lang.String) name, RecordType(JavaType(int) empid, JavaType(int) deptno, JavaType(class java.lang.String) name, JavaType(float) salary, JavaType(class java.lang.Integer) commission) ARRAY employees, RecordType(JavaType(int) x, JavaType(int) y) location)"; rel84 [label="rel#84:EnumerableTableScan(table=[hr, depts])\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io}",color=blue,shape=box] subset141 [label="rel#141:Subset#2.ENUMERABLE.[]"] } subgraph cluster3{ label="Set 3 RecordType(JavaType(int) deptno)"; rel142 [label="rel#142:LogicalProject(input=RelSubset#141,deptno=$0)\nrows=100.0, cost={inf}",shape=box] rel240 [label="rel#240:EnumerableProject(input=RelSubset#141,deptno=$0)\nrows=100.0, cost={200.0 rows, 201.0 cpu, 0.0 io}",color=blue,shape=box] subset143 [label="rel#143:Subset#3.NONE.[]"] subset241 [label="rel#241:Subset#3.ENUMERABLE.[]"] } subgraph cluster4{ label="Set 4 RecordType(JavaType(int) deptno)"; rel145 [label="rel#145:LogicalAggregate(input=RelSubset#140,group={0})\nrows=10.0, cost={inf}",shape=box] rel235 [label="rel#235:LogicalAggregate(input=RelSubset#138,group={1})\nrows=10.0, cost={inf}",shape=box] subset146 [label="rel#146:Subset#4.NONE.[]"] subset259 [label="rel#259:Subset#4.ENUMERABLE.[]",color=red] } subgraph cluster5{ label="Set 5 RecordType(JavaType(int) deptno, JavaType(int) deptno0)"; rel147 [label="rel#147:LogicalJoin(left=RelSubset#143,right=RelSubset#146,condition=>=($0, $1),joinType=inner)\nrows=500.0, cost={inf}",shape=box] rel205 [label="rel#205:LogicalProject(input=RelSubset#204,deptno=$1,deptno0=$0)\nrows=500.0, cost={inf}",shape=box] rel248 [label="rel#248:EnumerableProject(input=RelSubset#246,deptno=$1,deptno0=$0)\nrows=500.0, cost={inf}",shape=box] rel278 [label="rel#278:EnumerableFilter(input=RelSubset#262,condition=>=($0, $1))\nrows=500.0, cost={inf}",shape=box] subset148 [label="rel#148:Subset#5.NONE.[]"] subset244 [label="rel#244:Subset#5.ENUMERABLE.[]"] } subgraph cluster6{ label="Set 6 RecordType(JavaType(int) deptno, JavaType(int) deptno0)"; rel149 [label="rel#149:LogicalFilter(input=RelSubset#148,condition=<=($0, $1))\nrows=250.0, cost={inf}",shape=box] rel209 [label="rel#209:LogicalProject(input=RelSubset#208,deptno=$1,deptno0=$0)\nrows=250.0, cost={inf}",shape=box] rel220 [label="rel#220:LogicalJoin(left=RelSubset#143,right=RelSubset#146,condition=AND(>=($0, $1), <=($0, $1)),joinType=inner)\nrows=250.0, cost={inf}",shape=box] rel247 [label="rel#247:EnumerableProject(input=RelSubset#232,deptno=$1,deptno0=$0)\nrows=250.0, cost={inf}",shape=box] rel263 [label="rel#263:EnumerableFilter(input=RelSubset#262,condition=AND(>=($0, $1), <=($0, $1)))\nrows=250.0, cost={inf}",shape=box] rel280 [label="rel#280:EnumerableFilter(input=RelSubset#244,condition=<=($0, $1))\nrows=250.0, cost={inf}",shape=box] rel309 [label="rel#309:LogicalFilter(input=RelSubset#262,condition=AND(>=($0, $1), <=($0, $1)))\nrows=250.0, cost={inf}",shape=box] subset150 [label="rel#150:Subset#6.NONE.[]"] subset188 [label="rel#188:Subset#6.ENUMERABLE.[]"] } subgraph cluster7{ label="Set 7 RecordType(JavaType(int) deptno0)"; rel151 [label="rel#151:LogicalProject(input=RelSubset#150,deptno0=$1)\nrows=250.0, cost={inf}",shape=box] rel189 [label="rel#189:EnumerableProject(input=RelSubset#188,deptno0=$1)\nrows=250.0, cost={inf}",shape=box] rel210 [label="rel#210:LogicalProject(input=RelSubset#208,deptno0=$0)\nrows=250.0, cost={inf}",shape=box] rel239 [label="rel#239:EnumerableProject(input=RelSubset#232,deptno0=$0)\nrows=250.0, cost={inf}",shape=box] rel281 [label="rel#281:LogicalProject(input=RelSubset#188,deptno0=$1)\nrows=250.0, cost={inf}",shape=box] rel282 [label="rel#282:LogicalProject(input=RelSubset#232,deptno0=$0)\nrows=250.0, cost={inf}",shape=box] subset152 [label="rel#152:Subset#7.NONE.[]"] subset176 [label="rel#176:Subset#7.ENUMERABLE.[]"] } subgraph cluster8{ label="Set 8 RecordType(JavaType(int) deptno0)"; rel153 [label="rel#153:LogicalAggregate(input=RelSubset#152,group={0})\nrows=25.0, cost={inf}",shape=box] rel195 [label="rel#195:LogicalAggregate(input=RelSubset#150,group={1})\nrows=25.0, cost={inf}",shape=box] rel229 [label="rel#229:LogicalAggregate(input=RelSubset#208,group={0})\nrows=25.0, cost={inf}",shape=box] rel283 [label="rel#283:LogicalAggregate(input=RelSubset#232,group={0})\nrows=25.0, cost={inf}",shape=box] rel290 [label="rel#290:LogicalAggregate(input=RelSubset#188,group={1})\nrows=25.0, cost={inf}",shape=box] subset154 [label="rel#154:Subset#8.NONE.[]"] subset255 [label="rel#255:Subset#8.ENUMERABLE.[0]",color=red] subset273 [label="rel#273:Subset#8.ENUMERABLE.[]",color=red] subset273 -> subset255; } subgraph cluster9{ label="Set 9 RecordType(JavaType(int) deptno, JavaType(int) deptno0)"; rel155 [label="rel#155:LogicalJoin(left=RelSubset#140,right=RelSubset#154,condition==($0, $1),joinType=inner)\nrows=375.0, cost={inf}",shape=box] rel184 [label="rel#184:LogicalProject(input=RelSubset#183,deptno=$1,deptno0=$0)\nrows=375.0, cost={inf}",shape=box] rel252 [label="rel#252:EnumerableProject(input=RelSubset#186,deptno=$1,deptno0=$0)\nrows=375.0, cost={inf}",shape=box] rel258 [label="rel#258:EnumerableMergeJoin(left=RelSubset#256,right=RelSubset#255,condition==($0, $1),joinType=inner)\nrows=375.0, cost={inf}",shape=box] rel275 [label="rel#275:EnumerableJoin(left=RelSubset#175,right=RelSubset#273,condition==($0, $1),joinType=inner)\nrows=375.0, cost={inf}",shape=box] subset156 [label="rel#156:Subset#9.NONE.[]"] subset171 [label="rel#171:Subset#9.ENUMERABLE.[]"] } subgraph cluster10{ label="Set 10 RecordType(JavaType(int) deptno)"; rel157 [label="rel#157:LogicalProject(input=RelSubset#156,deptno=$0)\nrows=375.0, cost={inf}",shape=box] rel170 [label="rel#170:AbstractConverter(input=RelSubset#158,convention=ENUMERABLE,sort=[])\nrows=375.0, cost={inf}",shape=box] rel172 [label="rel#172:EnumerableProject(input=RelSubset#171,deptno=$0)\nrows=375.0, cost={inf}",shape=box] rel174 [label="rel#174:SemiJoin(left=RelSubset#140,right=RelSubset#152,condition==($0, $1),joinType=inner)\nrows=100.0, cost={inf}",shape=box] rel177 [label="rel#177:EnumerableSemiJoin(left=RelSubset#175,right=RelSubset#176,condition==($0, $1),joinType=inner)\nrows=100.0, cost={inf}",shape=box] rel185 [label="rel#185:LogicalProject(input=RelSubset#183,deptno=$1)\nrows=375.0, cost={inf}",shape=box] rel187 [label="rel#187:EnumerableProject(input=RelSubset#186,deptno=$1)\nrows=375.0, cost={inf}",shape=box] rel197 [label="rel#197:SemiJoin(left=RelSubset#140,right=RelSubset#150,condition==($0, $2),joinType=inner)\nrows=100.0, cost={inf}",shape=box] rel198 [label="rel#198:EnumerableSemiJoin(left=RelSubset#175,right=RelSubset#188,condition==($0, $2),joinType=inner)\nrows=100.0, cost={inf}",shape=box] rel231 [label="rel#231:SemiJoin(left=RelSubset#140,right=RelSubset#208,condition==($0, $1),joinType=inner)\nrows=100.0, cost={inf}",shape=box] rel233 [label="rel#233:EnumerableSemiJoin(left=RelSubset#175,right=RelSubset#232,condition==($0, $1),joinType=inner)\nrows=100.0, cost={inf}",shape=box] rel250 [label="rel#250:LogicalProject(input=RelSubset#171,deptno=$0)\nrows=375.0, cost={inf}",shape=box] rel253 [label="rel#253:LogicalProject(input=RelSubset#186,deptno=$1)\nrows=375.0, cost={inf}",shape=box] rel285 [label="rel#285:SemiJoin(left=RelSubset#140,right=RelSubset#232,condition==($0, $1),joinType=inner)\nrows=100.0, cost={inf}",shape=box] rel292 [label="rel#292:SemiJoin(left=RelSubset#140,right=RelSubset#188,condition==($0, $2),joinType=inner)\nrows=100.0, cost={inf}",shape=box] subset158 [label="rel#158:Subset#10.NONE.[]"] subset169 [label="rel#169:Subset#10.ENUMERABLE.[]"] } subgraph cluster11{ label="Set 11 RecordType(JavaType(int) deptno0, JavaType(int) deptno)"; rel181 [label="rel#181:LogicalJoin(left=RelSubset#154,right=RelSubset#140,condition==($1, $0),joinType=inner)\nrows=375.0, cost={inf}",shape=box] rel193 [label="rel#193:LogicalProject(input=RelSubset#156,deptno0=$1,deptno=$0)\nrows=375.0, cost={inf}",shape=box] rel249 [label="rel#249:EnumerableProject(input=RelSubset#171,deptno0=$1,deptno=$0)\nrows=375.0, cost={inf}",shape=box] rel257 [label="rel#257:EnumerableMergeJoin(left=RelSubset#255,right=RelSubset#256,condition==($0, $1),joinType=inner)\nrows=375.0, cost={inf}",shape=box] rel274 [label="rel#274:EnumerableJoin(left=RelSubset#273,right=RelSubset#175,condition==($0, $1),joinType=inner)\nrows=375.0, cost={inf}",shape=box] subset183 [label="rel#183:Subset#11.NONE.[]"] subset186 [label="rel#186:Subset#11.ENUMERABLE.[]"] } subgraph cluster12{ label="Set 12 RecordType(JavaType(int) deptno, JavaType(int) deptno0)"; rel202 [label="rel#202:LogicalJoin(left=RelSubset#146,right=RelSubset#143,condition=>=($1, $0),joinType=inner)\nrows=500.0, cost={inf}",shape=box] rel214 [label="rel#214:LogicalProject(input=RelSubset#148,deptno=$1,deptno0=$0)\nrows=500.0, cost={inf}",shape=box] rel245 [label="rel#245:EnumerableProject(input=RelSubset#244,deptno=$1,deptno0=$0)\nrows=500.0, cost={inf}",shape=box] rel272 [label="rel#272:EnumerableFilter(input=RelSubset#268,condition=>=($1, $0))\nrows=500.0, cost={inf}",shape=box] subset204 [label="rel#204:Subset#12.NONE.[]"] subset246 [label="rel#246:Subset#12.ENUMERABLE.[]"] } subgraph cluster13{ label="Set 13 RecordType(JavaType(int) deptno, JavaType(int) deptno0)"; rel206 [label="rel#206:LogicalFilter(input=RelSubset#204,condition=<=($1, $0))\nrows=250.0, cost={inf}",shape=box] rel217 [label="rel#217:LogicalProject(input=RelSubset#150,deptno=$1,deptno0=$0)\nrows=250.0, cost={inf}",shape=box] rel219 [label="rel#219:LogicalJoin(left=RelSubset#146,right=RelSubset#143,condition=AND(>=($1, $0), <=($1, $0)),joinType=inner)\nrows=250.0, cost={inf}",shape=box] rel243 [label="rel#243:EnumerableProject(input=RelSubset#188,deptno=$1,deptno0=$0)\nrows=250.0, cost={inf}",shape=box] rel269 [label="rel#269:EnumerableFilter(input=RelSubset#268,condition=AND(>=($1, $0), <=($1, $0)))\nrows=250.0, cost={inf}",shape=box] rel279 [label="rel#279:EnumerableFilter(input=RelSubset#246,condition=<=($1, $0))\nrows=250.0, cost={inf}",shape=box] rel311 [label="rel#311:LogicalFilter(input=RelSubset#268,condition=AND(>=($1, $0), <=($1, $0)))\nrows=250.0, cost={inf}",shape=box] subset208 [label="rel#208:Subset#13.NONE.[]"] subset232 [label="rel#232:Subset#13.ENUMERABLE.[]"] } subgraph cluster14{ label="Set 14 RecordType(JavaType(int) deptno, JavaType(int) deptno0)"; rel260 [label="rel#260:EnumerableJoin(left=RelSubset#241,right=RelSubset#259,condition=true,joinType=inner)\nrows=1000.0, cost={inf}",shape=box] subset262 [label="rel#262:Subset#14.ENUMERABLE.[]"] } subgraph cluster15{ label="Set 15 RecordType(JavaType(int) deptno, JavaType(int) deptno0)"; rel266 [label="rel#266:EnumerableJoin(left=RelSubset#259,right=RelSubset#241,condition=true,joinType=inner)\nrows=1000.0, cost={inf}",shape=box] subset268 [label="rel#268:Subset#15.ENUMERABLE.[]"] } root -> subset169; subset138 -> rel82[color=blue]; subset140 -> rel139; rel139 -> subset138; subset175 -> rel242[color=blue]; rel242 -> subset138[color=blue]; subset141 -> rel84[color=blue]; subset143 -> rel142; rel142 -> subset141; subset241 -> rel240[color=blue]; rel240 -> subset141[color=blue]; subset146 -> rel145; rel145 -> subset140; subset146 -> rel235; rel235 -> subset138; subset148 -> rel147; rel147 -> subset143[label="0"]; rel147 -> subset146[label="1"]; subset148 -> rel205; rel205 -> subset204; subset244 -> rel248; rel248 -> subset246; subset244 -> rel278; rel278 -> subset262; subset150 -> rel149; rel149 -> subset148; subset150 -> rel209; rel209 -> subset208; subset150 -> rel220; rel220 -> subset143[label="0"]; rel220 -> subset146[label="1"]; subset188 -> rel247; rel247 -> subset232; subset188 -> rel263; rel263 -> subset262; subset188 -> rel280; rel280 -> subset244; subset150 -> rel309; rel309 -> subset262; subset152 -> rel151; rel151 -> subset150; subset176 -> rel189; rel189 -> subset188; subset152 -> rel210; rel210 -> subset208; subset176 -> rel239; rel239 -> subset232; subset152 -> rel281; rel281 -> subset188; subset152 -> rel282; rel282 -> subset232; subset154 -> rel153; rel153 -> subset152; subset154 -> rel195; rel195 -> subset150; subset154 -> rel229; rel229 -> subset208; subset154 -> rel283; rel283 -> subset232; subset154 -> rel290; rel290 -> subset188; subset156 -> rel155; rel155 -> subset140[label="0"]; rel155 -> subset154[label="1"]; subset156 -> rel184; rel184 -> subset183; subset171 -> rel252; rel252 -> subset186; subset171 -> rel258; rel258 -> subset256[label="0"]; rel258 -> subset255[label="1"]; subset171 -> rel275; rel275 -> subset175[label="0"]; rel275 -> subset273[label="1"]; subset158 -> rel157; rel157 -> subset156; subset169 -> rel170; rel170 -> subset158; subset169 -> rel172; rel172 -> subset171; subset158 -> rel174; rel174 -> subset140[label="0"]; rel174 -> subset152[label="1"]; subset169 -> rel177; rel177 -> subset175[label="0"]; rel177 -> subset176[label="1"]; subset158 -> rel185; rel185 -> subset183; subset169 -> rel187; rel187 -> subset186; subset158 -> rel197; rel197 -> subset140[label="0"]; rel197 -> subset150[label="1"]; subset169 -> rel198; rel198 -> subset175[label="0"]; rel198 -> subset188[label="1"]; subset158 -> rel231; rel231 -> subset140[label="0"]; rel231 -> subset208[label="1"]; subset169 -> rel233; rel233 -> subset175[label="0"]; rel233 -> subset232[label="1"]; subset158 -> rel250; rel250 -> subset171; subset158 -> rel253; rel253 -> subset186; subset158 -> rel285; rel285 -> subset140[label="0"]; rel285 -> subset232[label="1"]; subset158 -> rel292; rel292 -> subset140[label="0"]; rel292 -> subset188[label="1"]; subset183 -> rel181; rel181 -> subset154[label="0"]; rel181 -> subset140[label="1"]; subset183 -> rel193; rel193 -> subset156; subset186 -> rel249; rel249 -> subset171; subset186 -> rel257; rel257 -> subset255[label="0"]; rel257 -> subset256[label="1"]; subset186 -> rel274; rel274 -> subset273[label="0"]; rel274 -> subset175[label="1"]; subset204 -> rel202; rel202 -> subset146[label="0"]; rel202 -> subset143[label="1"]; subset204 -> rel214; rel214 -> subset148; subset246 -> rel245; rel245 -> subset244; subset246 -> rel272; rel272 -> subset268; subset208 -> rel206; rel206 -> subset204; subset208 -> rel217; rel217 -> subset150; subset208 -> rel219; rel219 -> subset146[label="0"]; rel219 -> subset143[label="1"]; subset232 -> rel243; rel243 -> subset188; subset232 -> rel269; rel269 -> subset268; subset232 -> rel279; rel279 -> subset246; subset208 -> rel311; rel311 -> subset268; subset262 -> rel260; rel260 -> subset241[label="0"]; rel260 -> subset259[label="1"]; subset268 -> rel266; rel266 -> subset259[label="0"]; rel266 -> subset241[label="1"]; }
Eh, there's an issue with rel placement (see )
The example comes from CALCITE-2592, and raw output is
Set#1, type: RecordType(CHAR(1) id, CHAR(1) name) rel#4:Subset#1.NONE.[], best=null, importance=0.81 rel#1:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(CHAR(1) id, CHAR(1) name),tuples=[{ '1', 'x' }, { '2', 'y' }]), rowcount=2.0, cumulative cost={inf} rel#10:Subset#1.ENUMERABLE.[0], best=rel#21, importance=0.9 rel#21:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(CHAR(1) id, CHAR(1) name),tuples=[{ '1', 'x' }, { '2', 'y' }]), rowcount=2.0, cumulative cost={2.0 rows, 1.0 cpu, 0.0 io} rel#22:Subset#1.ENUMERABLE.[], best=rel#21, importance=0.45 rel#21:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(CHAR(1) id, CHAR(1) name),tuples=[{ '1', 'x' }, { '2', 'y' }]), rowcount=2.0, cumulative cost={2.0 rows, 1.0 cpu, 0.0 io}
It should put the rel under the most specific subset, and it should add extra links if the rel satisfies other subsets.
Ok, it is just another case of CALCITE-2593.
rel#21:EnumerableValues.ENUMERABLE has multiple collations, thus simplify puts it to .ENUMERABLE.[] subset
So I would wait a bit for CALCITE-2593 resolution
Sample output: