I have encountered what appears to be a bug in MySQL regarding ORDER
BY related to a date field when also used in conjunction with
DATE_FORMAT() or UNIX_TIMESTAMP(). When these functions are applied
to my date field, MySQL ignores any included ORDER BY within the
query. I first noticed this bug using PHP with MySQL but have
confirmed it occurs when entering the query directly within Terminal.
The table consists of the following 4 fields with their data types described:
EventId - number - mediumint(10) - auto_increment
EventDate - date
Event - text
EventOrder - number - mediumint(5)
The following query sorts properly:
SELECT * FROM EventList ORDER BY 'EventDate', 'EventOrder' LIMIT 50;
The following ignores ORDER BY and instead sorts by EventId:
SELECT EventId, DATE_FORMAT(EventDate, '%c-%e-%Y') as
FormattedEventDate, Event, EventOrder FROM EventList ORDER BY
'EventDate', 'EventOrder' LIMIT 50;
So does the following sort by EventId instead of using the ORDER BY:
SELECT EventId, UNIX_TIMESTAMP(EventDate), Event, EventOrder FROM
EventList ORDER BY 'EventDate', 'EventOrder' LIMIT 50;
Is there any way around this bug? I want to list dates in US style of
"m-d-yyyy" instead of "yyyy-m-d".
--
-Ken Goff
MacEssentials
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
- re: Bug in ORDER BY Ken Goff
- re: Bug in ORDER BY Victoria Reznichenko
- RE: Bug in ORDER BY Steven Roussey
- RE: Bug in ORDER BY Steven Roussey