MySQL’s handling of GROUP BY
In the year 2000, I was working on migrating a web application from MySQL to Oracle. One of the issues we had was a number of queries which took advantage of slackness in MySQLs handling of the GROUP BY clause. Oracle took exception to our poor SQL and forced the developers to rewrite queries to be a higher standard. To illustrate the issue, have a look at the following example.
mysql> create table `test1` (`a` int(11), `b` int(11) ); Query OK, 0 rows affected (0.14 sec) mysql> insert into test1 values (1,2), (1,3); Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 2 | | 1 | 3 | +------+------+ 2 rows in set (0.00 sec) mysql> select * from test1 group by a; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+ 1 row in set (0.00 sec)
The issue at hand, is the value for the b column. You’ll note that we’ve grouped by a, so we correctly have a single row result. If you’re not sure what the issue is, let’s repopulate the table in a different order and retry.
mysql> truncate test1; Query OK, 0 rows affected (0.10 sec) mysql> insert into test1 values (1,3), (1,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 3 | | 1 | 2 | +------+------+ 2 rows in set (0.00 sec) mysql> select * from test1 group by a; +------+------+ | a | b | +------+------+ | 1 | 3 | +------+------+ 1 row in set (0.00 sec)
So, the value for b was 2 in the first example, and is now 3, with the same data in the table and the same SQL. The problem is that there is no aggregation or GROUP BY clause for b. There’s only one possible value for a, but b is not aggregated – there are two possible values: 2 or 3. MySQL just picks one of the two possible values. Let’s see what PostgreSQL does.
database=# create temporary table test1 (a integer, b integer); CREATE TABLE database=# insert into test1 values (1,2), (1,3); INSERT 0 2 database=# select * from test1; a | b ---+--- 1 | 2 1 | 3 (2 rows) database=# select * from test1 group by a; ERROR: column "test1.b" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select * from test1 group by a;
PostgreSQL decides you don’t want undefined / random data in your result set and lets you know. If you’re using GROUP BY queries in MySQL, and rely on the result being, you know, correct, it may be time for a quick audit of your SQL.
A quick search of Stack Overflow questions shows many solutions which include this flaw. The solutions work fine on MySQL, but fail on PostgreSQL.
I’m surprised that 11 years after first learning of MySQL’s handling of the GROUP BY clause, it has the same behaviour enabled by default. MySQL’s behaviour can be modified to behave in a stricter manner. Worth doing if you want to ensure you receive results without including randomly selected data.