[CALCITE-1583] Wrong results for query with correlated subqueries with aggregate subquery expression

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: sub-query
• Assignee: xiong duan
• Reporter: Vineet Garg
• Created: 17/Jan/17 18:31
• Updated: 27/Apr/22 01:45

Description

Following query produces wrong result:

 select * from depts where exists (select sum(empno) from emps where depts.deptno = emps.deptno and 1=2) 

Expected Result:

deptno |   name    
--------+-----------
     10 | Sales
     20 | Marketing
     30 | Accounts
(3 rows)

Actual results

 zero rows 

Calcite rewrites such queries into JOIN which ignores the fact that aggregate functions such as sum always produce one row, effectively making EXISTS predicate always true.

Same is the case with Scalar and IN sub-queries.

Issue Links

• relates to: CALCITE-4693 Query with Lateral Join is converted to Inner Join instead of Left Join
• relates to: CALCITE-5117 Optimize the EXISTS sub-query by Metadata RowCount