SQL-92 и более ранние версии не допускают запросы, в которых список SELECT, HAVING условие или список ORDER BY ссылаются на неагрегированные столбцы, не указанные в GROUP BY определении. Например, этот запрос является некорректным в стандартном SQL-92, так как неагрегированный столбец name в списке SELECT не указан в GROUP BY:

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

Чтобы запрос был корректным в SQL-92, столбец name должен быть опущен из списка SELECT или указан в GROUP BY определении.

SQL:1999 и более поздние версии допускают такие неагрегированные столбцы в соответствии с опциональным свойством T301, если они функционально зависят от GROUP BY столбцов: Если такая зависимость существует между name и custid, запрос является корректным. Например, это будет так, если custid является первичным ключом customers.

MySQL 5.7.5 и более поздние версии реализуют обнаружение функциональной зависимости. Если включен режим SQL ONLY_FULL_GROUP_BY (что по умолчанию), MySQL отклоняет запросы, в которых список SELECT, HAVING условие или список ORDER BY ссылаются на неагрегированные столбцы, которые не указаны в GROUP BY определении и не функционально зависят от них. (До версии 5.7.5 MySQL не обнаруживал функциональную зависимость, и ONLY_FULL_GROUP_BY не был включен по умолчанию.)

MySQL 5.7.5 и более поздние версии также допускают неагрегированный столбец, не указанный в GROUP BY определении, когда включен режим SQL ONLY_FULL_GROUP_BY, при условии, что этот столбец ограничен единственным значением, как показано в следующем примере:

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+

Также возможно иметь более одного неагрегированного столбца в списке SELECT при использовании режима ONLY_FULL_GROUP_BY. В этом случае каждый такой столбец должен быть ограничен одним значением, а все такие ограничительные условия должны быть объединены логическими AND операторами, как показано здесь:

mysql> DROP TABLE IF EXISTS mytable;

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b VARCHAR(10),
    ->    c INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 'qrs', 1000),
    ->        (2, 'abc', 'tuv', 2000),
    ->        (3, 'def', 'qrs', 4000),
    ->        (4, 'def', 'tuv', 8000),
    ->        (5, 'abc', 'qrs', 16000),
    ->        (6, 'def', 'tuv', 32000);

mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode                                            |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+

mysql> SELECT a, b, SUM(c) FROM mytable
    ->     WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| abc  | qrs  |  17000 |
+------+------+--------+

Если режим ONLY_FULL_GROUP_BY отключен, расширение MySQL к стандартному SQL-использованию GROUP BY позволяет списку SELECT, HAVING условию или списку ORDER BY ссылаться на неагрегированные столбцы, даже если эти столбцы не функционально зависят от GROUP BY столбцов. Это заставляет MySQL принять предыдущий запрос. В этом случае сервер свободен выбирать любое значение из каждой группы, поэтому, если они не одинаковые, выбранные значения не являются детерминированными, что, вероятно, не то, что вам нужно. Кроме того, выбор значений из каждой группы не может быть изменён добавлением ORDER BY определения. Сортировка набора результатов происходит после выбора значений, и ORDER BY не влияет на то, какое значение из каждой группы выбирает сервер. Отключение ONLY_FULL_GROUP_BY полезно в основном в тех случаях, когда известно, что из-за некоторых свойств данных все значения в каждом неагрегированном столбце, не указанном в GROUP BY, одинаковы для каждой группы.

Можно добиться того же результата, не отключая ONLY_FULL_GROUP_BY, используя функцию ANY_VALUE() для ссылки на неагрегированный столбец.

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

Этот запрос может быть некорректным при включенном ONLY_FULL_GROUP_BY, так как неагрегированный столбец address в списке SELECT не указан в GROUP BY определении:

SELECT name, address, MAX(age) FROM t GROUP BY name;

Запрос корректен, если name является первичным ключом t или уникальным столбцом NOT NULL. В таких случаях MySQL распознаёт, что выбранный столбец функционально зависит от столбца группировки. Например, если name является первичным ключом, его значение определяет значение address, потому что каждая группа содержит только одно значение первичного ключа и, следовательно, только одну строку. В результате нет случайности в выборе значения address в группе и нет необходимости отклонять запрос.

Запрос некорректен, если name не является первичным ключом t или уникальным столбцом NOT NULL. В этом случае нельзя сделать вывод о функциональной зависимости, и возникает ошибка:

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

Если известно, что для данного набора данных каждое значение name фактически однозначно определяет значение address, address фактически функционально зависит от name. Чтобы заставить MySQL принять запрос, можно использовать функцию ANY_VALUE():

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Кроме того, можно отключить ONLY_FULL_GROUP_BY.

Однако предыдущий пример довольно простой. В частности, маловероятно, что вы будете группировать по одному столбцу первичного ключа, потому что каждая группа будет содержать только одну строку. Для дополнительных примеров, демонстрирующих функциональную зависимость в более сложных запросах, см. Раздел 12.19.4, «Обнаружение функциональной зависимости».

Если запрос содержит агрегатные функции и не имеет GROUP BY определения, он не может содержать неагрегированные столбцы в списке SELECT, HAVING условии или списке ORDER BY при включенном режиме ONLY_FULL_GROUP_BY:

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by

Без GROUP BY, существует единственная группа, и выбор значения name для этой группы является не детерминированным. Здесь тоже можно использовать ANY_VALUE(), если значение name, которое MySQL выбирает, не имеет значения:

SELECT ANY_VALUE(name), MAX(age) FROM t;

В MySQL 5.7.5 и более поздних версиях ONLY_FULL_GROUP_BY также влияет на обработку запросов, которые используют DISTINCT и ORDER BY. Рассмотрим случай таблицы t с тремя столбцами c1, c2 и c3, содержащей следующие строки:

c1 c2 c3
1  2  A
3  4  B
1  2  C

Предположим, что мы выполняем следующий запрос, ожидая результатов, отсортированных по c3:

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

Для сортировки результата сначала должны быть удалены дубликаты. Но какую строку нужно сохранить — первую или третью? Этот произвольный выбор влияет на сохранённое значение c3, которое в свою очередь влияет на сортировку и делает её произвольной. Для предотвращения этой проблемы запрос, содержащий DISTINCT и ORDER BY, отклоняется как некорректный, если какой-либо ORDER BY выражение не удовлетворяет хотя бы одному из следующих условий:

Другое расширение MySQL к стандартному SQL допускает ссылки в HAVING определении на алиасированные выражения в списке SELECT. Например, следующий запрос возвращает значения name, которые встречаются только один раз в таблице orders:

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

Расширение MySQL допускает использование псевдонима в HAVING определении для агрегированного столбца:

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

Стандартный SQL допускает только выражения столбцов в GROUP BY определениях, поэтому такое утверждение является некорректным, потому что FLOOR(value/100) является невыражением столбца:

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

MySQL расширяет стандартный SQL, чтобы разрешить невыражения столбцов в GROUP BY определениях, и считает предыдущее утверждение корректным.

Стандартный SQL также не допускает псевдонимов в GROUP BY определениях. MySQL расширяет стандартный SQL, чтобы разрешить псевдонимы, поэтому запрос можно записать ещё и так:

SELECT id, FLOOR(value/100) AS val
  FROM tbl_name
  GROUP BY id, val;

Псевдоним val считается выражением столбца в GROUP BY определении.

При наличии невыражения столбца в GROUP BY определении MySQL распознаёт равенство этого выражения и выражений в списке SELECT. Это означает, что при включенном режиме SQL ONLY_FULL_GROUP_BY запрос, содержащий GROUP BY id, FLOOR(value/100), корректен, так как это же выражение FLOOR() присутствует в списке SELECT. Однако MySQL не пытается распознать функциональную зависимость от GROUP BY невыражений столбца, поэтому следующий запрос некорректен при включенном режиме SQL ONLY_FULL_GROUP_BY, даже если третье выбранное выражение является простой формулой столбца id и выражения FLOOR() в GROUP BY определении:

SELECT id, FLOOR(value/100), id+FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

Решение — использовать производную таблицу:

SELECT id, F, id+F
  FROM
    (SELECT id, FLOOR(value/100) AS F
     FROM tbl_name
     GROUP BY id, FLOOR(value/100)) AS dt;