[
https://issues.apache.org/jira/browse/CALCITE-6032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17818756#comment-17818756
]
Hanumath Rao Maduri edited comment on CALCITE-6032 at 2/20/24 10:48 AM:
------------------------------------------------------------------------
[~julianhyde]
The NullPoinerException reported in this Jira(i.e CALCITE-6032) is not same as
that of NullPointerException reported in CALCITE-5390. I have cross verified
the test case of CALCITE-5390 on this Jira's PR fix and the issue in
CALCITE-5390 shows up with the same stack trace.
The issue in this Jira is because
* Current trimUnusedFields logic doesn't take into account correlated columns
and it prunes these correlated columns assuming they are no more referenced by
the downstream operators. This issue shows up only in Prepare.java path reason
being, trimUnusedFields is called based on config setting
[link|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L296]
whereas trimUnusedFields is not called at the same location in the PlannerImpl
path (before SubqueryRemoval Rule
[link|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/prepare/PlannerImpl.java#L265]).
To fix this issue there were two possible solutions
#
## Fix the RelFieldTrimmer to avoid pruning the fields which are only used by
correlated variables.
## Make the behavior same in both the code paths (Prepare and PlannerImpl) by
refraining from calling the RelFieldTrimmer in the prepareSql path.
I initially took the option 2 as it seems logical to call the RelFieldTrimmer
program after the SubQueryRemoval program (as done in the PlannerImpl).
Currently (before option 1 fix) Prepare path has two invocations of the
RelFieldTrimmer. However, during the review [~rubenql] raised a valid point
w.r.t the reason for calling RelFieldTrimmer being unknown, we might not be
sure about its repercussions. Hence I also coded up the fix 1 on a later
[commit|https://github.com/apache/calcite/pull/3640/commits/f1f1f1420399aaad8e47a5493a81b617d0f476bb]
but mentioned that I believe option 2 is more appropriate in this context as
RelFieldTrimmer is being invoked multiple times which might not be needed.
* Other fixes in the SqlToRelConverter are needed for the same reason of
de-duping the correlated variables as is done in many other places but not done
in these code paths.
Please find more context on the PR
([https://github.com/apache/calcite/pull/3640)] particularly in this comment
([https://github.com/apache/calcite/pull/3640#issuecomment-1935259456])
was (Author: hanu.ncr):
The NullPoinerException reported in this Jira(i.e CALCITE-6032) is not same as
that of NullPointerException reported in CALCITE-5390. I have cross verified
the test case of CALCITE-5390 on this Jira's PR fix and the issue in
CALCITE-5390 shows up with the same stack trace.
The issue in this Jira is because
* Current trimUnusedFields logic doesn't take into account correlated columns
and it prunes these correlated columns assuming they are no more referenced by
the downstream operators. This issue shows up only in Prepare.java path reason
being, trimUnusedFields is called based on config setting
[link|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L296]
whereas trimUnusedFields is not called at the same location in the PlannerImpl
path (before SubqueryRemoval Rule
[link|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/prepare/PlannerImpl.java#L265]).
To fix this issue there were two possible solutions
#
## Fix the RelFieldTrimmer to avoid pruning the fields which are only used by
correlated variables.
## Make the behavior same in both the code paths (Prepare and PlannerImpl) by
refraining from calling the RelFieldTrimmer in the prepareSql path.
I initially took the option 2 as it seems logical to call the RelFieldTrimmer
program after the SubQueryRemoval program (as done in the PlannerImpl).
Currently (before option 1 fix) Prepare path has two invocations of the
RelFieldTrimmer. However, during the review [~rubenql] raised a valid point
w.r.t the reason for calling RelFieldTrimmer being unknown, we might not be
sure about its repercussions. Hence I also coded up the fix 1 on a later
[commit|https://github.com/apache/calcite/pull/3640/commits/f1f1f1420399aaad8e47a5493a81b617d0f476bb]
but mentioned that I believe option 2 is more appropriate in this context as
RelFieldTrimmer is being invoked multiple times which might not be needed.
* Other fixes in the SqlToRelConverter are needed for the same reason of
de-duping the correlated variables as is done in many other places but not done
in these code paths.
Please find more context on the PR
([https://github.com/apache/calcite/pull/3640)] particularly in this comment
(https://github.com/apache/calcite/pull/3640#issuecomment-1935259456)
> 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
> Assignee: Hanumath Rao Maduri
> Priority: Major
> Labels: pull-request-available
>
> 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)
