MySQL Bugs: #36511: MySQL allows fields in SELECT list that are not in the GROUP BY statement

Bug #36511	MySQL allows fields in SELECT list that are not in the GROUP BY statement
Submitted:	5 May 2008 16:09	Modified:	5 May 2008 16:41
Reporter:	Daniel Magliola	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.51	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Select fields group by

Description:
MySQL allows sending a SQL statement that contains more fields in the SELECT list than in the GROUP BY. (These being non-aggregated fields).

I can do:

SELECT id, a, b, c
 FROM MyTable
 GROUP BY a,b
 ORDER BY id

Given each combination of a,b, MySQL will return the first "id" and "c" it finds in the table.

The correct behaviour would be for it to report an error in the statement, since when issuing it, the values I'll get for "id" and "c" are not determinate.

How to repeat:
SELECT id, a, b, c
 FROM MyTable
 GROUP BY a,b
 ORDER BY id

Should give an error instead of bringing any "id" and  "c" it can find.

Suggested fix:
Validate that where there is a GROUP BY clause, there are no fields in the SELECT list without an aggregation function (SUM, MAX, etc) that are also not in the GROUP BY list.

This is a matter of whether the ONLY_FULL_GROUP_BY SQL mode is enabled.

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

mysql> SELECT id, a, b, c
    ->  FROM MyTable
    ->  GROUP BY a,b
    ->  ORDER BY id;
ERROR 1055 (42000): 'test.MyTable.id' isn't in GROUP BY

See: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Thank you for the bug report. Please read:
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

c:\dbs>5.0\bin\mysql -uroot dbh
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.60-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table MyTable (id serial, a int, b int, c int);
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT id, a, b, c
    ->  FROM MyTable
    ->  GROUP BY a,b
    ->  ORDER BY id;
Empty set (0.02 sec)

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

mysql> SELECT id, a, b, c
    ->  FROM MyTable
    ->  GROUP BY a,b
    ->  ORDER BY id;
ERROR 1055 (42000): 'dbh.MyTable.id' isn't in GROUP BY
mysql>