MySQL Bugs: #119031: HAVING clause fails with "Unknown column" for a subquery alias that is valid in GROUP BY

Bug #119031	HAVING clause fails with "Unknown column" for a subquery alias that is valid in GROUP BY
Submitted:	18 Sep 6:12	Modified:	23 Sep 5:39
Reporter:	wang jack	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	8.4.6	OS:	MacOS
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
When executing the following query in MySQL, it fails with the error: [42S22][1054] Unknown column 't0.col_3' in 'having clause'.
SELECT t0.col_5 AS col_0, COUNT(t0.col_0), COUNT(t0.col_0), t0.col_5 AS col_3
FROM (SELECT t0.c4 AS col_0,
             t0.c3 AS col_1,
             t0.c2 AS col_2,
             t0.c7 AS col_3,
             t0.c5 AS col_4,
             t0.c0 AS col_5,
             t0.c1 AS col_6
      FROM tb2 AS t0) AS t0
         RIGHT JOIN (SELECT t0.c2 AS col_0 FROM tb1 AS t0) AS t1 ON (t0.col_2 = t1.col_0)
WHERE (PI() NOT IN (SELECT 1))
GROUP BY t0.col_3, t0.col_5, t0.col_2
HAVING (t0.col_3 < 88)
ORDER BY COUNT(t0.col_0) DESC, COUNT(t0.col_0) DESC;

I believe the query is valid because the referenced column (t0.col_3), which is an alias from a subquery, is successfully recognized and used in the GROUP BY clause. According to the logical processing order of SQL, a column available to GROUP BY should also be available to the subsequent HAVING clause.

As a point of comparison, I have tested the same query against both TiDB and MariaDB, and it executes successfully in both environments. This suggests the issue is specific to how MySQL handles column scope resolution.

How to repeat:
DROP TABLE IF EXISTS tb0;
DROP TABLE IF EXISTS tb1;
DROP TABLE IF EXISTS tb2;
CREATE TABLE tb0 (c0 VARBINARY(194), c1 INT, c2 MEDIUMINT, c3 BOOLEAN, c4 VARBINARY(221) NOT NULL, c5 BINARY(211) NOT NULL, c6 BINARY(11) NOT NULL, c7 TEXT, c8 VARCHAR(28));
CREATE TABLE tb1 (c0 BINARY(68), c1 TIME NOT NULL, c2 YEAR NOT NULL);
CREATE TABLE tb2 (c0 TIMESTAMP, c1 TEXT NOT NULL, c2 VARCHAR(242), c3 MEDIUMINT NOT NULL, c4 BINARY(196) NOT NULL, c5 INT NOT NULL, c6 DECIMAL(42,20), c7 DATE);

SELECT t0.col_5 AS col_0, COUNT(t0.col_0), COUNT(t0.col_0), t0.col_5 AS col_3
FROM (SELECT t0.c4 AS col_0,
             t0.c3 AS col_1,
             t0.c2 AS col_2,
             t0.c7 AS col_3,
             t0.c5 AS col_4,
             t0.c0 AS col_5,
             t0.c1 AS col_6
      FROM tb2 AS t0) AS t0
         RIGHT JOIN (SELECT t0.c2 AS col_0 FROM tb1 AS t0) AS t1 ON (t0.col_2 = t1.col_0)
WHERE (PI() NOT IN (SELECT 1))
GROUP BY t0.col_3, t0.col_5, t0.col_2
HAVING (t0.col_3 < 88)
ORDER BY COUNT(t0.col_0) DESC, COUNT(t0.col_0) DESC;

mysql> DROP TABLE IF EXISTS tb0;
Query OK, 0 rows affected, 1 warning (0.010 sec)

mysql> DROP TABLE IF EXISTS tb1;
Query OK, 0 rows affected, 1 warning (0.001 sec)

mysql> DROP TABLE IF EXISTS tb2;
Query OK, 0 rows affected, 1 warning (0.001 sec)

mysql> CREATE TABLE tb0 (c0 VARBINARY(194), c1 INT, c2 MEDIUMINT, c3 BOOLEAN, c4 VARBINARY(221) NOT NULL, c5 BINARY(211) NOT NULL, c6 BINARY(11) NOT NULL, c7 TEXT, c8 VARCHAR(28));
Query OK, 0 rows affected (0.025 sec)

mysql> CREATE TABLE tb1 (c0 BINARY(68), c1 TIME NOT NULL, c2 YEAR NOT NULL);
Query OK, 0 rows affected (0.008 sec)

mysql> CREATE TABLE tb2 (c0 TIMESTAMP, c1 TEXT NOT NULL, c2 VARCHAR(242), c3 MEDIUMINT NOT NULL, c4 BINARY(196) NOT NULL, c5 INT NOT NULL, c6 DECIMAL(42,20), c7 DATE);
Query OK, 0 rows affected (0.011 sec)

mysql> SELECT t0.col_5 AS col_0, COUNT(t0.col_0), COUNT(t0.col_0), t0.col_5 AS col_3
    -> FROM (SELECT t0.c4 AS col_0,
    ->              t0.c3 AS col_1,
    ->              t0.c2 AS col_2,
    ->              t0.c7 AS col_3,
    ->              t0.c5 AS col_4,
    ->              t0.c0 AS col_5,
    ->              t0.c1 AS col_6
    ->       FROM tb2 AS t0) AS t0
    ->          RIGHT JOIN (SELECT t0.c2 AS col_0 FROM tb1 AS t0) AS t1 ON (t0.col_2 = t1.col_0)
    -> WHERE (PI() NOT IN (SELECT 1))
    -> GROUP BY t0.col_3, t0.col_5, t0.col_2
    -> HAVING (t0.col_3 < 88)
    -> ORDER BY COUNT(t0.col_0) DESC, COUNT(t0.col_0) DESC;
ERROR 1054 (42S22): Unknown column 't0.col_3' in 'having clause'
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>

mysql> SELECT t0.col_5 AS col_0, COUNT(t0.col_0), COUNT(t0.col_0), t0.col_5 AS col_3
    -> FROM (SELECT t0.c4 AS col_0,
    ->              t0.c3 AS col_1,
    ->              t0.c2 AS col_2,
    ->              t0.c7 AS col_3,
    ->              t0.c5 AS col_4,
    ->              t0.c0 AS col_5,
    ->              t0.c1 AS col_6
    ->       FROM tb2 AS t0) AS t0
    ->          RIGHT JOIN (SELECT t0.c2 AS col_0 FROM tb1 AS t0) AS t1 ON (t0.col_2 = t1.col_0)
    -> WHERE (PI() NOT IN (SELECT 1))
    -> GROUP BY t0.col_3, t0.col_5, t0.col_2
    -> HAVING (col_3 < 88)
    -> ORDER BY COUNT(t0.col_0) DESC, COUNT(t0.col_0) DESC;
Empty set, 1 warning (0.003 sec)

mysql>

The error: 

    ERROR 1054 (42S22): Unknown column 't0.col_3' in 'having clause' 
     

is occurring because the column t0.col_3 does not exist in the output of the SELECT ... FROM tb2 AS t0 subquery that is being used as t0 in the outer query after the join

Hi,
Regarding your statement: "is occurring because the column t0.col_3 does not exist in the output of the SELECT ... FROM tb2 AS t0 subquery..."
I believe this is incorrect.
The GROUP BY t0.col_3 clause in the very same query executes successfully. Moreover, if I remove only the HAVING clause, the query executes without any errors.
This clearly demonstrates that t0.col_3 does exist in the derived table's output. 

-- [42S22][1054] Unknown column 't0.col_3' in 'having clause'
SELECT t0.col_5 AS col_0, COUNT(t0.col_0), COUNT(t0.col_0), t0.col_5 AS col_3
FROM (SELECT t0.c4 AS col_0,
             t0.c3 AS col_1,
             t0.c2 AS col_2,
             t0.c7 AS col_3,
             t0.c5 AS col_4,
             t0.c0 AS col_5,
             t0.c1 AS col_6
      FROM tb2 AS t0) AS t0
         RIGHT JOIN (SELECT t0.c2 AS col_0 FROM tb1 AS t0) AS t1 ON (t0.col_2 = t1.col_0)
WHERE (PI() NOT IN (SELECT 1))
GROUP BY t0.col_3, t0.col_5, t0.col_2
HAVING (t0.col_3 < 88)
ORDER BY COUNT(t0.col_0) DESC, COUNT(t0.col_0) DESC;

-- No having clause, execute success
SELECT t0.col_5 AS col_0, COUNT(t0.col_0), COUNT(t0.col_0), t0.col_5 AS col_3
FROM (SELECT t0.c4 AS col_0,
             t0.c3 AS col_1,
             t0.c2 AS col_2,
             t0.c7 AS col_3,
             t0.c5 AS col_4,
             t0.c0 AS col_5,
             t0.c1 AS col_6
      FROM tb2 AS t0) AS t0
         RIGHT JOIN (SELECT t0.c2 AS col_0 FROM tb1 AS t0) AS t1 ON (t0.col_2 = t1.col_0)
WHERE (PI() NOT IN (SELECT 1))
GROUP BY t0.col_3, t0.col_5, t0.col_2
ORDER BY COUNT(t0.col_0) DESC, COUNT(t0.col_0) DESC;