[jira] [Created] (IGNITE-18891) Sql. Not correlated condition is not pushed to table scan if there is also correlated condition in the filter

["Yury Gerzhedovich (Jira)" <ji...@apache.org>]

Yury Gerzhedovich created IGNITE-18891:
------------------------------------------

             Summary: 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


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}



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