Details
-
Bug
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
12.0
-
None
Description
Testcase:
Tables:
CREATE TABLE t10(a INT); |
INSERT INTO t10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
CREATE TABLE t12(a INT, b INT); |
INSERT INTO t12 SELECT a,a from t10; |
CREATE TABLE t13(a INT, b INT, c INT, filler CHAR(100), key (a,b)); |
INSERT INTO t13 select a,a,a, 'filler-data' FROM t10; |
Enable join_cache_hashed=on and set join_cache_level = 3
and check, that query plan use BNLH
set optimizer_switch= 'join_cache_hashed=on,join_cache_bka=on,mrr=on'; |
set join_cache_level = 3; |
EXPLAIN SELECT * FROM t12, t13 |
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1); |
Actual result:
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where |
1 SIMPLE t13 hash_ALL a #hash#a 5 test.t12.a 10 Using where; Using join buffer (flat, BNLH join) |
Now set join_cache_level = 5 and use hint BNL():
set join_cache_level = 5; |
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13 |
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1); |
Actual result:
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where |
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |
Expected result:
It is expected to be used BNL(H) for query plan
If disable join_cache_hashed=off it does not matter witch type of join_cache_level is used - BNL(H) doesn't enable with hint BNL()
Attachments
Issue Links
- is caused by
-
MDEV-35504 MySQL 8-style optimizer hints: milestone 1
-
- In Testing
-
- relates to
-
-
- Closed
-
Activity
There seems to be an already existing bug related to `join_cache_hashed=off`. If we run the test case below on any trunk version of MariaDB (before the addition of hints)
CREATE TABLE t10(a INT); |
INSERT INTO t10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
CREATE TABLE t12(a INT, b INT); |
INSERT INTO t12 SELECT a,a from t10; |
CREATE TABLE t13(a INT, b INT, c INT, filler CHAR(100), key (a,b)); |
INSERT INTO t13 select a,a,a, 'filler-data' FROM t10; |
|
|
set optimizer_switch= 'join_cache_hashed=off,join_cache_bka=on,mrr=on'; |
set join_cache_level = 3; |
EXPLAIN SELECT * FROM t12, t13 |
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1); |
|
|
drop table t10, t12, t13; |
we'll get the following output:
EXPLAIN SELECT * FROM t12, t13 |
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1); |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t12 ALL NULL NULL NULL NULL 10 Using where |
1 SIMPLE t13 ref a a 5 test.t12.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |
`join_cache_level == 3` means `non-incremental join cache used for BNLH join algorithm` while BKA/BKAH join buffers can only be employed starting from `join_cache_level == 5`. However, BKA buffer is employed with `join_cache_level == 3` .
The first issue seems to be fixed with this patch:
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
|
index 76239050a09..3ea9a88d8be 100644
|
--- a/sql/sql_select.cc
|
+++ b/sql/sql_select.cc
|
@@ -15647,6 +15647,8 @@ uint check_join_cache_usage(JOIN_TAB *tab,
|
BNL_HINT_ENUM, true);
|
bool no_bka_cache= !hint_table_state(join->thd, tab->tab_list->table,
|
BKA_HINT_ENUM, join->allowed_join_cache_types & JOIN_CACHE_BKA_BIT);
|
+ bool hint_forces_bnl= hint_table_state(join->thd, tab->tab_list->table,
|
+ BNL_HINT_ENUM, false);
|
bool hint_forces_bka= hint_table_state(join->thd, tab->tab_list->table,
|
BKA_HINT_ENUM, false);
|
join->return_tab= 0;
|
@@ -15833,7 +15835,8 @@ uint check_join_cache_usage(JOIN_TAB *tab,
|
|
if ((cache_level <=4 && !no_hashed_cache) || no_bka_cache ||
|
tab->is_ref_for_hash_join() ||
|
- ((flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6))
|
+ ((flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6) ||
|
+ hint_forces_bnl)
|
{
|
if (no_bnl_cache)
|
goto no_join_cache;
|
but the second one with `join_cache_hashed=off` still persists.
Branch `bb-12.0-MDEV-36133-bnl` is ready for review.