This is an automated email from the ASF dual-hosted git repository. tjbanghart pushed a commit to branch calcite-6452 in repository https://gitbox.apache.org/repos/asf/calcite.git
commit e4a5c1036dfb0a95b71683f2d41bf1cc56330f32 Author: TJ Banghart <[email protected]> AuthorDate: Wed Jul 17 21:37:51 2024 -0700 Test case for [CALCITE-6452] Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result --- core/src/test/resources/sql/scalar.iq | 67 +++++++++++++++++++++++++++++++++++ 1 file changed, 67 insertions(+) diff --git a/core/src/test/resources/sql/scalar.iq b/core/src/test/resources/sql/scalar.iq index 365c90e4af..447dc4f6f9 100644 --- a/core/src/test/resources/sql/scalar.iq +++ b/core/src/test/resources/sql/scalar.iq @@ -239,4 +239,71 @@ select deptno, (select empno from "scott".emp order by empno limit 1) as x from !ok +# Scalar subquery that joins on nullable column +select e.ename, + (select count(*) + from emp as f + where f.comm is not distinct from e.comm) as c +from emp as e; ++--------+----+ +| 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) + +!ok + +# As above, with WHERE +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; ++--------+----+ +| ENAME | C | ++--------+----+ +| CLARK | 0 | +| KING | 0 | +| MILLER | 0 | +| SCOTT | 0 | +| SMITH | 0 | +| TURNER | 10 | +| WARD | 10 | ++--------+----+ +(14 rows) + +!ok + +# Justification for previous result +select comm, count(*) as c +from emp +group by comm; ++---------+----+ +| COMM | C | ++---------+----+ +| 0.00 | 1 | +| 1400.00 | 1 | +| 300.00 | 1 | +| 500.00 | 1 | +| | 10 | ++---------+----+ +(5 rows) + +!ok + # End scalar.iq
