Description:
MySQL Community Server 9.7.1 rejects a valid grouped SELECT with `ERROR 1054: Unknown column 'd.x' in 'having clause'` when a derived table containing a computed column is eligible for merging. The column `d.x` exists, is explicitly included in GROUP BY, and is referenced by both the outer SELECT expression and HAVING.
The reduced reproduction uses one single-column table and one row. The original query succeeds. Applying the project's existing `remove_distinct` operator to the inner SELECT makes the query fail under default optimizer settings. Disabling `derived_merge` makes both queries return the expected result. The failing query also fails with EXPLAIN and on an empty input table.
These observations identify a failing optimization path involving derived-table merging and HAVING column resolution. The precise internal cause has not been established.
## Expected behaviour
Both queries: one row containing `3`.
## Actual behaviour
| Query | Result |
|---|---|
| Original | One row: `3` |
| Mutated | `ERROR 1054 (42S22): Unknown column 'd.x' in 'having clause'` |
How to repeat:
SET SESSION optimizer_switch = DEFAULT;
CREATE TEMPORARY TABLE valscope_merge_having (c INT NOT NULL);
INSERT INTO valscope_merge_having VALUES (1);
-- Original: returns one row containing 3.
SELECT (d.x + 1) AS result
FROM (
SELECT DISTINCT (c + 1) AS x
FROM valscope_merge_having
) AS d
GROUP BY d.x
HAVING d.x > 0;
-- Mutated by remove_distinct: incorrectly raises ERROR 1054.
SELECT (d.x + 1) AS result
FROM (
SELECT (c + 1) AS x
FROM valscope_merge_having
) AS d
GROUP BY d.x
HAVING d.x > 0;
Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders. It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: MySQL Community Server 9.7.1 rejects a valid grouped SELECT with `ERROR 1054: Unknown column 'd.x' in 'having clause'` when a derived table containing a computed column is eligible for merging. The column `d.x` exists, is explicitly included in GROUP BY, and is referenced by both the outer SELECT expression and HAVING. The reduced reproduction uses one single-column table and one row. The original query succeeds. Applying the project's existing `remove_distinct` operator to the inner SELECT makes the query fail under default optimizer settings. Disabling `derived_merge` makes both queries return the expected result. The failing query also fails with EXPLAIN and on an empty input table. These observations identify a failing optimization path involving derived-table merging and HAVING column resolution. The precise internal cause has not been established. ## Expected behaviour Both queries: one row containing `3`. ## Actual behaviour | Query | Result | |---|---| | Original | One row: `3` | | Mutated | `ERROR 1054 (42S22): Unknown column 'd.x' in 'having clause'` | How to repeat: SET SESSION optimizer_switch = DEFAULT; CREATE TEMPORARY TABLE valscope_merge_having (c INT NOT NULL); INSERT INTO valscope_merge_having VALUES (1); -- Original: returns one row containing 3. SELECT (d.x + 1) AS result FROM ( SELECT DISTINCT (c + 1) AS x FROM valscope_merge_having ) AS d GROUP BY d.x HAVING d.x > 0; -- Mutated by remove_distinct: incorrectly raises ERROR 1054. SELECT (d.x + 1) AS result FROM ( SELECT (c + 1) AS x FROM valscope_merge_having ) AS d GROUP BY d.x HAVING d.x > 0;