SQL GROUP BY — ONLY_FULL_GROUP_BY Rejected

Diagnose Error 1055: Expression of SELECT list is not in GROUP BY when ONLY_FULL_GROUP_BY SQL mode is enabled.

intermediatedebuggingsqlphase-16

Symptoms

A GROUP BY query fails because non-aggregated columns are not in the GROUP BY clause.
Error message: Error 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause
Failure occurs intermittently or after a specific code path

Possible causes

The SELECT includes columns not in GROUP BY and not wrapped in an aggregate.
ONLY_FULL_GROUP_BY SQL mode is enabled (MySQL 5.7+ default).
Environment or runtime version mismatch introduces the defect

Diagnosis steps

Reproduce the error in a minimal isolated test case

node -e "require('./repro')"

Interpretation

If the minimal case reproduces, the bug is in the logic itself, not the environment. If not, collect environment details.

Capture the full error stack trace and identify the originating line

Set NODE_OPTIONS=--stack-trace-limit=100 and run with verbose logging

Interpretation

The first frame in user code is the trigger. Compare against the expected execution path to find the divergence point.

Inspect the values of all variables at the failure point

Add console.log / debugger / breakpoint at the line before the throw

Interpretation

Check for undefined, null, wrong type, or unexpected shape. The error message tells you what was wrong; the variables tell you why.

Root cause

ONLY_FULL_GROUP_BY requires all non-aggregated SELECT columns to appear in GROUP BY, rejecting non-standard queries.

Fix

Add all non-aggregated columns to GROUP BY, or wrap them in ANY_VALUE(), or use an aggregate.

Risk note

Applying the fix without understanding the root cause may mask the symptom and introduce a different defect. Always verify with the reproduction case.

Verify the fix

Run the query and confirm it passes ONLY_FULL_GROUP_BY validation.

Symptom and Error Message

When running the SQL code, the runtime raises the following error:

Error 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause

This error indicates that a group by query fails because non-aggregated columns are not in the group by clause. The error is reproducible and appears consistently once the triggering condition is met. In production, you may see this error sporadically because the triggering condition depends on runtime state that is not always present.

Root Cause Analysis

The fundamental cause is: only_full_group_by requires all non-aggregated select columns to appear in group by, rejecting non-standard queries. Two factors converge to produce the visible error. First, the select includes columns not in group by and not wrapped in an aggregate. Second, only_full_group_by sql mode is enabled (mysql 5.7+ default). Either factor alone might not trigger the error, but together they create the conditions for failure.

Diagnosis Walkthrough

Start by reproducing the error in isolation. Strip away everything except the minimum code path that triggers the error. Once you have a minimal reproduction, inspect every variable at the failure point. Look for: undefined where an object is expected, wrong types, stale references, and off-by-one indices. The error message tells you what went wrong; the surrounding state tells you why.

Fix and Code Example

// Before (buggy)
// ... code that triggers the error ...

// After (fixed)
// ... corrected code with proper guard or type check ...

The fix addresses the root cause directly: add all non-aggregated columns to group by, or wrap them in any_value(), or use an aggregate. After applying the fix, the error no longer occurs because the triggering condition is either prevented or handled gracefully.

Verification

Run the query and confirm it passes ONLY_FULL_GROUP_BY validation. Run the reproduction case again and confirm the error is gone. Then run the full test suite to check for regressions. In production, monitor error logs for 24 hours to confirm the fix is effective under real load.

Common Pitfalls

A frequent mistake is to suppress the error with a try/catch or conditional without addressing the underlying cause. This masks the symptom but the defective logic remains, and the error will resurface in a different form. Always fix the root cause, not just the error message. Another pitfall is fixing the symptom only in the happy path while ignoring edge cases like empty input, null values, or concurrent access.