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;
