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.

3 thoughts on “MySQL Bug?

  1. 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.

  2. @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.

  3. Hello!
    True! I think that is a BUG…. and a BIG bug!
    I have the same database in two machines: one is windows based with version 5.0.51b and it returns th BAD order, that is the ID order instead of date (in my case i had really the SAME problem as you… I need to group by due to the fact that in some cases I had joins taht multply the outputs)
    With Mysql 5.0.37 in SlackwareLinux that (hopefully because is “on field” machine)
    Did you tried with newer verions (the last one is 5.0.64)?

Comments are closed.