[CALCITE-5645] Correlated scalar sub-query returns incorrect results when the correlating variable is NULL

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 13/Apr/23 17:36
• Updated: 16/Apr/23 16:25

Description

Correlated scalar sub-query returns incorrect results when the correlating variable is NULL. For example, consider the following view and query:

!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

The emp1 view just provides null values for the deptno column.

The two queries should be equivalent, but the first throws:

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)

The simpler query

select ename, deptno,
    (select count(*)
     from emp1 as e2
     where e1.deptno is null) as c
from emp1 as e1;
!ok

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.

Issue Links

• relates to: CALCITE-5210 "type mismatch" litmus test failure during during SqlToRelConverter for group-by on `case` having `in` expression predicates exceeding SqlRelConverter.Config InSubQueryThreshold with nullable left-hand-side