pingcap / pingcap/tidb

planner: preserve IN for varchar-vs-numeric comparisons with a common cmp type

Open
#67,498 0 comments 0 reactions 0 assignees View on GitHub
component/expression may-affects-8.5 report/customer severity/moderate 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
CREATE TABLE test_data (
id VARCHAR(10),
col_a VARCHAR(20),
col_b INT,
col_c DECIMAL(10,2),
col_d VARCHAR(30)
);

INSERT INTO test_data VALUES
('1', 'alpha', 1, 1.00, 'x'),
('2', 'beta', 2, 2.00, 'y'),
('3', 'gamma', 3, 3.00, 'z');

EXPLAIN FORMAT = 'plan_tree'
SELECT col_a, col_b
FROM test_data
WHERE id IN (1, 2, 3);
```

A larger real-world shape is a generated SQL with a long numeric literal list against a string column, for example:

```sql
EXPLAIN ANALYZE
SELECT col_a, col_b
FROM test_data
WHERE id IN (501,502,503,504,505, ...);
```

### 2. What did you expect to see? (Required)

TiDB should preserve a single `IN` predicate while keeping MySQL comparison semantics.

Since MySQL compares `VARCHAR` vs numeric literals as `DOUBLE`, the expected predicate shape is:

```sql
in(cast(test.test_data.id, double BINARY), 1, 2, 3)
```

This keeps the expression compact even for long `IN` lists.

### 3. What did you see instead (Required)

TiDB rewrites the `IN` predicate into a DNF of equality predicates:

```sql
or(
eq(cast(test.test_data.id, double BINARY), 1),
eq(cast(test.test_data.id, double BINARY), 2),
eq(cast(test.test_data.id, double BINARY), 3)
)
```

For long `IN` lists this produces a very large expression tree in the planner, even though every element uses the same comparison type.

Warning: even after this planner fix, `VARCHAR col IN (numeric literals...)` is still a non-ideal SQL shape for index usage. The comparison has to follow MySQL's mixed-type semantics and usually cannot use the string index as efficiently as a type-aligned predicate. Users should still align the literal type with the column type whenever possible, for example compare `VARCHAR` columns with string literals instead of numeric literals.

The root cause is that `IN` is only preserved when the comparison type is exactly the left operand's eval type. In this case the comparison type is consistently `DOUBLE`, but the left operand is `VARCHAR`, so TiDB unnecessarily expands it to OR-of-EQ.

### 4. What is your TiDB version? (Required)

Observed on `master` at commit `e07318bec6a3d7fd9b3be66c423eeafe25688eaf`.

Suggested labels: `type/bug`, `component/expression`, `sig/planner`, `severity/moderate`, `may-affects-8.5`

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.