News for hackers. Stuff that matters.
Re:MySQL - Do Not Pass This Way Again by zzzeek on Saturday January 26, 2013 @09:29PM (#5122798)
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.

Re:MySQL - Do Not Pass This Way Again by tveita on Sunday January 27, 2013 @03:05AM (#5123606)
I 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-lis...

Re:MySQL - Do Not Pass This Way Again by zzzeek on Sunday January 27, 2013 @09:11AM (#5124301)
> 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.

Re:MySQL - Do Not Pass This Way Again by jeltz on Sunday January 27, 2013 @09:22AM (#5124337)
I 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.
Re:MySQL - Do Not Pass This Way Again by taligent on Saturday January 26, 2013 @09:35PM (#5122816)
You say ONLY_FULL_GROUP_BY was late added. But there are posts talking about this setting in 2002.
Re:MySQL - Do Not Pass This Way Again by zzzeek on Saturday January 26, 2013 @09:45PM (#5122835)
but 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#...

> 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.

Re:MySQL - Do Not Pass This Way Again by HarrisonFisk on Sunday January 27, 2013 @03:11AM (#5123617)
You 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#...

Re:MySQL - Do Not Pass This Way Again by zzzeek on Sunday January 27, 2013 @09:10AM (#5124299)
the point is, it's outrageous MySQL even does GROUP BY this way and that doing it the "correct" way is not even the default.