Posts Tagged ‘aggregate’

MySQL silently ignores aggregate query errors by default

Monday, January 14th, 2013

In a SQL query, if you use aggregate functions (min, max, count, sum, etc.) and mix them with non-aggregate columns, you have to indicate how to “group” things. Otherwise, the output is not predictable.

MySQL by default will just ignore these problems and make up something. This can make bugs in complex queries hard to track down (and it virtually guarantees that a novice or dullard will slip some errors into such queries eventually).

You can fix this with:

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,’,ONLY_FULL_GROUP_BY’));

(That is, you want the “ONLY_FULL_GROUP_BY” option set. The SET above can be run in the mysql> prompt and affects that session only; thinking DBAs should strongly consider enforcing this as a server option.)

I am too tired and busy to give in to the temptation to unleash a rant about MySQL here, but PLEEEEZ. It’s the year 2013 and this is still an issue??

Hat tip Michael McLaughlin: http://blog.mclaughlinsoftware.com/2010/03/10/mysql-standard-group-by/

MySQL docs on this “extension” http://dev.mysql.com/doc/refman/5.1/en/group-by-extensions.html