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.
Posted June 24, 2008 by Casey Bisson
Categories: Technology. Tags: bug, bugs, GROUP BY, mysql, ORDER BY.
3 Comments
Comments RSS
TrackBack Identifier URI
Leave a comment
User contributed tags for this post:
www.sex wiew.com (13) - facebookofsex (8) - order (7) - www.sex.wiew (6) - www.sex.wıew.com (6) - sex wiew (6) - www.facebookofsex.com (5) - mysql bug (4) - www.sexwiew (4) - facebookofsex.com (3) - sexx wiew.com (3) - www sex grup com (3) - www.sex.wıew com (3) - sexs.com (3) - gurup sexs (3) - sex wiew.com (3) - sex.wiew.com (3) - www.sexwiew.tvpicturs (2) - www gurup sexs.com (2) - www.sex wiew sex video (2) - mysql5 group by order by (2) - mysql limit group by min (2) - sexwiew (2) - کوس (2) - داستان کوس (2) - mysql query for list in descending order with limit (2) - gurup sexs.com (2) - mysql select group by having limit order by (2) - www.sex.wiew.com. (2) - sexs wıew.com (2) - www.bangliguru.com (2) - sexs.wiew.com (2) - mysql 5.0.51b order by bug (2) - php\mySQL: GROUP BY (1) - mysql order by post most recent comment (1) - mysql order by field( and group problem (1) - mysql select group order by id (1) - php mysql number order bug (1) - order by mysql 5 (1) - www.sex wiew.it (1) - site:maisonbisson.com www.bangliguru.com (1) - mysql limit 1 bug (1) - se x wiew (1) - www.facebookofsex,com (1) - sexs gurup (1) - GROUP BY post_id ORDER BY (1) - mysql group by order by multiple (1) - s wiew (1) - group by order by mysql (1) - is FacebookOfSex.com real (1) - mysql query grouping (1) - کوس2 (1) - sex.video.grup (1) - facebookofsex (5) (1) - mysql order by 4 40 (1) - mysql order by multiple (1) - mysql group by bug multiple entries (1) - facebook-of-sex (1) - group by and order by in one query in mysql (1) - mysql5 group order by (1) - groupby and order by in mysql (1) - mysql group limit (1) - wordpress orderby bug (1) - www,bangliguru,com (1) - mysql select query with limit order by (1) - limit order by mysql bug (1) - mysql 6 order by (1) - mysql 5.0 group by order by (1) - www facebookofsex.com (1) - wordpress orderby category bug (1) - facebookofsex facebookofsex (1) - ORDER BY mysql 4 (1) - GROUP BY mysql order by (1) - mysql 6 order by problem (1) - mysql group by order by limit (1) - ww .sex .wiew . (1) - limit results with MySql 5.0.51b (1) - mysql avoid group by order by (1) - داستانهای مامانی (1) - mysql 5 group by bug (1) - mysql 5.0.51b wordpress order bug (1) - order by mysql bug 1 10 (1) - mysql select row values where min( returns group by weird results (1) - 5i sexs.com (1) - mysql 5.0.51b group by order by problem (1) - wordpress mysql grouping bug (1) - mysql order 1 10 (1) - mysql 5.0.51b limit 1 (1) - mysql select * WHERE MIN() (1) - php mysql 5.0.51b LIMIT 1 (1) - my sql bug (1) - mysql bug order by (1) - gurup sex (1) - www.sex wiew (1) - mysql order by bug (1) - mysql 5.0.51b bugs wordpress (1) - post mysql bug (1) - mysql bug LIMIT (1) - grup sexs.com (1) - ORDER BY post_date_gmt DESC (1) - ordering mysql bug desc (1) - asc desc mysql bug (1) - order by mysql bug (1) - mysql limit bug (1) - mysql control order of join with group by (1) - random desc order in mysql (1) - mysql october bug (1) - mysql group by and limit (1) - maisonbisson mysql bug (1) - select * where min(*) (1) - mysql 5.0.37 limit (1) - mysql select latest post order by groups (1) - sex wiew info (1) - mysql 5.0.37 order by bug (1) - wiew.sex (1) - mysql post id (1) - wiew.sex. (1) - bug myssql group by wordpress (1) - mysql 5 group by (1) - mysql select order by 1 10 (1) - GROUP BY mySQL (1) - group by and order by in mysql in one query (1) - MYSQL ORDER BY DESC LIMIT BUG (1) - 12 on one 2 -no limit sexs video (1) - mysql select limit order bug (1) - group by and order by in same query in mysql (1) - mysql order by desc bug (1) - www.facebookofsex (1) - bangliguru (1) - mysql query order by group by (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.
@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.
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)?