MySQL Bugs: #35628: 5.0.52+ undocumented incompatible change in default GROUP BY behavior

Bug #35628	5.0.52+ undocumented incompatible change in default GROUP BY behavior
Submitted:	28 Mar 2008 1:41	Modified:	3 Nov 2008 17:48
Reporter:	Sean Pringle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.52	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
MySQL has always had an extension to GROUP BY that allows use of non grouped fields.  This is a sometimes useful non-standard behavior providing one notes that the non-grouped columns must be constant or that the first random value found from that field is acceptable.  The ONLY_FULL_GROUP_BY sql mode exists to disable it.

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

MySQL also specifies that *omitting* a GROUP BY clause from a query using aggregate functions results in automatically grouping all the rows:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

The behavior of 5.0.52 with the following testcase now seems makes these two rules consistent.  However the release notes don't make this *incompatible change* visible:

http://dev.mysql.com/doc/refman/5.0/en/releasenotes-es-5-0-52.html

What bug/patch lead to this change?  Was it a side effect of something else?

(Not setting to 'verified' until someone else verifies I'm not simply going blind when reading release notes...)

How to repeat:
5.0.50:

mysql> create table a (id int, val int);
ERROR 1050 (42S01): Table 'a' already exists
mysql> drop table a;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> create table a (id int, val int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a(id, val) values(1, 1), (2, 2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> select id, sum(val) from a;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql> set sql_mode = ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, sum(val) from a;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

5.0.52:

mysql> create table a (id int, val int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a(id, val) values(1, 1), (2, 2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> select id, sum(val) from a;
+------+----------+
| id   | sum(val) |
+------+----------+
|    1 |        3 | 
+------+----------+
1 row in set (0.00 sec)

mysql> set sql_mode = ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, sum(val) from a;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Suggested fix:
...

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The change in behavior was a result of the fix for Bug#31794. I have updated the changelog for that bug to indicate how the server behavior changes:

This bugfix results in the following behavior:

- There is a check against mixing group and non-group columns *only*
if ONLY_FULL_GROUP_BY is enabled.

- This check is done both for the select list and for the HAVING clause
if there is one.

This behavior differs from previous versions as follows:

- Previously, the HAVING clause was not checked when ONLY_FULL_GROUP_BY
was enabled; now it is checked.

- Previously, the select list was checked even when ONLY_FULL_GROUP_BY
was not enabled; now it is checked only when ONLY_FULL_GROUP_BY is
enabled.