[MDEV-36133] Hint BNL() doesn't work when join_cache_level>= 5 or join_cache_hashed=off - Jira

Details

Type: Bug
Status: In Review (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 12.0
Fix Version/s: 12.0
Component/s: Optimizer
Labels:
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

MDEV-36165 BKA join cache buffer is employed despite join_cache_level=3 (flat BNLH)

Closed

Activity

Ascending order - Click to sort in descending order

Lena Startseva created issue - 2025-02-20 10:56

Lena Startseva made changes - 2025-02-20 10:56

Field	Original Value	New Value
Link		This issue is caused by MDEV-35504 [ MDEV-35504 ]

Lena Startseva made changes - 2025-02-20 10:57

Description

*Testcase:*
*_Tables:_*
{code:sql}
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;
{codel}

Enable *_join_cache_hashed=on_* and set *_join_cache_level = 3_*
and check, that query plan use *BNLH*

{code:sql}
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);
{code}

_*Actual result:*_
{code:sql}
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)
{code}

Now set *_join_cache_level = 5_* and use hint BNL():
{code:sql}
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
{code}

*_Actual result:_*
{code:sql}
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
{code}

*_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()

*Testcase:*
*_Tables:_*
{code:sql}
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;
{codel}

Enable *_join_cache_hashed=on_* and set *_join_cache_level = 3_*
and check, that query plan use *BNLH*

{code:sql}
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);
{code}

_*Actual result:*_
{code:sql}
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)
{code}

Now set *_join_cache_level = 5_* and use hint BNL():
{code:sql}
set join_cache_level = 5;
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
{code}

*_Actual result:_*
{code:sql}
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
{code}

*_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()

Lena Startseva made changes - 2025-02-20 10:57

Description

*Testcase:*
*_Tables:_*
{code:sql}
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;
{codel}

Enable *_join_cache_hashed=on_* and set *_join_cache_level = 3_*
and check, that query plan use *BNLH*

{code:sql}
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);
{code}

_*Actual result:*_
{code:sql}
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)
{code}

Now set *_join_cache_level = 5_* and use hint BNL():
{code:sql}
set join_cache_level = 5;
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
{code}

*_Actual result:_*
{code:sql}
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
{code}

*_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()

*Testcase:*
*_Tables:_*
{code:sql}
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;
{codel}

Enable *_join_cache_hashed=on_* and set *_join_cache_level = 3_*
and check, that query plan use *BNLH*

{code:sql}
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);
{code}

_*Actual result:*_
{code:sql}
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)
{code}

Now set *_join_cache_level = 5_* and use hint BNL():
{code:sql}
set join_cache_level = 5;
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
{code}

*_Actual result:_*
{code:sql}
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
{code}

*_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()

Lena Startseva made changes - 2025-02-20 10:58

Description

*Testcase:*
*_Tables:_*
{code:sql}
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;
{codel}

Enable *_join_cache_hashed=on_* and set *_join_cache_level = 3_*
and check, that query plan use *BNLH*

{code:sql}
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);
{code}

_*Actual result:*_
{code:sql}
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)
{code}

Now set *_join_cache_level = 5_* and use hint BNL():
{code:sql}
set join_cache_level = 5;
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
{code}

*_Actual result:_*
{code:sql}
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
{code}

*_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()

*Testcase:*
*_Tables:_*
{code:sql}
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;
{code}

Enable *_join_cache_hashed=on_* and set *_join_cache_level = 3_*
and check, that query plan use *BNLH*

{code:sql}
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);
{code}

_*Actual result:*_
{code:sql}
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)
{code}

Now set *_join_cache_level = 5_* and use hint BNL():
{code:sql}
set join_cache_level = 5;
EXPLAIN SELECT /*+ BNL() */ * FROM t12, t13
WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
{code}

*_Actual result:_*
{code:sql}
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
{code}

*_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()

Lena Startseva made changes - 2025-02-20 10:59

Assignee

Oleg Smirnov [ JIRAUSER50405 ]

Oleg Smirnov made changes - 2025-02-20 11:31

Priority

Major [ 3 ]

Critical [ 2 ]

Oleg Smirnov made changes - 2025-02-20 11:31

Fix Version/s

12.0 [ 29945 ]

Oleg Smirnov made changes - 2025-02-20 11:31

Status

Open [ 1 ]

In Progress [ 3 ]

Oleg Smirnov added a comment - 2025-02-20 15:11

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.

Oleg Smirnov added a comment - 2025-02-20 15:11 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.

Oleg Smirnov added a comment - 2025-02-21 06:43 - edited

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` .

Oleg Smirnov added a comment - 2025-02-21 06:43 - edited 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` .

Oleg Smirnov added a comment - 2025-02-24 05:28

Branch `bb-12.0-MDEV-36133-bnl` is ready for review.

Oleg Smirnov added a comment - 2025-02-24 05:28 Branch `bb-12.0- MDEV-36133 -bnl` is ready for review.

Oleg Smirnov made changes - 2025-02-24 05:28

Assignee	Oleg Smirnov [ JIRAUSER50405 ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleg Smirnov made changes - 2025-02-25 15:01

Link

This issue relates to ~~MDEV-36165~~ [ ~~MDEV-36165~~ ]

People

Assignee:: Sergei Petrunia

Reporter:: Lena Startseva

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2025-02-20 10:56

Updated:: 2 days ago 07:47

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration