[
https://issues.apache.org/jira/browse/CALCITE-2593?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16843456#comment-16843456
]
Stamatis Zampetakis commented on CALCITE-2593:
----------------------------------------------
I had again a look into this. It seems that the following change in
EnumerableSortRule also solves the CannotPlanException for this query.
{code:java}
return EnumerableSort.create(
convert(
input,
input.getTraitSet().replace(EnumerableConvention.INSTANCE).replace(RelCollations.EMPTY)),
sort.getCollation(),
null,
null);
{code}
Since this rule is going to perform a sort and thus fulfil some physical
properties (RelCollation) I was wondering if it makes sense to ask the input to
be sorted. In the textbook VolcanoPlanner when an enforcer is applied (in our
case EnumerableSort) the satisfied physical properties are removed from the
optimization of the subplan. Maybe removing all collations (as I did above) is
wrong but I think the rule should remove all collations that are going to be
enforced by the EnumerableSort operator.
> Sometimes fails to plan when a RelNode transform multiple collations to
> single collation
> ----------------------------------------------------------------------------------------
>
> Key: CALCITE-2593
> URL: https://issues.apache.org/jira/browse/CALCITE-2593
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Hongze Zhang
> Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Sample SQL:
> {code:java}
> select sum(X + 1) filter (where Y) as "SET" from (values (1, TRUE), (2,
> TRUE)) AS t(X, Y) limit 10{code}
> Error log:
> {code:java}
> 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
> {code}
> ---------------------
> 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.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
