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

• Submitted: 18 Sep 6:12
• Modified: 19 Sep 15:49
• Reporter: wang jack
• Status: Analyzing
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 8.4.6
• OS: MacOS
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[18 Sep 6:12] wang jack

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;