MySQL Bugs: #78349: w/ ONLY_FULL_GROUP_BY, having/order by + column alias have different results

Bug #78349	w/ ONLY_FULL_GROUP_BY, having/order by + column alias have different results
Submitted:	6 Sep 2015 12:12	Modified:	3 Dec 2015 14:22
Reporter:	Su Dylan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.22, 5.6.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Results:
======================
mysql> SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1; create table t1( id int );
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT id as A FROM t1 GROUP BY A having A=1;
ERROR 1463 (42000): Non-grouping field 'A' is used in HAVING clause
mysql> SELECT id FROM t1 GROUP BY id HAVING id=1;
Empty set (0.00 sec)

mysql> SELECT id as A FROM t1 GROUP BY A order by A;
Empty set (0.00 sec)
======================

Problem:
========
For the following two SQLs,
1. SELECT id as A FROM t1 GROUP BY A having A=1;
2. SELECT id as A FROM t1 GROUP BY A order by A;

since SQL #2(with order by) succeeds, why does SQL #1(with having) fail?

We got the following quote from document:
========
https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
 ONLY_FULL_GROUP_BY
Reject queries for which the select list, HAVING condition, or (as of MySQL 5.6.5) ORDER list refer to nonaggregated columns that are not named in the GROUP BY clause.
========
From this description, we assume that HAVING condition are treated similar to ORDER list.

How to repeat:
SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; 
drop table if exists t1; create table t1( id int );
SELECT id as A FROM t1 GROUP BY A having A=1;
SELECT id FROM t1 GROUP BY id HAVING id=1;
SELECT id as A FROM t1 GROUP BY A order by A;

Suggested fix:
The following two SQLs should both fail or both succeed.
1. SELECT id as A FROM t1 GROUP BY A having A=1;
2. SELECT id as A FROM t1 GROUP BY A order by A;

Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh

// 5.6.26

mysql> SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1; create table t1( id int );
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT id as A FROM t1 GROUP BY A having A=1;
ERROR 1463 (42000): Non-grouping field 'A' is used in HAVING clause
mysql> SELECT id FROM t1 GROUP BY id HAVING id=1;
Empty set (0.00 sec)

mysql> SELECT id as A FROM t1 GROUP BY A order by A;
Empty set (0.00 sec)

-- with 5.7.9

mysql> SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t1; create table t1( id int );
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT id as A FROM t1 GROUP BY A having A=1;
Empty set (0.00 sec)

mysql> SELECT id FROM t1 GROUP BY id HAVING id=1;
Empty set (0.00 sec)

mysql> SELECT id as A FROM t1 GROUP BY A order by A;
Empty set (0.00 sec)

1) regarding MySQL 5.6:
quoting https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-full :
"mysql> SELECT name, MAX(age) AS max_age FROM t GROUP BY name
    -> HAVING max_age < 30;
Empty set (0.00 sec)
ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
In the second example, the query could be rewritten to use HAVING MAX(age) instead, so that the reference is to a column named in an aggregate function. (max_age fails because it is an aggregate function.) "
So, what you observe is the same error as in the example in the documentation. It's documented and expected.

2) the SQL standard allows aliases (like "A") to be used in ORDER BY, but not in HAVING (rationale: creation of aliases happens after HAVING application and before ORDER BY application). So the 5.6 behaviour is correct.

3) but we found that it is not very intuitive behaviour, and that it would not harm to allow aliases to be used in HAVING. So in MySQL 5.7, only_full_group_by (which is on by default now) allows that. I checked that your queries pass with MySQL 5.7.8.

4) we won't change 5.6's behaviour, it's too old. So I'm closing the bug.

already fixed in 5.7.

Hi Umesh and Guilhem,

Thanks for ur reply.

I don't quite agree with closing this as "Not a bug" for 5.6.22/5.6.26.

For the scenario in the document, it is:
"""
mysql> SELECT name, MAX(age) AS max_age FROM t GROUP BY name
    -> HAVING max_age < 30;
Empty set (0.00 sec)
ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
"""
The reason mentioned in the manual is:
"""
 the second because max_age in the HAVING clause is not named in the GROUP BY clause:
"""

In the scenario mentioned in this bug:
"""
mysql> SELECT id as A FROM t1 GROUP BY A having A=1;
ERROR 1463 (42000): Non-grouping field 'A' is used in HAVING clause
"""

A is used in HAVING clause, and also named in GROUP BY clause.

Therefore, the error scenario from the document and the scenario here are different.

Hello Su. Ok, your case is a bit different. Anyway, using aliases is not allowed in HAVING, in only_full_group_by mode in versions older than 5.7. I'll ask the docs team to add a sentence about it.

The doc has been clarified:
https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html
"Another extension to standard SQL is that MySQL permits of aliases of select-list expressions in the HAVING clause. Enabling ONLY_FULL_GROUP_BY prevents this."