MySQL is a database that has been bending the SQL standard in ways that make it hard to move off MySQL. What may appear to be a clever technique for vendor lockin (or maybe just oversight of the standard) can be quite annoying in understanding the real meaning of the SQL language.
One such example is MySQL’s interpretation of how
GROUP BY works
. In MySQL, unlike any other database, you can put arbitrary expressions into your
clause, even if they do not have a formal dependency on the
expression. For instance:
SELECT employer, first_name, last_name
GROUP BY employer
This will work in MySQL, but what does it mean? If we only have one resulting record per
, which one of the employees will be returned? The semantics of the above query is really this one:
SELECT employer, ARBITRARY(first_name), ARBITRARY(last_name)
GROUP BY employer
If we assume that there is such an aggregation function as
. Some may claim that this can be used for some clever performance “optimisation”. I say: Don’t. This is so weakly specified, it is not even clear if the two references of this pseudo-
aggregate function will produce values from the same record.
Just look at the number of Stack Overflow questions that evolve around the “not a GROUP BY expression”
I’m sure that parts of this damage that has been caused to a generation of SQL developers is due to the fact that this works in some
But there is a flag in MySQL called
, and Morgan Tocker, the MySQL community manager suggests eventually turning it on by default
MySQL community members tend to agree that this is a good decision in the long run
While it is certainly very hard to turn this flag on for a legacy application, all new applications built on top of MySQL should make sure to turn on this flag. In fact, new applications should even consider turning on “strict SQL mode”
entirely, to make sure they get a better, more modern SQL experience.
For more information about MySQL server modes, please consider the manual: