Materializing a computed derived column changes a false `HAVING` result
- 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
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