SQL-92 и более ранние версии не допускают запросы, в которых список выбора, HAVING условие или ORDER BY список ссылаются на неагрегированные столбцы, которые не указаны в GROUP BY заданном условии. Например, этот запрос некорректен в стандартном SQL-92, потому что неагрегированный name столбец в списке выбора не появляется в 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 должен быть опущен из списка выбора или указан в GROUP BY заданном условии.

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

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

MySQL также допускает неагрегированный столбец, не указанный в 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. В этом случае каждый такой столбец должен быть ограничен одним значением в WHERE заданном условии, и все такие ограничивающие условия должны быть объединены логическими 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 позволяет списку выбора, 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 в списке выбора не указан в 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.

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

Если запрос содержит агрегатные функции и не имеет GROUP BY заданного условия, он не может содержать неагрегированные столбцы в списке выбора, 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;

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 заданном условии алиасы выражений в списке выбора. Например, следующий запрос возвращает значения 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 распознает равенство этого выражения и выражений в списке выбора. Это означает, что при включенном режиме ONLY_FULL_GROUP_BY запрос, содержащий GROUP BY id, FLOOR(value/100), является корректным, так как то же выражение FLOOR() встречается в списке выбора. Однако MySQL не пытается распознать функциональную зависимость от GROUP BY невыражений столбцов, поэтому следующий запрос некорректен при включенном режиме 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;