MySQL Bug?
After an upgrade to MySQL 5.0.51b on RHEL 5 I started seeing curious results in a fairly common query. Here’s a simplified version:
SELECT ID, post_date_gmt FROM wp_posts GROUP BY ID ORDER BY post_date_gmt DESC LIMIT 5
What I expected was to get a handful of post ID numbers sorted in descending order by the post_date_gmt. Instead, I got a list of post IDs sorted in ascending order by the ID number. Something like this:
3 2007-05-21 00:00:00 4 2007-05-21 00:00:00 5 2007-05-21 00:00:00 6 2007-05-21 00:00:00 7 2007-05-21 00:00:00
After some fiddling I discovered that the GROUP BY clause was causing a problem. So this query works:
SELECT ID, post_date_gmt FROM wp_posts ORDER BY post_date_gmt DESC LIMIT 5
…and outputs the results I expected:
337832 2008-06-20 15:20:03 335991 2008-06-17 13:00:42 337777 2008-06-02 12:15:46 337390 2008-05-28 00:00:00 337831 2008-05-28 00:00:00
The GROUP BY clause may be unnecessary, though it was originally written in to accommodate conditions where a JOIN (which is often added when the query is dynamically generated) causes MySQL to return multiple rows representing the same record ID.
Still, isn’t this behavior weird? It’s certainly different from previous versions.
2 Comments
Comments RSS TrackBack Identifier URI
Leave a comment
The group by in the first query is illegal on most databases. When there is a select clause which specifies columns that are not in a group by clause, then the returned values of such columns are random randomly selected among available values. All sorts of undesirable behavior can happen due to misuse of a group by which is allowed in MySQL.
My guess is that MySQL AB is not testing their builds against queries which should be illegal.
@Rob Wultsch: I think you found this post within minutes of when I published it. I edited it shortly after that to show the select on both ID and post_date_gmt, which isn’t how the real query is written, but I did test it that way. The result of the first query, with or without post_date_gmt is the same: I get posts returned in ascending order of post ID.