[CALCITE-2593] Sometimes fails to plan when a RelNode transform multiple collations to single collation

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.23.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Hongze Zhang
• Created: 25/Sep/18 11:15
• Updated: 24/May/20 11:59
• Resolved: 12/Apr/20 19:38

Description

Sample SQL:

select sum(X + 1) filter (where Y) as "SET" from (values (1, TRUE), (2, TRUE)) AS t(X, Y) limit 10

Error log:

java.lang.RuntimeException: exception while executing [select sum(X + 1) filter (where Y) as "SET" from (values (1, TRUE), (2, TRUE)) AS t(X, Y) limit 10] at org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1366) at org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1339) at org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1302) at org.apache.calcite.test.JdbcTest.testWithinGroupClause5(JdbcTest.java:6736) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.junit.runner.JUnitCore.run(JUnitCore.java:137) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68) at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47) at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242) at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) Caused by: java.lang.RuntimeException: With materializationsEnabled=false, limit=0 at org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:573) at org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1362) ... 25 more Caused by: java.sql.SQLException: Error while executing SQL "select sum(X + 1) filter (where Y) as "SET" from (values (1, TRUE), (2, TRUE)) AS t(X, Y) limit 10": Node [rel#22:Subset#3.ENUMERABLE.[]] could not be implemented; planner state: Root: rel#22:Subset#3.ENUMERABLE.[] Original rel: LogicalSort(subset=[rel#22:Subset#3.ENUMERABLE.[]], fetch=[10]): rowcount = 1.0, cumulative cost = {1.0 rows, 4.0 cpu, 0.0 io}, id = 17 LogicalAggregate(subset=[rel#16:Subset#2.NONE.[]], group=[{}], SET=[SUM($0) FILTER $1]): rowcount = 1.0, cumulative cost = {1.1375000476837158 rows, 0.0 cpu, 0.0 io}, id = 15 LogicalProject(subset=[rel#14:Subset#1.NONE.[1]], $f0=[+($0, 1)], Y=[$1]): rowcount = 2.0, cumulative cost = {2.0 rows, 4.0 cpu, 0.0 io}, id = 13 LogicalValues(subset=[rel#12:Subset#0.NONE.[]], tuples=[[{ 1, true }, { 2, true }]]): rowcount = 2.0, cumulative cost = {2.0 rows, 1.0 cpu, 0.0 io}, id = 1 Sets: Set#0, type: RecordType(INTEGER X, BOOLEAN Y) rel#12:Subset#0.NONE.[], best=null, importance=0.6561 rel#1:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER X, BOOLEAN Y),tuples=[{ 1, true }, { 2, true }]), rowcount=2.0, cumulative cost={inf} rel#37:Subset#0.ENUMERABLE.[], best=rel#36, importance=0.32805 rel#36:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(INTEGER X, BOOLEAN Y),tuples=[{ 1, true }, { 2, true }]), rowcount=2.0, cumulative cost={2.0 rows, 1.0 cpu, 0.0 io} Set#1, type: RecordType(INTEGER $f0, BOOLEAN Y) rel#14:Subset#1.NONE.[1], best=null, importance=0.7290000000000001 rel#13:LogicalProject.NONE.[[1]](input=rel#12:Subset#0.NONE.[],$f0=+($0, 1),Y=$1), rowcount=2.0, cumulative cost={inf} rel#41:Subset#1.ENUMERABLE.[], best=rel#40, importance=0.36450000000000005 rel#40:EnumerableProject.ENUMERABLE.[](input=rel#37:Subset#0.ENUMERABLE.[],$f0=+($0, 1),Y=$1), rowcount=2.0, cumulative cost={4.0 rows, 5.0 cpu, 0.0 io} rel#43:Subset#1.ENUMERABLE.[1], best=null, importance=0.7290000000000001 Set#2, type: RecordType(INTEGER SET) rel#16:Subset#2.NONE.[], best=null, importance=0.81 rel#15:LogicalAggregate.NONE.[](input=rel#14:Subset#1.NONE.[1],group={},SET=SUM($0) FILTER $1), rowcount=1.0, cumulative cost={inf} rel#29:LogicalProject.NONE.[](input=rel#28:Subset#4.NONE.[],SET=CASE(=($1, 0), null, $0)), rowcount=1.0, cumulative cost={inf} rel#24:Subset#2.ENUMERABLE.[], best=null, importance=0.9 rel#39:EnumerableProject.ENUMERABLE.[](input=rel#38:Subset#4.ENUMERABLE.[],SET=CASE(=($1, 0), null, $0)), rowcount=1.0, cumulative cost={inf} rel#44:EnumerableAggregate.ENUMERABLE.[](input=rel#43:Subset#1.ENUMERABLE.[1],group={},SET=SUM($0) FILTER $1), rowcount=1.0, cumulative cost={inf} Set#3, type: RecordType(INTEGER SET) rel#18:Subset#3.NONE.[], best=null, importance=0.9 rel#17:LogicalSort.NONE.[](input=rel#16:Subset#2.NONE.[],fetch=10), rowcount=1.0, cumulative cost={inf} rel#33:LogicalProject.NONE.[](input=rel#32:Subset#5.NONE.[],SET=CASE(=($1, 0), null, $0)), rowcount=1.0, cumulative cost={inf} rel#22:Subset#3.ENUMERABLE.[], best=null, importance=1.0 rel#23:AbstractConverter.ENUMERABLE.[](input=rel#18:Subset#3.NONE.[],convention=ENUMERABLE,sort=[]), rowcount=1.0, cumulative cost={inf} rel#25:EnumerableLimit.ENUMERABLE.[](input=rel#24:Subset#2.ENUMERABLE.[],fetch=10), rowcount=1.0, cumulative cost={inf} rel#35:EnumerableProject.ENUMERABLE.[](input=rel#34:Subset#5.ENUMERABLE.[],SET=CASE(=($1, 0), null, $0)), rowcount=1.0, cumulative cost={inf} Set#4, type: RecordType(INTEGER SET, BIGINT $f1) rel#28:Subset#4.NONE.[], best=null, importance=0.7290000000000001 rel#26:LogicalAggregate.NONE.[](input=rel#14:Subset#1.NONE.[1],group={},SET=$SUM0($0) FILTER $1,agg#1=COUNT() FILTER $1), rowcount=1.0, cumulative cost={inf} rel#38:Subset#4.ENUMERABLE.[], best=null, importance=0.81 rel#45:EnumerableAggregate.ENUMERABLE.[](input=rel#43:Subset#1.ENUMERABLE.[1],group={},SET=$SUM0($0) FILTER $1,agg#1=COUNT() FILTER $1), rowcount=1.0, cumulative cost={inf} Set#5, type: RecordType(INTEGER SET, BIGINT $f1) rel#32:Subset#5.NONE.[], best=null, importance=0.81 rel#30:LogicalSort.NONE.[](input=rel#28:Subset#4.NONE.[],fetch=10), rowcount=1.0, cumulative cost={inf} rel#34:Subset#5.ENUMERABLE.[], best=null, importance=0.9 rel#42:EnumerableLimit.ENUMERABLE.[](input=rel#38:Subset#4.ENUMERABLE.[],fetch=10), rowcount=1.0, cumulative cost={inf} at org.apache.calcite.avatica.Helper.createException(Helper.java:56) at org.apache.calcite.avatica.Helper.createException(Helper.java:41) at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163) at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) at org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:541) ... 26 more Caused by: org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node [rel#22:Subset#3.ENUMERABLE.[]] could not be implemented; planner state: Root: rel#22:Subset#3.ENUMERABLE.[] Original rel: LogicalSort(subset=[rel#22:Subset#3.ENUMERABLE.[]], fetch=[10]): rowcount = 1.0, cumulative cost = {1.0 rows, 4.0 cpu, 0.0 io}, id = 17 LogicalAggregate(subset=[rel#16:Subset#2.NONE.[]], group=[{}], SET=[SUM($0) FILTER $1]): rowcount = 1.0, cumulative cost = {1.1375000476837158 rows, 0.0 cpu, 0.0 io}, id = 15 LogicalProject(subset=[rel#14:Subset#1.NONE.[1]], $f0=[+($0, 1)], Y=[$1]): rowcount = 2.0, cumulative cost = {2.0 rows, 4.0 cpu, 0.0 io}, id = 13 LogicalValues(subset=[rel#12:Subset#0.NONE.[]], tuples=[[{ 1, true }, { 2, true }]]): rowcount = 2.0, cumulative cost = {2.0 rows, 1.0 cpu, 0.0 io}, id = 1 Sets: Set#0, type: RecordType(INTEGER X, BOOLEAN Y) rel#12:Subset#0.NONE.[], best=null, importance=0.6561 rel#1:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER X, BOOLEAN Y),tuples=[{ 1, true }, { 2, true }]), rowcount=2.0, cumulative cost={inf} rel#37:Subset#0.ENUMERABLE.[], best=rel#36, importance=0.32805 rel#36:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(INTEGER X, BOOLEAN Y),tuples=[{ 1, true }, { 2, true }]), rowcount=2.0, cumulative cost={2.0 rows, 1.0 cpu, 0.0 io} Set#1, type: RecordType(INTEGER $f0, BOOLEAN Y) rel#14:Subset#1.NONE.[1], best=null, importance=0.7290000000000001 rel#13:LogicalProject.NONE.[[1]](input=rel#12:Subset#0.NONE.[],$f0=+($0, 1),Y=$1), rowcount=2.0, cumulative cost={inf} rel#41:Subset#1.ENUMERABLE.[], best=rel#40, importance=0.36450000000000005 rel#40:EnumerableProject.ENUMERABLE.[](input=rel#37:Subset#0.ENUMERABLE.[],$f0=+($0, 1),Y=$1), rowcount=2.0, cumulative cost={4.0 rows, 5.0 cpu, 0.0 io} rel#43:Subset#1.ENUMERABLE.[1], best=null, importance=0.7290000000000001 Set#2, type: RecordType(INTEGER SET) rel#16:Subset#2.NONE.[], best=null, importance=0.81 rel#15:LogicalAggregate.NONE.[](input=rel#14:Subset#1.NONE.[1],group={},SET=SUM($0) FILTER $1), rowcount=1.0, cumulative cost={inf} rel#29:LogicalProject.NONE.[](input=rel#28:Subset#4.NONE.[],SET=CASE(=($1, 0), null, $0)), rowcount=1.0, cumulative cost={inf} rel#24:Subset#2.ENUMERABLE.[], best=null, importance=0.9 rel#39:EnumerableProject.ENUMERABLE.[](input=rel#38:Subset#4.ENUMERABLE.[],SET=CASE(=($1, 0), null, $0)), rowcount=1.0, cumulative cost={inf} rel#44:EnumerableAggregate.ENUMERABLE.[](input=rel#43:Subset#1.ENUMERABLE.[1],group={},SET=SUM($0) FILTER $1), rowcount=1.0, cumulative cost={inf} Set#3, type: RecordType(INTEGER SET) rel#18:Subset#3.NONE.[], best=null, importance=0.9 rel#17:LogicalSort.NONE.[](input=rel#16:Subset#2.NONE.[],fetch=10), rowcount=1.0, cumulative cost={inf} rel#33:LogicalProject.NONE.[](input=rel#32:Subset#5.NONE.[],SET=CASE(=($1, 0), null, $0)), rowcount=1.0, cumulative cost={inf} rel#22:Subset#3.ENUMERABLE.[], best=null, importance=1.0 rel#23:AbstractConverter.ENUMERABLE.[](input=rel#18:Subset#3.NONE.[],convention=ENUMERABLE,sort=[]), rowcount=1.0, cumulative cost={inf} rel#25:EnumerableLimit.ENUMERABLE.[](input=rel#24:Subset#2.ENUMERABLE.[],fetch=10), rowcount=1.0, cumulative cost={inf} rel#35:EnumerableProject.ENUMERABLE.[](input=rel#34:Subset#5.ENUMERABLE.[],SET=CASE(=($1, 0), null, $0)), rowcount=1.0, cumulative cost={inf} Set#4, type: RecordType(INTEGER SET, BIGINT $f1) rel#28:Subset#4.NONE.[], best=null, importance=0.7290000000000001 rel#26:LogicalAggregate.NONE.[](input=rel#14:Subset#1.NONE.[1],group={},SET=$SUM0($0) FILTER $1,agg#1=COUNT() FILTER $1), rowcount=1.0, cumulative cost={inf} rel#38:Subset#4.ENUMERABLE.[], best=null, importance=0.81 rel#45:EnumerableAggregate.ENUMERABLE.[](input=rel#43:Subset#1.ENUMERABLE.[1],group={},SET=$SUM0($0) FILTER $1,agg#1=COUNT() FILTER $1), rowcount=1.0, cumulative cost={inf} Set#5, type: RecordType(INTEGER SET, BIGINT $f1) rel#32:Subset#5.NONE.[], best=null, importance=0.81 rel#30:LogicalSort.NONE.[](input=rel#28:Subset#4.NONE.[],fetch=10), rowcount=1.0, cumulative cost={inf} rel#34:Subset#5.ENUMERABLE.[], best=null, importance=0.9 rel#42:EnumerableLimit.ENUMERABLE.[](input=rel#38:Subset#4.ENUMERABLE.[],fetch=10), rowcount=1.0, cumulative cost={inf} at org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:437) at org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:296) at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:657) at org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:298) at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:358) at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:188) at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:319) at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:230) at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:772) at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:636) at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:606) at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:229) at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550) at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675) at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156) ... 28 more

---------------------
Update:
AFAIK, SQL like the sample SQL triggers the error only if LogicalValue emits multiple collation trait combinations where LogicalProject only takes one of them.

Issue Links

• is related to: CALCITE-2764 RelCompositeTrait#satisfies does not work for non-trivial traits
• is related to: CALCITE-2010 Cannot plan query that is UNION ALL applied to VALUES
• is related to: CALCITE-2970 Performance issue when enabling abstract converter for EnumerableConvention
• links to: GitHub Pull Request #984
• links to: GitHub Pull Request #989
• links to: GitHub Pull Request #1914