Julian Hyde created CALCITE-6452:
------------------------------------
Summary: 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
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)
