Skip to content

Materializing a computed derived column changes a false HAVING result #185

Description

@chen8908917

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions