Hongze Zhang created CALCITE-2593:
-------------------------------------
Summary: Failed to plan when SQL is like "sum(X + 1) filter (where
Y)"
Key: CALCITE-2593
URL: https://issues.apache.org/jira/browse/CALCITE-2593
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Hongze Zhang
Assignee: Julian Hyde
{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}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
