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:
None 
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
Triage: Needs Triage: D2 (Serious)

[10 Feb 2010 12:18] Szymon Sobczak
Description:
Following query gives error:
ERROR 1463 (42000): non-grouping field 'ValueSum' is used in HAVING clause
if ONLY_FULL_GROUP_BY mode is on.

SELECT GroupId, SUM(Value) AS ValueSum
	FROM Dupa
	GROUP BY GroupId
	HAVING ValueSum > 10;

If we change HAVING statement to:
HAVING SUM(Value) > 10
the query works, but if I'm right, it works slower becouse of aggregating the same data twice.

Second way to make it work is to disable ONLY_FULL_GROUP_BY mode.

How to repeat:

DROP TABLE IF EXISTS Dupa;

CREATE TABLE Dupa(
	Id INTEGER AUTO_INCREMENT,
		PRIMARY KEY(Id),
	GroupId INTEGER,
	Value INTEGER
) ENGINE = InnoDB;

INSERT INTO Dupa (GroupId, Value) 
	VALUES (1, 1), (1, 3), (1, 5), (2, 2), (2, 4), (2, 6);

SET @@sql_mode := CONCAT(@@sql_mode, ",ONLY_FULL_GROUP_BY");

SELECT GroupId, SUM(Value) AS ValueSum
	FROM Dupa
	GROUP BY GroupId
	HAVING ValueSum > 10;

Suggested fix:
Check if columns used in HAVING statement are already groupped in SELECT sataement.
[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.