Weihua Zhang created CALCITE-7584:
-------------------------------------
Summary: 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
*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)
