Is MySQL syntax GROUP BY ... ASC/DESC removed or not?

Question

I would like to ask if MySQL syntax GROUP BY ... ASC/DESC was removed or not? By this worklog task it should be removed but it seems that it is not truth. There are no details in which version it was applied first time.

By this db-fiddle demo it still works well on any MySQL version (but on 5.7 and 8.0 sql_mode ONLY_FULL_GROUP_BY must be disabled or ANY_VALUE() must be used to avoid error ER_WRONG_FIELD_WITH_GROUP). I know that aim in this demo can be re-written using inner join and subquery but in my use case it is 5 times slower on table with 4.500.000 rows. Demo is simplified. My real use case is much similar to this fiddle.

MySQL doc says that results from GROUP BY, used that way I used, are non-deterministic and I should not rely on it. But in my case result are always identical on any MySQL version (can be tested be switching version in db-fiddle). There ore only two requirements: engine must be InnoDB and I have to create index for columns used in GROUP BY (both requirements are not problem - InnoDB and index is performance advantage). I'm using this solution because it is much faster then any other solution using inner join, subqueries etc.

However syntax is removed or not there is alternative which produces identical result. So GROUP BY col1 ASC/DESC can be rewriten as GROUP BY col1 ORDER BY col1 ASC/DESC. Which one is better to use in my case? Both are identical what about results and performance too.

Thanks for any advice.

Answer 1

From the "changelogs". This was unusually fast from 'deprecation' to 'removal'!

2018-07-27 8.0.12 General Availability & 2018-07-27 5.7.23 General Availability

Explicit ASC or DESC qualifiers for GROUP BY clauses are now deprecated and will be removed in a future MySQL version.

2018-10-22 8.0.13 General Availability -- Incompatible Change

The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. Queries that previously relied on GROUP BY sorting may produce results that differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.

Queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses should be amended. Otherwise, upgrading to MySQL 8.0.13 or higher may fail, as may replicating to MySQL 8.0.13 or higher slave servers.