planner: preserve IN for varchar-vs-numeric comparisons with a common cmp type
- 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
Assessment
This issue has not been assessed yet.