[
https://issues.apache.org/jira/browse/CALCITE-6032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17771240#comment-17771240
]
Julian Hyde commented on CALCITE-6032:
--------------------------------------
Is this a duplicate?
> 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
> Priority: Major
>
> 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)
