diff options
Diffstat (limited to 'mysql-test/r/join_cache.result')
| -rw-r--r-- | mysql-test/r/join_cache.result | 30 |
1 files changed, 30 insertions, 0 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 87c40790cfa..6b458e0733a 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -6107,4 +6107,34 @@ WHERE e IS NULL; 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; set @@optimizer_switch=@save_optimizer_switch; |