Why does MySQL allow "group by" queries WITHOUT aggregate functions?

Solution 1:

According to this page (the 5.0 online manual), it's for better performance and user convenience.

Solution 2:

I believe that it was to handle the case where grouping by one field would imply other fields are also being grouped:

SELECT user.id, user.name, COUNT(post.*) AS posts 
FROM user 
  LEFT OUTER JOIN post ON post.owner_id=user.id 
GROUP BY user.id

In this case the user.name will always be unique per user.id, so there is convenience in not requiring the user.name in the GROUP BY clause (although, as you say, there is definite scope for problems)

Solution 3:

Unfortunately almost all the SQL varieties have situations where they break ANSI and have unpredictable results.

It sounds to me like they intended it to be treated like the "FIRST(Y)" function that many other systems have.

More than likely, this construct is something that the MySQL team regret, but don't want to stop supporting because of the number of applications that would break.

Solution 4:

MySQL treats this is a single column DISTINCT when you use GROUP BY without an aggregate function. Using other options you either have the whole result be distinct, or have to use subqueries, etc. The question is whether the results are truly predictable.

Also, good info is in this thread.