LINUX.ORG.RU

Неконсистентность запроса с group by + order by + limit

 , ,


2

2

Столкнулся с необъяснимым поведением mysql 5.7 (разумеется под онтопиком).

В запросах нет хранимок, вьюшек, агрегатных функций и прочих сложностей. Таблица table_name статична, данные в ней не меняются. Запросы 1 и 2 отличаются только циферкой - лимитом, т.е. последим символом запроса.

Насколько я понимаю последовательность выполнения конкретного запроса (group by + order by + limit), mysql должен вычислить limit по уже отфильтрованным и отсортированным строкам. То есть при любом запросе вернет одинаковый результат - единственную строку подходящую под condition. Но практике это не так.

Запрос 1 использует лимит=1, возвращает 0 строк. Хотя запрос должен вернуть одну строку, таков condition и набор данных в таблице.

select
    tn.id
from
    table_name as tn
where
    condition
group by
    tn.id
order by
    tn.id
limit 1

Запрос 2 использует лимит=2, возвращает 1 строку. Именно одну строку этот запрос и должен извлечь, таков condition и набор данных в таблице.

select
    tn.id
from
    table_name as tn
where
    condition
group by
    tn.id
order by
    tn.id
limit 2

индексы есть? дропни попробуй без них/пересоздай

select distinct tn.id ... заместо групбай, аналогично?

drsm ★★
()
Ответ на: комментарий от WitcherGeralt

Покажи настоящий запрос

Настоящий запрос отличается от 1-2 только condition. В этом condition только статичные условия, никаких функций или хранимок. Что-то вроде такого:

tn.ready_to_process='yes'



>ты точно где-то накосячил

Возможно. Хотя запросы отличаются (и в этом нет сомнений) только лимитом.
outtaspace ★★★
() автор топика

limit - это костыль, не надо его использовать, особенно во всяких group by. Попробуй без группировки сделать для начала.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от crutch_master

limit - это костыль

Я могу этот лимит применить на стороне получивший данные запроса. Но лучше таки в самом запросе указать, чтобы не тащить из базы лишние данные. В моем случае лимит - требование задачи.

не надо его использовать, особенно во всяких group by.

Вот и хочу услышать обоснованное мнение, почему не надо и как это работает на самом деле.

outtaspace ★★★
() автор топика
Ответ на: комментарий от outtaspace

Я могу этот лимит применить на стороне получивший данные запроса. Но лучше таки в самом запросе указать, чтобы не тащить из базы лишние данные. В моем случае лимит - требование задачи.

Limit - mysql специфичная хрень, поведение которого не обозначено в стандартах.

Вот и хочу услышать обоснованное мнение, почему не надо

Потому что в sql и множествах нет понятия порядка в самой выборке. То есть, если ты делаешь select * from table он тебе не гарантирует порядок выдачи. Таким образом select * from table limit 2,4 на одном и том же запросе не обязан выдавать тебе одни и те же данные. Тут должна быть обязательная сортировка, но в mysql на это всем насрать, как и на поля с group by без функций, значения которых могут быть произвольными.

и как это работает на самом деле.

Как разрабы mysql считают нужным, так он и работает. Попробуй без группировки сделать выборку. Если всё нормально сделай с подзапросом.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от outtaspace

В моем случае лимит - требование задачи.

Вообще, в нормальных субд никто не бежит выковыривать данные из таблиц, сразу после получения select. Они выдаются после обработки sql по мере надобности клиента, поэтому limit там не нужен. Раскури вопрос, может в mysql они тоже по-нормальному это всё переделали.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от crutch_master

Потому что в sql и множествах нет понятия порядка в самой выборке.

wtf, order by? implicit порядка нет, да.

лимит без ордер бай выбирает случайные строки.

drsm ★★
()
Ответ на: комментарий от outtaspace

Суть его от этого не меняется. Простой запрос с лимитом принципиально не обязан выдавать тебе одно и тоже. Если, например, сделать запрос, потом таблицу почистить и залить записи обратно в произвольном порядке.

crutch_master ★★★★★
()
Ответ на: комментарий от drsm

wtf, order by

Order by сортирует **выборку**. В самой выборке никакого порядка нет. Select * from table выдаёт тебе что хочет. Если ты там видишь какой-то порядок, то это просто частный случай, опираться на это нельзя, поэтому limit может существовать только как часть order by. То, что он существует отдельно говорит только о том, что разработчики этих субд хер клали на основные принципы множеств.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 2)

Насколько я понимаю последовательность выполнения конкретного запроса (group by + order by + limit), mysql должен вычислить limit по уже отфильтрованным и отсортированным строкам.

нет.

order by - порядок начальной выборки

limit - ее макс размер.

потом идет группировка.

drsm ★★
()
Ответ на: комментарий от crutch_master

где тут хер, я хз. limit n без order by - это выбрать n любых (из множества на котором не задан порядок), но стиль плохой согласен.

drsm ★★
()

Вот этот запрос должен вернуть одну строку, где id=1 и value=33. Он возвращает пустой результат.

select
	tn.*
from
	(
		select 1 as id, '11' as value
		union all
		select 2 as id, '22' as value
		union all
		select 1 as id, '33' as value
		union all
		select 2 as id, '44' as value
	) as tn
where
	tn.value='33'
group by
	tn.id
order by
	tn.id
limit 1

Этот запрос должен вернуть одну строку, где id=1 и value=33. Он возвращает корректный результат.

select
	tn.*
from
	(
		select 1 as id, '11' as value
		union all
		select 2 as id, '22' as value
		union all
		select 1 as id, '33' as value
		union all
		select 2 as id, '44' as value
	) as tn
where
	tn.value='33'
group by
	tn.id
order by
	tn.id
limit 2

Как эта магия работает не знаю. Воспроизводится только на одной машине из трех, причем не всегда, т.е. это что-то «плавающее». Пока вижу только одну закономерность - при лимите 1 _иногда_ воспроизводится, при лимите 2 _всегда_ не воспроизводится.

outtaspace ★★★
() автор топика
Ответ на: комментарий от crutch_master

А, вот ты о чём.

Это абсолютно очевидное поведение, у кого вообще возникнет мысль юзать лимит без сортировки? Но и без сортировки, ЕМНИП MySQL выдаёт данные в порядке их поступления.

WitcherGeralt ★★
()
Ответ на: комментарий от WitcherGeralt

Что-то загоняешь, лимит как надо работает, по крайней мере в 5.5 так было.

У меня 5.7.18 и все как надо _работало_, но настал тот день, когда я сильно удивился. Сейчас собираю статистику, чтобы после ребута серверов снова собрать статистику.

outtaspace ★★★
() автор топика
Ответ на: комментарий от WitcherGeralt

Но и без сортировки, ЕМНИП MySQL выдаёт данные в порядке их поступления.

Ну да, выдаёт. Но он не обязан их так выдавать. Например, если там приделают кластер, перезалив или еще что-то.

Это абсолютно очевидное поведение, у кого вообще возникнет мысль юзать лимит без сортировки?

У любого нуба, который прочитает man и задумает использовать таблицу как стак.

crutch_master ★★★★★
()
Ответ на: комментарий от crutch_master

То ли склад ума, то ли везло, но я нубских граблей, подобных этим, никогда не проходил. Очевидные вещи очевидны. А если не очевидны, то нужно проверять гипотезы.

WitcherGeralt ★★
()

explain extended уже говорили?

SevikL ★★★★★
()
Ответ на: комментарий от crutch_master

Вообще limit вроде есть в стандартах, но работает он там странно, я уже напарывался. Автор темы, почитай в стандарте, как именно он работает.

den73 ★★★★★
()
Ответ на: комментарий от den73

Вообще limit вроде есть в стандартах

Нее. В sql-92 ничего такого не было, а всё, что новее - это какая-то ересь. Википедия говорит, что они даже не общедоступны. Ну и он не может быть никак сам по себе, если бы даже кто-то захотел limit в стандарте, то выборки не могут быть рандомными.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от crutch_master

Ты бы помолчал, чайник.

LIMIT/OFFSET никак не связаны с порядком строк. У тебя в головушке всё перемешалось.

Стандарт не гарантирует порядка без ORDER BY, но стандарт гарантирует стабильность _набора_ строк, и их кол-во. Что уже делает возможным с пользой (и гарантированным результатам) применять к этим строкам LIMIT/OFFSET.

LIMIT это вовсе не костыть, а полноценная конструкция. В других базах данных LIMIT и OFFSET наличествует точно не из-за «совместимости с mysql», а из-за того, что это полезная и _необходимая_ вещь. Мало какие разработчики БД выбирут MySQL как модель для совместимости. Расширения стандарта добавляют потому что они полезные или даже остро необходимые, и LIMIT/OFFSET - как раз тот случай.

anonymous
()

Автору. Чтобы проверить и подумать ты можешь обернуть запрос без LIMIT в другой запрос (т.е. появится подзапрос), а LIMIT нацепить на внешний контур.

anonymous
()
Ответ на: комментарий от anonymous

но стандарт гарантирует стабильность _набора_ строк, и их кол-во.

Вот у тебя набор [1,2,3]. Надо выбрать первые 2 записи (или последние 2, не важно). Набор один и тот же, количество одно и тоже, но порядок не гарантирован. Количество возможный вариантов посчитай сам.

LIMIT это вовсе не костыть, а полноценная конструкция.

Без order by - это даже не костыль, а явный косяк.

В других базах данных LIMIT и OFFSET наличествует точно не из-за «совместимости с mysql»,

Другие базы просто умеют выдавать клиенту result set кусками.

а из-за того, что это полезная и _необходимая_ вещь.

У этой «полезной и необходимой» вещи есть ровно одно применение - странички на вебмордах для крупных поисковых выборок.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)

Попробуй перезагрузить мускул, похоже на баг. И не слушай тех, кто говорит, что limit это костыль)

goingUp ★★★★★
()
Ответ на: комментарий от anonymous

Автору. Чтобы проверить и подумать ты можешь обернуть запрос без LIMIT в другой запрос (т.е. появится подзапрос), а LIMIT нацепить на внешний контур.

Спасибо. Это первая мысль которая вчера была, проверил этот workaround - не помогло.

outtaspace ★★★
() автор топика
Ответ на: комментарий от goingUp

Попробуй перезагрузить мускул, похоже на баг.

Уже. Не помогло. Сейчас апдейтится сам сервер, посмотрю что получится.

И не слушай тех, кто говорит, что limit это костыль)

Не слушаю.

outtaspace ★★★
() автор топика
Ответ на: комментарий от crutch_master

есть ровно одно применение - странички на вебмордах для крупных поисковых выборок

LIMIT часто используется (и полезен) в глубине запросов. Он может быть во view, в cte, в подзапросе, в хранимой процедуре. Т.е. где до отдачи клиенту еще очень далеко. Это точно не «ровно одно применение - странички на вебмордах». Ты просто почти не писал SQL, а «мнение имеешь».

Другие базы просто умеют выдавать клиенту result set кусками.

Предыдущие куски могут быть клиенту просто _не нужны_ и тут limit offset отлично работает. И это не не только и не столько веб-пагинация которую ты упомянул, реальность гораздо богаче чем твои представления.

Набор один и тот же, количество одно и тоже, но порядок не гарантирован. Количество возможный вариантов посчитай сам.

Так что тебе мешает добавить order by?

Порядок можно намеренно сделать случайным и непредсказумым, и даже в этом случае LIMIT окажется полезен: order by rand() limit 5.

Limit - mysql специфичная хрень

Хорошо, по-твоему какова была мотивация разработчиков других БД, реализовавших такой же LIMIT/OFFSET?

anonymous
()
Ответ на: комментарий от crutch_master

Я вынужден извиниться перед тобой.

Я сейчас читаю про MySQL (я почти не работал с MySQL). Конкретно в MySQL LIMIT использовать вообще нельзя. Тем более с ORDER BY.

В MySQL LIMIT + ORDER BY возвращают вообще не то, что я думаю. Оно даже для пагинации с ORDER BY некорректные результаты вернет.

anonymous
()
Ответ на: комментарий от anonymous

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

Это п-дец.

anonymous
()
Ответ на: комментарий от anonymous

Насколько я помню, filesort на языке MySQL это когда нет индекса т.е. нужно сортировать. Это просто назавание функции в коде, с файлами не связано.

anonymous
()
Ответ на: комментарий от anonymous

After the initial rows have been found, MySQL does not sort any remainder of the result set.

а в чем проблема то?

drsm ★★
()
Ответ на: комментарий от outtaspace

тут весь цирк походу из-за мускульной group by идиотии.

In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

кмк, так писать это вообще дно, лимит без сортировки фигня на фоне )))

drsm ★★
()
Последнее исправление: drsm (всего исправлений: 1)
Ответ на: комментарий от anonymous

Это точно не «ровно одно применение - странички на вебмордах». Ты просто почти не писал SQL, а «мнение имеешь».

Sql и рсубд это всё таки про множества. А все эти limit, курсоры, хранимки и приделывание древовидных структур - классическое натягивание совы на глобус и «оно тут не всралось, но если хочется, то можно». Когда ты юзаешь limit/offset ты выбираешь всё. Когда ты двигаешь offset и посылаешь второй запрос - ты дрочишь кэш, потому что нет принципиальной возможности вытащить из базы строго то, что тебе надо. Оно кривое от рождения. Хранимки - это изобретение велосипеда и позиция как у оракла - наша субд годная для всего и сразу, а больше ничего не надо. Все проблемы, которые сознаются логикой на стороне субд проще и лучше решать на чём угодно другом вне её.

Хорошо, по-твоему какова была мотивация разработчиков других

Рыночек. Все ровняются на ракл.

crutch_master ★★★★★
()
Ответ на: комментарий от outtaspace

Не слушаю.

Не слушай. Потом будешь всё переписывать, чтобы просто работало за конечное время. За кем-то.

crutch_master ★★★★★
()
Ответ на: комментарий от drsm

Я вообще зарёкся делать на рсубд что-то сложнее select+join. Можете считать неосилятором сколько хотите, но я вертел эти sql портянки с подзапросами, хранимками и всякими финтами от «про».

crutch_master ★★★★★
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.