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;

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.