[jira] [Created] (CALCITE-1605) Wrong result/plan for correlated subquery with windowing

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

Vineet Garg created CALCITE-1605:
------------------------------------

             Summary: Wrong result/plan for correlated subquery with windowing
                 Key: CALCITE-1605
                 URL: https://issues.apache.org/jira/browse/CALCITE-1605
             Project: Calcite
          Issue Type: Bug
          Components: core
            Reporter: Vineet Garg
            Assignee: Julian Hyde


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

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

Expected
{code} 0 rows {code}

Actual
{noformat}
+-------+------+--------+--------+------+-------+-----+---------+---------+----------+
| 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 |
+-------+------+--------+--------+------+-------+-----+---------+---------+----------+
{noformat}





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