Error related to only_full_group_by occurs quite frequently in MySQL when executing queries, especially for users who are transitioning from older versions of MySQL to newer ones. This article explains why this error happens, how to resolve it, and the contexts where it’s relevant.

Understanding the Error

The only_full_group_by error arises when the GROUP BY clause in a SQL query is used in a way that does not align with the SQL standard defined by the SQL:1999 specification. This option is enabled by default in MySQL versions 5.7 and later. When enabled, it requires that every column in the SELECT list that is not an aggregate function must be specified in the GROUP BY clause. The error occurs when a non-aggregated column that is not included in the GROUP BY clause is selected.

Example of the Error

Consider the following table and query:

1Table: Sales
2
3| sale_id | product_id | sale_date | amount |
4| --------- | ------------ | ------------ | -------- |
5| 1 | 1 | 2023-09-01 | 100 |
6| 2 | 1 | 2023-09-02 | 150 |
7| 3 | 2 | 2023-09-01 | 200 |
8
9Query: 
10SELECT product_id, sale_date, SUM(amount) 
11FROM Sales 
12GROUP BY product_id;

This query would produce an error under the only_full_group_by mode because sale_date is neither an aggregated function nor included in the GROUP BY clause.

Key Concepts Involved

• Aggregate Function: A function where the values of multiple rows are grouped together to form a single summary value, like SUM(), COUNT(), AVG(), etc.
• Non-aggregated Columns: Columns in the SELECT clause that aren't part of an aggregate function.
• GROUP BY Clause: Used to arrange identical data into groups. All non-aggregated columns in a SELECT statement must be in the GROUP BY clause when only_full_group_by is enabled.

Solutions to Resolve the Error

There are several approaches to deal with the only_full_group_by error:

1. Include All Non-aggregated Columns in the GROUP BY Clause:
   Modify the query by adding all non-aggregated columns to the GROUP BY clause. For example:

   SELECT product_id, sale_date, SUM(amount) 
   FROM Sales 
   GROUP BY product_id, sale_date;

1. Disable only_full_group_by Mode:
   If the strict mode is not a requirement, you can disable only_full_group_by by modifying the MySQL configuration or by running:

   SET sql_mode = (SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

1. Rewrite the Query Using Derived Tables:
   You can restructure your SQL query using subqueries or derived tables to ensure compliance:

1   SELECT product_id, sale_date, total_amount 
2   FROM (
3     SELECT product_id, sale_date, SUM(amount) as total_amount 
4     FROM Sales 
5     GROUP BY product_id, sale_date
6   ) as derived_table;

Configuring MySQL SQL Mode

To make permanent changes, edit the MySQL configuration file (usually my.cnf or my.ini) and adjust the sql_mode to exclude ONLY_FULL_GROUP_BY. Afterward, restart the MySQL server.

Conclusion

Understanding and addressing the only_full_group_by error is key to writing efficient and standards-compliant MySQL queries. The mode aims to enforce stricter SQL rules, preventing ambiguous results but may require substantial query modifications or mode adjustments by developers transitioning from older MySQL versions.

Summary Table

Understanding the nuances of only_full_group_by not only helps with writing correct queries but also enhances one's ability to maintain standards-compliance in SQL. Developers dealing with legacy systems may find this understanding especially beneficial when upgrading databases.