[HIVE-21074] Hive bucketed table query pruning does not work for IS NOT NULL condition

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 3.1.0, 3.0.0, 3.1.1
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Logical Optimizer
• Labels: pull-request-available
• Assignee: Ádám Szita
• Reporter: Thai Bui
• Created: 28/Dec/18 08:23
• Updated: 17/Nov/22 08:55
• Resolved: 13/Jan/22 08:31

Description

The current version of bucket pruning skips all the predicates when it detects that one of the predicates is a compound type (e.g. NOT(IS_NULL) ) when evaluating AND logical operators.

This logic is faulty since as long as one of the AND operators is a bucketed column (col = literal), the literal value of that col should be considered in the bucket pruning optimization no matter what. For example:

SELECT * FROM tbl WHERE bucketed_col = 1 AND (some_compound_expr)

Then the the value '1' should be considered for pruning in the query plan. This limitation has manifested into a simpler case where a table that I am trying to optimized using bucketing technique is not effective when IS NOT NULL is used. Since IS NOT NULL is parsed into NOT(IS_NULL) (a compound expression), the pruning phase is completed skipped causing unnecessary tasks to be spawned. For instance:

SELECT * FROM tbl WHERE bucketed_col = 1 AND some_other_col IS NOT NULL

Will not trigger bucket pruning logic and perform a full table scan.

Issue Links

• is duplicated by: HIVE-19097 related equals and in operators may cause inaccurate stats estimations
• links to: GitHub Pull Request #2931