Пусть у нас в коде есть такой примитивный кусок:
# views.py
ids = [5201, 5230, 5183, 5219, 5217, 5209, 5246, 5252, 5164, 5248, ...<и т.д.>...]
products = Product.objects.filter(id__in=ids)
Полученные товары про помощи пагинации выводятся на соответствующей страничке по 20 штук. Однажды звонит менеджер и говорит, что товар «прыгает» по страницам — сначала он был замечен на второй странице, а потом внезапно повторяется на пятой.
«Ха» — заявляем мы, ставим брейкпоинт после указанного блока кода и делаем print(products). Визуально и, для верности, циклом проверяем вывод — а там дубликатов нет!
Сделаем вот что: попробуем отловить дублированный товар индексированием и слайсами. Через некоторое время обнаруживаем негодяев: products[3] == products[20]. Так, нашли их. 3 и 20. Товар name.
Выводим: print(products), смотрим на позиции 3 и 20… а там разные товары! Да как так?
Пробуем print(products[0:10]) — товар в позиции 3 есть — name. Пробуем print(products[10:21]) — товар в позиции 20 тоже есть, и он такой же — name. @#! Ну что ж, видимо, django как-то по-разному делает итерацию и взятие по индексу (штоа?), проверим.
Лезим в QuerySet класс, там смотрим __getitem__ метод, вот он в кратком пересказе:
qs = self._clone()
qs.query.set_limits(k, k + 1)
return list(qs)[0]
То есть взятие по индексу — это просто установка set_limits для запроса, поэтому я решил проверить, как же это выглядит в SQL — может, туда закралась ошибка?
qs1 = products._clone()
qs1.query.set_limits(3, 4)
print(qs1.query)
qs2 = products._clone()
qs2.query.set_limits(20, 21)
print(qs2.query)
И когда я получил
SELECT "shop_product"."id", ... FROM "shop_product" WHERE ("shop_product"."id" IN (5201, 5230, 5183, 5219, 5217, 5209, 5246, 5252, 5164, 5248)) LIMIT 1 OFFSET 3
и
SELECT "shop_product"."id", ... FROM "shop_product" WHERE ("shop_product"."id" IN (5201, 5230, 5183, 5219, 5217, 5209, 5246, 5252, 5164, 5248)) LIMIT 1 OFFSET 20,
я понял, что ничего не понял. По разному смещению в базе находится одна и та же запись? Но там же constraint на id, дубликатов быть не может…
В общем, когда я выполнил ручками оба запроса прямо в Postgresql и получил одинаковые записи, я начал гуглить по postgres limit offset duplicates и нашёл ответ на stackoverflow. А штука вот какая:
Когда не указан порядок сортировки строк в запросе (ORDER_BY), то Postgres может применять любую сортировку, которая ему по душе — и я, в общем-то, не против, я же так и написал: Product.objects.filter(...), без всяких order_by(). Когда я только писал этот код, пагинации не было, и все товары выводились разом на страницу — тут Postgres сортировал все эти товары произвольно, но зато все сразу.
А потом, когда появилась разбивка на страницы, бд получала команду навроде «отсортируй строчки как тебе удобнее и дай мне строчки с 20 по 40», и вот при разных диапазонах (0-20 или 20-40) сортировка была разная — это зависило от оптимизаций postgres — и получается, что на вывод шли указанные строки из случайного списка.
А вот и цитата с сайта postgres:
The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.
Что ж, будем знать!
products = Product.objects.filter(...).order_by('price')
Да? НЕТ! Проверив всё, я опять обнаружил дубли — но на этот раз я уже попался на то, что order_by использовал параметр, который может быть одинаков — и теперь у всех товаров с одинаковой ценой порядок сортировки опять был неопределён. Так что:
products = Products.objects.filter(...).order_by('price', 'id')
Вот теперь точно всё.
P.S.: Лично для меня это было ещё одним наглядным подтверждением «Закона дырявых абстракций» — ты вроде пишешь ORM простой запрос «дай мне строки 20-40», и вроде даже необязательно знать SQL и Postgres, но в итоге в один прекрасный момент эта абстракция течёт, и вот ты уже изучаешь основы.
P.P.S.: Кстати, если есть желание, можно провернуть такой баг и в админке Django, если не указать ordering для modelAdmin :)
Комментарии (17)
Deepwalker
07.12.2015 22:43+1Плюс за смелость, вроде банальщина, но правда люди попадаются. Особенно именно на этом кейсе — кажется интуитивно, что выведется в том же порядке, в котором айдишники сунули :) Ан нет, хочешь порядок, напиши какой.
Deepwalker
07.12.2015 22:47Ну и для демонстрации, если очень захочется именно в порядке списка вывести, в SQL это будет так:
SELECT * FROM users JOIN (values (1, 7), (2, 9), (3, 8)) as ids(ordr, uid) ON uid = users.id ORDER BY ordr;
baldr
08.12.2015 14:26Очень спасибо за статью. Я считал что по-умолчанию сортируется по первичному ключу и удивился. Решил что в PostgreSQL такая «фича». Пошел смотреть для MySQL и нашел то же самое: http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html.
un1t
08.12.2015 16:20С какой радости по умолчанию запрос должен сортироваться по первичному ключу? И если так, то по вашему в каком направлении должен сортироваться ASC или DESC?
mayorovp
08.12.2015 16:39Когда индексов нет вообще — любой запрос выполняется с помощью сканирования кластерного индекса (так, где есть кластерные индексы). А кластерный индекс — это по умолчанию индекс по первичному ключу.
Отсюда и появляется у начинающих программистов ощущение, что «там по умолчанию сортируется по первичному ключу».
baldr
08.12.2015 16:46Ну век живи — век учись. Теперь буду знать.
А вот направление — по умолчанию ASC для 'order by' если не указано (если есть order by, как теперь понятно).
kesn
11.12.2015 17:06Ну а почему бы и нет? Primary Key, ASC — и никто не в претензии, я думаю :)
Это как C++ — пиши что хочешь, но можешь свалиться в undefined behaviour. А зачем вообще придуман undefined behaviour — чтобы баги появлялись? Почему бы постгресу не выдавать ошибку при непонятном (неуказанном) порядке сортировки? Кому нужны данные в случайно отсортированном виде? Почему это вообще от слайсов зависит? Почему, если в django не указать ordering в админке, будут те же самые баги при пагинации — разработчики шутят?
Вот, собственно, про это и статья.un1t
11.12.2015 17:13+1Например потому, что выдать запись в том порядке как они лежат на диске, гораздо быстре чем в отсортированном виде. Сортировка она не бесплатная, а предполагает дополнительные расходы. Во многих случаях сортировка не важна, а скорость важна. Поэтому такое поведение врядли сделают по умолчанию. И при чем тут джанго, это особенности работы базы.
FuN_ViT
08.12.2015 16:15Это не 100% решение (сортировка по цене).
Описываю кейс:
1. Пользователь просматривает стр № 1
2. В базу добавляется новый товар со стоимостью, которая попадает на 1 стр выдачи.
3. Тот же пользователь идет на стр № 2…
Возникает вероятность, что пользователь не увидит новый товар, а увидит дубль какого то товара со стр № 1.
А теперь представьте, что добавили не один товар, а N. Плюс еще N товарам изменили стоимость…
И это не считая странного id__in. А что, если в списке будет 2 млн. id?
un1t
08.12.2015 16:15Какая жесть. Если не знаешь в какие запросы трансформируются QuerySet, то использовать Django-ORM противопоказано. Я серьезно. Но что меня больше всего удивило, что ты посмотрел какой в итоге получается запрос, но как будто это был не SQL, а диалект китайского. Ладно бы это был какой нибудь навороченный запрос, но тут такой простой «SELECT… WHERE… IN ...».
При чем тут вообще Джанга, все БД, так работают хоть классические MySQL/Postgres, хоть новомодные MongoDB. Если не указываешь сортировку, то порядок не гарантирован.
knagaev
Не примите за критику — любой программист RDBMS должен как «Отче наш» знать, что без ORDER BY нельзя вообще говорить о нумерации записей и обо всём, что с этим связано (интервалы, предыдущая-последующая и т.п.)
kesn
Я — тот самый программист, взращенный ORM, который этого не знал, и притом очень долгое время. Конечно, нужно (и хочется) изучить поглубже и postgres, и memcached, и тонкости настройки nginx, и кучу всего ещё — но всё упирается во время, и изучаешь необходимый минимум. Вот тут-то я и прокололся
knagaev
Тогда да, Вы правы, и о Законе дырявых абстракций абсолютно верно сказали.
Можно сказать только, что в данном вопросе есть за что уважать :)
AEP
Мое мнение: это баг в ORM-ке, что она вообще дала такое написать.
guessss_who
Почему же? Разве ORM должна требовать указывать сортировку всегда и везде?
mayorovp
Могла бы и требовать сортировку при указании лимитов — ведь лимиты без указания порядка сортировки теряют семантику. И если ограничение на количество элементов еще может использоваться в защитных целях — то у OFFSET в отсутствии сортировки смысла нет ни малейшего.