Select и group by ошибки

“Что случилось с моим приложением? Я установил новую версию MySQL. Запросы, что выполнялись на старой версии теперь падают с кучей ошибок.”

Многие программисты сталкиваются с этим вопросом при обновлении до версий 5.7 или 8.
В этой статье мы рассмотрим один из самых частых кейсов и его решение.

Мы говорим об этой ошибке

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'test.web_log.user_id' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible 
with sql_mode=only_full_group_by

Видели ли вы когда-либо её?

SQL_MODE

Для начала разрешите мне представить концепцию SQL_MODE.

MySQL может работать используя разные SQL режимы, которые влияют
на синтаксис запросов и валидацию.
В зависимости от установленного значения переменной sql_mode
запрос может быть валидным и выполняться или может получить
ошибку валидации и не может быть выполнен.

Старейшие версии MySQL научили пользователей писать запросы, которые
семантически корректны, потому что разработаны для работы в “прощающем режиме”.
Пользователи могли писать любой синтаксически правильный запрос независимо от
соответствия SQL стандарту или сематических правил.

Это была плохая привычка, которая была исправлена введением sql_mode, чтобы настроить MySQL
работать более строгим способом для проверки запросов.

Некоторые пользователи не знают об этой функции, потому что значение по умолчанию не было таким строгим. Начиная с версии 5.7, значение по умолчанию является более строгим, и по этой причине у некоторых пользователей возникают проблемы с неожиданными ошибками запросов после перехода на 5.7 или 8.0.

Переменная sql_mode может быть установлена в файле конфигурации (/etc/my.cnf) или
может быть изменена во время выполнения.
Область действия переменной может быть GLOBAL или SESSION, поэтому может измениться
в соответствии с целью для любого отдельного соединения.

Переменная sql_mode может иметь несколько значений, разделённых запятой, для настройки различных поведений.
Например, вы можете проинструктировать MySQL как обращаться с датами с нулями, как 0000-00-00,
чтобы дата считалась действительной или нет.

В “прощающем режиме” (или если переменная sql_mode пуста), вы можете вставить такое значение без проблем.

# установка sql в "прощающий режим" 
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1( mydate date );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values('0000-00-00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------+
| mydate     |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

Но это не правильное поведение, как заявлено в режиме TRADITIONAL.
Как хорошие программисты знают, что нужно проверять даты
в коде приложения, чтобы избежать некорректных данных или некорректных результатов.

Далее мы показываем, как динамически проинструктировать MySQL вести себя в traditional режиме,
чтобы выбросить исключений вместо замалчивания ошибки:

mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'mydate' at row 1

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

Проблема ONLY_FULL_GROUP_BY

Давайте сосредоточимся на самом частом кейсе ошибок миграции с 5.7 на 8.0.
Как уже было сказано, в 5.7 более строгий режим, чем в 5.6, в 8.0 более строгий, чем в 5.7.

Это работает, если вы обновляете MySQL, копируя старый файл my.cnf,
который не имеет специфичных настроек для переменной sql_mode. Итак, имейте в виду.

Давайте создадим простую таблицу для хранения кликов на вебстраницах нашего сайта.
Мы будем записывать название страницы и ID зарегистрированного пользователя.

mysql> create table web_log ( id int auto_increment primary key, page_url varchar(100), user_id int, ts timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into web_log(page_url,user_id,ts) values('/index.html',1,'2019-04-17 12:21:32'),
    -> ('/index.html',2,'2019-04-17 12:21:35'),('/news.php',1,'2019-04-17 12:22:11'),('/store_offers.php',3,'2019-04-17 12:22:41'),
    -> ('/store_offers.php',2,'2019-04-17 12:23:04'),('/faq.html',1,'2019-04-17 12:23:22'),('/index.html',3,'2019-04-17 12:32:25'),
    -> ('/news.php',2,'2019-04-17 12:32:38');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from web_log;
+----+--------------------+---------+---------------------+
| id | page_url           | user_id | ts                  |
+----+--------------------+---------+---------------------+
|  1 | /index.html        |       1 | 2019-04-17 12:21:32 |
|  2 | /index.html        |       2 | 2019-04-17 12:21:35 |
|  3 | /news.php          |       1 | 2019-04-17 12:22:11 |
|  4 | /store_offers.php  |       3 | 2019-04-17 12:22:41 |
|  5 | /store_offers.html |       2 | 2019-04-17 12:23:04 |
|  6 | /faq.html          |       1 | 2019-04-17 12:23:22 |
|  7 | /index.html        |       3 | 2019-04-17 12:32:25 |
|  8 | /news.php          |       2 | 2019-04-17 12:32:38 |
+----+--------------------+---------+---------------------+

Теперь мы хотим написать запрос для подсчёта наиболее посещаемых страниц сайта

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT page_url, user_id, COUNT(*) AS visits
    -> FROM web_log
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+---------+--------+
| page_url          | user_id | visits |
+-------------------+---------+--------+
| /index.html       |       1 |      3 |
| /news.php         |       1 |      2 |
| /store_offers.php |       3 |      2 |
| /faq.html         |       1 |      1 |
+-------------------+---------+--------+
4 rows in set (0.00 sec)

Этот запрос работает, но на самом деле не корректен.
Легко понять, что page_url — столбик для группировки, значение, которое нас больше всего интересует
и мы хотим, чтобы оно было уникальным для подсчёта.
Также столбик visits понятен, это счётчик. Но как насчёт user_id?
Что представляет эта колонка?
Мы сгруппировали по page_url, поэтому значение, возвращаемое для user_id — только одно из значений в группе.
Фактически не только пользователь номер 1 посетил index.html, но также пользователи 2 и 3 посетили эту страницу.
Как нам интерпретировать значение? Это первый посетитель? Или последний?

Мы не знаем правильного ответа!
Мы должны рассматривать значение колонки user_id как случайный элемент из группы.

В любом случае, правильный ответ — запрос семантически некорректен,
так как нет смысла для возвращаемого значения столбика, что не является частью функции группировки.
Запрос будет недействительным в традиционном SQL.

Давайте проверим это

mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT page_url, user_id, COUNT(*) AS visits 
    -> FROM web_log 
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'test.web_log.user_id' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Как и ожидалось, у нас ошибка.

Режим SQL ONLY_FULL_GROUP_BY — это часть TRADITIONAL режима и включен по умолчанию
начиная с 5.7.

Множество программистов столкнулось с разновидностью этой ошибки после миграции на последнюю версию
MySQL.

Теперь мы знаем, что вызывает эту ошибку, но наше приложение всё ещё не работает.
Какие возможные решения у нас есть, чтобы вернуть приложение к работе?

Решение 1 — переписать запрос

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

mysql> SELECT page_url, COUNT(*) AS visits
    -> FROM web_log
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------+
| page_url          | visits |
+-------------------+--------+
| /index.html       |      3 |
| /news.php         |      2 |
| /store_offers.php |      2 |
| /faq.html         |      1 |
+-------------------+--------+

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

Но это решение заставляет вас писать правильные запросы и пусть конфигурация вашей базы данных
проверяет на такие ошибки в терминах SQL-валидации.

Решение 2 — вернуть “прощающий режим”

Вы можете поменять конфигурацию подключения или MySQL сервера и вернуть “прощающий” режим.

Или вы можете убрать только ONLY_FULL_GROUP_BY из настроек по умолчанию.
По умолчанию SQL режим в 5.7 включает режимы: ONLY_FULL_GROUP_BY, STRINCT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER.

#set the complete "forgiving" mode
mysql> SET GLOBAL sql_mode='';

# alternatively you can set sql mode to the following
mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';

Для yii2-приложения конфиг может выглядеть так:

<?php

return [
    'class' => 'yiidbConnection',
    'dsn' => 'mysql:host=' . getenv('MYSQL_HOST') . ';port=' . getenv('MYSQL_PORT') . ';dbname=' . getenv('MYSQL_DB'),
    'username' => getenv('MYSQL_USER'),
    'password' => getenv('MYSQL_PASSWORD'),
    'charset' => 'utf8',
    'attributes' => [
        PDO::ATTR_PERSISTENT => true,
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"'
    ],
];

Решение 3 — использование агрегирующих функций

Если ваше приложение точно нуждается в получении поля user_id для какой-то корректной причины,
или слишком сложно менять исходный код (например, для поддержки обратной совместимости с внешними приложениями),
вы можете положиться на агрегирующие функции, чтобы избежать изменения SQL-режима.
Тогда для всех новых запросов проверка уже будет выполняться.

Например мы можем использовать агрегирующие функции MAX(), MIN() или даже GROUP_CONCAT().

mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';

mysql> SELECT page_url, MAX(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------+--------+
| page_url          | MAX(user_id) | visits |
+-------------------+--------------+--------+
| /index.html       |            3 |      3 |
| /news.php         |            2 |      2 |
| /store_offers.php |            3 |      2 |
| /faq.html         |            1 |      1 |
+-------------------+--------------+--------+

mysql> SELECT page_url, GROUP_CONCAT(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+-----------------------+--------+
| page_url          | GROUP_CONCAT(user_id) | visits |
+-------------------+-----------------------+--------+
| /index.html       |                 1,2,3 |      3 |
| /news.php         |                   1,2 |      2 |
| /store_offers.php |                   3,2 |      2 |
| /faq.html         |                     1 |      1 |
+-------------------+-----------------------+--------+

MySQL даже предоставляет специальную функцию для решения этой проблемы: ANY_VALUE().

mysql> SELECT page_url, ANY_VALUE(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------------+--------+
| page_url          | ANY_VALUE(user_id) | visits |
+-------------------+--------------------+--------+
| /index.html       |                  1 |      3 |
| /news.php         |                  1 |      2 |
| /store_offers.php |                  3 |      2 |
| /faq.html         |                  1 |      1 |
+-------------------+--------------------+--------+

Заключение

Лично я предпочитаю решение номер 1, так как оно заставляет вас писать запросы по стандарту SQL-92.
Следование стандартам часто считается лучшей практикой. Также хочу заметить, что это ловит часть ошибок,
аналогично статическому анализу кода.

Решение 2 подходит, если вы не можете поменять код приложения или переписывание всех запросов
действительно очень сложное. Отличное решение исправить проблему за несколько секунд, хотя я настоятельно рекомендую иметь план по переписыванию запросов, которые соответствуют стандарту SQL-92.

Больше деталей: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

По мотивам
https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/

SELECT * FROM `таблица` GROUP BY user_id ORDER BY `time` DESC;

или

SELECT `user_id`, `post`, max(`time`) FROM `таблица` GROUP BY user_id;

Оба этих запроса неверные. В первом для каждого user_id будет выбрана произвольная строка с данным user_id после чего эти строки будут отсортированы по времени. Во втором, для каждого user_id будет выбрано max(`time`) и значение поля `post` из случайной строки с данным user_id, а не из той, которая соответствует max(`time`). А если результат правильный, то это не более чем случайность.

Проиллюстрируем ситуацию на простом примере.

Как видим значение поля `post` выбрано «неверно». В кавычках потому что неверно с человеческой точки зрения — мы ожидали увидеть значение, соответствующее максимальному времени, т.е. ‘post 3’. А сервер MySQL взял значение поля post из первой попавшейся строки с user_id =1, так как никаких указаний относительно этого поля ему не поступало.

Правильным решением данной задачи будет найти сначала комбинации (`user_id`,`time`), а по ним уже выбрать недостающее поле `post`. Для простоты предположим, что у пользователя не может быть двух одновременных сообщений, т.е. комбинация (`user_id`,`time`) однозначно идентифицирует строку. Тогда нужный нам запрос будет выглядеть так:

MariaDB [test]> SELECT t.`user_id`, t.`post`, t.`time` FROM `test_table` t JOIN
(SELECT `user_id`, max(`time`) as `mtime` FROM `test_table` GROUP BY `user_id`)
as t1 ON t.`user_id`= t1.`user_id` AND t.`time` = t1.`mtime`;
+———+———+———————+
| user_id | post   | time                |
+———+———+———————+
|       1 | post 3 | 2012-11-15 16:51:26 |
+———+———+———————+

При определенных условиях существует способ решить задачу без использования JOIN. Пусть поле `time` имеет тип данных DATETIME, т.е. принимает значения вида ‘2012-02-15 01:47:19’. Тогда мы знаем, что значение этого поля всегда будет иметь ширину в 19 символов. В этом случае мы можем искать максимум не от поля `time`, а от строки, склеивающей значения полей `time` и `post`, а потом результат разбить назад на составляющие.

SELECT `user_id`, SUBSTR(MAX(CONCAT(`time`, `post`)), 1, 19) as `time`, SUBSTR(MAX(CONCAT(`time`, `post`)), 20) as `post` FROM `таблица` GROUP BY `user_id`;

Такой метод может быть необходим в случае, если группирующая функция берется не от значения поля, а от результата каких-то вычислений над полем. Тогда первый метод через JOIN не применим, так как после выполнения группировки у нас будет значение `user_id` и max( от каких-то вычислений с полем `time` ), а не значение самого поля `time`. Т.е. обратится вновь к таблице мы сможем только по полю `user_id`, а это не позволит нам идентифицировать нужную строку.
Пример подобной задачи разобран в теме http://sqlinfo.ru/forum/viewtopic.php?id=6102

Третий способ решения — пользовательские переменные. Пример подобного решения см в конце темы http://sqlinfo.ru/forum/viewtopic.php?id=3839

Немного усложним запрос про получение количества товаров в категории из предыдущего задания. Исходный запрос:

SELECT category_id,
       count(*) as count_products
  FROM product
 GROUP BY category_id

Идентификаторы малоинформативны и обычно никому не интересны. Давай в дополнение к идентификатору категории добавим еще название. Для этого нужно присоединить таблицу category:

SELECT p.category_id,
       c.name,
       count(*) AS count_products
  FROM product p
  JOIN category c
    ON c.category_id = p.category_id
 GROUP BY p.category_id
 ORDER BY p.category_id

Но в результате получим ошибку:

column "c.name" must appear in the GROUP BY clause or be used in an aggregate function

Ошибка возникла потому, что мы в списке выборки указали c.name, но не добавили это поле в GROUP BY. Исправим это:

SELECT p.category_id,
       c.name,
       count(*) AS count_products
  FROM product p
  JOIN category c
    ON c.category_id = p.category_id
 GROUP BY p.category_id, c.name
 ORDER BY p.category_id

P.S. Ошибка бы не возникла, если вместо

GROUP BY p.category_id

написать

GROUP BY c.category_id

СУБД PostgreSQL поймет, что c.category_id является первичным ключом в таблице category и по c.category_id можно однозначно определить c.name. Но так лучше не делать, т.к. в других версиях PostgreSQL это может не работать, как это не работает в других СУБД, например в Oracle.

Если выражение используется в списке выборки, то его нужно включить в GROUP BY, либо вызвать агрегатную функцию от этого выражения. Обратное не требуется: использованное в GROUP BY выражение не обязательно должно присутствовать в списке выборки. В нашем запросе category_id можно было не включать в список выборки SELECT, но в GROUP BY его стоит оставить (название категории может повторяться в рамках таблицы category, и если в GROUP BY оставить только c.name, то останется только одна строка с таким названием, а количество товаров будет равно количеству товаров во всех категориях с таким названием).