[jira] [Assigned] (CALCITE-5631) Optimization to merge redundant joins

["Hanumath Rao Maduri (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-5631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Hanumath Rao Maduri reassigned CALCITE-5631:
--------------------------------------------

    Assignee: Hanumath Rao Maduri

> Optimization to merge redundant joins
> -------------------------------------
>
>                 Key: CALCITE-5631
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5631
>             Project: Calcite
>          Issue Type: Wish
>            Reporter: Ian Bertolacci
>            Assignee: Hanumath Rao Maduri
>            Priority: Major
>
> There are situations where multiple joins can be merged into one.
> For example:
> {code:sql}
> select
>    (select numarrayagg(C5633_203) from T893 where C5633_586 = T895.id), 
>    (select numarrayagg(C5633_170) from T893 where C5633_586 = T895.id) 
> from T895
> {code}
> having this RelNode tree (after decorrelation):
> {code:java}
> 01:LogicalProject(EXPR$0=[$70], EXPR$1=[$72])
> └─ 02:LogicalJoin(condition=[=($0, $71)], joinType=[left])
>    ├─ 03:LogicalProject(...)
>    |  └─ 04:LogicalJoin(condition=[=($0, $70)], joinType=[left])
>    |     ├─ 05:QueryTableScan(table=[[QUERY, T895]])
>    |     └─ 06:LogicalAggregate(group=[{0}], EXPR$0=[ARRAY_AGG($1)])
>    |        └─ 07:LogicalProject(C5633_586=[$85], C5633_203=[$45])
>    |           └─ 08:LogicalFilter(condition=[=($85, $85)])
>    |              └─ 09:QueryTableScan(table=[[QUERY, T893]])
>    └─ 10:LogicalAggregate(group=[{0}], EXPR$0=[ARRAY_AGG($1)])
>       └─ 12:LogicalProject(C5633_586=[$85], C5633_170=[$12])
>          └─ 12:LogicalFilter(condition=[=($85, $85)])
>             └─ 13:QueryTableScan(table=[[QUERY, T893]])
> {code}
> can be simplified as this SQL:
> {code:sql}
> select 
>     agg.agg1, 
>     agg.agg2 
> from 
>    T895 left join 
>    (select 
>       C5633_586, 
>       numarrayagg(C5633_203) as agg1, 
>       numarrayagg(C5633_170) as agg2 
>       from T893 group by C5633_586
>       where C5633_586 is not null
>    ) as agg 
> on agg.C5633_586 = T895.id
> {code}
> with this RelNode tree:
> {code:java}
> 20:LogicalProject(agg1=[$71], agg2=[$72])
> └─ 21:LogicalJoin(condition=[=($0, $70)], joinType=[left])
>    ├─ 23:QueryTableScan(table=[[QUERY, T895]])
>    └─ 24:LogicalAggregate(group=[{0}], agg1=[ARRAY_AGG($1)], agg2=[ARRAY_AGG($2)])
>       └─ 25:LogicalProject(C5633_586=[$85], C5633_203=[$45], C5633_170=[$12])
>          └─ 26:LogicalFilter(condition=[IS_NOT_NULL($85)])
>             └─ 27:QueryTableScan(table=[[QUERY, T893]])
> {code}
> This can be done by identifying joins on the same underlying columns with "mergeable" strings on of nodes on the same sides of the target joins
> (I wish I had a precise definition of "mergable", but I don't. but it is easy to see that the two RHSs in the first example can be combined (merged) into the RHS in the second example without changing the result.)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)