MySQL Bugs: #31141: Sorting DESC no longer working.

Bug #31141	Sorting DESC no longer working.
Submitted:	22 Sep 2007 9:46	Modified:	22 Sep 2007 16:47
Reporter:	Mark Simpson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.1.21/5.0	OS:	Windows (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	ORDER BY DESC

Description:
Reverse sorting by integer field works in 5.1.20, fails in 5.1.21.

Results of "How to repeat" SQL (...ORDER BY ThreadID DESC) in 5.1.21:

ThreadID ForumID
43       1
74       1
114      1
157      1
...

How to repeat:
CREATE TABLE `thread` (
  `ThreadID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ForumID` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`ThreadID`),
  KEY `ForumIndex` (`ForumID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `thread` VALUES(43, 1);
INSERT INTO `thread` VALUES(74, 1);
INSERT INTO `thread` VALUES(114, 1);
INSERT INTO `thread` VALUES(157, 1);
INSERT INTO `thread` VALUES(164, 1);
INSERT INTO `thread` VALUES(170, 1);
INSERT INTO `thread` VALUES(223, 1);
INSERT INTO `thread` VALUES(259, 1);
INSERT INTO `thread` VALUES(264, 1);
INSERT INTO `thread` VALUES(290, 1);
INSERT INTO `thread` VALUES(306, 1);

SELECT ThreadID, ForumID
FROM thread
WHERE thread.ForumID = 1
ORDER BY ThreadID DESC

Suggested fix:
Results of running the above SQL in 5.1.21 should be the same as 5.1.20.

Thank you for the bug report.

[miguel@skybr 5.1]$ bin/mysql -uroot db21
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-beta-debug Source distribution

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

mysql> CREATE TABLE `thread` (
    ->   `ThreadID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `ForumID` int(10) unsigned NOT NULL DEFAULT '1',
    ->   PRIMARY KEY (`ThreadID`),
    ->   KEY `ForumIndex` (`ForumID`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `thread` VALUES(43, 1);
Query OK, 1 row affected (0.01 sec)

<cut>

mysql> SELECT ThreadID, ForumID
    -> FROM thread
    -> WHERE thread.ForumID = 1
    -> ORDER BY ThreadID DESC
    -> ;
+----------+---------+
| ThreadID | ForumID |
+----------+---------+
|       43 |       1 | 
|       74 |       1 | 
|      114 |       1 | 
|      157 |       1 | 
|      164 |       1 | 
|      170 |       1 | 
|      223 |       1 | 
|      259 |       1 | 
|      264 |       1 | 
|      290 |       1 | 
|      306 |       1 | 
+----------+---------+
11 rows in set (0.03 sec)
------------------------------------------------------------
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.50-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> SELECT ThreadID, ForumID
    -> FROM thread
    -> WHERE thread.ForumID = 1
    -> ORDER BY ThreadID DESC
    -> ;
+----------+---------+
| ThreadID | ForumID |
+----------+---------+
|       43 |       1 | 
|       74 |       1 | 
|      114 |       1 | 
|      157 |       1 | 
|      164 |       1 | 
|      170 |       1 | 
|      223 |       1 | 
|      259 |       1 | 
|      264 |       1 | 
|      290 |       1 | 
|      306 |       1 | 
+----------+---------+
11 rows in set (0.00 sec)
------------------------------------------------------------
mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.24-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT ThreadID, ForumID
    -> FROM thread
    -> WHERE thread.ForumID = 1
    -> ORDER BY ThreadID DESC
    -> ;
+----------+---------+
| ThreadID | ForumID |
+----------+---------+
|      306 |       1 |
|      290 |       1 |
|      264 |       1 |
|      259 |       1 |
|      223 |       1 |
|      170 |       1 |
|      164 |       1 |
|      157 |       1 |
|      114 |       1 |
|       74 |       1 |
|       43 |       1 |
+----------+---------+
11 rows in set (0.00 sec)

Duplicate of bug #31001.