Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1583

Wrong results for query with correlated subqueries with aggregate subquery expression

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      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.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              vgarg Vineet Garg
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated: