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.