Bug #67958 IMPORTANT! MySQL Server 5.5.29(28) wrong sorting using complex index
Submitted: 21 Dec 2012 12:53 Modified: 18 Jan 2013 11:14
Reporter: Alexander Egelsky Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.5.29, 5.5.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: 5.5.29, 5.5.29, bug, core, INDEX, order by, sorting

[21 Dec 2012 12:53] Alexander Egelsky
 - What I did:
Query to get least element using "order by" with complex index which consist of 3 fields, it's in attached query file with schema for it,
this query worked 1.5 years by now on 20+ containers, with 5-6 millions unique records in this main table

 - What I expected to have happen:
MySQL server version <= 5.1.65 returns 525, which is *right* value for this example

 - What actually happened:
Latest MySQL server v5.5.28, v5.5.29 returns 99, which is *wrong* value

I'm attaching cleaned query and cleaned schema to reproduce this. 
You can use MySQL server v5.1.x to get right value 525 and v5.5.28(29) to get this wrong value 99.
Query selects least element of two: max element of `bids` table (should be 1111) and constant 525.

Please don't try to understand what should this query do, I minimized it as much as I could to show this bug, 
because original query contained 2+ screens of code, so I'm saving your time as much as I can here.
Cutting out any more pieces of this query makes this bug dissappear, so does changing schema (this last index in `bids` table).
Also if you will remove bid ID = 2, bug will vanish.

This bug was reproduced in MySQL Community Server v5.5.28, v5.5.29, on Linux and Windows, 
from PHP code, from phpmyadmin, from console, SSH and MySQLWorkbench, i.e. it seems to be server bug.

I spent 2 days on this and I see no logical explaination for this, it seems to be MySQL Server 5.5.28+ core bug.

How to repeat:
On MySQL Server 5.5.28-29 import my schema and run query,
you will get 99.0000 (wrong value)

On v.5.1.x you will get 525.000 (right value)

Suggested fix:
I don't know
[21 Dec 2012 12:54] Alexander Egelsky
Schema to reproduce

Attachment: __schema_5.5.29_bug.sql (application/octet-stream, text), 900 bytes.

[21 Dec 2012 12:55] Alexander Egelsky
Query which should return 525

Attachment: __query_5.5.29_bug.sql (application/octet-stream, text), 422 bytes.

[21 Dec 2012 13:10] MySQL Verification Team
Thank you for the report.  I tested a few versions and here are my results.

5.1.66 : 525.0000
5.5.8  : 525.0000
5.5.20 : 525.0000
5.5.24 : 525.0000
5.5.25a: 525.0000
5.5.27 : 99.0000    <-- regression ?
5.5.28 : 99.0000
5.5.29 : 99.0000
5.5.30 : 99.0000
5.6.11 : 525.0000
[21 Dec 2012 13:13] Alexander Egelsky

Thank you for fast response, yes, the only valid value is
[21 Dec 2012 13:57] MySQL Verification Team
Hi Alexander, please check if this is really a duplicate of a known bug:

[21 Dec 2012 16:27] Alexander Egelsky
Yep, looks quite the same.

The only thing that differs - is if you will remove complex key from `bids` table ( `bid_amt_ordering` ), my bug will vanish.
And that guy from bug you showed me didn't used complex keys.

Guess these bugs are related, but no clones of each other, I would add some relationship between them.
[7 Jan 2013 14:40] Olav Sandstå
This is caused by the same issue as reported in Bug#66845.
[18 Jan 2013 11:14] Olav Sandstå
Closing this bug as duplicate of Bug#66845.