| 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: | |
| 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 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>
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.