Incorrect `CAST(NULL AS var_string)` Semantics in `HAVING` Rewritten as MergeJoin `other cond` Causes Empty Result
- 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_tidb615_db16_min;
CREATE DATABASE repro_tidb615_db16_min;
USE repro_tidb615_db16_min;
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';
SET time_zone='+00:00';
CREATE TABLE src(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 DATETIME NULL
);
INSERT INTO src(c0) VALUES (NULL);
CREATE TABLE l(
id BIGINT NOT NULL PRIMARY KEY
);
CREATE TABLE r(
id BIGINT NOT NULL PRIMARY KEY,
c0 DATETIME NULL
);
INSERT INTO l SELECT id FROM src;
INSERT INTO r SELECT id, c0 FROM src;
-- Single-table: returns 1 row
SELECT c0, id, 1168174079 AS marker
FROM src
WHERE BINARY(id > id) < '}'
GROUP BY c0, id
HAVING (
CASE id
WHEN 'h' THEN c0 IS NULL
WHEN id THEN c0
ELSE NULL
END
) IS NULL
ORDER BY id;
-- Split/join via derived table: incorrectly returns empty set
SELECT v.c0, v.id, 1168174079 AS marker
FROM (
SELECT l.id, r.c0
FROM l JOIN r ON l.id = r.id
) v
WHERE BINARY(v.id > v.id) < '}'
GROUP BY v.c0, v.id
HAVING (
CASE v.id
WHEN 'h' THEN v.c0 IS NULL
WHEN v.id THEN v.c0
ELSE NULL
END
) IS NULL
ORDER BY v.id;
```
### 2. What Did You Expect to See? (Required)
Both queries operate on identical data (`id=1`, `c0=NULL`). The `WHERE` condition `BINARY(id > id) < '}'` evaluates to `TRUE`.
The `HAVING` condition:
```
CASE id WHEN 'h' THEN c0 IS NULL WHEN id THEN c0 ELSE NULL END
```
For `id=1`: branch `id = 'h'` is false, branch `id = id` (`1 = 1`) is true, so the `CASE` returns `c0`, which is `NULL`. Then `NULL IS NULL` is `TRUE`.
Therefore both queries should return:
```
+------+----+------------+
| c0 | id | marker |
+------+----+------------+
| NULL | 1 | 1168174079 |
+------+----+------------+
```
### 3. What Did You See Instead? (Required)
- Single-table query: returns the row as expected.
- Join query: returns `Empty set` (wrong).
### 4. What Is Your TiDB Version? (Required)
Version: TiDB-v9.0.0
### 5. Execution Plan Differences
**Single-table (correct)** — `HAVING` pushed down to TiKV coprocessor as a selection:
```
Selection_16 [cop[tikv]] isnull(case(...)), lt(cast(gt(src.id, src.id), binary(1)), "}")
```
The `CASE` expression operates directly on `src.c0` (type `DATETIME`) and the `IS NULL` check evaluates correctly.
**Split/join (wrong)** — `HAVING` becomes `other cond` of `MergeJoin` and the expression is rewritten:
```
MergeJoin_52 [root] other cond: isnull(case(... cast(r.c0, var_string(19)) ...))
├─Selection_36 [cop[tikv] on r] lt(from_binary(cast(gt(r.id, r.id), binary(1))), "}")
└─Selection_33 [cop[tikv] on l] lt(cast(gt(l.id, l.id), binary(1)), "}")
```
Two critical changes:
- The `CASE` expression inside `HAVING` now includes an explicit `cast(r.c0, var_string(19))` around the column reference.
- On the Build side (`r` table) the `BINARY` predicate is incorrectly wrapped with `from_binary()` (a separate, but not the primary cause of the empty result).
### 6. Root Cause
**Primary cause:** When the query is rewritten with a join (via the derived table `v`), the `HAVING` clause can no longer be pushed down to a single coprocessor scan. Instead it becomes a filter condition attached to the `MergeJoin` operator (`other cond`). During this transformation, the expression inside the `CASE` is altered: the reference to `c0` (originally `DATETIME`) is explicitly cast to `var_string(19)`. This cast changes the `NULL` semantics:
- In the single-table path, `c0` is `NULL` of type `DATETIME`, and the `CASE` returns `NULL`.
- In the join path, `cast(NULL AS var_string(19))` may be evaluated as an empty string `''` (or some non‑`NULL` value) by the expression evaluator used for `other cond`. Consequently the `CASE` returns `''` instead of `NULL`, and `'' IS NULL` is `FALSE`, filtering out the row.
The secondary issue with `from_binary()` on the Build side (similar to a previously reported bug) does not directly cause the empty result because the condition `from_binary(cast(...)) < '}'` still evaluates to true, but it highlights additional expression rewriting inconsistency.
Contributor guide
Assessment
This issue has not been assessed yet.