[CALCITE-5390] RelDecorrelator throws NullPointerException

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Assignee: Unassigned
• Reporter: Dmitry Sysolyatin
• Created: 18/Nov/22 11:23
• Updated: 10/Apr/24 10:30

Description

The current query throws NullPointerException

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;

Test case - https://github.com/apache/calcite/commit/46fe9bc456f2d34cf7dccd29829c9e85abe69d5f

Logical plan before it fails:

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]]) 

Stack trace:

 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

UPD: other queries failing with same trace

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));

Issue Links

• is related to: CALCITE-4560 Wrong plan when decorrelating EXISTS subquery with COALESCE in the predicate
• is related to: CALCITE-5127 Support correlation variables in Project
• relates to: CALCITE-5638 Assertion Failure during planning correlated query with orderby
• relates to: CALCITE-5655 Wrong plan for multiple IN/SOME sub-queries with OR predicate