MySQL/MariaDB-通过内部子查询排序 [英] MySQL/MariaDB - order by inside subquery

问题描述

多年来，我在MySQL 5.5(或更低版本)中使用以下查询没有任何问题:

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.

上周，我刚刚迁移到新的MySQL版本(实际上，我迁移到了MariaDB 10.0.14)，现在具有相同数据库的相同查询不再按降序排序.它以升序排序(或使用自然顺序排序，实际上不确定).

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).

那么，有人可以告诉我这是一个错误还是这是最新版本的MySQL/MariaDB中行为的改变?

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?

推荐答案

经过一番挖掘，我可以确认您的两种情况:

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

MySQL 5.1确实在子查询中应用了ORDER BY.

MySQL 5.1 does apply the ORDER BY inside the subquery.

MariaDB 5.5.39不会在子查询中应用ORDER BY. 确实，但是在给出相应的LIMIT时正确地应用了该顺序:

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;

如果没有LIMIT，则没有充分的理由在子查询中应用排序.它可以等效地应用于外部查询.

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.

事实证明，

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

根据SQL标准，表"(以及FROM子句中的子查询)是无序的行集.表中(或FROM子句中的子查询中)的行不按任何特定顺序排列.这就是为什么优化器可以忽略您指定的ORDER BY子句的原因.实际上，SQL标准甚至不允许ORDER BY子句出现在此子查询中(我们允许它，因为ORDER BY ... LIMIT ...会更改结果，行集，而不仅是行顺序).

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).

您需要将FROM子句中的子查询视为一组未指定且未定义顺序的行，并将ORDER BY放在顶级SELECT上.

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.

因此，MariaDB还建议在最外面的查询中应用ORDER BY，或者在必要时应用LIMIT.

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

注意:我目前无法访问正确的MySQL 5.5或5.6，以确认那里的行为是否相同(并且SQLFiddle.com出现故障). 对原始错误报告的评论(以非错误关闭)表明MySQL 5.6的行为方式可能相同作为MariaDB.

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.

这篇关于MySQL/MariaDB-通过内部子查询排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！