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

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

    [ https://issues.apache.org/jira/browse/CALCITE-1605?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15838939#comment-15838939 ] 

Vineet Garg commented on CALCITE-1605:
--------------------------------------

Plan generate by test case added in {{RelOptRulesTest}} for following query
{code} select * from emp where empno-1 IN (select min(e.empno) over () from emp e where e.empno = emp.empno) {code}

{code}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
    LogicalJoin(condition=[AND(=($0, $10), =(-($0, 1), $9))], joinType=[inner])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{0, 1}])
        LogicalProject(EXPR$0=[$0], EMPNO0=[$1])
          LogicalProject(EXPR$0=[MIN($0) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], EMPNO0=[$9])
            LogicalJoin(condition=[=($0, $9)], joinType=[inner])
              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
              LogicalAggregate(group=[{0}])
                LogicalProject(EMPNO=[$0])
                  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}

Note that in above plan windowing clause is missing {{partition by empno}}. Since {{empno}} is a correlated var it needs to be added to partition by otherwise windowing will be done on all groups of empno.

> 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
>              Labels: sub-query
>
> 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)