[ https://issues.apache.org/jira/browse/CALCITE-3376?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16939194#comment-16939194 ]
Ruben Q L edited comment on CALCITE-3376 at 9/27/19 7:58 AM: ------------------------------------------------------------- What I have found so far. The problem seems to happen under the following circumstances: - There must be a Join (of any type) implemented as a Correlate; note that if in the test we activate the {{ENUMERABLE_JOIN_RULE}}, the final join would be implemented as a HashJoin instead of as a Correlate, and the test would pass. - The left and right inputs of the Correlate must be equal (not just two equal scans, but something more "elaborate"). In the test we have two UNION ALL, but if we changed one (only one) of the unions to {{all=false}}, the test would pass; which means that both inputs of the Correlate must be exactly the same. - A more complex plan that follows this pattern and that also triggers the problem could be: {code} builder .scan("EMP") .filter( builder.equals( builder.field("DEPTNO"), builder.literal(1))) .scan("EMP") .filter( builder.equals( builder.field("DEPTNO"), builder.literal(2))) .union(false) .scan("EMP") .filter( builder.equals( builder.field("DEPTNO"), builder.literal(1))) .scan("EMP") .filter( builder.equals( builder.field("DEPTNO"), builder.literal(2))) .union(false) .antiJoin( builder.call( SqlStdOperatorTable.GREATER_THAN, builder.field(2, 1, "SAL"), builder.field(2, 0, "SAL"))); {code} was (Author: rubenql): What I have found so far. The problem seems to happen under the following circumstances: - There must be a Join (of any type) implemented as a Correlate; note that if in the test we activate the {{ENUMERABLE_JOIN_RULE}}, the final join would be implemented as a HashJoin instead of as a Correlate, and the test would pass. - The left and right inputs of the Correlate must be equal (not just two equal scans, but something more "elaborate"). In the test we have two UNION ALL, but if we changed one (only one) of the unions to {{all=false}}, the test would pass; which means that both inputs of the Correlate must be exactly the same. A more complex plan that follows this pattern and that also triggers the problem could be: {code} builder .scan("EMP") .filter( builder.equals( builder.field("DEPTNO"), builder.literal(1))) .scan("EMP") .filter( builder.equals( builder.field("DEPTNO"), builder.literal(2))) .union(false) .scan("EMP") .filter( builder.equals( builder.field("DEPTNO"), builder.literal(1))) .scan("EMP") .filter( builder.equals( builder.field("DEPTNO"), builder.literal(2))) .union(false) .antiJoin( builder.call( SqlStdOperatorTable.GREATER_THAN, builder.field(2, 1, "SAL"), builder.field(2, 0, "SAL"))); {code} > VolcanoPlanner CannotPlanException: best rel is null even though there is an > option with non-infinite cost > ---------------------------------------------------------------------------------------------------------- > > Key: CALCITE-3376 > URL: https://issues.apache.org/jira/browse/CALCITE-3376 > Project: Calcite > Issue Type: Bug > Affects Versions: 1.21.0 > Reporter: Ruben Q L > Priority: Major > Attachments: Graphviz.png, stackTrace.txt > > > The problem can be reproduced by adding this test to PlannerTest.java: > {code:java} > @Test public void testCannotPlanException() throws Exception { > RelBuilder builder = RelBuilder.create(RelBuilderTest.config().build()); > RuleSet ruleSet = > RuleSets.ofList( > //EnumerableRules.ENUMERABLE_JOIN_RULE, // with this rule it > works! > JoinToCorrelateRule.INSTANCE, > EnumerableRules.ENUMERABLE_CORRELATE_RULE, > EnumerableRules.ENUMERABLE_PROJECT_RULE, > EnumerableRules.ENUMERABLE_FILTER_RULE, > EnumerableRules.ENUMERABLE_SORT_RULE, > EnumerableRules.ENUMERABLE_UNION_RULE, > EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE); > builder > .scan("EMP") > .scan("EMP") > .union(true) > .scan("EMP") > .scan("EMP") > .union(true) > .join( > JoinRelType.INNER, > builder.equals( > builder.field(2, 0, "DEPTNO"), > builder.field(2, 1, "EMPNO"))); > RelNode relNode = builder.build(); > RelOptPlanner planner = relNode.getCluster().getPlanner(); > Program program = Programs.of(ruleSet); > RelTraitSet toTraits = relNode.getTraitSet() > .replace(EnumerableConvention.INSTANCE); > RelNode output = program.run(planner, relNode, toTraits, > ImmutableList.of(), ImmutableList.of()); > String outputStr = toString(output); > } > {code} > Running this test causes the following exception (full stack trace attached): > {code:java} > org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not > enough rules to produce a node with desired properties: > convention=ENUMERABLE, sort=[]. All the inputs have relevant nodes, however > the cost is still infinite. > Root: rel#13:Subset#2.ENUMERABLE.[] > {code} > The last part of the message (_All the inputs have relevant nodes, however > the cost is still infinite_) seems relevant, because we can see that > {{rel#13}}'s best is {{null}}, and it should be {{rel#21}} (which has a > non-infinite cost): > {code:java} > rel#13:Subset#2.ENUMERABLE.[], best=null, importance=1.0 > rel#14:AbstractConverter.ENUMERABLE.[](input=RelSubset#12, > convention=ENUMERABLE, sort=[]), rowcount=117.6, cumulative cost={inf} > rel#21:EnumerableCorrelate.ENUMERABLE.[](left=RelSubset#19, > right=RelSubset#20, correlation=$cor0, joinType=inner, requiredColumns={7}), > rowcount=1.0, cumulative cost={1770.6000000000001 rows, 2466.0 cpu, 0.0 io} > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)