SQL vs MySQL: Rules about aggregate operations and GROUP BY

Question

In this book I'm currently reading while following a course on databases, the following example of an illegal query using an aggregate operator is given:

Find the name and age of the oldest sailor.

Consider the following attempt to answer this query:

SELECT S.sname, MAX(S.age)
FROM Sailors S

The intent is for this query to return not only the maximum age but also the name of the sailors having that age. However, this query is illegal in SQL--if the SELECT clause uses an aggregate operation, then it must use only aggregate operations unless the query contains a GROUP BY clause!

Some time later while doing an exercise using MySQL, I faced a similar problem, and made a mistake similar to the one mentioned. However, MySQL didn't complain and just spit out some tables which later turned out not to be what I needed.

Is the query above really illegal in SQL, but legal in MySQL, and if so, why is that? In what situation would one need to make such a query?

Further elaboration of the question:

The question isn't about whether or not all attributes mentioned in a SELECT should also be mentioned in a GROUP BY. It's about why the above query, using atributes together with aggregate operations on attributes, without any GROUP BY is legal in MySQL.

Let's say the Sailors table looked like this:

+----------+------+
| sname    | age  |
+----------+------+
| John Doe |   30 |
| Jane Doe |   50 |
+----------+------+

The query would then return:

+----------+------------+
| sname    | MAX(S.age) |
+----------+------------+
| John Doe |         50 |
+----------+------------+

Now who would need that? John Doe ain't 50, he's 30! As stated in the citation from the book, this is a first attempt to get the name and age of the oldest sailor, in this example, Jane Doe at the age of 50.

SQL would say this query is illegal, but MySQL just proceeds and spits out "garbage". Who would need this kind of result? Why does MySQL allow this little trap for newcomers?

Answer 1

By the way, it is default MySQL behavior. But it can be changed by setting ONLY_FULL_GROUP_BY server mode in the my.ini file or in the session -

SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT * FROM sakila.film_actor GROUP BY actor_id;

Error: 'sakila.film_actor.film_id' isn't in GROUP BY

ONLY_FULL_GROUP_BY - Do not permit queries for which the select list refers to nonaggregated columns that are not named in the GROUP BY clause.