Description:
The source query and materialized rewrite use the same computed string value
and the same `GROUP BY` and `HAVING` expressions. Both `HAVING` branches
evaluate to `0`, so both forms must return an empty result.
MySQL returns the group before materialization but removes it after the
computed projection is materialized with CTAS. Disabling `derived_merge` also
restores the correct source result.
# Expected result
Both forms should return an empty result.
# Actual result
```text
source query: sample_uok2I1ZGcHNwMXOhxOoLn | 0 | 0
materialized query: Empty set
```
How to repeat:
DROP DATABASE IF EXISTS mysql_false_having_repro;
CREATE DATABASE mysql_false_having_repro
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
USE mysql_false_having_repro;
CREATE TABLE t (
s TINYTEXT,
n INT
);
INSERT INTO t VALUES ('sample_uok2I1ZGcHNwMXOhxOoLn', 100);
-- Source query: incorrectly returns one row whose predicates are both 0.
SELECT d.x,
d.x <= '0' AS predicate_1,
MAX(d.x) = 'sample_43' AS predicate_2
FROM (
SELECT LEFT(s, n) AS x
FROM t
) AS d
GROUP BY d.x
HAVING d.x <= '0'
OR MAX(d.x) = 'sample_43';
-- Materialize the computed projection.
CREATE TABLE vect_cut_expr AS
SELECT LEFT(s, n) AS x
FROM t;
-- Materialized rewrite: correctly returns an empty result.
SELECT input.x,
input.x <= '0' AS predicate_1,
MAX(input.x) = 'sample_43' AS predicate_2
FROM vect_cut_expr AS input
GROUP BY input.x
HAVING input.x <= '0'
OR MAX(input.x) = 'sample_43';
Description: The source query and materialized rewrite use the same computed string value and the same `GROUP BY` and `HAVING` expressions. Both `HAVING` branches evaluate to `0`, so both forms must return an empty result. MySQL returns the group before materialization but removes it after the computed projection is materialized with CTAS. Disabling `derived_merge` also restores the correct source result. # Expected result Both forms should return an empty result. # Actual result ```text source query: sample_uok2I1ZGcHNwMXOhxOoLn | 0 | 0 materialized query: Empty set ``` How to repeat: DROP DATABASE IF EXISTS mysql_false_having_repro; CREATE DATABASE mysql_false_having_repro CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; USE mysql_false_having_repro; CREATE TABLE t ( s TINYTEXT, n INT ); INSERT INTO t VALUES ('sample_uok2I1ZGcHNwMXOhxOoLn', 100); -- Source query: incorrectly returns one row whose predicates are both 0. SELECT d.x, d.x <= '0' AS predicate_1, MAX(d.x) = 'sample_43' AS predicate_2 FROM ( SELECT LEFT(s, n) AS x FROM t ) AS d GROUP BY d.x HAVING d.x <= '0' OR MAX(d.x) = 'sample_43'; -- Materialize the computed projection. CREATE TABLE vect_cut_expr AS SELECT LEFT(s, n) AS x FROM t; -- Materialized rewrite: correctly returns an empty result. SELECT input.x, input.x <= '0' AS predicate_1, MAX(input.x) = 'sample_43' AS predicate_2 FROM vect_cut_expr AS input GROUP BY input.x HAVING input.x <= '0' OR MAX(input.x) = 'sample_43';