MySQL allows selection of nonaggregated columns with GROUP BY

TIL: If ONLY_FULL_GROUP_BY is disabled via the sql_mode system variable, MySQL allows selecting nonaggregate columns with GROUP BY, even if these columns have different values in the same group.

In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. (TFM)

Wait, what?

Let’s cook up a minimal example. We create a table with two columns and insert two rows of data.

CREATE TABLE atable (
    akey    char(1),
    avalue  int
);

INSERT INTO atable
VALUES
    ("a", 1)
    ("a", 2);

Now we select from this table and group by the first column.

SELECT akey, avalue
FROM atable
GROUP BY akey;

At this point, the database should complain if ONLY_FULL_GROUP_BY is enabled. Otherwise it will just return a single row with either avalue=1 or avalue=2, nobody knows which.

Opinion

This seems like a really bad idea to me, especially since you can achieve the same with ONLY_FULL_GROUP_BY enabled by applying ANY_VALUE to the column. Saving the user nine key strokes doesn’t seem worth the bugs this setting is undoubtably causing – I’ve seen one today.

You can check the setting on your database with SELECT @@GLOBAL.sql_mode.