Bug #32202 ORDER BY not working with GROUP BY
Submitted: 8 Nov 2007 22:30 Modified: 19 Nov 2007 4:31
Reporter: Gary Pendergast Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.50 OS:Any
Assigned to: Alexey Kopytov

[8 Nov 2007 22:30] Gary Pendergast
Description:
A query that has a GROUP BY seems to be ignoring the subsequent ORDER BY clause. This is new as of 5.0.50

I have confirmed that the problem happens with InnoDB and MyISAM tables.

How to repeat:
mysql:test> create table a ( id int auto_increment primary key, sorter char(5));
Query OK, 0 rows affected (0.18 sec)

mysql:test> insert into a values (1,'z'),(2,'x'),(3,'a'),(4,'z');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql:test> select * from a;
+----+--------+
| id | sorter |
+----+--------+
| 1 | z |
| 2 | x |
| 3 | a |
| 4 | z |
+----+--------+
4 rows in set (0.00 sec)

mysql:test> select * from a order by sorter;
+----+--------+
| id | sorter |
+----+--------+
| 3 | a |
| 2 | x |
| 1 | z |
| 4 | z |
+----+--------+
4 rows in set (0.00 sec)

mysql:test> select * from a group by id order by sorter;
+----+--------+
| id | sorter |
+----+--------+
| 1 | z |
| 2 | x |
| 3 | a |
| 4 | z |
+----+--------+
4 rows in set (0.00 sec)

Suggested fix:
The last query below should produce the same results as the second-to-last query.
[8 Nov 2007 22:37] Gary Pendergast
It works as expected if the primary key is removed. I've dropped it to S2 as technically a work-around exists, even if it's a really bad work-around.

It appears to be related to:
http://bugs.mysql.com/bug.php?id=30596
[8 Nov 2007 23:02] Gary Pendergast
Another work-around is to re-write the last query as:

mysql> SELECT * FROM ( SELECT * FROM a GROUP BY id ) b ORDER BY sorter;

Again, this would cause a performance hit, but not as bad as removing the PK.
[9 Nov 2007 16:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37447

ChangeSet@1.2550, 2007-11-09 19:12:12+03:00, kaa@polly.(none) +3 -0
  Fix for bug #32202: ORDER BY not working with GROUP BY
  
  The bug is a regression introduced by the fix for bug30596. The problem
  was that in cases when groups in GROUP BY correspond to only one row,
  and there is ORDER BY, the GROUP BY was removed and the ORDER BY
  rewritten to ORDER BY <group_by_columns> without checking if the
  columns in GROUP BY and ORDER BY are compatible. This led to
  incorrect ordering of the result set as it was sorted using the
  GROUP BY columns. Additionaly, the code discarded ASC/DESC modifiers
  from ORDER BY even if its columns were compatible with the GROUP BY
  ones.
  
  This patch fixes the regression by checking if ORDER BY columns form a
  prefix of the GROUP BY ones, and rewriting ORDER BY only in that case,
  preserving the ASC/DESC modifiers. That check is sufficient, since the
  GROUP BY columns contain a unique index.
[16 Nov 2007 9:30] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:32] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:34] Bugs System
Pushed into 6.0.4-alpha
[19 Nov 2007 4:31] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

Under certain conditions, the presence of a GROUP BY clause could
cause an ORDER BY clause to be ignored.
[7 Feb 2008 22:25] Kolbe Kegel
This can be worked around by simply removing the GROUP BY clause. Using GROUP BY on a unique index (a Primary Key, of course, is a unique index) is pointless as each row will by definition be its own group.
[7 Feb 2008 22:49] Kolbe Kegel
It's also worth noting that many queries of this form would not function at all in other DBMS products or with the ONLY_FULL_GROUP_BY SQL mode set in MySQL.

mysql> select * from a group by id order by sorter;
+----+--------+
| id | sorter |
+----+--------+
|  1 | z      | 
|  2 | x      | 
|  3 | a      | 
|  4 | z      | 
+----+--------+
4 rows in set (0.00 sec)

mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a group by id order by sorter;
ERROR 1055 (42000): 'test.a.sorter' isn't in GROUP BY

MySQL has peculiar GROUP BY behavior described in more detail at http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
[19 Mar 2008 13:37] Sveta Smirnova
Bug #35181 was marked as duplicate of this one.
[10 Apr 2008 19:40] Michael Pelz-Sherman
This bug is causing major problems for users of WordPress.

Is there any chance of issuing a patch or update for 5.0.51a so my ISP can upgrade without having to use an RC release?
[15 Apr 2008 9:26] Alexey Kopytov
Michael,

This bug was fixed in the 5.0 tree, so the fix will be available in the next community release (and is already available in the latest enterprise releases).