sql server - MySQL Aggregate Functions without GROUP BY clause

Question

In MySQL, I observed that a statement which uses an AGGREGATE FUNCTION in SELECT list gets executed though there is no GROUP BY clause. Other RDBMS products like SQL Server throw an error if we do so.

For example, SELECT col1,col2,sum(col3) FROM tbl1; gets executed without any error and returns the first row values of col1,col2 and sum of all values of col3. The result of the above query is a single row.

Can anyone please tell why does this happen with MySQL?

Thanks in advance!!

Answer 1

It's by design - it's one of many extensions to the standard that MySQL permits.

For a query like SELECT name, MAX(age) FROM t; the reference docs says that:

Without GROUP BY, there is a single group and it is indeterminate which name value to choose for the group

See the documentation on group by handling for more information.

The setting ONLY_FULL_GROUP_BY controls this behavior, see 5.1.7 Server SQL Modes enabling this would disallow a query with an aggregate function lacking a group by statement and it's enabled by default from MySQL version 5.7.5.