Ошибка sql 1055

“Что случилось с моим приложением? Я установил новую версию 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' => 'yii\db\Connection',
    '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/

If you’ve been using MySQL for any decent amount of time, it’s likely you’ll be familiar with error 1055 that reads something like “Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column…“, where #N is the expression number of an expression/column in your SELECT list.

This error can occur when we include a column in the SELECT list, but we omit it from the GROUP BY clause.

There are several ways we can go about resolving this issue. Below are six options for dealing with this issue.

Example of Error

First, here’s an example of code that produces the error:

SELECT 
    District, 
    Name, 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

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

Here, #2 means that the second expression in our SELECT list is causing the problem. In our case, it’s the Name column. We included the Name column in our SELECT list but not the GROUP BY clause.

This is a common error which occurs when our SELECT list includes a nonaggregated column that is neither named among GROUP BY columns nor functionally dependent on them.

There are several ways of dealing with this. The method we choose will depend on our desired result.

Solution 1

The most obvious way to deal with this error is to simply remove the offending column:

SELECT 
    District, 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-----------------+
| District        | SUM(Population) |
+-----------------+-----------------+
| New South Wales |         3993949 |
| Victoria        |         2990711 |
| Queensland      |         1805236 |
| West Australia  |         1096829 |
| South Australia |          978100 |
| Capital Region  |          322723 |
| Tasmania        |          126118 |
+-----------------+-----------------+

However, this will only be suitable if we aren’t concerned with returning that column.

If we do want that column to be included in the result, we can look at the solutions below.

Solution 2

Another way to deal with this issue is to include the Name column in the GROUP BY clause:

SELECT 
    District, 
    Name, 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District, Name;

Result:

+-----------------+---------------+-----------------+
| District        | Name          | SUM(Population) |
+-----------------+---------------+-----------------+
| New South Wales | Sydney        |         3276207 |
| Victoria        | Melbourne     |         2865329 |
| Queensland      | Brisbane      |         1291117 |
| West Australia  | Perth         |         1096829 |
| South Australia | Adelaide      |          978100 |
| Capital Region  | Canberra      |          322723 |
| Queensland      | Gold Coast    |          311932 |
| New South Wales | Newcastle     |          270324 |
| New South Wales | Central Coast |          227657 |
| New South Wales | Wollongong    |          219761 |
| Tasmania        | Hobart        |          126118 |
| Victoria        | Geelong       |          125382 |
| Queensland      | Townsville    |          109914 |
| Queensland      | Cairns        |           92273 |
+-----------------+---------------+-----------------+

However, this may or may not return the results we want. If this isn’t our desired result, we can try one of the other solutions below.

In my case, it’s not the outcome I was looking for. In fact, it defeated the purpose of grouping by district (I wanted to see the total population of each district, but it’s only displaying the population of each city).

Solution 3

Another way to deal with this error is with the ANY_VALUE() function.

SELECT 
    District, 
    ANY_VALUE(Name), 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-----------------+-----------------+
| District        | ANY_VALUE(Name) | SUM(Population) |
+-----------------+-----------------+-----------------+
| New South Wales | Sydney          |         3993949 |
| Victoria        | Melbourne       |         2990711 |
| Queensland      | Brisbane        |         1805236 |
| West Australia  | Perth           |         1096829 |
| South Australia | Adelaide        |          978100 |
| Capital Region  | Canberra        |          322723 |
| Tasmania        | Hobart          |          126118 |
+-----------------+-----------------+-----------------+

The ANY_VALUE() function picks an arbitrary value from the specified column. In this case, it picked an arbitrary value from the Name column, and we got Sydney in the first row, Melbourne in the second, and so on. As we can see from the previous example, that column contains more than just those values for their respective districts, but MySQL has simply chosen one value to present.

This method is good if we’re not worried about which value is returned for that column. For example, if we just want a sample value in that column, but we don’t want to see all values.

However, if we want to see all values, we could try the next solution.

Solution 4

Another option is to use the GROUP_CONCAT() function:

SELECT 
    District, 
    GROUP_CONCAT(Name), 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-------------------------------------------+-----------------+
| District        | GROUP_CONCAT(Name)                        | SUM(Population) |
+-----------------+-------------------------------------------+-----------------+
| Capital Region  | Canberra                                  |          322723 |
| New South Wales | Sydney,Newcastle,Central Coast,Wollongong |         3993949 |
| Queensland      | Brisbane,Gold Coast,Townsville,Cairns     |         1805236 |
| South Australia | Adelaide                                  |          978100 |
| Tasmania        | Hobart                                    |          126118 |
| Victoria        | Melbourne,Geelong                         |         2990711 |
| West Australia  | Perth                                     |         1096829 |
+-----------------+-------------------------------------------+-----------------+

This option is similar to the ANY_VALUE() solution, except that GROUP_CONCAT() returns all values in a comma-separated list.

The good thing about this is that we don’t need to output a separate row for each city. Each city is output in the same column. So we can retain the grouping by District that we intended, while also getting all data from the Name column.

Solution 5

There are other aggregate functions that we could use to get rid of the error, depending on what data we want returned. For example, we could use the MIN() or MAX() function instead of the ANY_VALUE() function:

SELECT 
    District, 
    MIN(Name), 
    MAX(Name), 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+---------------+------------+-----------------+
| District        | MIN(Name)     | MAX(Name)  | SUM(Population) |
+-----------------+---------------+------------+-----------------+
| New South Wales | Central Coast | Wollongong |         3993949 |
| Victoria        | Geelong       | Melbourne  |         2990711 |
| Queensland      | Brisbane      | Townsville |         1805236 |
| West Australia  | Perth         | Perth      |         1096829 |
| South Australia | Adelaide      | Adelaide   |          978100 |
| Capital Region  | Canberra      | Canberra   |          322723 |
| Tasmania        | Hobart        | Hobart     |          126118 |
+-----------------+---------------+------------+-----------------+

This can be handy to give us an idea of the range of values in the column without returning all values.

Solution 6

We only get the error when our sql_mode contains ONLY_FULL_GROUP_BY.

We can check our sql_mode like this:

SELECT @@sql_mode;

Result:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

As expected, ONLY_FULL_GROUP_BY is enabled, which is why I got the error.

We can disable ONLY_FULL_GROUP_BY from our session’s sql_mode like this:

SET @@sql_mode = SYS.LIST_DROP(@@sql_mode, 'ONLY_FULL_GROUP_BY');
SELECT @@sql_mode;

Result:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

By using the SYS.LIST_DROP system function, we removed the item from the list without affecting any other items. We can see that ONLY_FULL_GROUP_BY is no longer in our sql_mode.

Let’s now run the original query that caused the error:

SELECT 
    District, 
    Name, 
    SUM(Population) FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-----------+-----------------+
| District        | Name      | SUM(Population) |
+-----------------+-----------+-----------------+
| New South Wales | Sydney    |         3993949 |
| Victoria        | Melbourne |         2990711 |
| Queensland      | Brisbane  |         1805236 |
| West Australia  | Perth     |         1096829 |
| South Australia | Adelaide  |          978100 |
| Capital Region  | Canberra  |          322723 |
| Tasmania        | Hobart    |          126118 |
+-----------------+-----------+-----------------+

This time the query runs without error. Our results resemble those we got when we used the ANY_VALUE() function.

Conclusion

MySQL error 1055 is a common error that occurs when using the GROUP BY clause. It occurs when we include a column in our SELECT list that’s not also included in the GROUP BY clause.

However, there are many simple ways of dealing with this error. The solution we chose will depend on the actual results that we want to see from the query.

Introduction

Recently, new versions of MySQL servers have begun to generate 1055 errors for queries that used to work. This topic explains those errors. The MySQL team has been working to retire the nonstandard extension to GROUP BY, or at least to make it harder for query writing developers to be burned by it.

SET  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Вступление

Недавно новые версии серверов MySQL начали генерировать 1055 ошибок для запросов, которые раньше работали. В этом разделе объясняются эти ошибки. Команда MySQL работает над отказом от нестандартного расширения до GROUP BY или, по крайней мере, для того, чтобы затруднить его работу с разработчиками запросов.

замечания

SET  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

 SELECT item.item_id, item.name,     /* not SQL-92 */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

покажет строки в таблице, называемой item , и покажет количество связанных строк в таблице с именем uses . Это хорошо работает, но, к сожалению, это не стандартный SQL-92.

Почему бы и нет? потому что предложение SELECT (и предложение ORDER BY ) в запросах GROUP BY должно содержать столбцы, которые

1. указанных в предложении GROUP BY , или
2. совокупные функции, такие как COUNT() , MIN() и тому подобное.

В предложении SELECT этого примера упоминается item.name , столбец, который не соответствует ни одному из этих критериев. MySQL 5.6 и ранее отклонят этот запрос, если режим SQL содержит ONLY_FULL_GROUP_BY .

Этот примерный запрос может быть выполнен в соответствии со стандартом SQL-92, изменив предложение GROUP BY , как это.

SELECT item.item_id, item.name, 
       COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id, item.name

Более поздний стандарт SQL-99 позволяет SELECT пропускать неагрегированные столбцы из группового ключа, если СУБД может доказать функциональную зависимость между ними и столбцами группового ключа. Поскольку item.name функционально зависит от item.item_id , исходный пример действителен SQL-99. MySQL получил версию функциональной зависимости в версии 5.7. Исходный пример работает под ONLY_FULL_GROUP_BY .

Неправильная GROUP BY возвращает непредсказуемые результаты: закон Мерфи

SELECT item.item_id, uses.category,   /* nonstandard */ 
       COUNT(*) number_of_uses 
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

покажет строки в таблице, называемой item, и покажет количество связанных строк в таблице с именем uses. Он также покажет значение столбца, называемого uses.category .

Этот запрос работает в MySQL (до ONLY_FULL_GROUP_BY флажка ONLY_FULL_GROUP_BY ). Он использует нестандартное расширение MySQL для GROUP BY .

Но у запроса есть проблема: если несколько строк в таблице uses соответствуют условию ON в предложении JOIN , MySQL возвращает столбец category только из одной из этих строк. Какая строка? Писатель запроса и пользователь приложения не знают об этом заранее. Формально говоря, это непредсказуемо : MySQL может вернуть любое значение, которое он хочет.

Непредсказуемый случайный, с одним существенным различием. Можно ожидать случайного выбора время от времени. Поэтому, если выбор был случайным, вы можете обнаружить его во время отладки или тестирования. Непредсказуемый результат хуже: MySQL возвращает тот же результат каждый раз, когда вы используете запрос, пока он этого не сделает. Иногда это новая версия сервера MySQL, которая приводит к другому результату. Иногда это вызывает растущую таблицу, вызывающую проблему. Что может пойти не так, пойдет не так, и когда вы этого не ожидаете. Это называется законом Мерфи .

Команда MySQL работает над созданием этой ошибки для разработчиков. Более новые версии MySQL в 5,7 последовательности имеют sql_mode флаг под названием ONLY_FULL_GROUP_BY . Когда этот флаг установлен, сервер MySQL возвращает ошибку 1055 и отказывается запускать такой запрос.

Неправильная команда GROUP BY с SELECT *, и как ее исправить.

Иногда запрос выглядит так: * в предложении SELECT .

 SELECT item.*,     /* nonstandard */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

Такой запрос должен быть реорганизован для соответствия стандарту ONLY_FULL_GROUP_BY .

Для этого нам нужен подзапрос, который правильно использует GROUP BY чтобы вернуть значение number_of_uses для каждого item_id . Этот подзапрос короткий и сладкий, потому что ему нужно только посмотреть таблицу uses .

                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id

Затем мы можем присоединиться к этому подзапросу с помощью таблицы item .

 SELECT item.*, usecount.number_of_uses
   FROM item
   JOIN (
                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id
        ) usecount ON item.item_id = usecount.item_id

Это позволяет сделать предложение GROUP BY простым и правильным, а также позволяет использовать спецификатор * .

Примечание: тем не менее, мудрые разработчики избегают использования спецификатора * в любом случае. Обычно лучше перечислять столбцы, которые вы хотите в запросе.

ANY_VALUE ()

 SELECT item.item_id, ANY_VALUE(uses.tag) tag,   
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

показывает строки в таблице, называемой item , количество связанных строк и одно из значений в связанной таблице, называемой uses .

Вы можете рассматривать эту ANY_VALUE() как странную своего рода совокупную функцию. Вместо того, чтобы возвращать счет, сумму или максимум, он инструктирует сервер MySQL выбирать произвольно одно значение из рассматриваемой группы. Это способ работы с ошибкой 1055.

Будьте внимательны при использовании ANY_VALUE() в запросах в производственных приложениях.

Его действительно следует называть SURPRISE_ME() . Он возвращает значение некоторой строки в группе GROUP BY. Какая строка возвращается, является неопределенной. Это означает, что это полностью зависит от сервера MySQL. Формально он возвращает непредсказуемое значение.

Сервер не выбирает случайное значение, это хуже, чем это. Он возвращает одно и то же значение каждый раз, когда вы запускаете запрос, пока он этого не сделает. Он может измениться или нет, когда таблица растет или сжимается, или когда сервер имеет больше или меньше ОЗУ, или когда версия сервера изменяется, или когда Марс находится в ретроградном (что бы это ни значило) или вообще без причины.

Вы были предупреждены.