This is a follow-up post to my recent announcement of
only_full_group_by improvements in 5.7, where
I’d like to address some tricky corner cases where GROUP BY
contains non-column expressions (functions).
In the SQL standard, GROUP BY should contain only table columns and never expressions. So this example query where we want to count how many people have the same name is illegal in the SQL2011 standard because the GROUP BY clause contains a function:
CREATE TABLE people (first_name VARCHAR(100), last_name VARCHAR(100)); SELECT CONCAT(first_name, ' ', last_name), COUNT(*) FROM people GROUP BY CONCAT(first_name, ' ', last_name);
But MySQL does allow expressions in a GROUP BY clause, and it’s a very convenient addition to the standard. The …[Read more]