[
https://issues.apache.org/jira/browse/IGNITE-18891?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yury Gerzhedovich updated IGNITE-18891:
---------------------------------------
Description:
The ticket is a copy of IGNITE-18186 to port it from AI2 to AI3.
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}
was:
The ticket is a copy ofIGNITE-18186 to port it from AI2 to AI3.
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}
> Sql. Not correlated condition is not pushed to table scan if there is also
> correlated condition in the filter
> -------------------------------------------------------------------------------------------------------------
>
> Key: IGNITE-18891
> URL: https://issues.apache.org/jira/browse/IGNITE-18891
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Reporter: Yury Gerzhedovich
> Priority: Major
> Labels: ignite-3
>
> The ticket is a copy of IGNITE-18186 to port it from AI2 to AI3.
> 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)
