[
https://issues.apache.org/jira/browse/CALCITE-6914?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Silun Dong updated CALCITE-6914:
--------------------------------
Description:
For the following SQL:
{code:java}
select t1.name from t1, t2
where t1.id = t2.id
and (
(t1.id > 20 and t2.height < 50)
or
(t1.weight < 200 and t2.sales > 100)
) {code}
The disjunction {{(t1.id > 20 and t2.height < 50) or (t1.weight < 200 and
t2.sales > 100)}} uses t1 and t2, thus cannot be pushed below the join.
In fact, we can extract redundant {{(t1.id > 20 or t1.weight < 200)}} ,
{{(t2.height < 50 or t2.sales > 100)}} from this predicate by a new rule and
push down these two predicates to both side of Join input. The paper
??Quantifying TPC-H choke points and their optimizations?? describes this
optimization in 4.4 section.
Some notes:
# The expanded redundant predicates can be pushed down, but the original
disjunction cannot be removed.
# This rule should only be applied once to avoid generate same redundant
expression.
# This rule should be used before {{FilterJoinRule}} and it seems to resolve
the TODO in {{{}FilterJoinRule{}}}.
was:
For the following SQL:
{code:java}
select t1.name from t1, t2
where t1.id = t2.id
and (
(t1.id > 20 and t2.height < 50)
or
(t1.weight < 200 and t2.sales > 100)
) {code}
The disjuction {{(t1.id > 20 and t2.height < 50) or (t1.weight < 200 and
t2.sales > 100)}} uses t1 and t2, thus cannot be pushed below the join.
In fact, we can extract redundant {{(t1.id > 20 or t1.weight < 200)}} ,
{{(t2.height < 50 or t2.sales > 100)}} from this predicate by a new rule and
push down these two predicates to both side of Join input. The paper
??Quantifying TPC-H choke points and their optimizations?? describes this
optimization in 4.4 section.
Some notes:
# The expanded redundant predicates can be pushed down, but the original
disjunction cannot be removed.
# This rule should only be applied once to avoid generate same redundant
expression.
# This rule should be used before {{FilterJoinRule}} and it seems to resolve
the TODO in {{{}FilterJoinRule{}}}.
> Expand join-dependent predicates from disjunction
> -------------------------------------------------
>
> Key: CALCITE-6914
> URL: https://issues.apache.org/jira/browse/CALCITE-6914
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Affects Versions: 1.39.0
> Reporter: Silun Dong
> Assignee: Silun Dong
> Priority: Major
> Labels: pull-request-available
>
> For the following SQL:
> {code:java}
> select t1.name from t1, t2
> where t1.id = t2.id
> and (
> (t1.id > 20 and t2.height < 50)
> or
> (t1.weight < 200 and t2.sales > 100)
> ) {code}
> The disjunction {{(t1.id > 20 and t2.height < 50) or (t1.weight < 200 and
> t2.sales > 100)}} uses t1 and t2, thus cannot be pushed below the join.
> In fact, we can extract redundant {{(t1.id > 20 or t1.weight < 200)}} ,
> {{(t2.height < 50 or t2.sales > 100)}} from this predicate by a new rule and
> push down these two predicates to both side of Join input. The paper
> ??Quantifying TPC-H choke points and their optimizations?? describes this
> optimization in 4.4 section.
>
> Some notes:
> # The expanded redundant predicates can be pushed down, but the original
> disjunction cannot be removed.
> # This rule should only be applied once to avoid generate same redundant
> expression.
> # This rule should be used before {{FilterJoinRule}} and it seems to resolve
> the TODO in {{{}FilterJoinRule{}}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
