Does "deprecated" in MySQL means "not guaranteed" or "still guaranteed"?

Question

MySQL 3.23/4.0/4.1 and 5.1 states:

If you use GROUP BY [ for select ], output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. ➫➫➫

However, MySQL 5.5 states:

Relying on implicit GROUP BY sorting in MySQL 5.5 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. ➫➫➫

(The above info is also stated in 5.6 and 5.7.)

What exactly does "deprecated" mean in MySQL?

Is the behavior of implicit group by still guaranteed to work on MySQL 5.5, 5.6, and 5.7 (the current latest version) just like in every single version before that?

Is ORDER BY NULL still required to stop MySQL from doing needless sorting?

Edit (by Rick James)

This covers database issues:

• The ordering of a GROUP BY without ORDER BY
• The meaning and usefulness of ORDER BY NULL
• The wisdom of using a 'deprecated' 'feature'
• The meaning of "deprecated" in the MySQL manual (not just for this case)

Answer 1

"Deprecated" means "don't use it; it will be removed/forbidden/erroneous/broken/etc is some future version". Meanwhile, it does work.

This is a slow, tedious, way for MySQL to change something that will break a lot of programs. No one knows how many people depend on GROUP BY do have an implicit ORDER BY. This particular "feature" was an extension to the Ansi standard. I suspect they would like to change MySQL for two reasons:

• Abide by the Standard
• Allow certain optimizations (see below)

The simple minded way (think Version 3.xx) to do GROUP BY is to sort the rows and walk through them, gathering dups and doing SUM(), etc as you go. And, back in the days of single-core machines this was quite reasonable. And the result would be naturally sorted. But... If you wanted to throw a GROUP BY at a 24-core machine, that gets really messy to maintain the sorting. So (I'm guessing), that is an optimization that will arrive at the same time that GROUP BY no longer does an implicit ORDER BY. That may happen in 5.8 (just a guess).

You can still get the effect, but you have to explicitly say ORDER BY with the same list of columns/expressions.

The ORDER BY NULL (currently) does nothing in most cases, since most cases find it more efficient to sort for the GROUP BY. ORDER BY NULL, in the future will probably be allowed, but effectively be a no-op; after all it will lead to the same result.

Other deprecated things have similar logic. CREATE TABLE (...) TYPE=MyISAM eventually (after a period of deprecation) became CREATE TABLE (...) ENGINE=MyISAM. That broke some export/import situations, but only if you wer moving from a really old version to a new one. SHOW INNODB STATUS became SHOW ENGINE INNODB STATUS, probably to make for cleaner parsing of SHOW.

Answer 2

Since MySQL 5.5, having GROUP BY do the order is not longer implicitly guaranteed.

Is ORDER BY NULL still required to stop MySQL from doing needless sorting?

Please note how MySQL handles NULL values with ORDER BY

Two NULL values are regarded as equal in a GROUP BY.

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC

Same thing applies to DISTINCT

Since ORDER BY NULL is ascending and the value for order is NULL, the dataset's order before ORDER BY NULL will be the same afterwards.

Another external issue that can impose or deny ordering is the InnoDB Storage Engine. I have an old post about how the Clustered Index can sometimes get in the way of forcing the table into a desired physical index order : MySQL InnoDB Sorting Issue. Here is an even better post (What is the default order of records for a SELECT statement in MySQL?) from Laurynas Biveinis on how the Storage Engine affects ordering.

Developers have relied on the implicit ordering for years. The rug can easily be pulled from under you should you decide to upgrade MySQL on every minor release.

To illustrate this, look at the option simplified_binlog_gtid_recovery. It was introduced in MySQL 5.6.21 to help mysqld traverse binlogs for GTID and crash recovery. Guess what ??? Two minor releases later (MySQL 5.6.23), Oracle introduced binlog_gtid_simple_recovery. You don't know when Oracle will pull the rug out and decommission simplified_binlog_gtid_recovery from use. Many infrastructures relying on that old option. So, BUYER BEWARE !!!

Of course, this is a new option that was quickly replaced by a new name.

Now, imagine how many applications were written that relied on the implicit sorting are affected by a mysql upgrade.

Therefore, you should always test code in Dev/Staging server for any behavioral changes in SELECTs. There is no substitute from such testing.

Answer 3

This is saying that older versions of MySQL used to have a default sort implicitly on the same column as "group by" and it's this behaviour that is deprecated. Whatever you specify in the group by clause will behave the same way as it did previously and is guaranteed to work, just the ordering may be different.

The last part of your question (ORDER BY NULL) is no longer required as the needless default sorting now longer occurs (as that's what has been deprecated)