Shiven Dvrk created CALCITE-6032:
------------------------------------
Summary: Multilevel correlated query is failing in RelDecorrelator
code path
Key: CALCITE-6032
URL: https://issues.apache.org/jira/browse/CALCITE-6032
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.35.0
Reporter: Shiven Dvrk
We have following query:
{code:java}
SELECT
Sum( In0.Col_5005 * (
select
MAX(mr.Col_147077 + mr.Col_147078 )
from
Bun mr
where
mr.Col_21084 = 10000
and mr.Col_21805 = 20000
and mr.Col_21807 = In0.Col_5003
and mr.Col_21806 =
(
select
Max(kr.Col_21806)
from
Bun kr
where
kr.Col_21807 = mr.Col_21807
and kr.Col_21804 = mr.Col_21804
and kr.Col_21805 = mr.Col_21805
and kr.Col_21806 <= In0.Col_4085
)
) ) as mmn,
count(*) as mmnCount
FROM
Den In0
where
In0.Col_5005 < 10
and In0.Col_5005 > 0
{code}
It is failing to decorrelate with standard program:
{code:java}
cm.mapCorToCorRel.get($cor4)
java.lang.NullPointerException: cm.mapCorToCorRel.get($cor4)
at java.util.Objects.requireNonNull(Objects.java:290)
at
org.apache.calcite.sql2rel.RelDecorrelator.getCorRel(RelDecorrelator.java:928)
at
org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:820)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1028)
at
org.apache.calcite.sql2rel.RelDecorrelator.maybeAddValueGenerator(RelDecorrelator.java:947)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1150)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1116)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:749)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:512)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:495)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1187)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1169)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:749)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1137)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1116)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:749)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:749)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:512)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:495)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1291)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1270)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:749)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:512)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:495)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:306)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:230)
at
org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:362)
{code}
If we use the following planner configuration without any sql to rel converter
config, the relnode is getting decorrelated properly.
{code:java}
val config: FrameworkConfig = Frameworks.newConfigBuilder
.parserConfig(parserConfig)
.defaultSchema(rootSchema)
.costFactory(None.orNull)
//.sqlToRelConverterConfig(converterConfig)
.programs(decorrelateProgram)
.build
{code}
It looks like default sql to rel converter with expand = true is decorrelating
the relNode properly without any explicit transform to apply the standard
program
this is the resultant relNode plan
{code:java}
LogicalAggregate(group=[{}], mmn=[SUM($0)], mmnCount=[COUNT()])
LogicalProject($f0=[*($2, $6)])
LogicalJoin(condition=[AND(=($0, $4), =($1, $5))], joinType=[left])
LogicalFilter(condition=[SEARCH($2, Sarg[(0..10)])])
LogicalTableScan(table=[[Den]])
LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($2)])
LogicalProject(Col_21807=[$4], Col_4085=[$8], $f0=[+($0, $1)])
LogicalFilter(condition=[AND(=($2, 10000), =($3, 20000), =($5, $7))])
LogicalProject(Col_147077=[$0], Col_147078=[$1], Col_21084=[$2],
Col_21805=[$3], Col_21807=[$4], Col_21806=[$5], ID=[$6], EXPR$0=[$11],
Col_4085=[$10])
LogicalJoin(condition=[AND(=($2, $7), =($3, $8), =($4, $9))],
joinType=[left])
LogicalTableScan(table=[[Bun]])
LogicalAggregate(group=[{0, 1, 2, 3}], EXPR$0=[MAX($4)])
LogicalProject(Col_210840=[$7], Col_218050=[$8],
Col_218070=[$9], Col_4085=[$10], Col_21806=[$5])
LogicalJoin(condition=[AND(=($4, $9), =($2, $7), =($3, $8),
<=($5, $10))], joinType=[inner])
LogicalTableScan(table=[[Bun]])
LogicalJoin(condition=[true], joinType=[inner])
LogicalAggregate(group=[{0, 1, 2}])
LogicalProject(Col_21084=[$2], Col_21805=[$3],
Col_21807=[$4])
LogicalTableScan(table=[[Bun]])
LogicalAggregate(group=[{0}])
LogicalProject(Col_4085=[$1])
LogicalFilter(condition=[SEARCH($2, Sarg[(0..10)])])
LogicalTableScan(table=[[Den]])
{code}
any thoughts on what is going on here?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
