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)
Let’s cook up a minimal example. We create a table with two columns and insert two rows of data.
CREATE TABLE atable (
char(1),
akey int
avalue
);
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.
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
.