4 ms·
this is my favorite MySQL "decision", that the GROUP BY keyword by default (that is, unless you turn it off with the late-added magic flag ONLY_FULL_GROUP_BY) w
by zzzeek 14y ago
this is my favorite MySQL "decision", that the GROUP BY keyword by default (that is, unless you turn it off with the late-added magic flag ONLY_FULL_GROUP_BY) will gladly select an essentially "random" (well, the first row based on INSERT order, which in SQL is as good as random) row for you:
mysql: create table data (token_a varchar(10), token_b varchar(10));
Query OK, 0 rows affected (0.05 sec)
mysql: insert into data (token_a, token_b) values ('A', 'A');
Query OK, 1 row affected (0.01 sec)
mysql: insert into data (token_a, token_b) values ('A', 'B');
Query OK, 1 row affected (0.00 sec)
mysql: insert into data (token_a, token_b) values ('B', 'B');
Query OK, 1 row affected (0.00 sec)
mysql: insert into data (token_a, token_b) values ('B', 'A');
Query OK, 1 row affected (0.00 sec)
mysql: select * from data group by token_a;
+---------+---------+
| token_a | token_b |
+---------+---------+
| A | A |
| B | B |
+---------+---------+
2 rows in set (0.00 sec)
Note here the value we get for "token_b" is based on whether or not "A" or "B" were inserted first. The second "token_b" for each "token_a" (as well as any number of other rows that might follow it for that "token_a") is just discarded.
The scary thing is that I semi-regularly come across applications in Very Important Industries that have large amounts of SQL that rely upon this behavior of "picking any old row" for you, rather than selecting a MAX() or MIN() of some column and then joining to a subquery of the GROUP BY + aggregate....because joining to a subquery in MySQL also performs like crap.
- taligent 14y agoYou say ONLY_FULL_GROUP_BY was late added. But there are posts talking about this setting in 2002.
- zzzeek 14y agobut only added in version 5 (I was using it back with version 3 in the late 90s): http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_only_full_group_by http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#... > Do not permit queries for which the select list or (as of MySQL 5.0.23) HAVING list refers to nonaggregated columns that are not named in the GROUP BY clause.
- HarrisonFisk 14y agoYou quote version 3.23, yet talk about subqueries which didn't get added until version 4.1. ONLY_FULL_GROUP_BY was added in 4.0 but only applied to select list columns, the 5.0.23 mentioned above applies to the HAVING clause list not allowing them as well: http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html#sqlmode_only_full_group_by http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html#...
- zzzeek 14y agothe point is, it's outrageous MySQL even does GROUP BY this way and that doing it the "correct" way is not even the default.
- tveita 14y agoI have encountered situations where this is convenient, and I have yet to see it cause any bugs or problems. In the places I have seen it used, the 'arbitrary' column typically has the same value for the entire group, e.g. for efficiently selecting distinct texts based on their hash values. PostgreSQL has a similar feature using SELECT DISTINCT ON: http://www.postgresql.org/docs/9.2/static/queries-select-lists.html http://www.postgresql.org/docs/9.2/static/queries-select-lis...
- zzzeek 14y ago> In the places I have seen it used, the 'arbitrary' column typically has the same value for the entire group, e.g. for efficiently selecting distinct texts based on their hash values. I agree that case is convenient. But I've seen it being used to actually pick a random row, where a different row would have a different result (such that the output of the program would definitely be different), and it's clear the developers who wrote it didn't fully understand this was going on. I wrote it up in a report for this particular client but they didn't seem to want to touch this particular very old and venerable code.
- jeltz 14y agoI have seen bugs caused by this and I have only worked a little with MySQL. I agree though that the behaviour is very convenient, but the PostgreSQL way of implementing it is superior since it is more explicit about what it does and it also can be combined with ORDER BY so it does not just give you a random row.