When the field type is varchar, condition query using IN (numbers) will become ineffective

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 当字段类型为varchar时，条件查询 in （数字），查询条件会失效。

| username: TiDBer_gotDAAy7

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Encountered Issues]
[Reproduction Path] What operations were performed to encounter the issue
[Issue Phenomenon and Impact]
When the field type is varchar, conditional query using in (numbers) will fail, whereas it works normally in MySQL.

[Attachments]

image
image850×371 6.88 KB

image
image831×445 16.5 KB

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: WalterWj | Original post link

It is probably a case issue. You can check the execution plan.
Also, for char type, don’t use int for in.

You can search on Baidu: database char type field, int type matching. There are many discussions on this.

3
| username: TiDBer_gotDAAy7 | Original post link

Yes, but MySQL will automatically convert it.

4
| username: WalterWj | Original post link

This issue only has technical discussion value; actually using it this way would require the application to change its code.

However, if MySQL can handle it, you can raise an issue on the TiDB GitHub and ask the developers to optimize it, brother.

5
| username: TiDBer_gotDAAy7 | Original post link

:ok_hand:

6
| username: OnTheRoad | Original post link

It’s better to standardize SQL. It’s not recommended to use varchar to compare with int in MySQL, let alone TiDB, which is not 100% compatible with MySQL.