MySQL Bugs: #62053: columns in SELECT clause not affected by group fn must be declared in GROUP BY

Bug #62053	columns in SELECT clause not affected by group fn must be declared in GROUP BY
Submitted:	2 Aug 2011 4:14	Modified:	2 Aug 2011 4:22
Reporter:	LUIS ANTONIO GAMA MORENO	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY, group functions

Description:
The group functions like SUM, AVG, MAX, MIN, COUNT, etc., returns only one value per group, then the standard says that: "if there are group fns., and columns not affected by a group fn in the SELECT clause, each of these columns MUST appear in a GROUP BY clause". This rule is logical due to: if there isn't a GROUP BY clause in this situation, then the group fns. will return only one value, but what value must be selected for the columns not affected by group fns.?

How to repeat:
CREATE TABLE emp(id int, name varchar(10), sal decimal(7,2), deptno int);
insert into emp values(1000, 'JONES', 3500.00, 10);
insert into emp values(2000, 'CLARK', 1500.00, 20);
insert into emp values(3000, 'KING',  5000.00, 10);
insert into emp values(4000, 'SMITH', 2000.00, 10);
insert into emp values(5000, 'WARD',  2500.00, 30);
insert into emp values(6000, 'BLAKE', 3000.00, 30);
insert into emp values(7000, 'FORD',  4000.00, 20);

SELECT deptno, SUM(sal)
FROM emp;

returns:

deptno   sum(sal)
------- ---------
     10  21500.00

AND IT IS WRONG!!!!! =( because summary of salaries for "deptno=10" must be 10,500 (not 21,500). THEN the deptno column MUST APPEAR IN THE GROUP BY CLAUSE like this:

SELECT deptno, sum(sal)
FROM emp
GROUP BY deptno;

Suggested fix:
Simply raise an error, because the behaviour is not coherent with the results.

Even DBMSs like ORACLE, MSSQL, SYBASE, IBM DB2, etc. they do raise an error.

Please, read the manual, http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html, where this situation is explained in details. Set ONLY_FULL_GROUP_BY SQL mode if you need standard behavior:

...
mysql> SELECT deptno, SUM(sal)
    -> FROM emp;
+--------+----------+
| deptno | SUM(sal) |
+--------+----------+
|     10 | 21500.00 |
+--------+----------+
1 row in set (0.04 sec)

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

mysql> SELECT deptno, SUM(sal) FROM emp;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql>