Bug #30368 GROUP BY primary_key query's output is not ordered when the PK is not ordered
Submitted: 11 Aug 2007 2:26 Modified: 29 Aug 2007 14:54
Reporter: Sergey Petrunya Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1/5.0/5.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[11 Aug 2007 2:26] Sergey Petrunya 
Description:
Output of " ... GROUP BY primary_key" query is not ordered when the Primary Key uses an unordered index.

I observed the problem with HEAP table but I beleive it will exist whenever there are unordered indexes, e.g. with NDB tables too.

How to repeat:
Run this: 

create table t10 (a int primary key) engine=heap;
insert into t10 values (10), (5),(6), (1);
select a from t10 group by a; 
+----+
| a  |
+----+
| 10 | 
|  5 | 
|  6 | 
|  1 | 
+----+
4 rows in set (0.00 sec)
[11 Aug 2007 4:10] MySQL Verification Team 
Thank you for the bug report. Verified as described.
[17 Aug 2007 15:12] Roland Bouman 
If you ask me, we should get rid of the behaviour of always returning results ordered according to the GROUP BY clause.

An ordered result is nice if you need it, but I don't see why GROUP BY must claim to always do it, esp. as it costs performance. Carelessly writing a GROUP BY (reporting tools do sometimes) result in costly queries, and for no reason at all. 

It would be much better if the user could turn the behaviour off with an sql_mode or similar.

(http://bugs.mysql.com/bug.php?id=30477)
[25 Aug 2007 20:12] Sergey Petrunya 
Roland, I side with PeterZ's position expressed in BUG#30477. However, in the "GROUP BY requires ordering" mode the output must be ordered no matter if there are some unique indexes or not.
[29 Aug 2007 14:54] Sergey Petrunya 
Fixed by fix for BUG#30596.