alibaba / alibaba/AliSQL

Materializing a computed derived column changes a false `HAVING` result

Open
#185 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
6k
Forks
902
PR merge metrics
No merged PRs in 30d

Description

# 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

```text
source query: sample_uok2I1ZGcHNwMXOhxOoLn | 0 | 0
materialized query: Empty set
```

# How to repeat

```sql
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

```text
AliSQL version: 8.0.44-2-alisql-dev
Docker image: songhuaxiong/alisql:8.0.44-2
Test date: 2026-09-02
```

Contributor guide

Open the contributing guide

Research direction

Start by running the SQL reproduction against AliSQL 8.0.44-2 and compare the source query with the CTAS materialized query. Investigate derived-table materialization and the derived_merge behavior, then add a regression test showing that both HAVING branches evaluate to 0 and both queries return an empty result.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp, mysql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.