Bug #81302 w/o group by, query with column in having clause not in select list, succeeds
Submitted: 4 May 2016 6:41 Modified: 4 May 2016 7:57
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8, 5.6.30, 5.7.12 OS:Any
Assigned to: CPU Architecture:Any

[4 May 2016 6:41] Su Dylan
Description:
Output:
=====
mysql> create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(2,2),(3,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select c1 from t1 having c1>c2;
ERROR 1054 (42S22): Unknown column 'c2' in 'having clause'
mysql> select c1 from t1 having c1>(select c2);
+------+
| c1   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

Problem:
=====
It is expected that "select c1 from t1 having c1>(select c2)" should fail with "Unknown column 'c2' in 'having clause'".
Otherwise, if column is not in select list, it can be easily bypassed by adding (SELECT col).

How to repeat:

drop table if exists t1;
create table t1(c1 int, c2 int);
insert into t1 values(2,2),(3,2);
select c1 from t1 having c1>c2; 
select c1 from t1 having c1>(select c2); 

Suggested fix:
It is expected that "select c1 from t1 having c1>(select c2)" should fail with "Unknown column 'c2' in 'having clause'".
[4 May 2016 7:57] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.
Observed this with 5.6.30 and 5.7.12 builds.

Thanks,
Umesh
[9 Apr 2023 11:55] dick Dick
According to doc of mysql https://dev.mysql.com/doc/refman/5.7/en/select.html:

"The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well."
The first query statement does not contain c2,so HAVING shouldn't reference,The nested query in the second statement contains c2, and the nested query executes first, so it can be referenced by HAVING
So I think it's not a bug.