MySQL Bugs: #33348: select does not return all records when we have a covering index on a column .

Bug #33348	select does not return all records when we have a covering index on a column .
Submitted:	19 Dec 2007 2:15	Modified:	14 Feb 2008 14:08
Reporter:	Victor Paul
Status:	Duplicate
Category:	Server: MyISAM	Severity:	S2 (Serious)
Version:	5.0.37 & 5.0.45 & 5.1	OS:	Linux (Ubontu Fiesty and Gutsy)
Assigned to:	Georgi Kodinov	Target Version:	5.0+
Tags:	order by, GROUP BY
Triage:	D2 (Serious) / R2 (Low) / E3 (Medium)

Description:
When we add covering index to a table ,some off the records are missing in the select
when using aggregate function.

How to repeat:
CREATE TABLE `tmp_customer` (
  `customer_id` int(11) NOT NULL default '0',
  `master_customer_id` int(11) default NULL,
  `cdate` datetime,
  `balance` bigint(20) default '0',
  UNIQUE KEY `customer_id` (`customer_id`),
  KEY `master_customer_id` (`master_customer_id`,`cdate`)
) ;

Run the Insert from the attached text file. I tried to put the insert in this Area ,but
the bug report did not allow me to do it 

select master_customer_id, max(cdate)
from tmp_customer
where master_customer_id is not null group by 1 order by 1

You will see that all these master_customer_ids: 97, 122, 174, 205, 246, 265 are missing.

If you drop the index on master_customer_id (`master_customer_id`,`cdate`)

You will see those records. It seems like the Btree index is having some bug

Insert statement for 555 records

Attachment: bug.txt (text/plain), 17.79 KiB.

Thank you for the bug report. This bug not affects InnoDB engine.

|                264 | 2007-10-31 00:00:00 |
|                266 | 2007-11-30 00:00:00 |
|                267 | 2007-11-30 00:00:00 |
|                268 | 2007-11-30 00:00:00 |
|                269 | 2007-11-20 00:00:00 |
|                270 | 2007-11-30 00:00:00 |
|                271 | 2007-10-31 00:00:00 |
|                272 | 2007-11-26 00:00:00 |
|                273 | 2007-11-28 00:00:00 |
|                274 | 2007-11-29 00:00:00 |
|                275 | 2007-11-30 00:00:00 |
|                276 | 2007-12-05 00:00:00 |
|                277 | 2007-12-09 00:00:00 |
|                278 | 2007-12-12 00:00:00 |
|                279 | 2007-12-13 00:00:00 |
|                280 | 2007-12-13 00:00:00 |
+--------------------+---------------------+
251 rows in set (0.03 sec)

mysql> alter table tmp_customer drop index master_customer_id;
Query OK, 555 rows affected (0.09 sec)
Records: 555  Duplicates: 0  Warnings: 0

|                265 | 2007-11-30 00:00:00 |
|                266 | 2007-11-30 00:00:00 |
|                267 | 2007-11-30 00:00:00 |
|                268 | 2007-11-30 00:00:00 |
|                269 | 2007-11-20 00:00:00 |
|                270 | 2007-11-30 00:00:00 |
|                271 | 2007-10-31 00:00:00 |
|                272 | 2007-11-26 00:00:00 |
|                273 | 2007-11-28 00:00:00 |
|                274 | 2007-11-29 00:00:00 |
|                275 | 2007-11-30 00:00:00 |
|                276 | 2007-12-05 00:00:00 |
|                277 | 2007-12-09 00:00:00 |
|                278 | 2007-12-12 00:00:00 |
|                279 | 2007-12-13 00:00:00 |
|                280 | 2007-12-13 00:00:00 |
+--------------------+---------------------+
257 rows in set (0.00 sec)

mysql> CREATE TABLE `tmp_customer` (
    ->   `customer_id` int(11) NOT NULL default '0',
    ->   `master_customer_id` int(11) default NULL,
    ->   `cdate` datetime,
    ->   `balance` bigint(20) default '0',
    ->   UNIQUE KEY `customer_id` (`customer_id`),
    ->   KEY `master_customer_id` (`master_customer_id`,`cdate`)
    -> ) engine = innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> select master_customer_id, max(cdate)
    -> from tmp_customer
    -> where master_customer_id is not null group by 1 order by 1
    -> ;

|                265 | 2007-11-30 00:00:00 |
|                266 | 2007-11-30 00:00:00 |
|                267 | 2007-11-30 00:00:00 |
|                268 | 2007-11-30 00:00:00 |
|                269 | 2007-11-20 00:00:00 |
|                270 | 2007-11-30 00:00:00 |
|                271 | 2007-10-31 00:00:00 |
|                272 | 2007-11-26 00:00:00 |
|                273 | 2007-11-28 00:00:00 |
|                274 | 2007-11-29 00:00:00 |
|                275 | 2007-11-30 00:00:00 |
|                276 | 2007-12-05 00:00:00 |
|                277 | 2007-12-09 00:00:00 |
|                278 | 2007-12-12 00:00:00 |
|                279 | 2007-12-13 00:00:00 |
|                280 | 2007-12-13 00:00:00 |
+--------------------+---------------------+
257 rows in set (0.00 sec)

You are right. we are aware that this bug does not effect Innodb. It is a MYISAM storage
Engine bug.

These techniques can be used to work around the problem

add max(customer_id) to query 
add ignore index(master_customer_id) to query 
alter table tmp_customer engine=innodb

This bug is a duplicate of bug #33758.
What happens here is that the key buffer in MyISAM was too short to hold the key + the
key block pointer.  As a result _mi_get_prev_key() call at the end of _mi_search() was
overwriting bytes of the key we're searching for (from mi_rkey()) when the key found was
not on a leaf page.