pingcap / pingcap/tidb

TiFlash Runtime Filter returns wrong results with signed/unsigned integer JOIN key type mismatch

Open
#65,647 7 comments 0 reactions 0 assignees View on GitHub
contribution first-time-contributor
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

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.