[ https://issues.apache.org/jira/browse/CALCITE-6032?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sergey Nuyanzin closed CALCITE-6032. ------------------------------------ Resolved in release 1.37.0 (2024-05-06) > 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 > Fix For: 1.37.0 > > > 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)