revision-id: 962624a3f82f36f13551afc049eab903f82de6e0 (mariadb-10.2.31-679-g962624a3f82) parent(s): a87320e335b6345f1ce04eea8b790a741c1ec23e author: Varun Gupta committer: Varun Gupta timestamp: 2021-01-20 17:20:53 +0530 message: MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index The issue happens when the secondary keys are extended with primary key parts. Inside the function TABLE_SHARE::init_from_binary_frm_image() adds the length bytes for the primary key key parts to the length of the secondary key. This is not needed because when the extended keys are used we recalculate the length for the used key parts. --- mysql-test/r/innodb_ext_key.result | 16 ++++++++++++++++ mysql-test/t/innodb_ext_key.test | 19 +++++++++++++++++++ sql/table.cc | 3 ++- 3 files changed, 37 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index c7a5b1a80ca..880d7a8bceb 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -790,3 +790,19 @@ EXPLAIN } drop table t1; SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( +pk VARCHAR(50), +a VARCHAR(20), +KEY k1(a), +PRIMARY KEY(pk) +)ENGINE=INNODB; +INSERT INTO t2 SELECT a,a FROM t1; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL k1 23 NULL 10 Using index +DROP TABLE t1,t2; diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test index f5edd736490..de9ca10ff1b 100644 --- a/mysql-test/t/innodb_ext_key.test +++ b/mysql-test/t/innodb_ext_key.test @@ -606,3 +606,22 @@ explain format= json select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and f1 <= '3'; drop table t1; SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +--echo # + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +CREATE TABLE t2 ( + pk VARCHAR(50), + a VARCHAR(20), + KEY k1(a), + PRIMARY KEY(pk) +)ENGINE=INNODB; + +INSERT INTO t2 SELECT a,a FROM t1; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); + +DROP TABLE t1,t2; diff --git a/sql/table.cc b/sql/table.cc index e4492f21a30..04bede3f8a3 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -2285,7 +2285,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, else key_part->key_part_flag|= HA_VAR_LENGTH_PART; key_part->store_length+=HA_KEY_BLOB_LENGTH; - keyinfo->key_length+= HA_KEY_BLOB_LENGTH; + if (i < keyinfo->user_defined_key_parts) + keyinfo->key_length+= HA_KEY_BLOB_LENGTH; } if (field->type() == MYSQL_TYPE_BIT) key_part->key_part_flag|= HA_BIT_PART;