HashAgg Encodes Empty `TEXT` as `NULL` Causing `MIN` to Ignore Empty String
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal Reproduce Step (Required)
```sql
DROP DATABASE IF EXISTS repro_db11;
CREATE DATABASE repro_db11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
USE repro_db11;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
CREATE TABLE s (rowid INT PRIMARY KEY AUTO_INCREMENT, c0 TEXT, c1 TEXT);
INSERT INTO s (c0, c1) VALUES
(NULL,'a'),(NULL,'b'),(NULL,'c'),(NULL,'d'),
(' ',NULL),('',''),(' ','y'),(' ','y'),(' ','y'),(' ','z'),
('101','w'),('}{',''),('abc','v'),('F}','u'),('HI',''),('ZV','t');
-- Single: correlated MIN decorrelated to Cartesian join + HashAgg
SELECT rowid, HEX(c0) AS c0_h,
HEX((SELECT MIN(c0) FROM s s2 WHERE s2.rowid <= s.rowid)) AS rmin_h
FROM s ORDER BY rowid;
-- Split (CTE rewrite, using VP split to avoid decorrelation)
CREATE TABLE l (rowid INT PRIMARY KEY, c1 TEXT, c0 TEXT);
CREATE TABLE r (rowid INT PRIMARY KEY, c0 TEXT);
INSERT INTO l SELECT rowid, c1, c0 FROM s;
INSERT INTO r SELECT rowid, c0 FROM s;
SELECT rowid, HEX(c0) AS c0_h,
HEX((SELECT MIN(c0) FROM (
WITH lc AS (SELECT * FROM l), rc AS (SELECT * FROM r)
SELECT lc.rowid, lc.c0, lc.c1 FROM lc JOIN rc ON lc.rowid = rc.rowid
) s2 WHERE s2.rowid <= s.rowid)) AS rmin_h
FROM (WITH lc AS (SELECT * FROM l), rc AS (SELECT * FROM r)
SELECT lc.rowid, lc.c0, lc.c1 FROM lc JOIN rc ON lc.rowid = rc.rowid) s
ORDER BY rowid;
```
### 2. What Did You Expect to See? (Required)
The correlated `MIN(c0) OVER (ORDER BY rowid ROWS UNBOUNDED PRECEDING)` should return the minimum `c0` value seen so far. The data contains an empty string `''` at row 6, which is the smallest possible string (smaller than a space `' '`). Therefore, for row 6 and all subsequent rows, the running minimum should be `''` (HEX empty). Both the single‑table and the CTE‑rewritten queries must produce identical results.
### 3. What Did You See Instead? (Required)
Single‑table query: For rows 6–16 the running minimum incorrectly returns a space `' '` or `' '`, ignoring the empty string `''`.
CTE‑rewritten query: Correctly returns the empty string `''` for all rows from row 6 onward.
Example of differing rows (rowid 6–11):
| rowid | c0 | Single rmin | Split rmin | Correct |
|-------|----|-------------|------------|---------|
| 6 | `''` | `' '` (`2020`) | `''` | `''` |
| 7 | `' '` | `' '` (`2020`) | `''` | `''` |
| 8 | `' '` | `' '` (`2020`) | `''` | `''` |
| 9 | `' '` | `' '` (`2020`) | `''` | `''` |
| 10 | `' '` | `' '` (`20`) | `''` | `''` |
| 11 | `'101'` | `' '` (`20`) | `''` | `''` |
The empty string `''` is entirely lost from the running minimum after it appears, leading to incorrect results for all subsequent rows.
### 4. What Is Your TiDB Version? (Required)
Version: TiDB‑v9.0.0
### 5. Execution Plan Differences
**Single‑table query (wrong)** — decorrelated to a Cartesian join with `HashAgg`:
```
HashAgg_13 root group by: rowid, funcs:firstrow(rowid), funcs:min(c0)->Column#13
└─HashJoin_17 root CARTESIAN left outer join, other cond:le(s.rowid, s2.rowid)
├─TableReader_25 Build table:s
└─TableReader_27 Probe table:s2
```
The `MIN` aggregation is performed by a `HashAgg` operator.
**CTE‑rewritten query (correct)** — preserves correlated subquery via `Apply` + `TopN`/`StreamAgg`:
```
Apply_68 root CARTESIAN left outer join
├─MergeJoin_71 Build inner join l.r
└─StreamAgg_100 Probe funcs:min(l.c0)
└─TopN_103 root l.c0, offset:0, count:1
└─MergeJoin_110 inner join
```
The `MIN` is evaluated using a `TopN` + `StreamAgg` combination, with standard comparator‑based sorting.
### 6. Root Cause
The bug is in the `HashAgg` execution operator. During aggregation, TiDB encodes column values into a compact internal representation for grouping and state management. When handling `TEXT` columns, the encoding logic has a boundary‑condition defect for an empty string `''` (length 0). Because `NULL` also has a length of 0, the encoder mistakenly treats the empty string as a `NULL` marker. As a result, when `MIN` processes the encoded value, it sees `NULL` and immediately skips it (since `MIN` ignores `NULL`). Consequently, the empty string is lost from the running minimum.
The CTE‑rewritten query avoids this defect because the optimizer chooses a different execution path: it evaluates the correlated subquery using an `Apply` operator that internally employs a `TopN` (sort‑based) + `StreamAgg`. The `TopN` comparator operates on the original string representation and correctly identifies `''` as smaller than any non‑empty string. The `StreamAgg` then sees the correct minimum value and does not need to encode empty strings internally, thus avoiding the encoding defect.
Contributor guide
Research direction
Start by running the supplied SQL reproduction on TiDB v9.0.0 and tracing the HashAgg execution operator's TEXT-value encoding path described in the report. Compare HashAgg with the Apply/TopN/StreamAgg path; done means the single-table and CTE queries both return an empty-string running MIN from row 6 onward.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100