Skip to main content

MY.L6.SQL_MODE_LOOSE_GROUP_BY — a GROUP BY that returns a value from a row nobody chose

  • Category: idiom
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: MySQL 8.4
  • Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.

Without ONLY_FULL_GROUP_BY, a GROUP BY may select a column that is neither grouped nor aggregated, and the server returns an arbitrary row's value for it.

Not random enough to notice in development. Not stable enough to rely on.

Flagged​

SET GLOBAL sql_mode = 'NO_ZERO_DATE';

Preferred​

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Why four rules and not one​

Four independent guards live in the single sql_mode variable, and a project may legitimately accept one while wanting the others.

Under a single id, one ignore-list entry would silence all four — and whoever added it could not say afterwards which one they had accepted. Each flag therefore gets its own rule, its own id and its own consequence, because a shared "a flag is missing" message would tell a reader what is wrong without telling them why it matters.

What is read, and what is not​

Only the server's value. Laravel sets sql_mode per connection from the connection's strict flag, so an application connection can be strict while the server hands something laxer to everything else that connects — a queue worker with different config, a migration run by hand, a reporting tool.

A flag is matched exactly, never as a substring: NO_ZERO_DATE is contained in NO_ZERO_IN_DATE, and a substring check would report a server carrying only the second as if it carried both.

An empty sql_mode is called out on its own. It is a legal value and a deliberate-looking one, and reporting it as "one flag missing" would understate a server running with every guard off at once.

A value the server withholds is reported as undetermined with a reason, never as a pass.

Sources​