Странное поведение limit X offset Y в запросах

Добрый день.
Есть таблица с двумя полями, одно из них ключ. Скрипт создания таблицы и заполнения можно взять здесь
Выполняю запрос, который сравнивает две порции таблицы для пагинации:
select * from ( select distinct id, created_at from aaa order by created_at desc limit 5 offset 0 ) a, ( select distinct id, created_at from aaa order by created_at desc limit 5 offset 5 ) b where a.id = b.id
Логично предположить, что не должно оказаться одинаковых записей в обоих подзапросах?
Однако mysql возвращает три таких записи, а Mariadb возвращает одну.
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper mysql Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1
Если у таблицы удалить первичный ключ, тогда повторяющиеся записи исчезнут

Ответ

Немного теории: Если порядок следования записей не определен однозначно, SQL имеет право применять любой порядок, который ему нравится. В данном случае вы просите SQL отсортировать данные по полю created_at. При этом в БД есть много записей с одинаковым created_at. Например, указанный order определяет такой порядок сортировки:
Дата ID 2017-06-09 15-08-26 4 2017-06-09 15-08-25 3 2017-06-09 15-08-25 2 2017-06-09 15-08-25 1
У трех записей дата создания одинакова, следовательно при сортировке по дате такой порядок так же полностью удовлетворяет условию:
Дата ID 2017-06-09 15-08-26 4 2017-06-09 15-08-25 2 2017-06-09 15-08-25 1 2017-06-09 15-08-25 3
А теперь рассмотрим план выполнения запроса (я задал limit 10):
+----+-------------+------------+------+---------------+-------------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+-------------+---------+------+------+----------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 10 | NULL | | 1 | PRIMARY | | ref | | | 108 | a.id | 2 | NULL | | 3 | DERIVED | aaa | ALL | NULL | NULL | NULL | NULL | 3019 | Using filesort | | 2 | DERIVED | aaa | ALL | NULL | NULL | NULL | NULL | 3019 | Using filesort | +----+-------------+------------+------+---------------+-------------+---------+------+------+----------------+
Из него следует, что таблицу из первого подзапроса MySQL берет целиком из области данных и сортирует. По второму подзапросу он принимает решение подобрать подходящие записи по ID из первой таблицы, т.е. фактически заранее выполнить объединение a.id=b.id, таким образом данные с диска он получает не в том порядке как они лежат в области данных, а выдергивая по одной на основании первичного ключа. После чего и эту выборку он сортирует. Но так как изначальный порядок записей был другим, то сортировка в пределах одной даты создания выдает другой порядок следования id. После этого MySQL наконец применяет к полученным данным offset и limit. И фактически оказывается, что лимиты были применены к разным наборам данных (отсортированным в разном порядке), что и влечет за собой одинаковые id на выходе после применения лимита.
В случае отсутствия первичного ключа на таблице у оптимизатора не остается другого выбора как только получать таблицу целиком из области данных. Поэтому оба набора при применении limit оказываются одинаковы и запрос уже не находит одинаковых id.
Показанное в Вашем вопросе отлично демонстрирует, на сколько важно однозначно определять порядок сортировки при использовании операции limit. Добавление поля ID в предложение order by полностью устраняет проблему, SQL в таком случае гарантирует одинаковый порядок записей и следовательно срез получаемый лимитом.