Zhen Chen created CALCITE-7936:
----------------------------------
Summary: Top-down decorrelator should avoid redundant domain joins
for nested LATERAL queries
Key: CALCITE-7936
URL: https://issues.apache.org/jira/browse/CALCITE-7936
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Zhen Chen
The top-down decorrelator can generate unnecessarily complex plans for nested
LATERAL queries. When a nested INNER Correlate has a left input that does not
reference any enclosing correlation variables, the current implementation still
joins that input with the outer correlation domain. This introduces a redundant
cross join and additional correlation columns into the generated plan.
For example, consider the following query with top-down decorrelation enabled:
```sql
WITH dept (deptno, dname) AS (
VALUES (10, 'ACCOUNTING'), (20, 'RESEARCH')
),
emp (empno, deptno, ename) AS (
VALUES (7369, 20, 'SMITH'),
(7782, 10, 'CLARK'),
(7499, 30, 'ALLEN')
)
SELECT *
FROM dept,
(VALUES 'A', 'M') AS v (v)
CROSS JOIN LATERAL (
SELECT *
FROM emp
WHERE emp.deptno = dept.deptno
AND POSITION(v.v IN emp.ename) > 0
) AS emp;
```
Here, the left input of the nested LATERAL join (`v`) is independent of `dept`,
while its right input references both `dept` and `v`. The enclosing correlation
variables can be carried by the right input, allowing the resulting join to use
an independently decorrelated left input without attaching the outer domain to
it.
The proposed improvement is to avoid this redundant domain propagation for
nested INNER Correlates. The combined domain required to decorrelate the right
input must remain available, and the rewrite must preserve duplicate rows, NULL
correlation keys, and output column mappings. Other join types and left inputs
that reference enclosing correlation variables require their existing treatment.
Generating a smaller equivalent plan reduces unnecessary joins and columns
passed to subsequent optimization, and can reduce the work required to explore
alternative plans. Regression coverage should verify both the simplified plan
structure and result correctness.
This opportunity was identified while investigating
[CALCITE-7928](https://issues.apache.org/jira/browse/CALCITE-7928). As
explained in [PR #5410](https://github.com/apache/calcite/pull/5410), the
original excessive planning time involves join-reordering rules and
`PROJECT_MERGE` generating many duplicate Projects. That PR disables
`PROJECT_MERGE` for the affected test as a workaround; the underlying planner
issue remains separate.
This ticket tracks the decorrelator improvement independently of that planner
issue. [PR #5408](https://github.com/apache/calcite/pull/5408) contains an
initial implementation.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
