阅读中文版本

MySQL ERROR 1055 — ONLY_FULL_GROUP_BY Rejects the Query

Fix MySQL 1055 by grouping or aggregating every non-aggregated selected column, and avoid disabling ONLY_FULL_GROUP_BY to hide invalid SQL.

mysql -e "SELECT id, COUNT(*) FROM orders GROUP BY status"

mysql -e "SELECT @@sql_mode"

Symptoms

This debugging guide addresses “MySQL ERROR 1055 — ONLY_FULL_GROUP_BY Rejects the Query”. Start from the symptoms, follow the ordered diagnostics, and confirm each hypothesis before moving on; the fix and verification steps then close the loop.

Observed symptoms include:

• ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause
• The query ran on an old MySQL and fails after upgrading to 5.7+/8.0
• Adding a column to the SELECT list suddenly errors

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Read the offending expression number

mysql -e "SELECT id, COUNT(*) FROM orders GROUP BY status"

The error names the position of the non-grouped column; add it to GROUP BY or aggregate it.

Step 2: Check the SQL mode

mysql -e "SELECT @@sql_mode"

ONLY_FULL_GROUP_BY being present explains why the lax query is rejected.

Likely contributors, in order of frequency:

• A selected column is neither aggregated nor listed in GROUP BY
• The intent was one representative row per group but ONLY_FULL_GROUP_BY is enabled
• The query relies on MySQL-specific lax grouping that is now disallowed

Root Cause

With ONLY_FULL_GROUP_BY enabled (default in MySQL 5.7+ and 8.0), every column in the SELECT list must be functionally dependent on the GROUP BY columns or wrapped in an aggregate. This prevents nondeterministic rows.

The Fix

Add the column to GROUP BY, wrap it in an aggregate such as MIN/MAX/ANY_VALUE when a specific value is acceptable, or rewrite using a window function or a join to select one row per group.

Watch out: Disabling ONLY_FULL_GROUP_BY makes results nondeterministic across servers and versions; do not disable it to silence errors.

-- ❌ id not grouped nor aggregated (ERROR 1055)
-- SELECT status, id FROM orders GROUP BY status;

-- ✅ aggregate a representative value
SELECT status, MIN(id) AS first_id, COUNT(*) AS n
FROM orders
GROUP BY status;

-- ✅ or group all selected columns
SELECT status, id, COUNT(*) FROM orders GROUP BY status, id;

Verify and Prevent

Run the rewritten query and confirm it returns one deterministic row per group; verify @@sql_mode still includes ONLY_FULL_GROUP_BY.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.