DevEquiv

    MySQL 5.7 to 8.0: GROUP BY No Longer Implicitly Sorts

    Why queries relying on GROUP BY's old implicit ordering can return differently-ordered rows after upgrading to MySQL 8.0, and why the fix is always the same.

    MySQL 5.7 → MySQL 8.0

    Quick answer

    Add an explicit ORDER BY to every query where row order matters. MySQL never officially guaranteed GROUP BY produced sorted output, but the pre-8.0 optimizer often did so as a side effect — 8.0 removed the internal implementation detail that caused it.

    Why it matters

    This is a silent behavior change: the query still runs successfully and returns the correct rows, just potentially in a different order. Application code, reports, or tests that implicitly depended on GROUP BY output order (without an explicit ORDER BY) can produce different-looking, but not necessarily wrong, output after upgrading.

    Example

    MySQL 5.7 
    SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;
-- 5.7: often returned in customer_id order (undocumented)
    MySQL 8.0 
    SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id ORDER BY customer_id;
-- 8.0+: explicit ORDER BY required for guaranteed order
    The GROUP BY clause itself is unchanged — the fix is always to add an explicit ORDER BY, matching what the SQL standard has always required for a guaranteed order.

    What to do about it

    Migration steps

    • Audit queries using GROUP BY without an explicit ORDER BY, especially anywhere output order is visible to users or compared in tests.
    • Add ORDER BY explicitly wherever a specific order is actually required — never rely on GROUP BY alone for ordering, on any MySQL/MariaDB version.

    References

    • MySQL 8.0 Reference Manual — GROUP BY Optimization (v8.0) — checked 2026-07-31
    • MySQL 8.0 Reference Manual — Changes Affecting Upgrades to MySQL 8.0 (v8.0) — checked 2026-07-31

    Last verified 2026-07-31.