Bug #31001 ORDER BY DESC in InnoDB not working
Submitted: 13 Sep 2007 14:36 Modified: 18 Sep 2007 1:13
Reporter: Nalle Jacobsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.48, 5.1.22 OS:Any
Assigned to: Georgi Kodinov
Tags: DESC, innodb, order by

[13 Sep 2007 14:36] Nalle Jacobsson
Description:
MySQL pays no attention to the DESC in the ORDER BY if you order by the primary key and also include a simple equals where condition on an indexed column.

Maybe this is related to Bug #28591 and the fix implemented in 5.0.48

How to repeat:
create table `test` (  `id` int UNSIGNED NOT NULL AUTO_INCREMENT , `nr` int UNSIGNED NOT NULL , PRIMARY KEY ( `id`));

alter table `test` add index `nr` (`nr`);

insert into `test`(`id`,`nr`) values ( '1','1');
insert into `test`(`id`,`nr`) values ( '2','2');
insert into `test`(`id`,`nr`) values ( '3','2');

#The two queries below should produce different results, but they don't.

select * from test where nr=2 order by id asc;
select * from test where nr=2 order by id desc;
[13 Sep 2007 14:57] Miguel Solorzano
Thank you for the bug report.

c:\dev\5.0>bin\mysql -uroot db9
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.50-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table `test` (  `id` int UNSIGNED NOT NULL AUTO_INCREMENT , `nr` int UNSIGNED NOT
    -> NULL , PRIMARY KEY ( `id`)) engine=InnoDB;
Query OK, 0 rows affected (0.27 sec)

mysql> alter table `test` add index `nr` (`nr`);
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> insert into `test`(`id`,`nr`) values ( '1','1');
Query OK, 1 row affected (0.03 sec)

mysql> insert into `test`(`id`,`nr`) values ( '2','2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into `test`(`id`,`nr`) values ( '3','2');
Query OK, 1 row affected (0.10 sec)

mysql> select * from test where nr=2 order by id asc;
+----+----+
| id | nr |
+----+----+
|  2 |  2 |
|  3 |  2 |
+----+----+
2 rows in set (0.09 sec)

mysql> select * from test where nr=2 order by id desc;
+----+----+
| id | nr |
+----+----+
|  2 |  2 |
|  3 |  2 |
+----+----+
2 rows in set (0.00 sec)

mysql> alter table test engine=MyISAM;
Query OK, 3 rows affected (0.21 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test where nr=2 order by id asc;
+----+----+
| id | nr |
+----+----+
|  2 |  2 |
|  3 |  2 |
+----+----+
2 rows in set (0.00 sec)

mysql> select * from test where nr=2 order by id desc;
+----+----+
| id | nr |
+----+----+
|  3 |  2 |
|  2 |  2 |
+----+----+
2 rows in set (0.00 sec)

mysql>
[13 Sep 2007 15:34] Heikki Tuuri
There are no relevant changes in InnoDB itself in 5.0.48. This bug is probably from the fix of MySQL Bug #28591.

This is a critical bug because users get wrong query results from a simple query.
[14 Sep 2007 4:55] Jeffrey Pugh
Confirmed also present in 5.1.22 with same test case as above.

Also did "alter table test engine=myisam", and ordering is then correct.

I agree that this is a showstopper in both 5.0.48 and 5.1.22
[14 Sep 2007 6:12] Vasil Dimov
It looks like the fix for Bug#28591 was incomplete:

MySQL need not sort the result when "ORDER BY primary_key" is used because InnoDB always returns the results in that order. _BUT_ if "ORDER BY primary_key DESC" is used MySQL need to reverse the result returned from InnoDB.

I have not checked this hypothesis, though.
[14 Sep 2007 6:13] Vasil Dimov
The bug is OS independent.
[14 Sep 2007 9:26] 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/34245

ChangeSet@1.2526, 2007-09-14 12:26:49+03:00, gkodinov@magare.gmz +3 -0
  Bug #31001: ORDER BY DESC in InnoDB not working
  
  When using the primary key as a suffix when ordering on a 
  secondary key for the supporting engines, the optimizer
  was not taking into account that implicitly the order in
  which the secondary key is retrieved is forward and if 
  the first primary key part has a DESC it will simply be
  ignored.
  
  Fixed by not using the primary key suffix if all the 
  secondary key parts were compared to a constant and 
  the first primary key part in the suffix is DESC.
[14 Sep 2007 11:47] 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/34265

ChangeSet@1.2526, 2007-09-14 14:46:54+03:00, gkodinov@magare.gmz +3 -0
  Bug #31001: ORDER BY DESC in InnoDB not working
  
  The engine sets index traversal in reverse order only if there are 
   used key parts that are not compared to a constant.
  However using the primary key as an ORDER BY suffix rendered the check
  incomplete : going in reverse order must still be used even if 
  all the parts of the secondary key are compared to a constant.
  
  Fixed by having a special return value that signals that going in 
  reverse order must still be used even if all the secondary key parts
  are compared to a constant.
  Also account for the case when all the primary keys are compared to a
  constant.
[14 Sep 2007 12:12] Jeffrey Pugh
Confirmed that this bug is *not* present in 5.0.44sp1 (mysql-enterprise-gpl-5.0.44sp1-win32) using same test case as above.
[14 Sep 2007 12:48] James Day
A second review of the fix for bug #28570 also looks like a good idea. A similar handler interface fix pushed into 5.0.48 and 5.1.21 that could have a similar logic flaw in its fix. One of the pair 28570 / 28591 that were noticed because they caused InnoDB to take unnecessary locks. I don't know if there is a flaw, just noting that it seems wise to check at this particular point in time.
[14 Sep 2007 14:43] 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/34279

ChangeSet@1.2526, 2007-09-14 17:43:14+03:00, gkodinov@magare.gmz +3 -0
  Bug #31001: ORDER BY DESC in InnoDB not working
  
  The optimizer sets index traversal in reverse order only if there are 
   used key parts that are not compared to a constant.
  However using the primary key as an ORDER BY suffix rendered the check
  incomplete : going in reverse order must still be used even if 
  all the parts of the secondary key are compared to a constant.
  
  Fixed by relaxing the check and set reverse traversal even when all
  the secondary index keyparts are compared to a const.
  Also account for the case when all the primary keys are compared to a
  constant.
[14 Sep 2007 15:31] MC Brown
A note has been added to the 5.1.22 and 5.0.48 changelogs: 

When sorting rows in an INNODB table using a primary key, where the sort was on the the primary key column and the DESC operator was applied, the rows would be incorrectly sorted. if you included a simple WHERE field = value clause in the query.
[14 Sep 2007 16:18] James Day
MC, the bug was introduced in 5.0.48 so the note for that one would be in a "Bugs Introduced" section in the present tense with a note that it's fixed in 5.0.50 (I expect). Same for 5.1.21. We haven't normally been documenting regression bugs in the release in which they were introduced but it does seem like a helpful thing to be doing.

Instead of:

"When sorting rows in an INNODB table using a primary key, where the sort was on the the[sic] primary key column and the DESC operator was applied, the rows would be incorrectly sorted. if you included a simple WHERE field = value clause in the query."

how about this:

"For an InnoDB table if a SELECT was ordered by the primary key and also had a WHERE field = value clause on a different field that was indexed, a DESC order instruction would be ignored".

The mention of the indexed field is new and I think that is a requirement for the bug to show up.
[16 Sep 2007 15:04] Joerg Bruehe
I do not understand the entry of Sep 14, 17:31.

The bug is present in 5.0.48 and, probably, in 5.1.21 (I did not check).

The bug is fixed in 5.1.22-rc (just verified this),
and the fix will also be part of 5.0.50 when that will bet built (near future).

I set status to "documenting" for 5.1.22 inclusion already now,
and it seems safe to document it for 5.0.50 as well even though the fix is not in the 5.0 main tree yet.
[18 Sep 2007 1:13] MC Brown
The changelog entry has been updated for 5.1.22 and 5.0.50.
[22 Sep 2007 16:48] Valerii Kravchuk
Bug #31141 was marked as a duplicate of this one.
[24 Sep 2007 8:31] Bugs System
Pushed into 5.0.50
[24 Sep 2007 8:34] Bugs System
Pushed into 5.1.23-beta
[4 Oct 2007 16:32] Valerii Kravchuk
Bug #31392 was marked as a duplicate of this one.
[5 Oct 2007 17:56] Bugs System
Pushed into 5.1.23-beta
[6 Oct 2007 14:23] Shane Bester
bug #31429 was marked as a duplicate of this one.
[9 Oct 2007 10:22] Valerii Kravchuk
Bug #31474 was marked as a duplicate of this one.
[11 Jul 2008 13:21] Georgi Kodinov
Bug #37830 marked as a duplicate
[23 Sep 2008 9:42] Szu Kiak Lim
This issue is occurred again in 5.0.67.
[23 Sep 2008 16:44] Todd Farmer
Thanks for your report, but I am unable to repeat this using 5.0.67:

mysql> SELECT VERSION();
+---------------------+
| VERSION()           |
+---------------------+
| 5.0.67-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test` (
    ->  `id` int UNSIGNED NOT NULL AUTO_INCREMENT ,
    ->  `nr` INT UNSIGNED NOT NULL ,
    ->  PRIMARY KEY ( `id`),
    ->  KEY (`nr`)
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test`(`id`,`nr`) VALUES ( '1','1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`(`id`,`nr`) VALUES ( '2','2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`(`id`,`nr`) VALUES ( '3','2');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test WHERE nr=2 ORDER BY id ASC;
+----+----+
| id | nr |
+----+----+
|  2 |  2 |
|  3 |  2 |
+----+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE nr=2 ORDER BY id DESC;
+----+----+
| id | nr |
+----+----+
|  3 |  2 |
|  2 |  2 |
+----+----+
2 rows in set (0.00 sec)
[23 Sep 2008 16:49] Miguel Solorzano
Mr. Szu Kiak Lim

If you have have a different test case than the original could you please provide it beside your my.cnf/my.ini file?. Thanks in advance.
[24 Sep 2008 2:17] Szu Kiak Lim
The following are the simplified scripts:

DROP TABLE IF EXISTS balance;
DROP TABLE IF EXISTS user;

CREATE TABLE user(ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID));
INSERT INTO user VALUES(11),(35), (42);

CREATE TABLE `balance` (
  `cb_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `cb_cm_id` INTEGER(10) UNSIGNED NOT NULL,
  `cb_balance` FLOAT(10,2) NOT NULL,
  PRIMARY KEY (`cb_id`),
  KEY `balance_fk1` (`cb_cm_id`),
  CONSTRAINT `balance_fk1` FOREIGN KEY (`cb_cm_id`) REFERENCES `user` (`ID`)
)ENGINE=INNODB;

INSERT INTO balance (cb_id, cb_cm_id, cb_balance) 
VALUES(1,42,8.10),(2,42,7.40),(3,11,6.70),(4,11,6.00),(5, 11, 5.30),(6,35,0.00);

SELECT * FROM balance WHERE cb_cm_id = 11 ORDER BY cb_id DESC LIMIT 1;

The result suppose to return 5.3 but it return 6.7
The intention of this query is to get the latest balance for a user.
[24 Sep 2008 2:18] Szu Kiak Lim
Attached is the my.ini file and no modification on it after installation

Attachment: my.ini (application/octet-stream, text), 8.69 KiB.

[24 Sep 2008 4:00] Jared S
LOL, make yourself a procy or user 5.1 tree..

select cb_balance from balance where cb_id =
(
select max(cb_id) from balance where cb_cm_id = 11
)
[24 Sep 2008 4:30] Todd Farmer
Verified with new test case in 5.0.67, but perhaps fixed in http://bugs.mysql.com/bug.php?id=37830 .  Retesting with 5.0.68 now.
[24 Sep 2008 11:55] Miguel Solorzano
Testing last test case with server source 1 week older:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.70-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test
Database changed
mysql 5.0 > set FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.03 sec)

mysql 5.0 > DROP TABLE IF EXISTS balance;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.0 > DROP TABLE IF EXISTS user;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.0 >
mysql 5.0 > CREATE TABLE user(ID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID));
Query OK, 0 rows affected (0.06 sec)

mysql 5.0 > INSERT INTO user VALUES(11),(35), (42);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > CREATE TABLE `balance` (
    ->   `cb_id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `cb_cm_id` INTEGER(10) UNSIGNED NOT NULL,
    ->   `cb_balance` FLOAT(10,2) NOT NULL,
    ->   PRIMARY KEY (`cb_id`),
    ->   KEY `balance_fk1` (`cb_cm_id`),
    ->   CONSTRAINT `balance_fk1` FOREIGN KEY (`cb_cm_id`) REFERENCES `user` (`ID`)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.09 sec)

mysql 5.0 >
mysql 5.0 > INSERT INTO balance (cb_id, cb_cm_id, cb_balance)
    -> VALUES(1,42,8.10),(2,42,7.40),(3,11,6.70),(4,11,6.00),(5, 11, 5.30),(6,35,0.00);
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > SELECT * FROM balance WHERE cb_cm_id = 11 ORDER BY cb_id DESC LIMIT 1;
+-------+----------+------------+
| cb_id | cb_cm_id | cb_balance |
+-------+----------+------------+
|     5 |       11 |       5.30 |
+-------+----------+------------+
1 row in set (0.05 sec)

mysql 5.0 >