revision-id: 90cb7212742e9ae3a63bd183e171c95bd12d559f (mariadb-5.5.60-42-g90cb721) parent(s): 9d41dd2f39f5a0c840d77e5fb7fc8d1396bf1a33 author: Igor Babaev committer: Igor Babaev timestamp: 2018-06-29 22:46:38 -0700 message: MDEV-16603 Crash with set join_cache_level=4 When the definition of the index used for hash join was created in create_hj_key_for_table() it could cause memory overwrite due to a bug that led to an underestimation of the number of the index component. --- mysql-test/r/join_cache.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/join_cache.test | 31 +++++++++++++++++++++++++++++++ sql/sql_select.cc | 4 ++-- 3 files changed, 66 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 386f711..f1e6fb5 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5871,4 +5871,37 @@ SET join_buffer_size = default; SET join_buffer_space_limit= default; set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t4,t5,t2; +# +# MDEV-16603: BNLH for query with materialized semi-join +# +set join_cache_level=4; +CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (7,'x'); +CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; +INSERT INTO t2 VALUES +(NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), +(228,'x'),(3,'y'),(1,'z'),(9,'z'); +CREATE TABLE temp +SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); +SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); +f1 f2 +7 x +7 x +7 x +7 x +7 x +7 x +7 x +EXPLAIN EXTENDED SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 +1 PRIMARY temp hash_ALL NULL #hash#$hj 9 test.t1.i1,test.t1.v1 7 100.00 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 1 100.00 Using where +2 MATERIALIZED t2 hash_index v1 #hash#v1:v1 4:9 test.t1.v1 10 10.00 Using join buffer (flat, BNLH join) +Warnings: +Note 1003 select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where ((`test`.`temp`.`f1` = `test`.`t1`.`i1`) and (`test`.`t2`.`v1` = `test`.`t1`.`v1`) and (`test`.`temp`.`f2` = `test`.`t1`.`v1`)) +DROP TABLE t1,t2,temp; +SET join_cache_level = default; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 58a7b88..d82b4fa 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3836,5 +3836,36 @@ set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t4,t5,t2; +--echo # +--echo # MDEV-16603: BNLH for query with materialized semi-join +--echo # + +--source include/have_innodb.inc + +set join_cache_level=4; + +CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (7,'x'); + +CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; + +INSERT INTO t2 VALUES + (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), + (228,'x'),(3,'y'),(1,'z'),(9,'z'); + +CREATE TABLE temp +SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); + +let $q = +SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2,temp; + +SET join_cache_level = default; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b79431a..700b7b3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7994,7 +7994,6 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, if (first_keyuse) { key_parts++; - first_keyuse= FALSE; } else { @@ -8004,7 +8003,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, if (curr->keypart == keyuse->keypart && !(~used_tables & curr->used_tables) && join_tab->keyuse_is_valid_for_access_in_chosen_plan(join, - keyuse) && + curr) && are_tables_local(join_tab, curr->used_tables)) break; } @@ -8012,6 +8011,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, key_parts++; } } + first_keyuse= FALSE; keyuse++; } while (keyuse->table == table && keyuse->is_for_hash_join()); if (!key_parts)