Group by and strict ONLY_FULL_GROUP_BY mode

Problem

MySQL 5.7 has a different SQL mode by default. Something which is probably a good thing, but causes problems nonetheless.

You can find more information about the SQL mode in the official documentation : http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

The error itself manifests like this:

/* SQL Error (1055): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.field' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by */

What we are going to look at is ONLY_FULL_GROUP_BY.


Solution

Let's assume we've got a table like this:

person age salary
bob 40 200
jeff 50 180
joe 40 190

We can now run a query like this to group by the age of each person:

SELECT age, salary FROM ourtable GROUP BY age

In other RDBMS' like Oracle this simply doesn't work, the query itself doesn't make much sense anyway. We are grouping by age and then simply output salary. Both, bob and joe have the same age, but a different salary. Which value is MySQL going to display here? Well, it's not so obvious and you should never expect MySQL to show you the right value.

Some might say this query is fine if salary stays the same. That might happen if you group by a date and then show the year for this. It's the same for all aggregated dates and you're right, but semantically this isn't a clean solution. The database also can't know that till it runs your query. While it might run okay during development, data might change in production and you're software runs into an error, better write clean code from the start.

If you do a bit of digging, you'll find plenty of articles telling you to change your configuration. Check this article to see how it's done: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html. While this might be the only solution if you're installing a 3rdparty software, you should definitely change your code if you're seeing this problem.

In our example it's rather simple, you just have to ask yourself which salary you want to see, any? Probably not.. Assuming it's the smallest, you just change your query to look like this:

SELECT age, min(salary) FROM ourtable GROUP BY age

Comments


Please sign-in to post a comment