Bug in ORDER BY

[Ken Goff]

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