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

Wrong result/plan for correlated subquery with windowing

    XMLWordPrintableJSON

    Details

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

      Description

      Calcite produces wrong plan, hence wrong results for correlated subquery which contains windowing function.

      Query

      select * from emps where empid-1 IN (select min(e.empid) over () from emps e where e.empid =emps.empid); 

      Expected

       0 rows 

      Actual

      +-------+------+--------+--------+------+-------+-----+---------+---------+----------+
      | EMPNO | NAME | DEPTNO | GENDER | CITY | EMPID | AGE | SLACKER | MANAGER | JOINEDAT |
      +-------+------+--------+--------+------+-------+-----+---------+---------+----------+
      | 110   | John | 40     | M      | Vancouver | 2     | null | false   | true    | 2002-05-03 |
      | 130   | Alice | 40     | F      | Vancouver | 2     | null | false   | true    | 2007-01-01 |
      +-------+------+--------+--------+------+-------+-----+---------+---------+----------+
      

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated: