Bug #36914 ORDER BY DESC ignored in Query Result
Submitted: 23 May 2008 12:02 Modified: 23 May 2008 12:50
Reporter: Michael Dülm Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:mysql-5.0.51a-linux-i686-glibc23 OS:Linux (SuSE 10.3 32 bit)
Assigned to: CPU Architecture:Any
Tags: IGNORE, order by, query

[23 May 2008 12:02] Michael Dülm 
Description:
we have installed the newest available version for linux: mysql-5.0.51a-linux-i686-glibc23

When running a query
SELECT id, ts
FROM group_order
GROUP BY id
ORDER BY ts DESC 

The "ORDER BY ts DESC" is ignored.
The result is always presented in ascending order.

How to repeat:
CREATE TABLE `test`.`group_order` (
`id` INT UNSIGNED NOT NULL ,
`ts` INT UNSIGNED NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;

INSERT INTO `test`.`group_order` (`id` ,`ts`) VALUES
('1', '1'),
('2', '2');

SELECT id, ts
FROM group_order
GROUP BY id
ORDER BY ts DESC

Result on 5.0.51a (mysql-5.0.51a-linux-i686-glibc23)
id | ts
-------
 1 | 1
 2 | 2

Result on 5.0.45 (mysql-5.0.45-linux-i686-glibc23)
id | ts
-------
 2 | 2
 1 | 1

Suggested fix:
Bugs that may be related to this one:

http://bugs.mysql.com/bug.php?id=30596
GROUP BY optimization gives wrong result order

http://bugs.mysql.com/bug.php?id=31001
ORDER BY DESC in InnoDB not working
[23 May 2008 12:12] MySQL Verification Team 
Thank you for the bug report. Looks that is already fixed in source tree:

[miguel@hegel dbs]$ 5.0/bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.64-debug Source distribution

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

mysql> CREATE TABLE `test`.`group_order` (
    -> `id` INT UNSIGNED NOT NULL ,
    -> `ts` INT UNSIGNED NOT NULL ,
    -> PRIMARY KEY ( `id` )
    -> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO `test`.`group_order` (`id` ,`ts`) VALUES
    -> ('1', '1'),
    -> ('2', '2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT id, ts
    -> FROM group_order
    -> GROUP BY id
    -> ORDER BY ts DESC;
+----+----+
| id | ts |
+----+----+
|  2 |  2 | 
|  1 |  1 | 
+----+----+
2 rows in set (0.01 sec)

mysql>
[23 May 2008 12:50] Michael Dülm 
Thanks for testing this.

Do you have an (estimated) date of release for the next build of 5.0 ?

We have gone back to 5.0.45.