[
https://issues.apache.org/jira/browse/CALCITE-5390?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17835683#comment-17835683
]
Ruben Q L commented on CALCITE-5390:
------------------------------------
Recently I stumbled upon this bug, in my case it's a LogicalFilter with 2
RexSubquery using the same $cor0 which triggers the NPE when calling the
Decorrelator. It's a far more complicated query that the ones already mentioned
previously (so far I haven't been able to reproduce my issue with a
minimalistic test), but I think the principle is the same.
I'm not sure trimming plays a role here (at least on my case I get the error
without trimming the plan). I pretty much agree with [~libenchao]'s analysis
here, it seems strange that the SubQueryRemoveRule generates a plan with two
LogicalCorrelate using the same corr variable (it's exactly the same on my
case). The plan looks odd, but... if I am not mistaken, if we skip the
decorrelation, that odd plan seems to run fine (at least on my case, where we
run the decorrelator within a try-catch, given that it's been traditionally a
problematic module), and it case of exception, we simply go with the original
(non-decorrelated) plan, and everything is fine. Actually, it seems that this
"bizarre plan structure" of having 2 LogicalCorrelate with the same variable is
not that uncommon: if we look inside {{RelOptRulesTest.xml}} we can see around
10 coincidences of said pattern.
Moreover, it'd seem that this pattern is a necessary condition for the
Decorrelator NPE, but not sufficient (according to my tests, some of these
plans can be correctly decorrelated; it probably depends on which fields are
accessed on each "instance" of the corr variable, as [~julianhyde] mentioned
here).
To sum up, I agree with [~libenchao] that one potential solution could be
modifying SubQueryRemoveRule to introduce different correlating variables
instead of one in these cases (and that will probably make the Decorralator
happy). But, given that the current plan generated by SubQueryRemoveRule seems
"odd by correct" (it gets executed correctly if we skip the Decorrelator and
its potential NPE); I wonder if perhaps our efforts should rather focus on
fixing the Decorrelator and make it work with this type of plans. I'm not sure
which approach would be simpler, but I just wanted to mention it.
PS: I haven't looked in detail, but I feel that on Decorrelator side, the
problem (or at least one of them) might originate because its {{CorelMap}}
contains just a Map for {{<CorrelationId, RelNode>}} , and it would seems that
in case like this, a MultiMap would be required? (two different
LogicalCorrelate values having the same CorrelationId key?)
> RelDecorrelator throws NullPointerException
> -------------------------------------------
>
> Key: CALCITE-5390
> URL: https://issues.apache.org/jira/browse/CALCITE-5390
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Dmitry Sysolyatin
> Priority: Major
>
> The current query throws NullPointerException
> {code:java}
> SELECT
> (SELECT 1 FROM emp d WHERE d.job = a.job LIMIT 1) AS t1,
> (SELECT a.job = 'PRESIDENT' FROM emp s LIMIT 1) as t2
> FROM emp a;
> {code}
> Test case -
> [https://github.com/apache/calcite/commit/46fe9bc456f2d34cf7dccd29829c9e85abe69d5f]
> Logical plan before it fails:
> {code:java}
> LogicalProject(T1=[$8], T2=[$9])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f09=[$9])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f00=[$10])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{9}])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f9=[=($2,
> 'PRESIDENT')])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{2}])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalSort(fetch=[1])
> LogicalProject(EXPR$0=[1])
> LogicalFilter(condition=[=($2, $cor0.JOB)])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalSort(fetch=[1])
> LogicalProject(EXPR$0=[$cor0.$f9])
> LogicalTableScan(table=[[scott, EMP]]) {code}
> Stack trace:
> {code:java}
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:833)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1028)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:764)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
> at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
> 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:464)
> 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.GeneratedMethodAccessor13.invoke(Unknown Source)
> 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.GeneratedMethodAccessor12.invoke(Unknown Source)
> 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.GeneratedMethodAccessor9.invoke(Unknown Source)
> 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.GeneratedMethodAccessor9.invoke(Unknown Source)
> 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.GeneratedMethodAccessor9.invoke(Unknown Source)
> 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)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:337)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:177)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:312)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:648)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:514)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:484)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:623)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
> ... 10 more
> {code}
> UPD: other queries failing with same trace
> {code:sql}
> SELECT * FROM emps e WHERE e.name NOT IN (SELECT d.name FROM depts d WHERE
> e.deptno = d.deptno OR e.gender ='M');
> SELECT city
> FROM emps e
> WHERE ((CASE WHEN name NOT IN (SELECT name FROM sdepts s WHERE s.deptno =
> e.deptno) THEN '1' ELSE '2' END)
> NOT IN (SELECT name FROM depts d WHERE e.deptno = d.deptno));
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
