MySQL/MariaDB - порядок внутри подзапроса

В течение многих лет я без проблем использовал следующий запрос с MySQL 5.5 (или предыдущими версиями):

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

Порядок результата всегда уменьшался по мере необходимости.

На прошлой неделе я только что перешел на новую версию MySQL (фактически я перешел на MariaDB 10.0.14), и теперь тот же запрос с той же базой данных больше не сортируется по убыванию. Он сортируется по возрастанию (или сортируется с использованием естественного порядка, на самом деле не уверен).

Итак, может кто-нибудь сказать мне, если это ошибка или это изменение поведения в последних версиях MySQL/MariaDB?

Ответ 1

После небольшого копания я могу подтвердить оба ваших сценария:

MySQL 5.1 применяет ORDER BY внутри подзапроса.

MariaDB 5.5.39 в Linux не применяет ORDER BY внутри подзапроса, когда не предоставляется LIMIT. Тем не менее, он правильно применяет порядок, когда задан соответствующий LIMIT:

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

Без этого LIMIT нет веской причины применять сортировку внутри подзапроса. Он может быть эквивалентно применен к внешнему запросу.

Документированное поведение:

Как оказалось, MariaDB зафиксировал это поведение, и это не считается ошибкой:

"Таблица" (и подзапрос в предложении FROM тоже) - в соответствии со стандартом SQL - неупорядоченный набор строк. Строки в таблице (или в подзапросе в предложении FROM) не входят в какой-либо конкретный порядок. Поэтому оптимизатор может игнорировать предложение ORDER BY, которое вы указали. На самом деле, стандарт SQL даже не позволяет выражать предложение ORDER BY в этом подзапросе (мы его разрешаем, потому что ORDER BY ... LIMIT... изменяет результат, набор строк не только их порядок).

Вам нужно обработать подзапрос в предложении FROM в виде набора строк в некотором неуказанном и undefined порядке и поместить ORDER BY на верхний уровень SELECT.

Поэтому MariaDB также рекомендует применять ORDER BY в самом внешнем запросе или при необходимости LIMIT.

Примечание. В настоящее время у меня нет доступа к надлежащим MySQL 5.5 или 5.6, чтобы подтвердить, что поведение там же (и SQLFiddle.com работает неправильно). Комментарии к исходному отчету об ошибках (закрыто как не-ошибка) показывают, что MySQL 5.6, вероятно, ведет себя так же, как MariaDB.

Ответ 2

В более новых версиях MySQL и MariaDB вы можете форсировать ORDER BY в подзапросе, применяя LIMIT. Если вы не хотите ограничивать строки, используйте наибольшее число BIGINT в качестве ограничения.

Это может пригодиться иногда, когда необходимо сформировать подзапрос в желаемом порядке, например, для применения номеров строк.

Ответ 3

В ходе тестирования я обнаружил, что использование GROUP BY в подзапросе, кажется, заставляет весь запрос принять предложение ORDER BY в подзапросе.

Это правильное и ожидаемое поведение?