Bug #4557 DESC seems to be ignored when more than one column is specified after ORDER BY
Submitted: 15 Jul 2004 3:26 Modified: 15 Jul 2004 7:38
Reporter: Allen Arakaki Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.18 OS:NT, Linux
Assigned to: CPU Architecture:Any

[15 Jul 2004 3:26] Allen Arakaki
Description:
On a SELECT, the keyword DESC seems to be ignored if you specify more than one column after ORDER BY

The following does not work:
SELECT appointmentId, start, end FROM bug_desc 
WHERE start<'2004-07-23 10:30:00' ORDER BY start, end DESC LIMIT 3;

Following works:
SELECT appointmentId, start, end FROM bug_desc 
WHERE start<'2004-07-23 10:30:00' ORDER BY start  DESC LIMIT 3;

BTW, it doesn't matter if LIMIT is specified - it would be nice if it worked with LIMIT too!

How to repeat:
DROP TABLE IF EXISTS bug_desc;
CREATE TABLE bug_desc (
appointmentId           BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
start                   DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
end                     DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
);

INSERT INTO bug_desc VALUES (0, '2004-07-12 10:00:00', '2004-07-12 11:00:00');
INSERT INTO bug_desc VALUES (0, '2004-07-13 10:00:00', '2004-07-13 11:00:00');
INSERT INTO bug_desc VALUES (0, '2004-07-14 10:00:00', '2004-07-14 11:00:00');
INSERT INTO bug_desc VALUES (0, '2004-07-15 10:00:00', '2004-07-15 11:00:00');
INSERT INTO bug_desc VALUES (0, '2004-07-16 10:00:00', '2004-07-16 11:00:00');

Following does not work properly:
SELECT appointmentId, start, end FROM bug_desc 
WHERE start<'2004-07-23 10:30:00' ORDER BY start, end DESC LIMIT 3;

Following works:
SELECT appointmentId, start, end FROM bug_desc 
WHERE start<'2004-07-23 10:30:00' ORDER BY start  DESC LIMIT 3;
[15 Jul 2004 7:38] Heikki Tuuri
Hi!

According to the SELECT syntax, you must specify DESC after EACH column listed in the ORDER BY. The default for sorting a column in an ORDER BY is the ascending order.

Regards,

Heikki

http://dev.mysql.com/doc/mysql/en/SELECT.html

mysql> SELECT appointmentId, start, end FROM bug_desc
    -> WHERE start<'2004-07-23 10:30:00' ORDER BY start DESC, end DESC;
+---------------+---------------------+---------------------+
| appointmentId | start               | end                 |
+---------------+---------------------+---------------------+
|             5 | 2004-07-16 10:00:00 | 2004-07-16 11:00:00 |
|             4 | 2004-07-15 10:00:00 | 2004-07-15 11:00:00 |
|             3 | 2004-07-14 10:00:00 | 2004-07-14 11:00:00 |
|             2 | 2004-07-13 10:00:00 | 2004-07-13 11:00:00 |
|             1 | 2004-07-12 10:00:00 | 2004-07-12 11:00:00 |
+---------------+---------------------+---------------------+
5 rows in set (0.01 sec)

mysql>