Short answer
It's a speed hack
That is enabled by default, but that can be disabled with this setting: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
Long answer
The reason for the non-standard shorthand group by clause is that it's a speed hack.
MySQL lets the programmer determine whether the selected fields are functionally dependent on the group by clause.
The DB does not do any testing, but just selects the first result that it finds as the value of the field.
This results in considerable speed ups.
Consider this code:
SELECT f1, f2, f3, f4 FROM t1 GROUP BY f2
-- invalid in most SQL flavors, valid in MySQL
MySQL will just select the first value it finds, spending a minimum amount of time.
f1,f3, f4 will be from the same row, but this relation will fall apart if multiple tables with joins are involved.
In order to do the same something simular in SQL-server you'd have to do
SELECT MIN(f1), f2, MIN(f3), MIN(f4) FROM t1 GROUP BY f2
-- valid SQL, but really a hack
The DB will now have to examine all results to find the minimum value, huffing and puffing.
f1, f3, f4 will most likely have no relation to each other and will not be from the same row.
If however you do:
SELECT id as `primary_key`, count(*) as rowcount, count(f2) as f2count, f2, f3, f4
FROM t1
GROUP BY id
All the rest of the fields will be functionally dependent on id.
Rowcount will always be 1, and f2count will be either 0 (if f2 is null) or 1.
On joins, where lots of tables are involved, in a 1-n configuration like so:
Example:
Website 1 -> n Topics 1 -> n Threads 1 -> n Posts 1 -> 1 Person.
And you do a complicated select involving all tables and just do a GROUP BY posts.id
Obviously all other fields are functionally dependent on posts.id (and ONLY on posts.id).
So it makes no sense to list more fields in the group by clause, or to force you to use aggregate functions.
In order to speed things up. MySQL does not force you to do this.
But you do need to understand the concept of functional dependency and the relations in the tables and the join you've written, so it puts a pot of burden on the programmer.
However using:
SELECT
posts.id, MIN(posts.f2)
,MIN(threads.id), min(threads.other)
,MIN(topics.id), ....
,MIN(website.id), .....
,MIN(Person.id), ...
FROM posts p
INNER JOIN threads t on (p.thread_id = t.id)
INNER JOIN topic to on (t.topic_id = to.id)
INNER JOIN website w ON (w.id = to.website_id)
INNER JOIN person pe ON (pe.id = p.person_id)
GROUP BY posts.id //NEVER MIND THE SYNTAX ERROR WITH THE ALIASES
Puts exactly the same mental burden on the programmer.
