Description:
This hint in SQL queries /*+ NO_BNL() */
is expected to disable hash join in the query, as explained in
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-table-level
<quote>
hint_name: These hint names are permitted:
...
BNL, NO_BNL: Enable or disable block nested loop for the specified tables. In MySQL 8.0.18 and later, these hints also enable and disable the hash join optimization.
...
tbl_name: The name of a table used in the statement. The hint applies to all tables that it names. If the hint names no tables, it applies to all tables of the query block in which it occurs.
</quote>
It doesn't work if one of the tables is a merged view:
create table t(a int, b int);
create view v as select 3 from t;
explain format=tree select /*+ no_bnl() */ * from t join v on t.a=v.`3`*100;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (no condition) (cost=0.70 rows=1)
...
"Hash join" should not be seen here, "Nested loop join" should be. That's the bug.
If I use optimizer_switch="block_nested_loop=off" then it works, I get "nested loop join".
If I replace the view 'v' with a normal table it works too.
How to repeat:
create table t(a int, b int); create view v as select 3 from t;
# you'll see hash join
explain format=tree select /*+ no_bnl() */ * from t join v on t.a=v.`3`*100;
Suggested fix:
As the view is merged, what we get in the list of tables, after merging, is table "t" (initially specified) and merged table "t" (merged from "v").
Then when the optimizer decides to do hash join or not, for each of the two tables it calls:
bool get_hint_table_state(const TABLE_LIST *table_list, opt_hints_enum type_arg,
bool *hint_val) {
if (table_list->opt_hints_qb) {
bool ret_val = false;
if (get_hint_state(table_list->opt_hints_table, table_list->opt_hints_qb,
type_arg, &ret_val)) {
*hint_val = ret_val;
return true;
}
}
return false;
}
Likely, for the merged "t", opt_hints_qb above is not set. Because hint parsing is done before view merging. So, the merged "t" is not prevented from doing hash join. It's only a guess.
