Silun Dong created CALCITE-6914:
-----------------------------------
Summary: Expand join-dependent predicates from disjuction
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
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.
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)
