Bug #14016 ORDER BY DATE_FORMAT() and uppercase/lowercase DATE_FORMAT argument pairs
Submitted: 13 Oct 2005 19:28 Modified: 19 Jan 2006 22:28
Reporter: Geoff Traugott Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.14-standard/5.0 BK source OS:Linux (Linux (x86, glibc-2.3))
Assigned to: Evgeny Potemkin CPU Architecture:Any

[13 Oct 2005 19:28] Geoff Traugott
Description:
When a SELECT query includes DATE_FORMAT(field, '%M'), and ORDER BY DATE_FORMAT(field, '%m'), the result is sorted by the '%M' value instead of '%m'.

Likewise, if the SELECT query includes DATE_FORMAT(field, '%m') and ORDER BY DATE_FORMAT(field, '%M'), it is sorted by the '%m' value instead of '%M'.

Further, if the SELECT contains multiple DATE_FORMAT functions referencing '%m' and '%M', and is ORDER BY DATE_FORMAT(field, '%m'), it will sort the results by whichever reference to month appears first in the SELECT portion of the query.

This applies to other uppercase/lowercase DATE_FORMAT argument pairs, such as '%w' and '%W'

Examples:

mysql> SELECT * FROM tblDateSort;
+---------------------+
| dtmDate             |
+---------------------+
| 2005-01-05 00:00:00 |
| 2005-02-06 00:00:00 |
| 2005-03-22 00:00:00 |
| 2005-04-11 00:00:00 |
| 2005-05-01 00:00:00 |
| 2005-06-08 00:00:00 |
| 2005-07-07 00:00:00 |
| 2005-08-29 00:00:00 |
| 2005-09-20 00:00:00 |
| 2005-10-03 00:00:00 |
| 2005-11-14 00:00:00 |
| 2005-12-25 00:00:00 |
+---------------------+
12 rows in set (0.00 sec)

mysql> SELECT DATE_FORMAT(dtmDate, "%M") FROM tblDateSort ORDER BY DATE_FORMAT(dtmDate, "%m");
+----------------------------+
| DATE_FORMAT(dtmDate, "%M") |
+----------------------------+
| April                      |
| August                     |
| December                   |
| February                   |
| January                    |
| July                       |
| June                       |
| March                      |
| May                        |
| November                   |
| October                    |
| September                  |
+----------------------------+
12 rows in set (0.00 sec)

mysql> select date_format(dtmDate, "%m") from tblDateSort order by date_format(dtmDate, "%M");
+----------------------------+
| date_format(dtmDate, "%m") |
+----------------------------+
| 01                         |
| 02                         |
| 03                         |
| 04                         |
| 05                         |
| 06                         |
| 07                         |
| 08                         |
| 09                         |
| 10                         |
| 11                         |
| 12                         |
+----------------------------+
12 rows in set (0.00 sec)

mysql> select date_format(dtmDate, "%m") as Date1, date_format(dtmDate, "%M") as Date2 from tblDateSort order by date_format(dtmDate, "%M");
+-------+-----------+
| Date1 | Date2     |
+-------+-----------+
| 01    | January   |
| 02    | February  |
| 03    | March     |
| 04    | April     |
| 05    | May       |
| 06    | June      |
| 07    | July      |
| 08    | August    |
| 09    | September |
| 10    | October   |
| 11    | November  |
| 12    | December  |
+-------+-----------+
12 rows in set (0.00 sec)

Switiching the field order in the SELECT changes the ORDER BY behavior:

mysql> select date_format(dtmDate, "%M") as Date1, date_format(dtmDate, "%m") as Date2 from tblDateSort order by date_format(dtmDate, "%M");
+-----------+-------+
| Date1     | Date2 |
+-----------+-------+
| April     | 04    |
| August    | 08    |
| December  | 12    |
| February  | 02    |
| January   | 01    |
| July      | 07    |
| June      | 06    |
| March     | 03    |
| May       | 05    |
| November  | 11    |
| October   | 10    |
| September | 09    |
+-----------+-------+
12 rows in set (0.00 sec)

Additionally, this behavior is seen with other uppercase/lowercase DATE_FORMAT argument pairs, such as '%w' with '%W'

mysql> select date_format(dtmDate, "%w") as Date1, date_format(dtmDate, "%W") as Date2 from tblDateSort order by date_format(dtmDate, "%W");
+-------+-----------+
| Date1 | Date2     |
+-------+-----------+
| 0     | Sunday    |
| 0     | Sunday    |
| 0     | Sunday    |
| 1     | Monday    |
| 1     | Monday    |
| 1     | Monday    |
| 1     | Monday    |
| 2     | Tuesday   |
| 2     | Tuesday   |
| 3     | Wednesday |
| 3     | Wednesday |
| 4     | Thursday  |
+-------+-----------+
12 rows in set (0.00 sec)

(Thursday should be before Tuesday alphabetically)

This behavior can be reproduced on MySQL server versions 4.1.10a and 4.1.14.

This bug does not exist on MySQL server version 4.0.23. An example of the same query on MySQL 4.0.23:

mysql> select date_format(dtmDate, "%m") as Date1, date_format(dtmDate, "%M") as Date2 from tblDateSort order by date_format(dtmDate, "%M");
+-------+-----------+
| Date1 | Date2     |
+-------+-----------+
| 04    | April     |
| 08    | August    |
| 12    | December  |
| 02    | February  |
| 01    | January   |
| 07    | July      |
| 06    | June      |
| 03    | March     |
| 05    | May       |
| 11    | November  |
| 10    | October   |
| 09    | September |
+-------+-----------+
12 rows in set (0.00 sec)

How to repeat:
mysql> create database test;
mysql> use test;
mysql> CREATE TABLE tblDateSort (dtmDate DATETIME);
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-01-05");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-02-06");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-03-22");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-04-11");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-05-1");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-06-08");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-07-07");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-08-29");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-09-20");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-10-03");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-11-14");
mysql> INSERT INTO tblDateSort (dtmDate) VALUES ("2005-12-25");
mysql> select date_format(dtmDate, "%m") as Date1, date_format(dtmDate, "%M") as Date2 from tblDateSort order by date_format(dtmDate, "%M");

Suggested fix:
Use month() or another appropriate function in ORDER BY.
[13 Oct 2005 22:45] MySQL Verification Team
Thank you for the bug report.

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.16-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select date_format(dtmDate, "%m") as Date1, date_format(dtmDate, "%M") as
    -> Date2 from tblDateSort order by date_format(dtmDate, "%M");
+-------+-----------+
| Date1 | Date2     |
+-------+-----------+
| 01    | January   |
| 02    | February  |
| 03    | March     |
| 04    | April     |
| 05    | May       |
| 06    | June      |
| 07    | July      |
| 08    | August    |
| 09    | September |
| 10    | October   |
| 11    | November  |
| 12    | December  |
+-------+-----------+
12 rows in set (0.00 sec)

-----------------------------------------------------------------------------------
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.15-rc-debug |
+-----------------+
1 row in set (0.00 sec)

mysql> select date_format(dtmDate, "%m") as Date1, date_format(dtmDate, "%M") as
    -> Date2 from tblDateSort order by date_format(dtmDate, "%M");
+-------+-----------+
| Date1 | Date2     |
+-------+-----------+
| 01    | January   |
| 02    | February  |
| 03    | March     |
| 04    | April     |
| 05    | May       |
| 06    | June      |
| 07    | July      |
| 08    | August    |
| 09    | September |
| 10    | October   |
| 11    | November  |
| 12    | December  |
+-------+-----------+
12 rows in set (0.00 sec)
-----------------------------------------------------------------------------------

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.26-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select date_format(dtmDate, "%m") as Date1, date_format(dtmDate, "%M") as
    -> Date2 from tblDateSort order by date_format(dtmDate, "%M");
+-------+-----------+
| Date1 | Date2     |
+-------+-----------+
| 04    | April     |
| 08    | August    |
| 12    | December  |
| 02    | February  |
| 01    | January   |
| 07    | July      |
| 06    | June      |
| 03    | March     |
| 05    | May       |
| 11    | November  |
| 10    | October   |
| 09    | September |
+-------+-----------+
12 rows in set (0.00 sec)
[25 Oct 2005 16:38] Evgeny Potemkin
By default constant strings in second parameter of date_time() have case
insensitive collation. Because of this expressions date_format(f,'%m') and 
date_format(f,'%M') wrongly becomes equal, which results in choosing wrong 
column to sort by.

Fixed in 4.1.16, cset  1.2462.2.1
[28 Oct 2005 14:08] Evgeny Potemkin
Fixed in 5.0.16
[19 Jan 2006 22:28] Mike Hillyer
Added to 4.1.16 and 5.0.16 changelogs:

      <listitem>
        <para>
          When the <function>DATE_FORMAT()</function> function appeared
          in both the <literal>SELECT</literal> and <literal>ORDER
            BY</literal> clauses of a query but with arguments that
          differ by case (i.e. %m and %M), incorrect sorting may have
          occurred.
          (Bug #14016)
        </para>
      </listitem>