Bug #39941 Sort is removed when ordering by primary key for some Innodb queries

• Submitted: 8 Oct 2008 19:31
• Modified: 8 Oct 2008 20:35
• Reporter: Ed Dawley
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0.67
• OS: Any
• CPU Architecture: Any
• Tags: innodb, order, primary

[8 Oct 2008 19:31] Ed Dawley

Description:

When selecting by a secondary index and ordering by primary key desc with a limit of 1, this particular query for this particular dataset ignores the sort and returns the wrong result.  Looking at the explain, the optimizer is using the wrong type of scan (range instead of ref) as well as not including a filesort.  

I would assume this is due to a faulty optimization relying on Innodb storing data by primary key on disk. 

A simple workaround for the test case is to order by id desc, ctime (any other column).

How to repeat:
create table ed (id int unsigned not null auto_increment, child_id int unsigned not null, ctime datetime, primary key(id), index(child_id)) engine=innodb;

insert into ed (child_id) values(1),(2),(3),(4),(5),(5),(5);

select * from ed where child_id = 5 order by id desc limit 1;

[8 Oct 2008 19:48] Sveta Smirnova

Thank you for the report.

I can not repeat described behavior with current development sources, although bug is repeatable with version 5.0.67. Please wait next release.

[8 Oct 2008 20:35] Ed Dawley

Sorry, but shouldn't the status be something else since it is a valid, repeatable bug?  I am under the impression can't repeat means that no QA will be performed for this bug for next release (and thus is might creep back in during the dev cycle).

Thans for any clarification.