[jira] [Commented] (IGNITE-18186) Calcite engine. Not correlated condition is not pushed to table scan if there is also correlated condition in the filter

["Konstantin Orlov (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-18186?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17635752#comment-17635752 ] 

Konstantin Orlov commented on IGNITE-18186:
-------------------------------------------

[~alex_pl], LGTM!

> Calcite engine. Not correlated condition is not pushed to table scan if there is also correlated condition in the filter
> ------------------------------------------------------------------------------------------------------------------------
>
>                 Key: IGNITE-18186
>                 URL: https://issues.apache.org/jira/browse/IGNITE-18186
>             Project: Ignite
>          Issue Type: Improvement
>            Reporter: Aleksey Plekhanov
>            Assignee: Aleksey Plekhanov
>            Priority: Major
>              Labels: calcite, calcite2-required, calcite3-required
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> Not correlated condition cannot be pushed to table scan if there is also correlated condition in the filter.
> For example, in this query:
> {code:java}
> SELECT (SELECT id FROM tbl AS t2 WHERE t2.id < 50 AND t2.id = t1.id) FROM tbl AS t1 {code}
> Condition {{t2.id < 50}} can be pushed to table scan and condition {{t2.id = t1.id}} can be used as hash spool search row, but currently filter can't be splitted and plan uses table spool instead of hash spool and become very ineffective:
> {noformat}
> IgniteProject(EXPR$0=[$1])
>   IgniteCorrelatedNestedLoopJoin(condition=[true], joinType=[left], variablesSet=[[$cor0]], correlationVariables=[[$cor0]])
>     IgniteExchange(distribution=[single])
>       IgniteTableScan(table=[[PUBLIC, TBL]])
>     IgniteColocatedHashAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
>       IgniteFilter(condition=[AND(<($0, 50), =($0, $cor0.ID))])
>         IgniteTableSpool(readType=[LAZY], writeType=[EAGER])
>           IgniteExchange(distribution=[single])
>             IgniteTableScan(table=[[PUBLIC, TBL]]){noformat}



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