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

Bug #31001	ORDER BY DESC in InnoDB not working
Submitted:	13 Sep 2007 16:36	Modified:	18 Sep 2007 3:13
Reporter:	Nalle Jacobsson
Status:	Closed
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	5.0.48, 5.1.22	OS:	Any
Assigned to:	Georgi Kodinov	Target Version:
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.