[ASTERIXDB-3352] Join hints not honored

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: COMP - Compiler
• Labels: triaged
• Assignee: Vijay Sarathy
• Reporter: Vijay Sarathy
• Created: 07/Feb/24 18:08
• Updated: 09/Feb/24 18:06
• Resolved: 09/Feb/24 18:06

Description

For the following query, the join hints are not honored.

select 061, count(1) from R join S
on int64_default_null (R.u64) /+ indexnl */ /+ productivity R 65536.0 */ = int64_default_null (S.u64)
join T on int64_default_null (S.u256) /+ hashjoin build (S) */ /+ productivity S 16384.0 */ = int64_default_null (T.u256)
where R.rand /*+ selectivity 0.000015258789063 */ <= 64
and S.rand /*+ selectivity 0.000003814697266 */ <= 16
and T.rand /*+ selectivity 0.000003814697266 */ <= 16;

Upon debugging, I see that the hints are being used during plan enumeration, but another plan which uses one of the hints (but not both hints), is costed cheaper and is picked. The fix would be to favor plans that use more hints when comparing two plans irrespective of cost.