Compatibility Issues of Implicit Conversion Between TiDB and MySQL

translator_bot · June 23, 2024, 6:28am

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

Original topic: 关于TIDB与MySQL隐式转换兼容性问题

| username: TiDBer_fy4M8OAI

[TiDB Usage Environment] Online
[TiDB Version] 5.7.25-TiDB-v6.1.0
[Encountered Problem] Due to historical reasons, the foreign key type of the main table is varchar, while the associated key type of the secondary table is Bigint. When performing a join, an implicit conversion occurs, converting both keys to double type for matching, resulting in precision loss and joining to other rows in the secondary table.
[Reproduction Path]
Due to confidentiality of the business database, I created two tables to reproduce the scenario:

CREATE TABLE `tmp_t1`  (
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
INSERT INTO `tmp_t1` VALUES (1295772036504131620);
INSERT INTO `tmp_t1` VALUES (1295772036504131621);

CREATE TABLE `tmp_t2`  (
  `id` bigint(20) NOT NULL,
  `id_f` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
INSERT INTO `tmp_t2` VALUES (11, '1295772036504131620');

[Problem Phenomenon and Impact]
[Query Statement]

SELECT * from tmp_t2 t2 LEFT JOIN tmp_t1 t1 on t2.id_f = t1.id

[Query Result]

|11|1295772036504131620|1295772036504131620|
|11|1295772036504131620|1295772036504131621|

[Personal Thoughts]
I later forced a conversion, and it worked fine. I understand the cause of the issue, and although it stems from some non-standard practices during development, I still want to bring it up. This query runs without issues on MySQL. If a business database is switched from MySQL to TiDB, this problem might not be easily detected and could lead to severe production incidents. TiDB has indeed made many efforts to be compatible with MySQL, and I hope TiDB continues to improve. Small issues like this can damage its reputation, and different companies and individuals may not be able to accept it. Encountering small issues that are not easy to troubleshoot might lead to the perception that the product is immature.

translator_bot · June 23, 2024, 6:28am

| username: yilong | Original post link

Thank you very much for your feedback. We have recorded an issue, and you can follow it.

github.com/pingcap/tidb

TIDB and MySQL implicit conversion results are different

opened 05:41AM - 22 Aug 22 UTC

closed 08:01AM - 23 Aug 22 UTC

yilongrong

type/bug sig/execution severity/minor affects-4.0 affects-5.0 affects-5.1 affects-5.2 affects-5.3 affects-5.4 affects-6.0 affects-6.1 affects-6.2 found/community

## Bug Report Please answer these questions before submitting your issue. Tha…nks! ### 1. Minimal reproduce step (Required) CREATE TABLE tmp_t1 ( id bigint(20) NOT NULL, PRIMARY KEY (id) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact; INSERT INTO tmp_t1 VALUES (1295772036504131620); INSERT INTO tmp_t1 VALUES (1295772036504131621); CREATE TABLE tmp_t2 ( id bigint(20) NOT NULL, id_f varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL, PRIMARY KEY (id) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact; INSERT INTO tmp_t2 VALUES (11, '1295772036504131620'); SELECT * from tmp_t2 t2 LEFT JOIN tmp_t1 t1 on t2.id_f = t1.id; ### 2. What did you expect to see? (Required) +----+---------------------+---------------------+ | id | id_f | id | +----+---------------------+---------------------+ | 11 | 1295772036504131620 | 1295772036504131620 | +----+---------------------+---------------------+ mysql> explain analyze SELECT * from tmp_t2 t2 LEFT JOIN tmp_t1 t1 on t2.id_f = t1.id; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop left join (cost=0.70 rows=1) (actual time=0.095..0.103 rows=1 loops=1) -> Table scan on t2 (cost=0.35 rows=1) (actual time=0.050..0.057 rows=1 loops=1) -> Filter: (cast(t2.id_f as double) = cast(t1.id as double)) (cost=0.35 rows=1) (actual time=0.041..0.041 rows=1 loops=1) -> Single-row covering index lookup on t1 using PRIMARY (id=t2.id_f) (cost=0.35 rows=1) (actual time=0.031..0.031 rows=1 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ### 3. What did you see instead (Required) +----+---------------------+---------------------+ | id | id_f | id | +----+---------------------+---------------------+ | 11 | 1295772036504131620 | 1295772036504131621 | | 11 | 1295772036504131620 | 1295772036504131620 | +----+---------------------+---------------------+ mysql> explain analyze SELECT * from tmp_t2 t2 LEFT JOIN tmp_t1 t1 on t2.id_f = t1.id; +------------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+------------+---------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +------------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+------------+---------+ | HashJoin_12 | 1.25 | 2 | root | | time:11ms, loops:2, build_hash_table:{total:10.6ms, fetch:10.6ms, build:14.9µs}, probe:{concurrency:5, total:53.2ms, max:10.7ms, probe:28.4µs, fetch:53.1ms} | left outer join, equal:[eq(Column#4, Column#5)] | 10.4 KB | 0 Bytes | | ├─Projection_13(Build) | 1.00 | 1 | root | | time:10.5ms, loops:2, Concurrency:OFF | test.tmp_t2.id, test.tmp_t2.id_f, cast(test.tmp_t2.id_f, double BINARY)->Column#4 | 1016 Bytes | N/A | | │ └─TableReader_15 | 1.00 | 1 | root | | time:10.5ms, loops:2, cop_task: {num: 1, max: 10.6ms, proc_keys: 1, rpc_num: 1, rpc_time: 10.6ms, copr_cache_hit_ratio: 0.00} | data:TableFullScan_14 | 250 Bytes | N/A | | │ └─TableFullScan_14 | 1.00 | 1 | cop[tikv] | table:t2 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 55, total_keys: 2, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo | N/A | N/A | | └─Projection_16(Probe) | 2.00 | 2 | root | | time:10.6ms, loops:2, Concurrency:OFF | test.tmp_t1.id, cast(test.tmp_t1.id, double BINARY)->Column#5 | 380 Bytes | N/A | | └─TableReader_18 | 2.00 | 2 | root | | time:10.6ms, loops:2, cop_task: {num: 1, max: 10.7ms, proc_keys: 2, rpc_num: 1, rpc_time: 10.7ms, copr_cache_hit_ratio: 0.00} | data:TableFullScan_17 | 215 Bytes | N/A | | └─TableFullScan_17 | 2.00 | 2 | cop[tikv] | table:t1 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 2, total_process_keys_size: 54, total_keys: 3, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo | N/A | N/A | +------------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+------------+---------+ 7 rows in set (0.02 sec) ### 4. What is your TiDB version? (Required) 6.1.0

Once there is a response to this issue, you can directly check the replies in the issue. Thank you.

translator_bot · June 23, 2024, 6:28am

| username: 张雨齐0720 | Original post link

Mark it.

translator_bot · June 23, 2024, 6:28am

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.