[
https://issues.apache.org/jira/browse/CALCITE-7936?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18143801#comment-18143801
]
Zhen Chen commented on CALCITE-7936:
------------------------------------
To be honest, I’m not entirely clear on the details of the new top-down
decoupler; this JIRA ticket was created based on AI analysis and suggestions
from [~rubenql] . Ideally, this PR will pass the review process, but if it
doesn’t, I hope someone with the necessary expertise can take it over.
> 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)
