Why Does My MariaDB Subquery Ignore `ORDER BY` Without `LIMIT`?

MySQL/MariaDB Subquery ORDER BY Behavior

In MySQL versions prior to 5.5, the ORDER BY clause within a subquery was applied as expected. However, in recent versions of MariaDB (e.g., 10.0.14), the ORDER BY clause is not applied inside subqueries when no LIMIT clause is specified.

Bug or Behavior Change?

After investigating this issue, it was confirmed that this behavior is intended and not a bug. MariaDB follows the SQL standard, which does not require a specific order for rows within subqueries. Therefore, the ORDER BY clause is ignored inside subqueries.

Documented Behavior

According to MariaDB documentation, it is recommended to apply the ORDER BY clause to the outermost query or add a LIMIT clause to enforce the ordering within the subquery.

Example

The following query demonstrates the difference in behavior:

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

Without a LIMIT clause, MariaDB will not apply the ORDER BY clause inside the subquery, resulting in an unordered result. To enforce the descending order, a LIMIT clause can be added:

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

By adding the LIMIT clause, the ORDER BY clause is applied within the subquery, resulting in a correctly descending result.

Note

It is important to note that this behavior change may also affect MySQL versions beyond 5.5. Always consult the official documentation or release notes to confirm the specific behavior for the version you are using.

The above is the detailed content of Why Does My MariaDB Subquery Ignore `ORDER BY` Without `LIMIT`?. For more information, please follow other related articles on the PHP Chinese website!