MySQL Bugs: #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested column

Bug #37742	HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested column
Submitted:	30 Jun 2008 14:33	Modified:	29 Jan 2009 20:21
Reporter:	Zardosht Kasheff (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.23-rc	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	HA_EXTRA_KEYREAD, prefix

Description:
Take a table with the following schema:

CREATE TABLE `foo` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`c`(3)),
  KEY `b` (`b`)
)

Insert 10 elements into the table. The perform the following query:
select c from please where b > SOME_VALUE;

For storage engines that expose the flag HA_PRIMARY_KEY_IN_READ_INDEX, MySQL will claim that this query is a covering index on the key b. The problem is that the primary key does not have the entire column of 'c', it has a prefix. Unless I misunderstand the meaning of the flag, the HA_EXTRA_KEYREAD flag should not be set here, because more than the key needs to be copied from the storage engine.

I will note that this scenario does not cause a behavioral bug in InnoDB. InnoDB seems to know about this problem and works around it. Nevertheless, it seems to be a bug.

How to repeat:
mentioned in the description

Suggested fix:
In such scenarios, the flag HA_EXTRA_KEYREAD should not be set

Which storage engine exactly do you used?

Verified as described by using 5.1 bzr tree.
CREATE TABLE `foo` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` char(10) NOT NULL DEFAULT '',
 PRIMARY KEY (`c`(3)),
KEY `b` (`b`)
)engine=innodb;

CREATE TABLE `bar` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` char(10) NOT NULL DEFAULT '',
 PRIMARY KEY (`c`(3)),
KEY `b` (`b`)
)engine=myisam;

insert into foo values(1,2,'abcdefghij');
insert into bar values(1,2,'abcdefghij');
insert into foo values(2,3,'');
insert into bar values(2,3,'');
insert into foo values(3,4,'klmnopqrst');
insert into bar values(3,4,'klmnopqrst');
insert into foo values(4,5,'uvwxyz');
insert into bar values(4,5,'uvwxyz');
insert into foo values(5,6,'meotnsyglt');
insert into bar values(5,6,'meotnsyglt');
insert into foo values(4,5,'asfdewe');
insert into bar values(4,5,'asfdewe');

INNODB:

explain select c from foo where b>2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: b
          key: b
      key_len: 5
          ref: NULL
         rows: 3
        Extra: Using where; Using index

MYISAM:
explain select c from bar where b>2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bar
         type: ALL
possible_keys: b
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where

I recognised that I did the wrong test.

Actually, the test you describe above demonstrates the problem. The InnoDB query plan shows the issue, that MySQL is reporting this as a covering index when it is not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58436

2703 Georgi Kodinov	2008-11-11
      Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested
      column
      
      When the storage engine uses secondary keys clustered with the primary key MySQL was
      adding the primary key parts to each secondary key.
      In doing so it was not checking whether the index was on full columns and this
      resulted in the secondary keys being added to the list of covering keys even if 
      they have partial columns.
      Fixed by cleaning up the list of covering keys if the primary key has a partial
      column as a key part.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60242

2703 Georgi Kodinov	2008-11-29
      Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested
      column
            
      When the storage engine uses secondary keys clustered with the primary key MySQL was
      adding the primary key parts to each secondary key.
      In doing so it was not checking whether the index was on full columns and this
      resulted in the secondary keys being added to the list of covering keys even if 
      they have partial columns.
      Fixed by not adding a primary key part to the list of columns that can be used 
      for index read of the secondary keys when the primary key part is a partial key part.

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

Noted in 5.1.31, 6.0.10 changelog.

Primary keys were treated as part of a covering index even if only a
prefix of a key column was used.