MySQL Bugs: #81302: w/o group by, query with column in having clause not in select list, succeeds

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

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'".

Hello Su Dylan,

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

Thanks,
Umesh

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.