Bug #51058 | Recognizing agregated columns in HAVING clausule | ||
---|---|---|---|
Submitted: | 10 Feb 2010 12:18 | Modified: | 8 Jun 2010 15:34 |
Reporter: | Szymon Sobczak | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.1.37, 5.1.43 | OS: | Any |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | grouping, having |
[10 Feb 2010 12:18]
Szymon Sobczak
[10 Feb 2010 12:44]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with 5.1.43: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.1.43-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP TABLE IF EXISTS Dupa; Query OK, 0 rows affected, 1 warning (0.17 sec) mysql> CREATE TABLE Dupa( -> Id INTEGER AUTO_INCREMENT, -> PRIMARY KEY(Id), -> GroupId INTEGER, -> Value INTEGER -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.72 sec) mysql> INSERT INTO Dupa (GroupId, Value) -> VALUES (1, 1), (1, 3), (1, 5), (2, 2), (2, 4), (2, 6); Query OK, 6 rows affected (0.28 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SET @@sql_mode := CONCAT(@@sql_mode, ",ONLY_FULL_GROUP_BY"); Query OK, 0 rows affected (0.09 sec) mysql> SELECT GroupId, SUM(Value) AS ValueSum -> FROM Dupa -> GROUP BY GroupId -> HAVING ValueSum > 10; ERROR 1463 (42000): non-grouping field 'ValueSum' is used in HAVING clause mysql> set @@sql_mode=''; Query OK, 0 rows affected (0.02 sec) mysql> SELECT GroupId, SUM(Value) AS ValueSum -> FROM Dupa -> GROUP BY GroupId -> HAVING ValueSum > 10; +---------+----------+ | GroupId | ValueSum | +---------+----------+ | 2 | 12 | +---------+----------+ 1 row in set (0.09 sec) ONLY_FULL_GROUP_BY SQL mode does NOT apply to (aggregated!) column aliases, as far as I can see in the manual. So, this is a bug.
[8 Jun 2010 15:34]
Paul DuBois
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. Added this text: For example, the following query returns id values that occur only once in table t1: SELECT id, COUNT(id) FROM t1 GROUP BY id HAVING COUNT(id) = 1; However, the result of the following similar query that uses an alias for the aggregated column depends on the SQL mode: SELECT id, COUNT(id) AS c FROM t1 GROUP BY id HAVING c = 1; In this case, a "non-grouping field 'c' is used in HAVING clause" error occurs if ONLY_FULL_GROUP_BY is enabled because the extension does not apply. The column c in the HAVING clause is not enclosed in an aggregate function (instead, it _is_ an aggregate function).
[28 Nov 2014 14:08]
Guilhem Bichot
Hello Szymon. Good news: we recently released MySQL 5.7.5, where the only_full_group_by mode has been made to accept your query with an alias in HAVING.