TiFlash Runtime Filter returns wrong results with signed/unsigned integer JOIN key type mismatch
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
### 1. Minimal reproduce step (Required)
We observed this issue in a production environment with complex queries. A minimal reproduction case has not been established, but the issue consistently occurs under the following conditions:
**Environment Setup:**
- Tables with JOIN keys having type mismatch: `INT` (signed) vs `INT UNSIGNED`
- 3+ tables joined together
- TiFlash replicas enabled
- Runtime Filter enabled (default)
**Schema Example:**
```sql
-- Table A has: column_fk INT (signed)
-- Table B has: id INT UNSIGNED
-- JOIN condition: A.column_fk = B.id
```
**Query Pattern:**
```sql
SET tidb_isolation_read_engines = 'tiflash';
SET tidb_enforce_mpp = 1;
SELECT COUNT(*) FROM
table_a
INNER JOIN table_b ON table_a.fk_id = table_b.id -- type mismatch here
INNER JOIN table_c ON table_b.other_fk = table_c.id
WHERE
table_a.deleted_at IS NULL
AND table_b.deleted_at IS NULL
AND table_c.deleted_at IS NULL
AND ;
```
**Note:** Simple 3-table JOINs with small datasets do not reproduce the issue. The problem appears to depend on data distribution, query complexity, and execution plan selection.
### 2. What did you expect to see? (Required)
TiFlash should return the same result as TiKV regardless of the `tidb_runtime_filter_mode` setting.
Expected: **36 rows** (same as TiKV)
### 3. What did you see instead (Required)
With Runtime Filter enabled (default):
- TiFlash returns **17-20 rows** (fluctuating, incorrect)
With Runtime Filter disabled:
```sql
SET tidb_runtime_filter_mode = 'OFF';
```
- TiFlash returns **36 rows** (correct, matches TiKV)
The EXPLAIN output shows Runtime Filter is generated from the signed INT column and applied to the unsigned INT column:
```
HashJoin: inner join,
equal:[eq(table_a.fk_id, table_b.id)],
runtime filter:0[IN] <- table_a.fk_id
```
### 4. What is your TiDB version? (Required)
```
TiDB: v8.1.1
TiFlash: v8.1.1
```
### Additional Information
**Workaround:**
```sql
SET tidb_runtime_filter_mode = 'OFF';
```
**Difference from #59877:**
| Aspect | #59877 | This Issue |
|--------|--------|------------|
| Root Cause | Fine Grained Shuffle | Runtime Filter |
| Workaround | `tiflash_fine_grained_shuffle_stream_count = -1` | `tidb_runtime_filter_mode = 'OFF'` |
| #59877 workaround helps? | Yes | **No** |
We confirmed that `tiflash_fine_grained_shuffle_stream_count = -1` does **NOT** resolve this issue, indicating a different code path.
**We can provide:**
- Sanitized EXPLAIN ANALYZE output
- Table schema (with anonymized names)
- Additional debugging information if requested
Contributor guide
Assessment
This issue has not been assessed yet.