[CALCITE-2241] Join equality condition should be considered while pushing down filter conditions with expressions disjunction for both tables

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.16.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Vitalii Diravka
• Created: 05/Apr/18 16:45
• Updated: 31/Oct/19 11:02

Description

The filter condition on Join with expressions for both join inputs can be pushed further to inputs only if that condition is conjunction of expressions. It will be good if filter condition with disjunction of such expressions will be pushed past a Join by taking into account equality inference of join condition.
Query example:

SELECT t1.deptno FROM sales.emp t1 join sales.emp t2 ON t1.deptno = t2.deptno WHERE t1.deptno = 1 OR t2.deptno = 4

Expected plan:

LogicalProject(DEPTNO=[$7])
  LogicalJoin(condition=[AND(=($7, $16))], joinType=[inner])
      LogicalFilter(condition=[OR(=($7, 1), =($7, 4))])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalFilter(condition=[OR(=($16, 1), =($16, 4))])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Actual plan:

LogicalProject(DEPTNO=[$7])
  LogicalJoin(condition=[AND(=($7, $16), OR(=($7, 1), =($16, 4)))], joinType=[inner])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])

The changes can be done for RelMdPredicates.JoinConditionBasedPredicateInference code and will be applied by using JoinPushTransitivePredicatesRule

Issue Links

• Is contained by: DRILL-6350 Umbrella jira which tracks issues connected to Transitive Closure Inference
• is related to: CALCITE-2296 Extra logic to derive additional filters in the FilterJoinRule
• is related to: DRILL-6173 Support transitive closure during filter push down and partition pruning