Bug #22607 SELECT: give an error for including not aggregated colums
Submitted: 22 Sep 2006 17:09 Modified: 24 Sep 2006 18:09
Reporter: Maciej Pilichowski Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.24a OS:Linux (openSuSE 10.0)
Assigned to: CPU Architecture:Any

[22 Sep 2006 17:09] Maciej Pilichowski
Description:
General example:
SELECT SUM(a),b,c FROM t GROUP BY b;

This select does not make much sense since c is not aggregated here -- it should be an error, but MySQL works with this select.

How to repeat:
Enter the SELECT I gave against any table (change columns names of course).

Suggested fix:
Show an error, do not accept such selects.
[22 Sep 2006 17:47] MySQL Verification Team
Thank you for the bug report. Please read:

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
[22 Sep 2006 19:01] Maciej Pilichowski
Ok, it is not a bug per se, it should be in a wishlist but I don't see any wish-status.

It is not SQL compliant and it really not make sense -- you design your databases to last time in terms of years (at least mine work that long). So what advantage you have "saving" additional column in group by? _NONE_. Only a headache when data are not duplicated.

In other words -- the guarantee for correct results are extremely weak, the advantages are at about 8 characters in typing ONCE, the disadvantages are CONSTANT (I mean every day could lead to get corrupted data).

This is classic premature "optimization". Please, fix it (or -- extend -- if you like). If anyone is not convinced -- do you seriously think that anybody would use this "feature" in in a bank? Insurance company? Safety first.
And this (error on not aggregated column) is just useful safety belt.
[23 Sep 2006 10:48] Maciej Pilichowski
Severity changes (I found the "wish").
[24 Sep 2006 18:09] MySQL Verification Team
Thank you for the feedback. What are you asking is already implemented
using the sql_mode:

c:\mysql\bin>mysql -uroot db5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24a-community-nt

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

mysql> SELECT SUM(a),b,c FROM tb5 GROUP BY b;
Empty set (0.00 sec)

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

mysql> SELECT SUM(a),b,c FROM tb5 GROUP BY b;
ERROR 1055 (42000): 'db5.tb5.c' isn't in GROUP BY