EC
1055
MySQLERRORCommonQueryHIGH confidence

Expression not in GROUP BY and not in aggregate — ONLY_FULL_GROUP_BY

Production Risk

Low — query is rejected cleanly; fix the SQL.

What this means

ER_WRONG_GROUP_FIELD (1055, SQLSTATE 42000) is raised when ONLY_FULL_GROUP_BY SQL mode is active and a SELECT list contains a non-aggregated column that is not in the GROUP BY clause. MySQL 5.7+ enables this mode by default.

Why it happens
  1. 1SELECT includes a column that is neither in GROUP BY nor wrapped in an aggregate function
  2. 2Query was written for MySQL 5.6 (where ONLY_FULL_GROUP_BY was off by default) and then run on 5.7+
How to reproduce
trigger — this will error
trigger — this will error
SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;
-- 'name' is not in GROUP BY and not aggregated

expected output

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

Fix

Add the column to GROUP BY or wrap it in ANY_VALUE()

WHEN The column is functionally dependent on the GROUP BY key.

Add the column to GROUP BY or wrap it in ANY_VALUE()
-- Option 1: Add to GROUP BY
SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id, name;

-- Option 2: Use ANY_VALUE() if you don't care which value is picked
SELECT user_id, ANY_VALUE(name), COUNT(*) FROM orders GROUP BY user_id;

Why this works

ANY_VALUE() tells MySQL to pick any value of the column within the group, suppressing the ONLY_FULL_GROUP_BY check.

What not to do

✕ Disable ONLY_FULL_GROUP_BY globally to suppress the error

ONLY_FULL_GROUP_BY catches ambiguous queries that return non-deterministic results; disabling it hides real bugs.

Version notes
MySQL 5.7

ONLY_FULL_GROUP_BY was added to the default sql_mode, making 1055 much more common on upgrade from 5.6.

Sources
Official documentation ↗

MySQL 8.0 — 1055 ER_WRONG_GROUP_FIELD

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors