MySQL Bugs: #78395: Get "Unknown column 'c1' in 'having clause' " when c1 is a valid column

Bug #78395	Get "Unknown column 'c1' in 'having clause' " when c1 is a valid column
Submitted:	10 Sep 2015 11:46	Modified:	11 Sep 2015 4:20
Reporter:	Su Dylan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.6.22, 5.1.77, 5.5.46, 5.6.26, 5.7.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> select c1+1 from t1 group by c1+1 having c1+1;
ERROR 1054 (42S22): Unknown column 'c1' in 'having clause'

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

Problem:
========
The error message is not correct. The column 'c1' exists.

How to repeat:
drop table if exists t1;
create table t1(c1 int, c2 int); 
select c1+1 from t1 group by c1+1 having c1+1;

Suggested fix:
If the SQL "select c1+1 from t1 group by c1+1 having c1+1 " is invalid, a more accurate error message should be issued.

Hello Su Dylan,

Thank you for the report.

Thanks,
Umesh

Bug #78402 marked as duplicate of this

Hi,

Today I realize that this sql should succeed, since in the document, it is mentioned that MySQL permits expressions in GROUP By clauses.

Therefore I am changing the expected result to be :
SQL "select c1+1 from t1 group by c1+1 having c1+1 " succeeds.

Please confirm if this sql should succeed or not. Thanks.

Quote:
=====
https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

MySQL permits expressions in GROUP BY clauses, so the alias is unnecessary:
=====

About queries with "GROUP BY <expression>". Yes, MySQL
supports that, but such support is not as complete as that of
"GROUP BY <column>". "GROUP BY <expression>" isn't allowed in the SQL
standard.
Supporting this extension is more complex than "GROUP BY <column>", so
we do it only in simple cases. In essence, MySQL doesn't try to spot
<expression> everywhere in the query. So, in the query of the report:
select c1+1 from t1 group by c1+1 having c1+1;
it doesn't see that what's in HAVING is what's in GROUP BY, so it
complains that HAVING isn't properly formed. If we make the query just
a bit more complex:
select c1+1 from t1 group by c1+1 having (c1+1) <> 0;
we can easilty understand why MySQL's job is difficult: it should
analyze parts of the expression "(c1+1) <> 0", to discover that one
part ("c1+1") is in GROUP BY.
A workaround is to help MySQL see that fact, simply by using an alias:
select c1+1 as z from t1 group by z having z;
It will also make it more obvious to the human reader that expressions
are repeated. And it may sometimes be more performant, as redundant
calculations will not be done.