Merge 10.4 into 10.5

1 files changed, 30 insertions, 0 deletions

diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index 36cc3ad0a93..79c5b7923bd 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -6126,6 +6126,36 @@ a	b	c	d	e
 DROP TABLE t1,t2,t3,t4;
 set join_cache_level=@save_join_cache_level;
 #
+# MDEV-24767: forced BNLH used for equi-join supported by compound index
+#
+create table t1 (a int, b int, c int ) engine=myisam ;
+create table t2 (a int, b int, c int, primary key (c,a,b)) engine=myisam ;
+insert into t1 values (3,4,2), (5,6,4);
+insert into t2 values (3,4,2), (5,6,4);
+select t1.a, t1.b, t1.c from t1,t2
+where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c;
+a	b	c
+3	4	2
+5	6	4
+explain select t1.a, t1.b, t1.c from t1,t2
+where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	12	test.t1.c,test.t1.a,test.t1.b	1	Using index
+set join_cache_level=3;
+select t1.a, t1.b, t1.c from t1,t2
+where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c;
+a	b	c
+3	4	2
+5	6	4
+explain select t1.a, t1.b, t1.c from t1,t2
+where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	hash_index	PRIMARY	#hash#PRIMARY:PRIMARY	12:12	test.t1.c,test.t1.a,test.t1.b	2	Using index; Using join buffer (flat, BNLH join)
+drop table t1,t2;
+set join_cache_level=@save_join_cache_level;
+#
 # MDEV-21243: Join buffer: condition is checked in wrong place for range access
 #
 create table t1(a int primary key);