revision-id: 8f7a6cde580298116b50b144984f996dc6af60ae (mariadb-10.2.31-811-g8f7a6cd) parent(s): 56274bd5e4115d86059936ddd3bf656dab1b4354 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-22 22:04:54 -0700 message: 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. --- mysql-test/r/join_cache.result | 30 ++++++++++++++++++++++++++++++ mysql-test/t/join_cache.test | 24 ++++++++++++++++++++++++ sql/sql_join_cache.cc | 2 +- 3 files changed, 55 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 87c4079..6b458e0 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; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 15cd1e9..55ae6af 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -4050,5 +4050,29 @@ DROP TABLE t1,t2,t3,t4; set join_cache_level=@save_join_cache_level; +--echo # +--echo # MDEV-24767: forced BNLH used for equi-join supported by compound index +--echo # + +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); + +let $q= +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; + +eval $q; +eval explain $q; + +set join_cache_level=3; +eval $q; +eval explain $q; + +drop table t1,t2; + +set join_cache_level=@save_join_cache_level; + # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 594094a..4f9facc 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -1199,7 +1199,7 @@ bool JOIN_CACHE::check_emb_key_usage() Item *item= ref->items[i]->real_item(); Field *fld= ((Item_field *) item)->field; CACHE_FIELD *init_copy= field_descr+flag_fields+i; - for (j= i, copy= init_copy; i < local_key_arg_fields; i++, copy++) + for (j= i, copy= init_copy; j < local_key_arg_fields; j++, copy++) { if (fld->eq(copy->field)) {