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

["Julian Hyde (Jira)" <ji...@apache.org>]

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)