pingcap / pingcap/tidb

Incorrect `CAST(NULL AS var_string)` Semantics in `HAVING` Rewritten as MergeJoin `other cond` Causes Empty Result

Open
#69,294 2 comments 0 reactions 0 assignees View on GitHub
contribution may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/critical sig/planner type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.