Understanding and Resolving SQL `only_full_group_by` Errors in MySQL 5.7+

In this tutorial, we’ll explore the concept of the ONLY_FULL_GROUP_BY SQL mode introduced with MySQL 5.7. This setting enforces stricter group-by behavior to ensure that any non-aggregated columns in a SELECT statement are properly handled within GROUP BY clauses. We will also discuss strategies for modifying queries to comply with this mode or alternatively, how to disable it if necessary.

Introduction to `ONLY_FULL_GROUP_BY`

MySQL’s ONLY_FULL_GROUP_BY SQL mode was introduced as part of the effort to align MySQL more closely with the SQL standard and improve query predictability. Before version 5.7, MySQL allowed SELECT statements with non-aggregated columns that were not included in the GROUP BY clause, which could lead to ambiguous or unpredictable results.

Problem Overview

When ONLY_FULL_GROUP_BY is enabled, a SELECT statement must ensure:

Every column in the select list is either aggregated (using functions like SUM(), AVG()),
Or explicitly mentioned in the GROUP BY clause,
Or wrapped with an aggregate function such as ANY_VALUE() if the value is deterministic within each group.

Here’s a common scenario that triggers the error:

SELECT mod_users_groups.group_id AS 'value', 
       group_name AS 'text' 
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id 
WHERE  mod_users_groups.active = 1 
  AND mod_users_groups.department_id = 1 
  AND mod_users_groups.manage_work_orders = 1 
  AND group_name != 'root' 
  AND group_name != 'superuser' 
GROUP BY group_name 
HAVING COUNT(`user_id`) > 0 
ORDER BY group_name;

The error arises because group_id is not included in the GROUP BY clause nor aggregated, leading to ambiguity.

Solutions

1. Modify the Query

One of the most straightforward ways to resolve this issue is to include all non-aggregated columns in the GROUP BY clause:

SELECT 
  g.group_id AS 'value', 
  g.group_name AS 'text' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY 
  g.group_name, 
  g.group_id 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name;

2. Use Aggregate Functions

If the intent is to ensure only one row per group based on certain criteria, use aggregate functions:

AVG(), MIN(), MAX(): Use these when you want specific values from groups.

SELECT 
  g.group_name AS 'text', 
  MAX(g.group_id) AS 'value' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY g.group_name 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name;

3. Use `ANY_VALUE()`

If you are certain that all values in a group are identical, ANY_VALUE() can be used:

SELECT 
  g.group_name AS 'text', 
  ANY_VALUE(g.group_id) AS 'value' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY g.group_name 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name;

Disabling `ONLY_FULL_GROUP_BY`

If you prefer to maintain legacy behavior, you can disable this mode. However, it is generally recommended only for transitional phases:

Global Setting:

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

Session Setting (applies to current connection):
```
SET SESSION sql_mode = '';
```

Remember that disabling ONLY_FULL_GROUP_BY can lead to ambiguous query results and is not a long-term solution.

Conclusion

Adopting the ONLY_FULL_GROUP_BY mode enhances SQL standards compliance, reducing potential errors from non-deterministic queries. By understanding how to structure your SELECT statements or adjust SQL modes as necessary, you maintain control over database operations in MySQL 5.7+ environments.

Introduction to ONLY_FULL_GROUP_BY