MySQL Bugs: #34708: ORDER-BY-DESC shows incorrect sort order

Bug #34708	ORDER-BY-DESC shows incorrect sort order
Submitted:	20 Feb 2008 22:05	Modified:	14 Mar 2008 18:54
Reporter:	Trim Pershad	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.23-rc	OS:	Windows
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	DESC, innodb, order by

Description:
Using ORDER-BY-DESC on a join between two innoDB tables results in incorrect sort order.

Please also check the information on the forum for details.
http://forums.mysql.com/read.php?132,196655,196655
Was able to reproduce internally on 5.1.23-rc

Problem originally reported in : http://bugs.mysql.com/bug.php?id=31001 which was fixed but somehow did not make it into 5.1.23-rc.

How to repeat:
Will post

Bug 34708

Attachment: bug-34708.txt (text/plain), 5.89 KiB.

Can you please verify against 5.0 as well. Thanks.

Does not reproduce for me on 5.0.54 at least:

mysql> select tv.fromClientID as ClientID, tv.trackDate,p.realname  
    -> from trackviews tv left join mym p on (tv.fromClientID=p.clientID) 
    -> where tv.clientID=32 order by trackdate ASC limit 5;
+----------+------------+--------------+
| ClientID | trackDate  | realname     |
+----------+------------+--------------+
|   395891 | 1202525736 | da           | 
|   395218 | 1202549293 | sop          | 
|   396069 | 1202669189 | t            | 
|   394466 | 1202929904 | LaurenAndNic | 
|   396326 | 1202945744 | stepha       | 
+----------+------------+--------------+
5 rows in set (0.03 sec)

mysql> select tv.fromClientID as ClientID, tv.trackDate,p.realname  
    -> from trackviews tv left join mym p on (tv.fromClientID=p.clientID) 
    -> where tv.clientID=32 order by trackdate DESC limit 5;
+----------+------------+--------------+
| ClientID | trackDate  | realname     |
+----------+------------+--------------+
|   396326 | 1202945744 | stepha       | 
|   394466 | 1202929904 | LaurenAndNic | 
|   396069 | 1202669189 | t            | 
|   395218 | 1202549293 | sop          | 
|   395891 | 1202525736 | da           | 
+----------+------------+--------------+
5 rows in set (0.00 sec)

Current 5.0.58-bk build is find also:

mysql> CREATE TABLE `trackviews` ( `clientID` int(11) NOT NULL DEFAULT '0', `fromClientID` int(11) NOT NULL DEFAULT '0', `trackDate` int
(11) NOT NULL DEFAULT '0', `data` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`clientID`,`fromClientID`), KEY `trackDate` (`trackDate`) )
 ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `mym` ( `clientID` int(11) NOT NULL DEFAULT '0', `realname` char(15) DEFAULT NULL, PRIMARY KEY (`clientID`) ) ENGINE
=innoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into trackviews values (32, 394466, 1202929904,999905), (32, 395218, 1202549293, 999904), (32, 395891, 1202525736, 999903)
, (32, 396069, 1202669189, 999902), (32, 396326,1202945744, 999901);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into mym values (394466, 'LaurenAndNic'), (395218, 'sop'), (395891,'da'), (396069, 't'), (396326, 'stepha'); 
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select tv.fromClientID as ClientID, tv.trackDate,p.realname   from trackviews tv left join mym p on (tv.fromClientID=p.clientID) 
 where tv.clientID=32 order by trackdate ASC limit 5;
+----------+------------+--------------+
| ClientID | trackDate  | realname     |
+----------+------------+--------------+
|   395891 | 1202525736 | da           | 
|   395218 | 1202549293 | sop          | 
|   396069 | 1202669189 | t            | 
|   394466 | 1202929904 | LaurenAndNic | 
|   396326 | 1202945744 | stepha       | 
+----------+------------+--------------+
5 rows in set (0.00 sec)

mysql> select tv.fromClientID as ClientID, tv.trackDate,p.realname from trackviews tv, mym p where tv.fromClientID=p.ClientID and tv.cli
entID=32 order by trackdate DESC limit 5;
+----------+------------+--------------+
| ClientID | trackDate  | realname     |
+----------+------------+--------------+
|   396326 | 1202945744 | stepha       | 
|   394466 | 1202929904 | LaurenAndNic | 
|   396069 | 1202669189 | t            | 
|   395218 | 1202549293 | sop          | 
|   395891 | 1202525736 | da           | 
+----------+------------+--------------+
5 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.58-debug | 
+--------------+
1 row in set (0.09 sec)

More information:

If you use wildcard instead of column name then there is no problem.

mysql> select * from trackviews tv where tv.clientID=32 
order by tv.trackdate desc limit 5;
+----------+--------------+------------+--------+
| clientID | fromClientID | trackDate  | data   |
+----------+--------------+------------+--------+
|       32 |       396326 | 1202945744 | 999901 | 
|       32 |       394466 | 1202929904 | 999905 | 
|       32 |       396069 | 1202669189 | 999902 | 
|       32 |       395218 | 1202549293 | 999904 | 
|       32 |       395891 | 1202525736 | 999903 | 
+----------+--------------+------------+--------+
5 rows in set (0.00 sec)

mysql> select * from trackviews tv left join mym p on (tv.fromClientID=p.ClientID) 
and tv.clientId=32 order by tv.trackdate desc limit 5;
+----------+--------------+------------+--------+----------+--------------+
| clientID | fromClientID | trackDate  | data   | clientID | realname     |
+----------+--------------+------------+--------+----------+--------------+
|       32 |       396326 | 1202945744 | 999901 |   396326 | stepha       | 
|       32 |       394466 | 1202929904 | 999905 |   394466 | LaurenAndNic | 
|       32 |       396069 | 1202669189 | 999902 |   396069 | t            | 
|       32 |       395218 | 1202549293 | 999904 |   395218 | sop          | 
|       32 |       395891 | 1202525736 | 999903 |   395891 | da           | 
+----------+--------------+------------+--------+----------+--------------+
5 rows in set (0.00 sec)

My initial report was for linux centos 5 x64 rpm, on an 8 way machine. i.e. it's not windows specfic.

This bug doesn't seem to appear in 5.1.23a or in 5.1.22 is just a missed patch?

Miguel and team:
It's still not clear that this bug actually exists in
5.1.24 (the most-recent 5.1 bk version); it may already
be fixed by another patch mentioned by Igor. Please
re-verify; I can't repeat on 5.1.24 but don't want this
to slip by if I'm wrong.

Many thanks!

Bug is not exists in current 5.1 BK tree, although it is repeatabel with version 5.1.23

Bug does not exists in current 5.0 and 6.0 trees as well

How soon will this fix be released? This is affecting our own release schedule as we're seeing this bug in several different join + order-by queries between InnoDB tables.

This bug isn't in 5.1.23a try that instead, if you still have this bug download the BK version and compile and test and see if it still crops up.