An update on GROUP BY implicit sort

In the MySQL team, we have been planning for some time to remove the implicit sorting that is provided by GROUP BY. In doing so, we will make a number of existing queries faster (by no longer requiring a sort) as well as unlock opportunities to implement further optimizations.

This is one of the more complicated behaviours to remove, because it is not possible to tell if an application relies upon implicit ordering. Since a GROUP BY query without an ORDER BY clause is a valid query, it is also not reasonable to issue deprecation warnings.

However, one piece of the puzzle that was missing when I last wrote about this problem, is that MySQL 5.7 will support server-side query rewrite. What this means, is that Database Administrators will have the ability to inject an ORDER BY into queries that require this legacy behaviour. This is useful in the case where modifying the application directly is not possible.

The second part to this update, is that we also plan to deprecate the closely related syntax GROUP BY .. [ASC|DESC]. I am sure many users are probably unaware that exists, but you can change the implicit ordering to be in descending order with:

SELECT MAX(Population), Name FROM Country GROUP BY Name DESC;

(Note the missing "ORDER BY").

This represents an extension from the SQL standard, that what I can tell is not present in other databases.

Our current plan is to make GROUP BY .. [ASC|DESC] deprecated as of MySQL 5.7, with removal in 5.8. As part of this, we also plan to remove the implicit GROUP BY sort as early as MySQL 5.8.

We are inviting feedback from the MySQL Community regarding this plan. Please leave a comment, or get in touch! I would love to hear from you.

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I've also worked at Percona and InPowered.

  • Roland Bouman

    Excellent idea! Completely in favor.

  • Mark Callaghan

    Will query rewrite be as easy to use as my.cnf option that restores old behavior? Oracle DBMS added hash aggregation about 10 years ago and some customers needed the option to get old behavior (group by was sorted).

    • http://www.tocker.ca/ Morgan Tocker

      The query rewrite would require each incompatible query to be in the query rewrite table, so it will be more effort than a configuration variable (but to answer your question, it is easy to use).

      We would love to speak with affected users to know more about this feature request.

  • gopal

    Hi Morgan, It is really good Idea. I came from Oracle Background and I came to know about this behaviour after a lot of time. Also till now I have updated many of our Old Queries to include 'ORDER BY NULL'.