MySQL Bugs: #82317: Unrelevant error message: Unknown column in 'having' clause

Bug #82317	Unrelevant error message: Unknown column in 'having' clause
Submitted:	22 Jul 2016 7:42	Modified:	22 Jul 2016 15:02
Reporter:	Dmitry Savchenkov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.5.49	OS:	Any (v14.04.1)
Assigned to:		CPU Architecture:	Any
Tags:	groupby, having

Description:
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.5.49-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)

#1 Query works as expected
#2 But if one add the column from 2-times joined table t2 to GROUP BY clause it returns an unrelevant error

#1 

mysql> SELECT
    ->    t1.id
    -> FROM t1
    ->   LEFT JOIN t2 AS code1tmp
    ->     ON t1.id = code1tmp.t1_id
    ->       AND code1tmp.code = 'code1'
    ->   LEFT JOIN t2 AS code2tmp
    ->     ON t1.id = code2tmp.t1_id
    ->       AND code2tmp.code = 'code2'
    -> GROUP BY code2tmp.ext_id
    -> HAVING code2tmp.ext_id = MIN(1);
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

#2

mysql> SELECT
    ->    t1.id
    -> FROM t1
    ->   LEFT JOIN t2 AS code1tmp
    ->     ON t1.id = code1tmp.t1_id
    ->       AND code1tmp.code = 'code1'
    ->   LEFT JOIN t2 AS code2tmp
    ->     ON t1.id = code2tmp.t1_id
    ->       AND code2tmp.code = 'code2'
    -> GROUP BY code2tmp.ext_id, code1tmp.ext_id
    -> HAVING code2tmp.ext_id = MIN(1);
ERROR 1054 (42S22): Unknown column 'code2tmp.ext_id' in 'having clause'

How to repeat:
CREATE TABLE `t1` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
INSERT INTO t1 (`id`) VALUES (NULL);

CREATE TABLE `t2` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `t1_id` INT(11) NOT NULL,
  `ext_id` INT(11) NOT NULL,
  `code` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO t2 (`id`, `t1_id`, `ext_id`, `code`) VALUES (NULL, 1, 1, 'code1');
INSERT INTO t2 (`id`, `t1_id`, `ext_id`, `code`) VALUES (NULL, 1, 1, 'code2');

Suggested fix:
Expecting a relevant message explaining why query #2 is not executing (e.g. ambigious column...), as well as documentation update to clarify this case.

According to myql documentation query #2 should be executed just like query #1
Here's a quote from documentation, and the link to the documentation source:

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.

https://dev.mysql.com/doc/refman/5.5/en/select.html

Remove exact column name from synopsis

I have tried latest 5.6 and 5.7 version and I get the same error:

ERROR 1054 (42S22) at line 8: Unknown column 'code2tmp.ext_id' in 'having clause'

with  a very similar test case:

CREATE TABLE IF NOT EXISTS `t1` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`) );
INSERT INTO t1 (`id`) VALUES (NULL);

CREATE TABLE  IF NOT EXISTS `t2` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `t1_id` INT(11) NOT NULL,  `ext_id` INT(11) NOT NULL,  `code` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,  PRIMARY KEY (`id`) );
INSERT INTO t2 (`id`, `t1_id`, `ext_id`, `code`) VALUES (NULL, 1, 1, 'code1');
INSERT INTO t2 (`id`, `t1_id`, `ext_id`, `code`) VALUES (NULL, 1, 1, 'code2');

 SELECT t1.id FROM t1 LEFT JOIN t2 AS code1tmp ON t1.id = code1tmp.t1_id AND code1tmp.code = 'code1'  LEFT JOIN t2 AS code2tmp ON t1.id = code2tmp.t1_id AND code2tmp.code = 'code2' GROUP BY code2tmp.ext_id, code1tmp.ext_id    HAVING code2tmp.ext_id = MIN(1);

DROP TABLE IF NOT EXISTS t1;
DROP TABLE IF NOT EXISTS t2;

Bug #91688 marked as duplicate of this one