[ASTERIXDB-3445] Hash join picked by CBO in the presence of non-equality join predicates.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: COMP - Compiler
• Labels: triaged
• Assignee: Vijay Sarathy
• Reporter: Vijay Sarathy
• Created: 26/Jun/24 04:34
• Updated: 07/Aug/24 14:39
• Resolved: 24/Jul/24 13:32

Description

When we have multiple join predicates with a mix of equality and non-equality predicates, CBO picks a hash join as an applicable join method with the incorrect assumption that the physical optimizer will use only the equality predicate as the hash join predicate and apply the non-equality predicate as a post join filter. However, currently, the physical optimizer does have this capability, and until this enhancement is added, CBO should not pick hash join as an applicable join method when there is a non-equality predicate even though there are other equality join predicates. A hash join should only be considered if all the join predicates are equality predicates.