[DRILL-6173] Support transitive closure during filter push down and partition pruning

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.12.0
• Fix Version/s: 1.14.0
• Component/s: Query Planning & Optimization
• Labels: doc-complete ready-to-commit
• Assignee: Vitalii Diravka
• Reporter: Vitalii Diravka
• Reviewer: Aman Sinha
• Created: 21/Feb/18 14:26
• Updated: 14/Jun/18 23:18
• Resolved: 29/Apr/18 20:45

Description

There is Calcite rule JoinPushTransitivePredicatesRule but it does not work in Drill.
Applying it in Drill will allow for equi-join queries to push filter condition from one table to another:

select * 
from A, B 
where
A.id = B.id and 
B.id = 100

In that case it is possible that Scan operator for A table will not scan all data.
For table A it can lead for applying:
1. Partition pruning for Hive tables and partiotion/directory pruning for file system tables
2. Parquet filter pushdown

 

Note: transitive closure doesn't work for some cases, these Calcite issues can resolve them:
CALCITE-1048, CALCITE-2274, CALCITE-2275, CALCITE-2241. They are tracked by DRILL-6350

Issue Links

• Dependency: DRILL-6350 Umbrella jira which tracks issues connected to Transitive Closure Inference
• depends upon: CALCITE-1048 Make metadata more robust
• is blocked by: CALCITE-2200 Infinite loop for JoinPushTransitivePredicatesRule
• is blocked by: CALCITE-2205 JoinPushTransitivePredicatesRule should not create a Filter on top of an equivalent Filter
• is related to: DRILL-6374 Transitive Closure leads to TPCH Queries regressions and OOM when run concurrency test
• is related to: CALCITE-2275 Do not push down NOT condition in JOIN
• is related to: CALCITE-2274 Filter predicates aren't inferred while using dynamic star in subquery
• is related to: CALCITE-1362 Filter aboved Join should consider the join key equivalence to push through
• is related to: CALCITE-2257 Combination of predicates can be proved to be always true
• relates to: CALCITE-1299 Reference type of Pulled Up Predicates is not handled properly
• relates to: CALCITE-2242 Using custom RelBuilder for FilterRemoveIsNotDistinctFromRule
• relates to: CALCITE-2241 Join equality condition should be considered while pushing down filter conditions with expressions disjunction for both tables
• links to: GitHub Pull Request #1216