Bug #34659 Unexpected error & wrong msg.: 1054: Unknown column '...' in 'having clause'
Submitted: 19 Feb 2008 10:36 Modified: 11 Mar 2008 12:01
Reporter: Gleb Shchepa Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Triage: D4 (Minor)

[19 Feb 2008 10:36] Gleb Shchepa
Description:
Query "SELECT a FROM t1 GROUP BY a HAVING COUNT(*) + b"
always fails with error message: "1054: Unknown column 'b' in 'having clause'".

However, it should:
1. work without error messages if sql_mode is not ONLY_FULL_GROUP_BY;
2. fail with error message 1055: 'test.t1.b' isn't in GROUP BY if sql_mode includes ONLY_FULL_GROUP_BY.
For example, the query "SELECT b FROM t1 GROUP BY a HAVING COUNT(*) + b" has this behavior.

Server version: 5.0.56-debug Source distribution

mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.01 sec)

mysql> set @@sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT b FROM t1 GROUP BY a HAVING COUNT(*) + b;
Empty set (0.00 sec)

mysql> SELECT a FROM t1 GROUP BY a HAVING COUNT(*) + b;
ERROR 1054 (42S22): Unknown column 'b' in 'having clause'
mysql>
mysql> set @@sql_mode=ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT b FROM t1 GROUP BY a HAVING COUNT(*) + b;
ERROR 1055 (42000): 'test.t1.b' isn't in GROUP BY
mysql> SELECT a FROM t1 GROUP BY a HAVING COUNT(*) + b;
ERROR 1054 (42S22): Unknown column 'b' in 'having clause'

How to repeat:
--disable-warnings
DROP TABLE IF EXISTS t1;
--enable-warnings
CREATE TABLE t1 (a INT, b INT);

set @@sql_mode=TRADITIONAL;
SELECT b FROM t1 GROUP BY a HAVING COUNT(*) + b;

# unexpected error:
--error 1054
SELECT a FROM t1 GROUP BY a HAVING COUNT(*) + b;

set @@sql_mode=ONLY_FULL_GROUP_BY;
--error 1055
SELECT b FROM t1 GROUP BY a HAVING COUNT(*) + b;

# incorrect error number, should be 1055:
--error 1054
SELECT a FROM t1 GROUP BY a HAVING COUNT(*) + b;

DROP TABLE t1;
[19 Feb 2008 11:10] Miguel Solorzano
Thank you for the bug report.
[11 Mar 2008 12:01] Evgeny Potemkin
This is the correct behavior and it is documented in the manual.
See http://dev.mysql.com/doc/refman/5.0/en/select.html .