[
https://issues.apache.org/jira/browse/CALCITE-6946?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Silun Dong updated CALCITE-6946:
--------------------------------
Description:
Similar to Calcite-6914, we can expand redundant predicates from the
disjunction and push them down. However, the predicates expanded by
Calcite-6914 must belong to a single table, that is, if join_type does not
restrict pushdown, the redundant predicates can be pushed directly on TableScan.
However, pushing predicates down to the inputs of Join is also very useful
([link|https://github.com/apache/calcite/pull/4267#discussion_r2021576009]),
for example:
{code:java}
select t1.id from t1, t2, t3
where t1.id = t2.id
and t1.id = t3.id
and (
(t1.age < 50 and t3.age > 20)
or
(t2.weight > 70 and t3.height < 180)
) {code}
Because {{(t1.age < 50 and t3.age > 20) or (t2.weight > 70 and t3.height <
180)}} a is a disjunction and involves multiple tables, it cannot be pushed
down.
However, we can expand it to {{(t1.age < 50 or t2.weight > 70)}} , {{{}(t3.age
> 20 or t3.height < 180){}}}, and push them down to both sides of Join.
was:
Similar to Calcite-6914, we can expand redundant predicates from the
disjunction and push them down. However, the predicates expanded by
Calcite-6914 must belong to a single table, that is, if join_type does not
restrict pushdown, the redundant predicates can be pushed directly on TableScan.
However, pushing predicates down to the inputs of Join is also very useful
([link|https://github.com/apache/calcite/pull/4267#discussion_r2021576009]),
for example:
{code:java}
select t1.id from t1, t2, t3
where t1.id = t2.id
and t1.id = t3.id
and (
(t1.age < 50 and t3.age > 20)
or
(t2.weight > 70 and t3.height < 180)
) {code}
Because {{(t1.age < 50 and t3.age > 20) or (t2.weight > 70 and t3.height <
180)}} a is a disjunction and involves multiple tables, it cannot be pushed
down.
However, we can expand it to {{(t1.age < 50 or t2.weight > 70)}} , {{{}(t3.age
> 20 or t3.height < 180){}}}, and push them down to both sides of Join.
The initial plan is like this:
{code:java}
Filter
Join(true)
Join(true)
Scan(t1)
Scan(t2)
Scan(t3){code}
Push down directly without expanding the predicate:
{code:java}
Filter
Join(t1.id=t3.id)
Join(t1.id=t2.id)
Scan(t1)
Scan(t2)
Scan(t3){code}
Push down after expansion:
{code:java}
Filter
Join(t1.id=t3.id)
Join(t1.id=t2.id)
Scan(t1)
Scan(t2)
Filter()
Scan(t3) {code}
> Expand predicates from disjunction for inputs of Join
> -----------------------------------------------------
>
> Key: CALCITE-6946
> URL: https://issues.apache.org/jira/browse/CALCITE-6946
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Affects Versions: 1.39.0
> Reporter: Silun Dong
> Assignee: Silun Dong
> Priority: Major
>
> Similar to Calcite-6914, we can expand redundant predicates from the
> disjunction and push them down. However, the predicates expanded by
> Calcite-6914 must belong to a single table, that is, if join_type does not
> restrict pushdown, the redundant predicates can be pushed directly on
> TableScan.
> However, pushing predicates down to the inputs of Join is also very useful
> ([link|https://github.com/apache/calcite/pull/4267#discussion_r2021576009]),
> for example:
> {code:java}
> select t1.id from t1, t2, t3
> where t1.id = t2.id
> and t1.id = t3.id
> and (
> (t1.age < 50 and t3.age > 20)
> or
> (t2.weight > 70 and t3.height < 180)
> ) {code}
> Because {{(t1.age < 50 and t3.age > 20) or (t2.weight > 70 and t3.height <
> 180)}} a is a disjunction and involves multiple tables, it cannot be pushed
> down.
> However, we can expand it to {{(t1.age < 50 or t2.weight > 70)}} ,
> {{{}(t3.age > 20 or t3.height < 180){}}}, and push them down to both sides of
> Join.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
