Julian Hyde created CALCITE-5645: ------------------------------------ Summary: Correlated scalar sub-query returns incorrect results when the correlating variable is NULL Key: CALCITE-5645 URL: https://issues.apache.org/jira/browse/CALCITE-5645 Project: Calcite Issue Type: Bug Reporter: Julian Hyde
Correlated scalar sub-query returns incorrect results when the correlating variable is NULL. For example, consider the following view and query: {code} !use scott !set outputformat mysql create view emp1 as select empno, ename, job, sal, case when job = 'ANALYST' then null else deptno end as deptno from emp); !ok select ename, (select count(*) as c from emp1 as e2 where e2.deptno is not distinct from e1.deptno) as c from emp1 as e1; !ok select ename, deptno, count(*) over (partition by deptno) as c from emp1 as e1; +--------+--------+---+ | ENAME | DEPTNO | C | +--------+--------+---+ | ADAMS | 20 | 3 | | ALLEN | 30 | 6 | | BLAKE | 30 | 6 | | CLARK | 10 | 3 | | FORD | | 2 | | JAMES | 30 | 6 | | JONES | 20 | 3 | | KING | 10 | 3 | | MARTIN | 30 | 6 | | MILLER | 10 | 3 | | SCOTT | | 2 | | SMITH | 20 | 3 | | TURNER | 30 | 6 | | WARD | 30 | 6 | +--------+--------+---+ (14 rows) !ok {code} The {{emp1}} view just provides null values for the {{deptno}} column. The two queries should be equivalent, but the first throws: {noformat} java.lang.AssertionError: RexInputRef index 7 out of range 0..4 at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31) at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:125) at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:61) at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:113) at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:144) at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:61) at org.apache.calcite.rex.RexCall.accept(RexCall.java:189) at org.apache.calcite.rel.core.Project.isValid(Project.java:262) at org.apache.calcite.rel.core.Project.<init>(Project.java:107) at org.apache.calcite.rel.logical.LogicalProject.<init>(LogicalProject.java:75) at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:166) at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:143) at org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:199) at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:2117) at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1888) at org.apache.calcite.tools.RelBuilder.projectNamed(RelBuilder.java:2209) at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:4638) {noformat} The simpler query {code} select ename, deptno, (select count(*) from emp1 as e2 where e1.deptno is null) as c from emp1 as e1; !ok {code} throws a similar exception. This functionality is necessary for supporting measures (see CALCITE-4496) with NULL keys in the GROUP BY, or with GROUPING SETS. -- This message was sent by Atlassian Jira (v8.20.10#820010)