Why Does My `ORDER BY` Clause in a MySQL/MariaDB Subquery No Longer Work?

MySQL/MariaDB: Changing Behavior of ORDER BY in Subqueries

Consider the following query, previously used successfully in MySQL 5.5:

SELECT t2.Code from (select Country.Code from Country order by Country.Code desc ) AS t2;

In recent versions of MySQL (including MariaDB 10.0.14), this query no longer sorts the results in descending order as expected. This change in behavior has raised concerns about a potential bug.

However, upon investigation, it has been confirmed that this change is intentional and is not considered a bug. MariaDB has documented this behavior, explaining that a subquery returns an unordered set of rows and that the ORDER BY clause in the subquery may be ignored.

To ensure the desired order, it is recommended to apply the ORDER BY clause to the outermost query or, if necessary, add a LIMIT clause to the subquery:

SELECT t2.Code 
FROM (
  SELECT Country.Code FROM Country ORDER BY Country.Code DESC LIMIT 2
) AS t2;

This revised query correctly applies the sort and produces the desired results.

Note that this change in behavior also applies to MySQL 5.6, according to comments on the original bug report. Developers should be aware of this change and adjust their queries accordingly to maintain the desired sorting behavior.

The above is the detailed content of Why Does My `ORDER BY` Clause in a MySQL/MariaDB Subquery No Longer Work?. For more information, please follow other related articles on the PHP Chinese website!