[MDEV-11172] EXPLAIN shows non-sensical value for key_len with type=index Created: 2016-10-29  Updated: 2021-06-10  Resolved: 2021-01-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.18, 10.0.28, 10.2.2
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-9658 Make MyRocks in MariaDB stable Closed
Sprint: 10.1.31

 Description    

create table ten(a int);
 
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
create table one_k(a int);
 
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;


create table t1 (
 
  pk varchar(50),
 
  a varchar(20),
 
  filler varchar(100),
 
  index(a),
 
  primary key(pk)
 
) engine=innodb;
 
insert into t1 select a,a,a from test.one_k;


explain select pk from t1 order by a;
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  t1      index   NULL    a       25      NULL    1120    Using index

The key_len=25 does not make any sense. They key format is

offset what
0-1 Column a, NULL-byte
1-3 Column a, length bytes
3-23 Column a, 20 characters
23-25 PK, length bytes
25- PK, 50 values

25 bytes means include PK's length but not include the PK value itself.

 Comments   
Comment by Sergei Petrunia [ 2016-10-29 ]

MySQL 5.6 shows key_len=23.

Comment by Sergei Petrunia [ 2016-10-29 ]

The wrong values comes from KEY_INFO::key_length It is computed in TABLE_SHARE::init_from_binary_frm_image(), create_key_infos(). One can see HA_KEY_BLOB_LENGTH to be added twice.

Comment by Sergei Petrunia [ 2016-10-29 ]

This issue was discovered when working on MDEV-9658. (but there's really no connection. For MDEV-9658, I can just update the .result files)

Comment by Varun Gupta (Inactive) [ 2019-04-30 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-January/011917.html

Comment by Michael Widenius [ 2021-01-28 ]

Patch generally ok. However I would like to see the following additions:

Change in structs.h:
typedef struct st_key {
uint key_length; /* Tot length of key */
to
uint key_length; /* total length of user defined key parts */

In table.cc, add a comment before:
keyinfo->key_length+= HA_KEY_NULL_LENGTH;
Why we don't have to use
if (i < keyinfo->user_defined_key_parts)
here

Comment by Varun Gupta (Inactive) [ 2021-01-30 ]

Made the changes and pushed the patch

Generated at Thu Feb 08 07:47:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.