[
https://issues.apache.org/jira/browse/CALCITE-5390?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18036770#comment-18036770
]
Dmitry Sysolyatin edited comment on CALCITE-5390 at 11/10/25 9:57 AM:
----------------------------------------------------------------------
My time for calcite contributions is limited. I will have time to continue this
work only next month. If anyone wishes to take over or continue this task in
the meantime, please feel free to do so.
I currently have a draft solution [1], but it has a few problems that need
resolving:
# Solution throws a NullPointerException in cases where a correlation variable
is used but has not been properly introduced. This specifically happens in join
plans where a subquery exists in the ON clause, but not in the WHERE clause.
(See example: CALCITE-7286). Also in this case of CALCITE-7286 LogicalJoin
should be replaced by LogicalCorrelate
# Solution depends on a proposal to introduce a new RelNode.copy method with a
3rd parameter to replace the variablesSet CALCITE-7280. As suggested by
Stamatis in a comment on this task, this functionality should probably be
implemented using the RelBuilder instead.
# Better test coverage
PR contains two commits. The first commit relates to CALCITE-7280 (introducing
copy method) and second contains the logic for the main fix itself. Feel free
to add any comment you wish, especially if you know a better solution.
[1] [https://github.com/apache/calcite/pull/4627]
was (Author: dmsysolyatin):
My time for calcite contributions is limited. I will have time to continue this
work only next month. If anyone wishes to take over or continue this task in
the meantime, please feel free to do so.
I currently have a draft solution [1], but it has a few problems that need
resolving:
# The current logic throws a NullPointerException in cases where a correlation
variable is used but has not been properly introduced. This specifically
happens in join plans where a subquery exists in the ON clause, but not in the
WHERE clause. (See example: CALCITE-7286). Also in this case of CALCITE-7286
LogicalJoin should be replaced by LogicalCorrelate
# Solution depends on a proposal to introduce a new RelNode.copy method with a
3rd parameter to replace the variablesSet CALCITE-7280. As suggested by
Stamatis in a comment on this task, this functionality should probably be
implemented using the RelBuilder instead.
# Better test coverage
PR contains two commits. The first commit relates to CALCITE-7280 (introducing
copy method) and second contains the logic for the main fix itself. Feel free
to add any comment you wish, especially if you know a better solution.
[1] [https://github.com/apache/calcite/pull/4627]
> 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
> Assignee: Dmitry Sysolyatin
> Priority: Major
> Labels: pull-request-available
>
> 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)
