MySQL Bugs: #32202: ORDER BY not working with GROUP BY

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	CPU Architecture:	Any

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.

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

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.

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.

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

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.

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.

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

Bug #35181 was marked as duplicate of this one.

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?

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