[
https://issues.apache.org/jira/browse/CALCITE-7584?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7584:
------------------------------------
Labels: pull-request-available (was: )
> RelDecorrelator produces incorrect results for correlated LATERAL sub-queries
> with window functions
> ---------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7584
> URL: https://issues.apache.org/jira/browse/CALCITE-7584
> Project: Calcite
> Issue Type: Bug
> Reporter: Weihua Zhang
> Assignee: Weihua Zhang
> Priority: Major
> Labels: pull-request-available
>
> *RelDecorrelator* may produce incorrect results when decorrelating a
> correlated *LATERAL* sub-query that contains a window function.
> The problem appears when the inner query has correlation variables that are
> partially satisfied by equality predicates, while the window expression still
> references additional outer fields.
> For example:
> - `WHERE e.deptno = d.deptno` can satisfy the correlation on `e.deptno` using
> the inner field `d.deptno`;
> - but the window expression still references another outer field such as
> `e.empno`;
> - during decorrelation, the already satisfied correlation mapping may be
> overwritten or not preserved correctly when a value generator is introduced
> for the remaining correlation variables;
> - as a result, the equality predicate or the window partitioning semantics
> may be broken.
> This can lead to incorrect results for correlated *LATERAL* queries with
> *OVER* expressions.
> Reproducer
> Using the *scott* schema:
> {code:sql}
> SELECT e.ename, s.empno, s.m
> FROM emp e
> JOIN LATERAL (
> SELECT e2.empno,
> MAX(e2.empno + e.sal) OVER (PARTITION BY e.deptno, e.job) AS m
> FROM emp e2
> WHERE e2.deptno = e.deptno
> AND e2.job = e.job
> ) s ON TRUE
> WHERE e.empno IN (7369, 7499, 7788)
> ORDER BY e.empno, s.empno;
> +-------+-------+----------+
> | ENAME | EMPNO | M |
> +-------+-------+----------+
> | SMITH | 7369 | 8676.00 |
> | SMITH | 7876 | 8676.00 |
> | ALLEN | 7499 | 9444.00 |
> | ALLEN | 7521 | 9444.00 |
> | ALLEN | 7654 | 9444.00 |
> | ALLEN | 7844 | 9444.00 |
> | SCOTT | 7788 | 10902.00 |
> | SCOTT | 7902 | 10902.00 |
> +-------+-------+----------+
> (8 rows)
> {code}
> but return:
> {code:java}
> +-------+-------+----------+
> | ENAME | EMPNO | M |
> +-------+-------+----------+
> | SMITH | 7369 | 8734.00 |
> | SMITH | 7499 | 8734.00 |
> | SMITH | 7521 | 8734.00 |
> | SMITH | 7566 | 8734.00 |
> | SMITH | 7654 | 8734.00 |
> | SMITH | 7698 | 8734.00 |
> | SMITH | 7782 | 8734.00 |
> | SMITH | 7788 | 8734.00 |
> | SMITH | 7839 | 8734.00 |
> | SMITH | 7844 | 8734.00 |
> | SMITH | 7876 | 8734.00 |
> | SMITH | 7900 | 8734.00 |
> | SMITH | 7902 | 8734.00 |
> | SMITH | 7934 | 8734.00 |
> | ALLEN | 7369 | 9534.00 |
> | ALLEN | 7499 | 9534.00 |
> | ALLEN | 7521 | 9534.00 |
> | ALLEN | 7566 | 9534.00 |
> | ALLEN | 7654 | 9534.00 |
> | ALLEN | 7698 | 9534.00 |
> | ALLEN | 7782 | 9534.00 |
> | ALLEN | 7788 | 9534.00 |
> | ALLEN | 7839 | 9534.00 |
> | ALLEN | 7844 | 9534.00 |
> | ALLEN | 7876 | 9534.00 |
> | ALLEN | 7900 | 9534.00 |
> | ALLEN | 7902 | 9534.00 |
> | ALLEN | 7934 | 9534.00 |
> | SCOTT | 7369 | 10934.00 |
> | SCOTT | 7499 | 10934.00 |
> | SCOTT | 7521 | 10934.00 |
> | SCOTT | 7566 | 10934.00 |
> | SCOTT | 7654 | 10934.00 |
> | SCOTT | 7698 | 10934.00 |
> | SCOTT | 7782 | 10934.00 |
> | SCOTT | 7788 | 10934.00 |
> | SCOTT | 7839 | 10934.00 |
> | SCOTT | 7844 | 10934.00 |
> | SCOTT | 7876 | 10934.00 |
> | SCOTT | 7900 | 10934.00 |
> | SCOTT | 7902 | 10934.00 |
> | SCOTT | 7934 | 10934.00 |
> +-------+-------+----------+
> (42 rows)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
