MySQL/MariaDB Subqueries: Why Does ORDER BY Behavior Change in Newer Versions?

MySQL/MariaDB: Order by within Subqueries

Recent versions of MySQL and MariaDB have introduced changes in handling of order by operations within subqueries.

Original Query:

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

Issue:

In MySQL 5.5 and earlier, the above query sorted the subquery results in descending order as specified. However, in newer versions of MySQL and MariaDB, the subquery results are no longer sorted descending by default.

Reason:

The change in behavior is due to a stricter adherence to the SQL standard. According to the standard, subquery results are unordered sets of rows. Therefore, applying an order by clause within a subquery may not guarantee the desired sorting.

Resolution:

To resolve the issue, there are two recommended approaches:

• Outermost Order By: Apply the order by clause to the outermost SELECT statement:

SELECT t2.Code from (select Country.Code from Country) AS t2 ORDER BY t2.Code DESC;

• Limit in Subquery: If necessary, specify a limit within the subquery:

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

This ensures that the subquery produces a sorted set of results, regardless of whether an order by clause is applied to the outermost SELECT.

Documented Behavior:

MariaDB has officially documented this behavior, stating that specifying an order by clause within a subquery is not allowed by the SQL standard and should be treated as an unordered set of rows. The recommended approach is to apply the order by to the outermost query or use a limit if necessary.

The above is the detailed content of MySQL/MariaDB Subqueries: Why Does ORDER BY Behavior Change in Newer Versions?. For more information, please follow other related articles on the PHP Chinese website!