MySQL Bugs: #19116: "SELECT DATE_FORMAT(c,f) AS alias ORDER BY alias": incorrect order.

Bug #19116	"SELECT DATE_FORMAT(c,f) AS alias ORDER BY alias": incorrect order.
Submitted:	15 Apr 2006 17:28	Modified:	18 Apr 2006 16:26
Reporter:	Marcello Romani	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19-nt	OS:	Windows (Windows XP Professional SP2)
Assigned to:		CPU Architecture:	Any

Description:
Results not sorted correctly by ORDER BY <alias> when <alias> refers to DATE_FORMAT(column, format) and format is different from mysql default '%Y-%m-%d'.

Results are sorted correctly if we ORDER BY column instead of <alias> (provided <alias> is not equal to column).

How to repeat:
CREATE TABLE date_bug(
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subscription_date DATE
);

INSERT INTO date_bug VALUES(NULL, '2006-04-01');
INSERT INTO date_bug VALUES(NULL, '2006-01-30');
INSERT INTO date_bug VALUES(NULL, '2006-03-26');
INSERT INTO date_bug VALUES(NULL, '2006-04-01');
INSERT INTO date_bug VALUES(NULL, '2006-01-17');

Queries are built out of the following template:

SELECT DATE_FORMAT(<column>, <format>) [AS <alias>] FROM date_bug ORDER BY { <column> | <alias> }

<column> is always 'subscription_date'.

1) <format> = '%Y-%m-%d' (mysql default date format, i.e. DATE_FORMAT() is redundant)
All combinations give the expected result.

2) <format> != '%Y-%m-%d' (e.g. '%d-%m-%Y')
2a) ORDER BY <column> gives the expected result, provided <alias> != <column>
2b) ORDER BY <alias> gives wrong order.

Example:

SELECT DATE_FORMAT(subscription_date,'%Y-%m-%d') AS p FROM date_bug ORDER BY p;

2006-01-17
2006-01-30
2006-03-26
2006-04-01
2006-04-01

SELECT DATE_FORMAT(subscription_date,'%d-%m-%Y') AS p FROM date_bug ORDER BY p;

01-04-2006
01-04-2006
17-01-2006
26-03-2006
30-01-2006

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

The result of DATE_FORMAT is a string, and ORDER BY alias alphabeticly sorts by that string

Thank you.

Sorry for submitting a non-bug. Will check docs better next time :-)