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

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

    [ https://issues.apache.org/jira/browse/CALCITE-5645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17712810#comment-17712810 ] 

Julian Hyde commented on CALCITE-5645:
--------------------------------------

The "RexInputRef index 7 out of range 0..4" error is similar to CALCITE-5210.

> 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
>            Priority: Major
>
> 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)