I am used to Microsoft technologies including SQL Server. Today I ran across a post where the following passage from the MySQL documentation was quoted:
> **Standard SQL** would reject your query because you can not SELECT
> non-aggregate fields that are not part of the GROUP BY clause in an
> aggregate query. MySQL extends the use of GROUP BY so that the select
> list can refer to nonaggregated columns not named in the GROUP BY
> clause. This means that the preceding query is legal in MySQL. You
> can use this feature to get better performance by avoiding unnecessary
> column sorting and grouping. However, this is useful primarily when
> all values in each nonaggregated column not named in the GROUP BY are
> the same for each group. The server is free to choose any value from
> each group, so unless they are the same, the values chosen are
What is the reason for this MySQL extension, if it conflicts with the SQL Standard?
> Standard SQL would reject your query because you **can not SELECT non-aggregate fields** that are **not part of the GROUP BY clause** in an aggregate query
This is **correct, up to 1992**.
But it is **plainly wrong, from 2003 and beyond.**
From SQL-2003 standard, **[6IWD6-02-Foundation-2011-01.pdf, from http://www.wiscorp.com/], paragraph-7.12 (query specification), page 398**:
> 17) If T is a grouped table, then let G be the set of grouping columns of T. In each ((value expression)) contained
in ((select list)) , each column reference that references a column of T shall reference some column C that
is **functionally dependent** on G **or** shall be **contained in an aggregated argument** of a ((set function specification))
whose aggregation query is QS
Now MYSQL, has implemented this feature by allowing **not only** columns that are **functionally dependent** on the grouping columns **but** allowing **all columns**. This is causing some problems with users that do not understand how grouping works and get indeterminate results where they don't expect.
But you are right to say that MySQL has added a feature that conflicts with SQL-standards (although you seem to think that for the wrong reason). It's not entirely accurate as they have added a SQL-standard feature but not in the best way (more like the easy way) but it does conflict with the latest standards.
To answer your question, the reason for this MySQL feature (extension) is I suppose to be accordance with latest SQL-standards (2003+). Why they chose to implement it this way (not fully compliant), we can only speculate.
It's mainly a performance and maintainability issue. But one can't easily change the RDBMS to be clever enough (Skynet excluded) to recognize functionally dependent columns, so MySQL developers made a choice:
> We (MySQL) give you (MySQL users) this feature which is in SQL-2003 standards. It improves speed in certain `GROUP BY` queries but there's a catch. You have to be careful (and not the SQL engine) so columns in the `SELECT` and `HAVING` lists are functionally dependent on the `GROUP BY` columns. If not, you may get indeterminate results.
> If you want to disable it, you can set `sql_mode` to **[`ONLY_FULL_GROUP_BY`]**.
It's all in the [MySQL docs: Extensions to `GROUP BY` (5.5)] - although not in the above wording but as in your quote (they even forgot to mention that it's a deviation from standard SQL-2003 while not standard SQL-92). This kind of choices is common I think in all software, other RDBMS included. They are made for performance, backward compatibility and a lot of other reasons. Oracle has the famous `''` is the same as `NULL` for example and SQL Server has probably some, too.
There is also this blog post by Peter Bouman, where MySQL developers' choice is defended: [Debunking GROUP BY myths][bouman-1].
[PostgreSQL 9.1 added a new feature][postgres] (release date: September 2011) designed for this purpose. It is more restrictive than MySQL's implementation and closer to the standard.
MySQL announced that in 5.7 version, the behaviour is improved to conform with the standard and actually recognize functional dependencies, (even better than the Postgres implementation). The documentation: [MySQL Handling of `GROUP BY` (5.7)] and another blog post by Peter Bouman: [MySQL 5.7.5: `GROUP BY` respects functional dependencies!][bouman-2]
> Is MySQL breaking the standard by allowing this? How?
It lets you write a query like that:
SELECT a.*, COUNT(*)
ON b.a = a.id
Other systems would require you to add all columns from `a` into the `GROUP BY` list which makes the query larger, less maintanable and less efficient.
In this form (with grouping by the `PK`), this does not contradict the standard since every column in `a` is functionally dependent on its primary key.
However, `MySQL` does not really check the functional dependency and lets you select columns not functionally dependent on the grouping set. This can yield indeterminate results and should not be relied upon. The only thing guaranteed is that the column values belong to some of the records sharing the grouping expression (not even to one record!).
This behavior can be disabled by setting `sql_mode` to `ONLY_FULL_GROUP_BY`.
It's a speed hack
That is enabled by default, but that can be disabled with this setting: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_only_full_group_by
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 <strike>the same</strike> 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
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:
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.
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.