Обновление до mysql 5.7 с 5.5, MyISAM до InnoDB, LEFT JOIN и NULL проблема
Я недавно обновил базу данных с MySQL 5.5 с таблицами MyISAM до MySQL 5.7 таблиц InnoDB. Большинство вещей работают абсолютно нормально, но есть несколько конкретных запросов, которые дают странные результаты.
Предыстория: предложение сначала одобрено, а затем может быть поставлено на голосование. Предложение является видимым только для участника до его утверждения.
Запрос:
SELECT description, suggested_by, voted, votes
FROM shop_suggestions s
LEFT JOIN (SELECT suggestion, 1 AS voted FROM shop_suggestion_votes WHERE student = 60910) AS voted ON (voted.suggestion = s.id)
LEFT JOIN (SELECT suggestion, COUNT(student) AS votes FROM shop_suggestion_votes GROUP BY suggestion) AS votes ON (votes.suggestion = s.id)
WHERE approved > 0 OR suggested_by = 60910 ORDER BY votes
- Первое присоединение
(Слева), чтобы увидеть, проголосовал ли избиратель по этому - Второй
Присоединиться (слева), чтобы увидеть, сколько голосов в общей сложности
Что действительно странно, это когда ORDER BY votes часть есть, проголосовавшее значение всегда равно 1, тогда как если оно пропущено, то оно равно NULL, как и ожидалось, когда кто-то еще не проголосовал.
Это поведение отличается от того, что было до моего обновления, когда запрос работал отлично. Я предполагаю, что где-то есть какая-то логическая ошибка, но я не могу понять это. Любая помощь будет принята с благодарностью!
Решение
Ваш запрос выглядит хорошо для меня. Но вы можете упростить это:
SELECT description, suggested_by, voted, votes
FROM shop_suggestions s LEFT JOIN
(SELECT suggestion, COUNT(student) as votes,
MAX(student = 60910) as voted
FROM shop_suggestion_votes
GROUP BY suggestion
) votes
ON (votes.suggestion = s.id)
WHERE approved > 0 OR suggested_by = 60910
ORDER BY votes;
Если бы мне пришлось размышлять о фактической проблеме, я бы предположил, что вы слишком упростили запрос и удалили проблему, которая обидела. Документировано, что некоторые часто используемые (неправильно) функции MySQL не всегда работают, но они все равно используются. На ум приходит использование столбцов в select которые не в group by, Другой порядок оценки выражений, которые используют переменные. Ваш код не имеет ни одного из них, но, возможно, ваш оригинальный код имеет что-то подозрительное.
Другие решения
Вы нашли ошибку, при которой оптимизатор получает литерал непосредственно перед тем, как left join есть какие-либо результаты. Это производная от известной ошибки,
Ошибка # 77707 Правое внешнее соединение возвращает неверный результат, когда задействовано буквенное выражение ( left join-версия слилась с этим).
Это было предположительно исправлено в 5.7.8,
Неправильные результаты могут быть получены для представлений и производных таблиц на внутренней стороне внешнего соединения, из которых были выбраны ненулевые выражения, такие как литералы. (Ошибка # 73953, Ошибка № 20841369, Ошибка № 67014, Ошибка № 15967464, Ошибка № 65936, Ошибка № 14358878, Ошибка № 67300, Ошибка № 15936817, Ошибка № 76327, Ошибка № 20708288)
Поскольку (новая) файловая сортировка имеет свой собственный оптимизатор, похоже, что ошибка по-прежнему сохраняется и появляется при использовании order by (раньше это происходило с заказом или без него).
Отправить отчет об ошибке.
Чтобы ваш запрос работал, вы можете (кроме переписанного кода Гордона) обмануть mysql в оценке чего-либо (и, таким образом, не оптимизировать литерал), используя, например,
...
LEFT JOIN (SELECT suggestion,
case when student = student then 1 else null end AS voted
FROM shop_suggestion_votes WHERE student = 60910) AS voted
...