[jira] [Updated] (CALCITE-6452) Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-6452?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

ASF GitHub Bot updated CALCITE-6452:
------------------------------------
    Labels: pull-request-available  (was: )

> Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result
> ------------------------------------------------------------------------
>
>                 Key: CALCITE-6452
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6452
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: Julian Hyde
>            Assignee: Adam Markowitz
>            Priority: Major
>              Labels: pull-request-available
>
> Scalar sub-query that uses an {{IS NOT DISTINCT FROM}} condition returns incorrect result. For example,
> {code:java}
> select e.ename,
>   (select count(*)
>     from emp as f
>     where f.comm is not distinct from e.comm) as c
> from emp as e{code}
> returns
> {noformat}
> +--------+----+
> | ENAME  | C  |
> +--------+----+
> | ADAMS  |  0 |
> | ALLEN  |  1 |
> | BLAKE  |  0 |
> | CLARK  |  0 |
> | FORD   |  0 |
> | JAMES  |  0 |
> | JONES  |  0 |
> | KING   |  0 |
> | MARTIN |  1 |
> | MILLER |  0 |
> | SCOTT  |  0 |
> | SMITH  |  0 |
> | TURNER |  1 |
> | WARD   |  1 |
> +--------+----+
> (14 rows)
> {noformat}
> but should return
> {noformat}
> +--------+----+
> | ENAME  | C  |
> +--------+----+
> | ADAMS  |  0 |
> | ALLEN  | 10 |
> | BLAKE  |  0 |
> | CLARK  |  0 |
> | FORD   |  0 |
> | JAMES  |  0 |
> | JONES  |  0 |
> | KING   |  0 |
> | MARTIN | 10 |
> | MILLER |  0 |
> | SCOTT  |  0 |
> | SMITH  |  0 |
> | TURNER | 10 |
> | WARD   | 10 |
> +--------+----+
> (14 rows)
> {noformat}
> Also, and perhaps related, if we add a {{WHERE}} to the above query, like this:
> {code:java}
> select e.ename,
>   (select count(*)
>     from emp as f
>     where f.comm is not distinct from e.comm) as c
> from emp as e
> where e.deptno = 10{code}
> Calcite throws:
> {noformat}
> Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 6 out of bounds for length 5
> > 	at com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:77)
> > 	at org.apache.calcite.rex.RexBuilder.makeInputRef(RexBuilder.java:1037)
> > 	at org.apache.calcite.sql2rel.RelDecorrelator$AdjustProjectForCountAggregateRule.lambda$onMatch2$4(RelDecorrelator.java:2679)
> {noformat}
> I ran into this error while trying to rewrite queries that had measures and used {{ROLLUP}}. (Since {{ROLLUP}} will generate group keys whose value is NULL, we cannot use regular {{=}} when doing a self-join.)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)