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: | |
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
[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