diff options
| author | Igor Babaev <igor@askmonty.org> | 2021-03-22 22:04:54 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2021-03-22 22:04:54 -0700 |
| commit | 8f7a6cde580298116b50b144984f996dc6af60ae (patch) | |
| tree | 4c72a5ad525c72dc9e2214110d26bdbff52d0038 /mysql-test/r | |
| parent | 56274bd5e4115d86059936ddd3bf656dab1b4354 (diff) | |
| download | mariadb-git-8f7a6cde580298116b50b144984f996dc6af60ae.tar.gz | |
MDEV-24767 Wrong result when forced BNLH is used for join supported
by compound index
This typo bug may lead to wrong result sets for equi-join queries where
the join operation is supported by a compound index such that the order of
its components differs from the order of the corresponding columns in
the table the index belongs to. The bug manifests itself only when usage
of the BNLH algorithm is forced.
The fix for the bug was provided by Chu Huaxing.
Diffstat (limited to 'mysql-test/r')
| -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; |