Bug #34928 Confusion by having Primary Key and Index
Submitted: 28 Feb 2008 14:18 Modified: 7 Apr 2008 17:31
Reporter: Lars Johansson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.23 OS:Any
Assigned to: Alexey Kopytov CPU Architecture:Any

[28 Feb 2008 14:18] Lars Johansson
Description:
Hi,
I got a problem which I find hard to pinpoint. I think it is the combination of 
distinct and order by and vs 5.1.23.

If my table DB3_BOM_PROD is created in 5.1.22 version or the select is run in 5.1.22 the select is ok. But using vs 5.1.23 the select produces garbage randomly. If I run a repair on the table the select seems to work.
This time it looks like garbage is produced only if limit is set to >24, but I do not think that is always the case. A careful look at the script may tell whats going on. (This time it looks like blanks is produced, but I think it can be any 'hex-char')

Server version: 5.1.23-rc-log MySQL Community Server (GPL)
use ACTADW;
mysql> select distinct P_PLANT, P_MATNR,ALT_BOM,BOM_USE from DB3_BOM_PROD  order by  P_PLANT, P_MATNR,ALT_BOM,BOM_USE limit 3;
+---------+------------+---------+---------+
| P_PLANT | P_MATNR    | ALT_BOM | BOM_USE |
+---------+------------+---------+---------+
| 0101    | 0102024200 | 01      | 1       |
| 0101    | 0102095135 | 01      | 1       |
| 0101    | 0102095142 | 01      | 1       |
+---------+------------+---------+---------+
3 rows in set (0.00 sec)
select distinct P_PLANT, P_MATNR,ALT_BOM,BOM_USE from DB3_BOM_PROD  order by  P_PLANT, P_MATNR,ALT_BOM,BOM_USE limit 25;
+---------+--------------------+---------+---------+
| P_PLANT | P_MATNR            | ALT_BOM | BOM_USE |
+---------+--------------------+---------+---------+
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
|         |                    |         |         |
+---------+--------------------+---------+---------+
25 rows in set (0.00 sec)

mysql> check table   DB3_BOM_PROD EXTENDED;
+---------------------+-------+----------+----------+
| Table               | Op    | Msg_type | Msg_text |
+---------------------+-------+----------+----------+
| ACTADW.DB3_BOM_PROD | check | status   | OK       |
+---------------------+-------+----------+----------+
1 row in set (52.84 sec)

 repair table DB3_BOM_PROD;
+---------------------+--------+----------+----------+
| Table               | Op     | Msg_type | Msg_text |
+---------------------+--------+----------+----------+
| ACTADW.DB3_BOM_PROD | repair | status   | OK       |
+---------------------+--------+----------+----------+
1 row in set (4.73 sec)

mysql> select distinct P_PLANT, P_MATNR,ALT_BOM,BOM_USE from DB3_BOM_PROD  order by  P_PLANT, P_MATNR,ALT_BOM,BOM_USE limit 25;
+---------+------------+---------+---------+
| P_PLANT | P_MATNR    | ALT_BOM | BOM_USE |
+---------+------------+---------+---------+
| 0101    | 0102024200 | 01      | 1       |
| 0101    | 0102095135 | 01      | 1       |
| 0101    | 0102095142 | 01      | 1       |
| 0101    | 0108120100 | 01      | 1       |
| 0101    | 0108169039 | 01      | 1       |
| 0101    | 0108169044 | 01      | 1       |
| 0101    | 0108169089 | 01      | 1       |
| 0101    | 0160099647 | 01      | 1       |
| 0101    | 0261110400 | 01      | 1       |
| 0101    | 0333221200 | 01      | 1       |
| 0101    | 0333321400 | 01      | 1       |
| 0101    | 0333411400 | 01      | 1       |
| 0101    | 0335122000 | 01      | 1       |
| 0101    | 0335123600 | 01      | 1       |
| 0101    | 0335213300 | 01      | 1       |
| 0101    | 0335350022 | 01      | 1       |
| 0101    | 0502109059 | 01      | 1       |
| 0101    | 0502315400 | 01      | 1       |
| 0101    | 0502742308 | 01      | 1       |
| 0101    | 0517010022 | 01      | 1       |
| 0101    | 0517010023 | 01      | 1       |
| 0101    | 0517010024 | 01      | 1       |
| 0101    | 0517011700 | 01      | 1       |
| 0101    | 0518140008 | 01      | 1       |
| 0101    | 0623250500 | 01      | 1       |
+---------+------------+---------+---------+
25 rows in set (0.00 sec)25 rows in set (0.00 sec)

How to repeat:
I do not know how to replicate this one, but I find i serious enough to be worth looking at.
[28 Feb 2008 15:54] Valeriy Kravchuk
Thak you for the problem report. Can you upload that DB3_BOM_PROD EXTENDED table? Please, send SHOW CREATE TABLE and SHOW TABLE STATUS results for it.
[28 Feb 2008 16:16] Lars Johansson
Hi,
Here are show create and show status displays,
i submit two status first on one repaired version and then on an unrepaired version of the table. The index lengths do not match.
I try to send the table tomorrow 

show create table DB3_BOM_PROD
    -> ;

| Table        | Create Table
| DB3_BOM_PROD | CREATE TABLE `DB3_BOM_PROD` (
  `P_PLANT` char(4) NOT NULL DEFAULT '',
  `P_MATNR` char(18) NOT NULL DEFAULT '',
  `P_REFCD` char(2) DEFAULT NULL,
  `P_DIS_CON_IND` char(1) DEFAULT NULL,
  `P_PRO_TYPE` char(1) DEFAULT NULL,
  `P_SPEC_PRO` char(2) DEFAULT NULL,
  `BOM_USE` char(1) NOT NULL DEFAULT '',
  `ALT_BOM` char(2) NOT NULL DEFAULT '',
  `P_MRP_CON` char(3) DEFAULT NULL,
  `DISCON_IND` char(1) DEFAULT NULL,
  `POSNR` char(4) NOT NULL DEFAULT '',
  `C_PLANT` char(4) NOT NULL DEFAULT '',
  `C_MATNR` char(18) NOT NULL DEFAULT '',
  `C_REFCD` char(2) DEFAULT NULL,
  `C_DIS_CON_IND` char(1) DEFAULT NULL,
  `C_PRO_TYPE` char(1) DEFAULT NULL,
  `C_SPEC_PRO` char(2) DEFAULT NULL,
  `C_MRP_CON` char(3) DEFAULT NULL,
  `QTY` decimal(13,3) DEFAULT NULL,
  `UNIT` char(3) DEFAULT NULL,
  `ITEM_CAT` char(1) DEFAULT NULL,
  `REL_COST` char(1) DEFAULT NULL,
  `SORT_STR` char(10) DEFAULT NULL,
  `MAT_TYPE` char(4) DEFAULT NULL,
  `VALID_FR` date DEFAULT NULL,
  `CREATED_DATE` date DEFAULT NULL,
  `CHANGED_DATE` date DEFAULT NULL,
  `CREATED` datetime DEFAULT NULL,
  PRIMARY KEY (`P_PLANT`,`P_MATNR`,`BOM_USE`,`ALT_BOM`,`POSNR`,`C_PLANT`,`C_MATNR`),
  KEY `COMP2` (`P_PLANT`,`P_MATNR`,`ALT_BOM`,`BOM_USE`),
  KEY `P_PLANT` (`P_PLANT`),
  KEY `P_MATNR` (`P_MATNR`),
  KEY `C_PLANT` (`C_PLANT`),
  KEY `C_MATNR` (`C_MATNR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)

mysql> show table status LIKE 'DB3_BOM_PROD'; (from a repaired version)
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| DB3_BOM_PROD | MyISAM |      10 | Fixed      | 266423 |            116 |    30905068 | 32651097298436095 |     17341440 |         0 |           NULL | 2008-02-28 04:02:35 | 2008-02-28 14:51:39 | 2008-02-28 14:51:43 | latin1_swedish_ci |     NULL |                |         |
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> show table status LIKE 'DB3_BOM_PROD'; (from an unrepaired version)
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| DB3_BOM_PROD | MyISAM |      10 | Fixed      | 266423 |            116 |    30905068 | 32651097298436095 |     20739072 |         0 |           NULL | 2008-02-28 04:02:35 | 2008-02-28 04:03:20 | 2008-02-28 04:02:46 | latin1_swedish_ci |     NULL |                |         |
+--------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)
[28 Feb 2008 18:19] Lars Johansson
Here is a log from last nite create of the table DB3_BOM_PR
Note this is mysql vs 5.1.22, (so this works).

080228 040235 2500 Log enabled, invoked as:  ./scriptS.php schedule=dts_bom.xml logmode=warning
080228 040235 2500 Log PHP version 5.2.2, Zend version 2.2.0
080228 040235 2500 Note Current directory=/home/actaadm/dw_data_mngr/data/080228040215_dts_bom/dts_bom_prod, SQL-log=SQLLOG_0.TXT
080228 040235 2500 Note Host info 10.40.23.126 via TCP/IP
080228 040235 2500 Note mysql version 5.1.22-rc-log
080228 040235 2500 Note 10.40.23.126 via TCP/IP protocol version 10
080228 040235 2500 Note Client library vs 5.1.12-beta
080228 040235 2500 Note Default database is ACTADW
080228 040235 2500 Note Start executing sqlscript create
080228 040235 2500 Note Exec USE ACTADW;
080228 040235 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040235 2500 Note Exec DROP TABLE IF EXISTS DB3_BOM_PROD;
080228 040235 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040235 2500 Note Exec CREATE TABLE DB3_BOM_PROD(
 PRIMARY KEY COMP1 (P_PLANT, P_MATNR, BOM_USE, ALT_BOM, POSNR, C_PLANT, C_MATNR)
,INDEX COMP2 (P_PLANT,  P_MATNR, ALT_BOM ,BOM_USE)
,P_PLANT  CHAR(4) 
,INDEX P_PLANT (P_PLANT)
,P_MATNR  CHAR(18) 
,INDEX P_MATNR (P_MATNR)
,P_REFCD  CHAR(2) 
,P_DIS_CON_IND CHAR(1) 
,P_PRO_TYPE  CHAR(1) 
,P_SPEC_PRO CHAR(2) 
,BOM_USE CHAR(1) 
,ALT_BOM CHAR(2)  
,P_MRP_CON  CHAR(3) 
,DISCON_IND CHAR(1) 
,POSNR    CHAR(4) 
,C_PLANT   CHAR(4) 
,INDEX C_PLANT (C_PLANT)
,C_MATNR   CHAR(18) 
,INDEX C_MATNR (C_MATNR)
,C_REFCD   CHAR(2) 
,C_DIS_CON_IND CHAR(1) 
,C_PRO_TYPE CHAR(1) 
,C_SPEC_PRO CHAR(2) 
,C_MRP_CON  CHAR(3) 
,QTY  NUMERIC (13,3)
,UNIT CHAR(3) 
,ITEM_CAT   CHAR(1) 
,REL_COST CHAR(1) 
,SORT_STR CHAR(10) 
,MAT_TYPE CHAR(4) 
,VALID_FR   DATE
,CREATED_DATE DATE
,CHANGED_DATE DATE
,CREATED DATETIME
);
080228 040235 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040235 2500 Note Start executing sqlscript load
080228 040235 2500 Note Exec USE ACTADW;
080228 040235 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040235 2500 Note Exec LOAD DATA LOCAL INFILE 'DTS_STPO_PROD.TXT'                                         
    REPLACE                                                                     
    INTO TABLE DB3_BOM_PROD                                                      
    FIELDS                                                                      
           TERMINATED BY ';'                                                    
    IGNORE 0 LINES                                                              
;
080228 040246 2500 Note SQLSTATE=00000, ERRORNO=0
080228 040246 2500 Note SQLINFO Records: 207483  Deleted: 1  Skipped: 0  Warnings: 0 SQLSTATE=00000, ERRORNO=0
080228 040246 2500 Log disabled ./scriptS.php
[28 Feb 2008 18:23] Lars Johansson
Hi,
the infile we load to create DB3_BOM_PROD is 25mB so I do not know how to transfer it. but I send you a couple of lines. Please tell if you want more and how I should send.

1100;0101121280;;;E;CT;1;01;306;;0010;1100;0101121200;;;F;;140;2.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;0517530095;;;E;CT;1;01;305;;0010;1100;0515800014;;;F;;176;1.000;ST;L;X;0;ZMAT;20050101;20060109;00000000;2008-02-28 02:14:12
1100;0517530095;;;E;CT;1;01;305;;0020;1100;0517530015;;;F;;176;2.000;ST;L;X;0;ZMAT;20050101;20060109;00000000;2008-02-28 02:14:12
1100;0909100260;;;E;CT;1;01;305;;0010;1100;0909100200;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;0909100260;;;E;CT;1;01;305;;0020;1100;4150166760;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;0909100260;;;E;CT;1;01;305;;0030;1100;9900000301;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;0909100290;;;E;CT;1;01;305;;0010;1100;0909100200;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;0909100290;;;E;CT;1;01;305;;0020;1100;4150166760;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;0909100290;;;E;CT;1;01;305;;0030;1100;9900000301;;;F;;140;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;1000000001;Z0;;X;;1;01;178;;0010;1100;0217110041;;;F;;140;2.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;1000000001;Z0;;X;;1;01;178;;0020;1100;0217110042;;;F;;140;2.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;1000000001;Z0;;X;;1;01;178;;0030;1100;0661100048;;;F;;140;2.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;1000000001;Z0;;X;;1;01;178;;0040;1100;4175080801;;;F;;143;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;1000000001;Z0;;X;;1;01;178;;0050;1100;4175088502;;;F;;176;1.000;ST;L;X;0;ZMAT;20040425;20050427;00000000;2008-02-28 02:14:12
1100;2050002813;;;E;;1;01;201;;0010;1100;0054371339;;;F;;184;0.212;KG;L;X;;ROH;20051231;20070125;00000000;2008-02-28 02:14:12
1100;2050114272;;;E;CT;1;01;326;;0005;1100;0335212500;;;F;;140;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
1100;2050114272;;;E;CT;1;01;326;;0006;1100;4250189400;;;F;;143;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
1100;2050114272;;;E;CT;1;01;326;;0007;1100;4250202101;;;F;;143;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
1100;2050114272;;;E;CT;1;01;326;;0008;1100;4250201900;;;F;;143;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
1100;2050114272;;;E;CT;1;01;326;;0009;1100;4250202111;;;F;;143;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
1100;2050114272;;;E;CT;1;01;326;;0011;1100;0663210246;;;F;;140;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
1100;2050114272;;;E;CT;1;01;326;;0012;1100;4250222200;;;F;;176;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
1100;2050114272;;;E;CT;1;01;326;;0013;1100;0663900300;;;F;;140;1.000;ST;L;X;0;ZMAT;20050101;20070917;00000000;2008-02-28 02:14:12
[29 Feb 2008 10:27] Lars Johansson
Hi,
You can create the table in vs 5.1.22 and still se the garbage in vs 5.1.23.
I reproduced the bug with following script and supplied file.

CREATE TABLE DB3_BOM_PROD_LJ1(  PRIMARY KEY COMP1 (P_PLANT, P_MATNR, BOM_USE, ALT_BOM, POSNR, C_PLANT, C_MATNR) ,INDEX COMP2 (P_PLANT,  P_MATNR, ALT_BOM ,BOM_USE) ,P_PLANT  CHAR(4)  ,INDEX P_PLANT (P_PLANT) ,P_MATNR  CHAR(18)  ,INDEX P_MATNR (P_MATNR) ,P_REFCD  CHAR(2)  ,P_DIS_CON_IND CHAR(1)  ,P_PRO_TYPE  CHAR(1)  ,P_SPEC_PRO CHAR(2)  ,BOM_USE CHAR(1)  ,ALT_BOM CHAR(2)   ,P_MRP_CON  CHAR(3)  ,DISCON_IND CHAR(1)  ,POSNR    CHAR(4)  ,C_PLANT   CHAR(4)  ,INDEX C_PLANT (C_PLANT) ,C_MATNR   CHAR(18)  ,INDEX C_MATNR (C_MATNR) ,C_REFCD   CHAR(2)  ,C_DIS_CON_IND CHAR(1)  ,C_PRO_TYPE CHAR(1)  ,C_SPEC_PRO CHAR(2)  ,C_MRP_CON  CHAR(3)  ,QTY  NUMERIC (13,3) ,UNIT CHAR(3)  ,ITEM_CAT   CHAR(1)  ,REL_COST CHAR(1)  ,SORT_STR CHAR(10)  ,MAT_TYPE CHAR(4)  ,VALID_FR   DATE ,CREATED_DATE DATE ,CHANGED_DATE DATE ,CREATED DATETIME );
Query OK, 0 rows affected (0.01 sec)

LOAD DATA LOCAL INFILE '/home/actaadm/dw_data_mngr/data/080229040205_dts_bom/dts_bom_prod/DTS_STPO_PROD_LJ.TXT'                         
    ->     REPLACE
    ->     INTO TABLE DB3_BOM_PROD_LJ1
    ->     FIELDS
    ->            TERMINATED BY ';'
    ->     IGNORE 0 LINES
    -> ;
Query OK, 3500 rows affected (0.20 sec)
Records: 3500  Deleted: 0  Skipped: 0  Warnings: 0

select distinct P_PLANT, P_MATNR,ALT_BOM,BOM_USE from DB3_BOM_PROD_LJ1  order by  P_PLANT, P_MATNR,ALT_BOM,BOM_USE limit 25;
[11 Mar 2008 8:26] Susanne Ebrecht
Lars,

many thanks for reporting this bug. We can reproduce it without problems ...
The bug in analyzing status because I am on the topic to figure out more details at the moment.
[11 Mar 2008 14:01] Susanne Ebrecht
That the "SELECT" displayed the right results after "CHECK TABLE" is the result of bug #35212

I am still on my way to analyze what's wrong with "Index + Load data infile" here.
[11 Mar 2008 15:12] Susanne Ebrecht
This bug needs a long time for analysing.

The problem here is that MyISAM seems to get confused when limit is higher then 9 by having a PK and an index with the same columns but different order.

I will attache a dump for reproducing.

mysql> select distinct a,b,d,c from test order by a,b,d,c limit 10;

Primary key is: a,b,c,d,e
Index is: a,b,d,c

This will only display nothing and Yen-sign when you have both: PK and Index.
When you drop Index or PK it will show you the right result.

It also doesn't matter if you make a "load data infile" or extended insert or a single insert statement for every row.

Consider, this only occurs when limit is 10 or higher.
[11 Mar 2008 15:14] Susanne Ebrecht
dump for reproducing

Attachment: bug34928.dump (application/octet-stream, text), 131.82 KiB.

[12 Mar 2008 13:48] Susanne Ebrecht
Just to be sure this is a MyISAM problem. I also tested this with Innodb and Memory and both don't get confused with this indexes. They work as expected. Only MyISAM has this problem.
[20 Mar 2008 10:07] Mattias Jonsson
Smaller dump that still shows the bug

Attachment: bug34928_small.dump (application/octet-stream, text), 4.12 KiB.

[20 Mar 2008 10:08] Mattias Jonsson
Test case for the bug

Attachment: bug34928.test (application/octet-stream, text), 1.20 KiB.

[26 Mar 2008 15:54] Alexey Kopytov
A shorter test case:

CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, 
                PRIMARY KEY(a,b,c,d,e), 
                KEY(a,b,d,c) 
); 
 
INSERT INTO t1(a, b, c) VALUES (1, 1, 1), 
                               (1, 1, 2), 
                               (1, 1, 3), 
                               (1, 2, 1), 
                               (1, 2, 2), 
                               (1, 2, 3); 
 
EXPLAIN SELECT DISTINCT a, b, d, c FROM t1; 
 
SELECT DISTINCT a, b, d, c FROM t1;
[26 Mar 2008 16:38] Bugs System
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/44461

ChangeSet@1.2569, 2008-03-26 19:37:36+03:00, kaa@kaamos.(none) +3 -0
  Fix for bug #34928: Confusion by having Primary Key and Index
  
  The bug is a regression introduced in 5.1 by the patch for bug28404.
  
  Under some circumstances test_if_skip_sort_order() could leave some
  data structures in an inconsistent state so that some parts of code
  could assume the selected execution strategy for GROUP BY/DISTINCT as
  a loose index scan (e.g. JOIN_TAB::is_using_loose_index_scan()), while
  the actual strategy chosen was an ordered index scan, which led to
  wrong data being returned.
  
  Fixed test_if_skip_sort_order() so that when changing the type for a
  join table, select->quick is reset not only for EXPLAIN, but for the 
  actual join execution as well, to not confuse code that depends on its
  value to determine the chosen GROUP BY/DISTINCT strategy.
[31 Mar 2008 14:54] Bugs System
Pushed into 5.1.24-rc
[2 Apr 2008 19:01] Paul DuBois
Noted in 5.1.24 changelog.

For some queries, the optimizer used an ordered index scan for GROUP
BY or DISTINCT when it was supposed to use a loose index scan,
leading to incorrect results.

Resetting report to Patch queued waiting for push into 6.0.x.
[3 Apr 2008 13:02] Bugs System
Pushed into 6.0.5-alpha
[7 Apr 2008 17:31] Paul DuBois
Noted in 6.0.5 changelog.