MySQL Bugs: #30477: Allow returning unordered result from GROUP BY

Bug #30477	Allow returning unordered result from GROUP BY
Submitted:	17 Aug 2007 15:12	Modified:	20 Aug 2007 10:11
Reporter:	Roland Bouman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	all, or asap	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL is committed to returning results from GROUP BY queries according to the order implied by the GROUP BY clause (unless overridden by an ORDER BY clause)

It is entirely possible that this costs performance. As there is no requirement from a standard it seems unreasonable to force this behaviour on the user. 

It would make sense to allow this behaviour to be configurable, through a server variable. I can see a number of options:

1) ordered_group_by = always -- the default, current behaviour
2) ordered_group_by = never  -- never do extra work to return an ordered result. Of course, the result may happen to be ordered, but no guarantee is made, and ASC and DEC in the GROUP BY clause are ignored. 
3) ordered_group_by = explicit -- results are ordered only if ASC and/or DESC are specified in the GROUP BY clause, and sorting occurs only on those columns that explicitly specify ASC/DESC

It might seem as if the current behaviour is a minor problem as one can work around it. However, this is only true if it is possible to explicitly specify the SQL. Reporting/BI/ROLAP tools, -which often generate GROUP BY queries- will more often than not be aware of MySQL's behaviour in this respect. This makes MySQL less usable for BI solutions. Another factor is the writing of SQL92 compliant GROUP BY clauses. In those cases, the group by contains all non-aggregated columns that appear in the SELECT list, making a wide GROUP BY list, which is costly to sort on. 

BTW - ordered result from GROUP BY is a non-standard feature unsupported by other major RDMBS-es.

Regardless of the proposed setting, ORDER BY will still work as expected.

How to repeat:
na

Suggested fix:
na

Good idea. 

I think this can be part of the SQL_MODE also ANSI mode may define Unordered Group By as it breaks a lot of MySQL things anyway.

Thank you for the reasonable feature request.

This bug becomes very important in business intelligence situations where queries are dynamically generated.  Taking into account intimate knowledge of the indexed columns in the query generation (the Group by column order in this case) is cumbersome and not done by most ad-hoc query tools.  In these cases, query performance is negatively impacted almost always.

Search the manual for "ORDER BY NULL"

3 years later, I wish to support this feature request,

From http://dev.mysql.com/doc/refman/5.5/en/select.html

>>>
If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY  produces, add ORDER BY NULL: 
>>>

Why did they document it like this? Now they really are committed to ordering the group by results. Users of an engine that provides persistent hash indexes will be unhappy when they forget to add "order by null". Even someone with a really, really big MEMORY table might be unhappy.

I wrote about this in a longer way here:

http://www.sheeri.com/content/should-you-use-group-by%E2%80%A6order-null-default

But in summary: In this day and age when performance is king, having *implicit overhead* in MySQL is wasteful.

I'd make Roland's suggestion consistent with other features (specifically query_cache):

ordered_group_by=OFF (0) - never order GROUP BY, produce a warning if ORDER BY is present in a query, stating that ORDER BY may not be used. (this state is not recommended)

ordered_group_by=ON (or 1) - always order GROUP BY by the grouped fields; consistent with current behavior, and this is the default value

ordered_group_by=DEMAND (or 2) - only order GROUP BY if an explicit ORDER BY is present. (and this is what folks will recommend to put the setting at)

In 5.6 relying on this behaviour was deprecated, and may change in a future version.  From:
http://dev.mysql.com/doc/refman/5.6/en/select.html

"Relying on implicit GROUP BY sorting in MySQL 5.6 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead."

When changing the behavior please allow one major release with the new behavior as optional.  That is, if in 5.7, it is possible to disable implicit sorting, it should be optional, and via an SQL mode (or optimizer_switch).  This will allow 5.6, 5.7 and 5.8 applications to interact with the same behavior, assuming 5.8 defaults it ON, but you can set the SQL_MODE to <= 5.7 behavior.