re: Bug in ORDER BY
On Monday 17 February 2003 21:06, Ken Goff wrote: 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- instead of -m-d. It works fine for me. Could you provide example of wrong ordering? Please, include also EvendDate column in the output. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
I'm glad you found the problem! Sorry my suggestion did not work. I'm still confused on why you have quotes around the field names in the order by part of the query, though. All the best! -steve- - 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
SELECT * FROM EventList ORDER BY 'EventDate', 'EventOrder' LIMIT 50; I'm surprised you happened to get anything in order. Maybe the message got simplified by the list manager, but did you really mean to order by a constant string? Why not: SELECT * FROM EventList ORDER BY `EventDate`, `EventOrder` LIMIT 50; Or better: SELECT * FROM EventList ORDER BY EventDate, EventOrder LIMIT 50; This wasn't the query you had the issue with, but it is easier to quote. ;) Same theory applies. Discard this if the list manager had mangled your post. -steve- - 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: InnoDB ORDER BY DESC may hang in 4.0.10
Heikki Tuuri [EMAIL PROTECTED], Do you think the following links may help? * http://www.mysql.com/doc/en/DESCRIBE.html * http://www.mysql.com/doc/en/ALTER_TABLE.html * http://www.mysql.com/doc/en/Group_by_functions.html * http://www.mysql.com/doc/en/ORDER_BY_optimisation.html * http://www.mysql.com/doc/en/Change_column_order.html This was an automated response to your email 'BUG: InnoDB ORDER BY DESC may hang in 4.0.10'. Final search keyword used to query the manual was 'BUG: InnoDB ORDER BY DESC may hang 4.0.10'. Feedbacks, suggestions and complaints about me should be directed to [EMAIL PROTECTED] - 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
