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.
AliSQL 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
source query: sample_uok2I1ZGcHNwMXOhxOoLn | 0 | 0
materialized query: Empty set
How to repeat
DROP DATABASE IF EXISTS alisql_false_having_repro;
CREATE DATABASE alisql_false_having_repro
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
USE alisql_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';
Version
AliSQL version: 8.0.44-2-alisql-dev
Docker image: songhuaxiong/alisql:8.0.44-2
Test date: 2026-09-02
Description
The source query and materialized rewrite use the same computed string value
and the same
GROUP BYandHAVINGexpressions. BothHAVINGbranchesevaluate to
0, so both forms must return an empty result.AliSQL returns the group before materialization but removes it after the
computed projection is materialized with CTAS. Disabling
derived_mergealsorestores the correct source result.
Expected result
Both forms should return an empty result.
Actual result
How to repeat
Version