What are the benefits of only_full_group_by mode?

25
votes

I updated mysql and I went from MySQL Version 5.6.17 to version 5.7.14

Since I have errors on my sql queries

Indeed, many of my queries look like this:

SELECT count (id) as nbr, lic from prep WHERE key = '18'

And I have this error:

1140 - In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'operator.preparation.orig_lic'; this is incompatible with sql_mode=only_full_group_by

After some research, I learn that Mysql 5.7.14 activates ONLY_FULL_GROUP_BY by default

Why is it enabled by default?

What is the best solution (for performance)? Disable ONLY_FULL_GROUP_BY or add a 'group by' on my query?

Thank you

3
mysqlsql
only_full_group_by forces you to name all SELECT fields within the GROUP BY cause.. this way you can't write wrong GROUP BY querys that not name all fields... read this psce.com/en/blog/2012/05/15/… - Raymond Nijland
@RaymondNijland Great post on GROUP BY mode 'ONLY_FULL_GROUP_BY'. I was searching for such a presentation. Thank you so much !!! - Swr7der

3 Answers

14
votes

only_full_group_by = on tells MySQL engine: Do not apply GROUP BY when you have doubt about what results to show and throw an error. only apply it if the command specifically tells you what to do. i.e. when the command is full and complete!

only_full_group_by = off tells MySQL engine: always apply GROUP BY and if you have doubt about what results to choose, just pick one randomly!

You will not turn it off if you use GROUP BY properly!

Example:

Table: users

 id   |  name
----------------
  1      ali
  2      john
  3      ali

When you use GROUP BY on the name column:

SELECT * FROM users GROUP BY name;

There are two possible results:

  1      ali
  2      john

OR

  2      john
  3      ali

MYSQL does not know what result to choose! Because there are different ids but both have name=ali.

Solution1:

only selecting the name field:

SELECT name FROM users GROUP BY name;

result:

  ali
  john

This is a perfect solution. removing columns that makes GROUP BY confused. This means you know what you're doing. Usually, you do not need
those columns, but if you need them, go to solution3!

Solution2:

Turning off only_full_group_by. MYSQL will show you one of the two possible results RANDOMLY!! (It's ok if you do not really care what id it will choose)

Solution3

Use an Aggregate function like MIN(), MAX() to help MYSQL to decide what it must choose.

For example, I want the minimum id:

SELECT MIN(id), name FROM users GROUP BY name;

result:

  1      ali
  2      john

It will choose the ali row which has the minimum id.

10
votes

The "best" solution is to do the correct thing and fix your query by adding a group by, rather than override the error being thrown. If you override the error with ONLY_FULL_GROUP_BY the error your experiencing will go away but you'll likely experience two new errors as a result of doing so:

  1. Unexpected results of including an aggregated value with non-aggregated values, the problem your error is trying to prevent.

  2. Inability to execute your query on other environments. If you ever need to switch settings or give your code to someone else not using this database, the query will throw the error again. If you get into a habit of overriding the error or other errors, your code could become unusable to others and severely cripple the usefulness of it.

In general, if you are receiving an error, fix it rather than just telling the compiler/optimizer to ignore it.

6
votes

The correct solution is to add the column you group by:

SELECT count (id) as nbr, lic 
from prep 
WHERE key = '18'
group by lic

for the performance this depends on the index you have.

The ONLY_FULL_GROUP_BY is the normal behavior for aggregation function in SQL and the adoption is 5.7 to avoid ambiguity on the casual result for non aggregated column result.