pingcap / pingcap/tidb

Result inconsistency between TiDB and TiKV for expression ~(NULLIF(~DECIMAL, '')) during predicate pushdown

Open
#66,923 2 comments 0 reactions 0 assignees View on GitHub
contribution may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/execution 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!

Both queries should return **1 row** (`1084540452`).

**Logic Trace for `c1 = 0`:**
1. `~t0.c1` -> `~0` is `-1` (in 64-bit integer context).
2. `NULLIF(-1, '')` -> Compares `-1` with `''`. Since `''` converts to `0` in numeric context, `-1 != 0`, so it returns `-1`.
3. `~(-1)` -> Bitwise NOT of `-1` (all ones) is `0`.
4. Result: `0` is treated as `FALSE`. The row with `c1 = 0` should be filtered out.

### **What is actually happening?**
* **Query 1** mistakenly returns **2 rows**, including `c1 = 0`. The execution plan shows the predicate is pushed down to **TiKV (coprocessor)**. This suggests the Rust implementation of this complex expression evaluates to a non-zero value for `0`.
* **Query 2** correctly returns **1 row**. The filtering happens at the **TiDB (root)** layer using Go logic, which correctly evaluates the expression to `0` (FALSE).

#### **Comparison with MySQL / MariaDB**
I have tested the same schema and data on **MySQL 9.2.0** and **MariaDB 11.8.5**:
* **MySQL Result:** Both 1 rows (1084540452)
* **MariaDB Result:** Both 1 rows (1084540452)

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE t0(c1 DECIMAL NOT NULL UNIQUE , c2 BOOL NOT NULL );

INSERT INTO t0 VALUES (0, true), (1084540452, false);

-- cardinality: 2
SELECT t0.c1 FROM t0 GROUP BY t0.c1 HAVING ~(NULLIF(~t0.c1, ''));
-- cardinality: 1
SELECT ref0 FROM (SELECT t0.c1 AS ref0, ~(NULLIF(~t0.c1, '')) AS ref1 FROM t0 GROUP BY t0.c1) AS s WHERE ref1;

```
### 2. What did you expect to see? (Required)
```shell
mysql> SELECT t0.c1 FROM t0 GROUP BY t0.c1 HAVING ~(NULLIF(~t0.c1, ''));
+------------+
| c1 |
+------------+
| 1084540452 |
+------------+
1 row in set (0.01 sec)

mysql> SELECT ref0 FROM (SELECT t0.c1 AS ref0, ~(NULLIF(~t0.c1, '')) AS ref1 FROM t0 GROUP BY t0.c1) AS s WHERE ref1;
+------------+
| ref0 |
+------------+
| 1084540452 |
+------------+
1 row in set (0.00 sec)
```
### 3. What did you see instead (Required)
```shell
mysql> SELECT t0.c1 FROM t0 GROUP BY t0.c1 HAVING ~(NULLIF(~t0.c1, ''));
+------------+
| c1 |
+------------+
| 0 |
| 1084540452 |
+------------+
2 rows in set, 4 warnings (0.00 sec)

mysql> SELECT ref0 FROM (SELECT t0.c1 AS ref0, ~(NULLIF(~t0.c1, '')) AS ref1 FROM t0 GROUP BY t0.c1) AS s WHERE ref1;
+------------+
| ref0 |
+------------+
| 1084540452 |
+------------+
1 row in set, 2 warnings (0.00 sec)
```
### 4. What is your TiDB version? (Required)

```shell
mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.5.5
Edition: Community
Git Commit Hash: 1fa258b833ff113883beeba40bc130be7ce66610
Git Branch: HEAD
UTC Build Time: 2026-01-14 22:20:57
GoVersion: go1.25.5
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

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.