[
https://issues.apache.org/jira/browse/CALCITE-7936?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen updated CALCITE-7936:
-------------------------------
Description:
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:
{code:java}
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;
{code}
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|#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|#5408]([https://github.com/apache/calcite/pull/5408]) contains
an initial implementation.
was:
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.
> 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
> Priority: Major
>
> 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:
>
> {code:java}
> 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;
> {code}
>
>
> 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|#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|#5408]([https://github.com/apache/calcite/pull/5408])
> contains an initial implementation.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
