[DRILL-8400] Fix pruning partitions with pushed transitive predicates

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.20.3
• Fix Version/s: 1.21.0
• Assignee: Vova Vysotskyi
• Reporter: Vova Vysotskyi
• Created: 12/Feb/23 12:57
• Updated: 14/Feb/23 15:00
• Resolved: 14/Feb/23 15:00

Description

See TestHivePartitionPruning.prunePartitionsBasedOnTransitivePredicates() test for details.

The issue occurs for queries like these:

SELECT * FROM hive.partition_pruning_test t1 
JOIN hive.partition_with_few_schemas t2 ON t1.`d` = t2.`d` AND t1.`e` = t2.`e` 
WHERE t2.`e` IS NOT NULL AND t1.`d` = 1

The expected behavior is to create additional filters based on the existing filters and join conditions. We have a TRANSITIVE_CLOSURE planning phase, which is responsible for such query transformations, but Drill pushes down filters from the WHERE condition before that phase, so the optimization is not performed.

Ideally, we should move rules from the TRANSITIVE_CLOSURE phase to the LOGICAL phase so that the planner will choose the most optimal plan, but it wouldn't help until CALCITE-1048 is fixed (it is required to pull predicates when three has RelSubset nodes).