NobiGo commented on code in PR #4750: URL: https://github.com/apache/calcite/pull/4750#discussion_r2714892018
########## core/src/test/resources/sql/sub-query.iq: ########## @@ -7880,4 +7880,546 @@ WHERE deptno NOT IN ( # Reset to default value 20 !set trimfields true +!use scott +# [CALCITE-7379] LHS correlated variables are shadowed by nullable RHS outputs in LEFT JOIN +SELECT + d.dname, + ( + SELECT COUNT(sub.empno) + FROM emp e + LEFT JOIN ( + SELECT * FROM emp e2 WHERE e2.deptno = d.deptno + ) sub + ON e.mgr = sub.mgr + ) as matched_subordinate_count +FROM dept d; ++------------+---------------------------+ +| DNAME | MATCHED_SUBORDINATE_COUNT | ++------------+---------------------------+ +| ACCOUNTING | 4 | +| OPERATIONS | 0 | +| RESEARCH | 9 | +| SALES | 28 | ++------------+---------------------------+ +(4 rows) + +!ok + +SELECT + d.dname, + ( + SELECT COUNT(sub.empno) + FROM ( + SELECT * FROM emp e2 WHERE e2.deptno = d.deptno + ) sub + RIGHT JOIN emp e + ON sub.mgr = e.mgr + ) as matched_subordinate_count +FROM dept d; ++------------+---------------------------+ +| DNAME | MATCHED_SUBORDINATE_COUNT | ++------------+---------------------------+ +| ACCOUNTING | 4 | +| OPERATIONS | 0 | +| RESEARCH | 9 | +| SALES | 28 | ++------------+---------------------------+ +(4 rows) + +!ok + +SELECT + d.dname, + ( + SELECT COUNT(sub.empno) + FROM ( + SELECT * FROM emp e2 WHERE e2.deptno = d.deptno + ) sub + FULL JOIN emp e + ON sub.mgr = e.mgr + ) as matched_subordinate_count +FROM dept d +order by d.dname; ++------------+---------------------------+ +| DNAME | MATCHED_SUBORDINATE_COUNT | ++------------+---------------------------+ +| ACCOUNTING | 5 | +| OPERATIONS | 0 | +| RESEARCH | 9 | +| SALES | 28 | ++------------+---------------------------+ +(4 rows) + +!ok + +SELECT * FROM dept d Review Comment: I suggest adding a brief description to each test case. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
