MySQL/MariaDB - order by inside subquery

Question

I used the following query with MySQL 5.5 (or previous versions) for years without any problems:

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

The order of the result was always descending as I needed.

Last week, I just migrated to a new MySQL Version (In fact, I migrated to MariaDB 10.0.14) and now the same query with the same database is not sorted descending anymore. It is sorted ascending (or sorted using the natural order, not sure in fact).

So, can somebody could tell me if this is a bug or if this is a change of the behaviour in recent versions of MySQL/MariaDB?

Answer 1

After a bit of digging, I can confirm both your scenarios:

MySQL 5.1 does apply the ORDER BY inside the subquery.

MariaDB 5.5.39 on Linux does not apply the ORDER BY inside the subquery when no LIMIT is supplied. It does however correctly apply the order when a corresponding LIMIT is given:

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

Without that LIMIT, there isn't a good reason to apply the sort inside the subquery. It can be equivalently applied to the outer query.

Documented behavior:

As it turns out, MariaDB has documented this behavior and it is not regarded as a bug:

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order).

You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT.

So MariaDB also recommends applying the ORDER BY in the outermost query, or a LIMIT if necessary.

Note: I don't currently have access to a proper MySQL 5.5 or 5.6 to confirm if the behavior is the same there (and SQLFiddle.com is malfunctioning). Comments on the original bug report (closed as not-a-bug) suggest that MySQL 5.6 probably behaves the same way as MariaDB.

Answer 2

In newer versions of MySQL and MariaDB you can force the ORDER BY in a sub query by applying a LIMIT. If you don't want to limit the rows, use the biggest number of BIGINT as a LIMIT.

This may come in handy at times, when the sub query needs to be generated in a desired order, for applying line numbers, for example.