ONLY_FULL_GROUP_BY, ROLLUP and GROUP_CONCAT

Before MySQL 5.7.5, SELECT city, name, SUM(x) ... GROUP BY city ran and returned an arbitrary name from each city. Since 5.7.5 the default sql_mode includes ONLY_FULL_GROUP_BY, and the query fails with error 1055 instead of silently returning an arbitrary value.

What the rule actually checks

Every expression in the select list, HAVING and ORDER BY must be one of: in the GROUP BY, inside an aggregate, or functionally dependent on the GROUP BY columns, meaning MySQL can prove it has exactly one value per group. Grouping by a primary key makes every column of that table dependent. So does an equality in the join that ties a grouped column to that key.

1CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(20));2CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, amount INT);3INSERT INTO customers VALUES (1, 'Ann', 'Tbilisi'), (2, 'Bob', 'Batumi'), (3, 'Cy', 'Tbilisi');4INSERT INTO orders VALUES (1, 1, 10), (2, 1, 20), (3, 2, 5), (4, 3, 7);5-- A helper that runs a statement and records the error instead of stopping.6CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);7CREATE PROCEDURE try_sql(IN stmt TEXT)8BEGIN9  DECLARE EXIT HANDLER FOR SQLEXCEPTION10  BEGIN11    GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;12    INSERT INTO attempts (statement, outcome)13    VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), '')));14  END;15  SET @sql = stmt;16  PREPARE s FROM @sql;17  EXECUTE s;18  DEALLOCATE PREPARE s;19  INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');20END;

1CALL try_sql('SELECT c.city, c.name, SUM(o.amount) FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.city');2CALL try_sql('SELECT c.id, c.name, c.city, SUM(o.amount) FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id');3CALL try_sql('SELECT o.customer_id, c.name, SUM(o.amount) FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY o.customer_id');4CALL try_sql('SELECT city, ANY_VALUE(name), COUNT(*) FROM customers GROUP BY city');5 6SELECT n, outcome FROM attempts ORDER BY n;

• Attempt 1 fails: many customers share a city, so name has no single value per city.
• Attempt 2 works: grouping by c.id, the primary key, determines name and city.
• Attempt 3 works too: grouping by o.customer_id determines c.name because the join says o.customer_id = c.id.
• Attempt 4 uses ANY_VALUE(name), which tells MySQL "any one is fine". It is honest only when you really do not care which value you get.

The fix is almost never to turn the mode off. Group by the key of the thing you are reporting on, or decide which value you want (MIN, MAX, the latest by date with a window function).

WITH ROLLUP

GROUP BY a, b WITH ROLLUP adds a subtotal row for each a and a grand total, with NULL in the rolled-up columns. Because real data can also contain NULL, use GROUPING(col), which is 1 only on a rollup row.

1CREATE TABLE sales (region VARCHAR(10), product VARCHAR(10), amount INT);2INSERT INTO sales VALUES3  ('east', 'pen', 10), ('east', 'ink', 5),4  ('west', 'pen', 7),  ('west', 'pen', 3), ('west', 'pad', 1);

1SELECT region, product, SUM(amount) AS total,2       GROUPING(region) AS all_regions, GROUPING(product) AS all_products3FROM sales4GROUP BY region, product WITH ROLLUP5ORDER BY GROUPING(region), region, GROUPING(product), product;

GROUP_CONCAT truncates silently

GROUP_CONCAT(expr ORDER BY ... SEPARATOR ...) joins a group's values into one string, and DISTINCT removes repeats. Its result is capped by group_concat_max_len, 1024 bytes by default, and anything longer is cut off with only a warning.

1CREATE TABLE words (id INT PRIMARY KEY, w VARCHAR(20));2INSERT INTO words3WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 200)4SELECT n, CONCAT('word', n) FROM seq;

1SET @default_len = (SELECT LENGTH(GROUP_CONCAT(w ORDER BY id)) FROM words);2 3SET SESSION group_concat_max_len = 100000;4SELECT 1024 AS default_limit,5       @default_len AS length_at_default,6       LENGTH(GROUP_CONCAT(w ORDER BY id)) AS length_after_raising,7       COUNT(*) AS words8FROM words;

The full list is 1491 bytes; the default cap returned exactly 1024. Raise the session variable before running a query that builds long lists, or aggregate into JSON with JSON_ARRAYAGG instead.

MySQL also lets HAVING refer to a select-list alias (HAVING total > 50), which standard SQL does not; the exercise uses it.

Common mistakes

• Grouping by a name instead of a key. Two customers called "Ann" become one row. Group by the id and select the name; it is functionally dependent.
• Switching off ONLY_FULL_GROUP_BY to make error 1055 go away. You get an arbitrary value back and no warning.
• Treating NULL in a rollup row as the total marker. Use GROUPING(); a real NULL group looks the same.
• Building long lists with GROUP_CONCAT at the default limit. The 1024-byte cut in the example produced no error.

What interviewers ask

• What does ONLY_FULL_GROUP_BY do? Rejects non-aggregated select expressions that are not functionally dependent on the GROUP BY.
• Why does GROUP BY customers.id let you select customers.name? is the primary key, so each group has exactly one .