MySQL DATE_FORMAT function and ORDER BY

An interesting scenario that I came across today was the fact that if you use the same alias as the column name in a formatted string (using DATE_FORMAT) and you also use same alias in ORDER BY or GROUP BY clause then MySQL uses the string output of DATE_FORMAT to sort records rather than the actual date column. Let me clarify using an example. Suppose you have the following table definition:

CREATE TABLE test (id INT, date_added DATE);

with the following records:

+------+------------+
| id   | date_added |
+------+------------+
|    1 | 2011-08-01 |
|    2 | 2010-12-01 |
+------+------------+

And the following query results in below:

mysql> SELECT id, date_added 
FROM test ORDER BY date_added DESC;
+------+------------+
| id   | date_added |
+------+------------+
|    1 | 2011-08-01 |
|    2 | 2010-12-01 |
+------+------------+
2 rows in set (0.00 sec)

But, if you format date column and run query below then you will be surprised to see the order of rows.

mysql> SELECT id, DATE_FORMAT(date_added, '%e %b, %Y') AS date_added 
FROM test ORDER BY date_added DESC;
+------+-------------+
| id   | date_added  |
+------+-------------+
|    2 | 1 Dec, 2010 |
|    1 | 1 Aug, 2011 |
+------+-------------+
2 rows in set (0.00 sec)

The reason why this happens is because the alias for DATE_FORMAT formatted string is the same as column name, so MySQL chooses the alias to sort records for the resultset. An easy fix is to choose a different alias name for DATE_FORMAT string.

mysql> SELECT id, DATE_FORMAT(date_added, '%e %b, %Y') AS date_added_formatted 
FROM test ORDER BY date_added DESC;
+------+----------------------+
| id   | date_added_formatted |
+------+----------------------+
|    1 | 1 Aug, 2011          |
|    2 | 1 Dec, 2010          |
+------+----------------------+
2 rows in set (0.00 sec)

As of MySQL 5.1.15, DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters. Before 5.1.15, the return value is a binary string.