Why Mysql's Group By and Oracle's Group by behaviours are different

aggregate-functions, group-by, mysql, oracle, sql

Solution

The MySQL designers put in their nonstandard extension to `GROUP BY` in an attempt to make development easier and certain queries more efficient.

Here's their rationale.

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

There is a server mode called `ONLY_FULL_GROUP_BY` which disables the nonstandard extensions. You can set this mode using this statement.

 SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'  

Here's a quote from that page, with emphasis added.

If `ONLY_FULL_GROUP_BY` is disabled, a MySQL extension to the standard SQL use of `GROUP BY` permits the select list, `HAVING` condition, or `ORDER BY` list to refer to nonaggregated columns even if the columns are not functionally dependent on `GROUP BY` columns... 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.

The important word here is nondeterministic. What does that mean? It means random, but worse. If the server chose random values, that implies it would return different values in different queries, so you have a chance of catching the problem when you test your software. But nondeterministic in this context means the server chooses the same value every time, until it doesn't.

Why might it change the value it chooses? A server upgrade is one reason. A change to table size might be another. The point is, the server is free to return whatever value it wants.

I wish people newly learning SQL would set this `ONLY_FULL_GROUP_BY` mode; they'd get much more predictable results from their queries, and the server would reject nondeterministic queries.

Problem

Original source