Result inconsistency between TiDB and TiKV for expression ~(NULLIF(~DECIMAL, '')) during predicate pushdown
- 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
Assessment
This issue has not been assessed yet.