I avoid MySQL wherever possible but some clients and ISPs use it so it makes an unwelcome appearance in my working life from time to time. I had to repeat a test yesterday to remind myself why I consider MySQL's GROUP BY implementation broken. Rather than repeat the tests again next time I forget

;-)

I thought I'd blog it ?

Consider the following:

mysql> select * from sales;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2004 | UK      | Foo     |      2 |
| 2004 | UK      | Foo     |      1 |
| 2004 | UK      | Foo     |      3 |
| 2004 | UK      | Bar     |      4 |
| 2004 | UK      | Bar     |      6 |
+------+---------+---------+--------+
5 rows in set (0.00 sec)


mysql> select year, product, sum(profit) from sales group by year, product;
+------+---------+-------------+
| year | product | sum(profit) |
+------+---------+-------------+
| 2004 | Bar     |          10 |
| 2004 | Foo     |           6 |
+------+---------+-------------+
2 rows in set (0.00 sec)


mysql> select year, product, profit from sales group by year, product;
+------+---------+--------+
| year | product | profit |
+------+---------+--------+
| 2004 | Bar     |      4 |
| 2004 | Foo     |      2 |
+------+---------+--------+
2 rows in set (0.00 sec)

Erm, what happened with that last query?! The query was wrong, how can MySQL possibly guess what I wanted? The answer is that MySQL can't guess. Instead, it simply chooses the first row of each group and ignores the others (even though the order of rows returned is largely arbitrary).

For reference, here's what PostgreSQL says for the same query:

test=> select year, product, profit from sales group by year, product;
ERROR:  column "sales.profit" must appear in the GROUP BY clause or
be used in an aggregate function

Another one for MySQL Gotchas perhaps.