Description:
When a query references a column alias from a derived table in the HAVING clause, and the alias is defined by a Boolean expression (such as (id>1)), MySQL erroneously reports' unknown column '. However, the same alias can be correctly identified and used in the GROUP BY clause.
As stated in the analysis of expected results. According to the official manual, MySQL should support the use of aliases for expressions in 'having'. It may be due to parsing failure caused by Derived Table.
Association with known bug # 78395: This bug describes a "unknown column" error that occurs when referencing a GROUP BY expression in a HAVING clause. The official developer's response pointed out that the support for GROUP BY<expressions>is not as complete as GROUP BY<column names>, and the parser may not be able to recognize complex expressions in HAVING clauses that are the same as GROUP BY. This provides important background and evidence for the current issue, indicating that it is a known type of parsing limitation or flaw that occurs under specific conditions (involving derived tables and expression aliases).
How to repeat:
Please execute the following SQL statements in order:
-- 1. Create a test table and insert data
CREATE TABLE class_members (id INT);
CREATE TABLE class_members1 (id INT);
INSERT INTO class_members1 VALUES (1), (2), (3);
INSERT INTO class_members VALUES (1), (2);
-- 2. Execute queries that will fail (using Boolean expressions)
SELECT 1
FROM (
class_members AS t0
JOIN (
SELECT (id>1) AS c0 -- c0 is a Boolean expression alias
FROM class_members1
) AS t2 ON true
)
GROUP BY t2.c0 -- t2.c0 can be recognized here
HAVING t2.c0; --Error here: Unknown column 't2. C0'
Actual result: Error returned: ERROR 1054 (42S22): Unknown column 't2. C0' in 'having claim'
Expected result: The query should be executed successfully, grouped and filtered based on the values of t2.c0 (True/False)
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html ,According to the official documentation, 'MySQL does not try to recognize functional dependency on noncolumn expressions' should be error free
