[ https://issues.apache.org/jira/browse/CALCITE-6452?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17867113#comment-17867113 ]
TJ Banghart commented on CALCITE-6452: -------------------------------------- I can see why the first case results in {{1}} rather than {{10}} for matching cases. If we flatten the query to: {code:java} select e.ename, e.comm as ecomm, f.comm as fcomm, f.comm is not distinct from e.comm as is_distinct from emp as e, emp f; {code} We get the following for {{ALLEN}} {noformat} +--------+---------+---------+-------------+ | ENAME | ECOMM | FCOMM | IS_DISTINCT | +--------+---------+---------+-------------+ ... | ALLEN | 300.00 | 0.00 | false | | ALLEN | 300.00 | 1400.00 | false | | ALLEN | 300.00 | 300.00 | true | | ALLEN | 300.00 | 500.00 | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | | ALLEN | 300.00 | | false | ... {noformat} Exactly one case where {{e.comm}} and {{f.comm}} are NOT distinct from each other. The same is true for {{MARTIN}}, {{TURNER}}, and {{WARD}} entries. To me this looks correct, but I may be misunderstanding `IS NOT DISTINCT` in this context. > 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: TJ Banghart > Priority: Major > > 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)