If one enables BNL-H joins, this may change efficient plans with ref access into BNL-H joins with full table scans. See MDEV-16337.

This makes it impossible to recommend to enable BNL-H joins except in some restricted scenarios.

This MDEV is about changing the optimizer code in a way that enabling BNL-H joins does not introduce regressions.

Limitations

Note that join buffering code is very inefficient in case of VARCHAR columns. A quote from https://jira.mariadb.org/browse/MDEV-34352?focusedCommentId=286897&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-286897

Indeed, the BNL-H join has "non-embedded keys" (check JOIN_CACHE::check_emb_key_usage()). If we are joining a varchar(100) column, the join buffer record for it WILL include space for 100 characters (lookup tuple in KeyTupleFormat), even if the joined values are much shorter

Some notes about the code

Starting from 11.0, best_access_path does this:

• Sets pos->type= JT_HASH for BNL-H plans (also sets pos->use_join_buffer).
• Sets pos->use_join_buffer for full table scan plans that intend to use BNL.

After get_best_combination(), for BNL-H plans we have:

tab->is_ref_for_hash_join()==TRUE.

tab->type == JT_REF  (NOT JT_HASH)