[jira] [Created] (CALCITE-1583) Wrong results for query with correlated subqueries with aggregate subquery expression

["Vineet Garg (JIRA)" <ji...@apache.org>]

Vineet Garg created CALCITE-1583:
------------------------------------

             Summary: Wrong results for query with correlated subqueries with aggregate subquery expression
                 Key: CALCITE-1583
                 URL: https://issues.apache.org/jira/browse/CALCITE-1583
             Project: Calcite
          Issue Type: Bug
            Reporter: Vineet Garg
            Assignee: Julian Hyde


Following query produces wrong result:

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

Expected Result:
{noformat}
deptno |   name    
--------+-----------
     10 | Sales
     20 | Marketing
     30 | Accounts
(3 rows)
{noformat}

Actual results
{noformat} zero rows {noformat}


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.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)