Why Does My MariaDB Subquery's ORDER BY Clause Seem to Be Ignored?

MySQL/MariaDB: Order By Subquery Discrepancy

In MySQL versions prior to 5.5, subqueries honored the ORDER BY clause within. However, this behavior has changed in recent versions, including MariaDB 10.0.14.

Observed Issue

When executing the following query in MariaDB 10.0.14:

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

The results are now sorted in ascending order (or by natural order), in contrast to the expected descending order.

Investigation

After examining MariaDB's documentation, the observed behavior is not considered a bug. According to the SQL standard, tables and subqueries are unordered sets of rows. Therefore, the optimizer may choose to ignore the ORDER BY clause within the subquery.

Recommended Solution

To ensure consistent ordering of results, it is recommended to apply the ORDER BY clause to the outer query:

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

Alternatively, using a LIMIT clause within the subquery may also force the ORDER BY to be applied:

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

Behavior in MySQL 5.6

While specific testing is currently unavailable, comments on the bug report suggest that MySQL 5.6 may also exhibit the same behavior as MariaDB 10.0.14, ignoring the ORDER BY clause within subqueries.

The above is the detailed content of Why Does My MariaDB Subquery's ORDER BY Clause Seem to Be Ignored?. For more information, please follow other related articles on the PHP Chinese website!