MySQL Bugs: #31001: ORDER BY DESC in InnoDB not working

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	CPU Architecture:	Any
Tags:	DESC, innodb, order by

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;

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>

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.

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

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.

The bug is OS independent.

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.

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.

Confirmed that this bug is *not* present in 5.0.44sp1 (mysql-enterprise-gpl-5.0.44sp1-win32) using same test case as above.

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.

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.

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.

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.

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.

The changelog entry has been updated for 5.1.22 and 5.0.50.

Bug #31141 was marked as a duplicate of this one.

Pushed into 5.0.50

Pushed into 5.1.23-beta

Bug #31392 was marked as a duplicate of this one.

Pushed into 5.1.23-beta

bug #31429 was marked as a duplicate of this one.

Bug #31474 was marked as a duplicate of this one.

Bug #37830 marked as a duplicate

This issue is occurred again in 5.0.67.

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)

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.

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.

Attached is the my.ini file and no modification on it after installation

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

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
)

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.

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 >