planner: output a warning if implicit join key type conversion happend
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
See the example below, MySQL outputs some warnings to notify the user of the implicit type conversion on `t_int.id = t_varchar.id`, but TiDB doesn't have such warnings:
```
CREATE TABLE t_int (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
val VARCHAR(100),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE t_varchar (
id VARCHAR(20) NOT NULL PRIMARY KEY, -- THE TYPE IS VARCHAR
info TEXT,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
TiDB [test]> EXPLAIN ANALYZE
-> SELECT COUNT(*)
-> FROM t_int
-> JOIN t_varchar ON t_int.id = t_varchar.id;
+--------------------------------+----------+---------+-----------+-------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------+----------+---------+-----------+-------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+---------+
| HashAgg_12 | 1.00 | 1 | root | | time:16.1ms, loops:2, RU:20.239984, partial_worker:{wall_time:16.038683ms, concurrency:5, task_num:5, tot_wait:79.548356ms, tot_exec:350.341µs, tot_time:79.908775ms, max:16.030858ms, p95:16.030858ms}, final_worker:{wall_time:16.052659ms, concurrency:5, task_num:5, tot_wait:80.18886ms, tot_exec:12.354µs, tot_time:80.203542ms, max:16.044875ms, p95:16.044875ms} | funcs:count(?)->Column#7 | 169.1 KB | N/A |
| └─HashJoin_15 | 1078.75 | 5000 | root | | time:16ms, loops:6, build_hash_table:{total:15.2ms, fetch:13.7ms, build:1.55ms}, probe:{concurrency:5, total:78.9ms, max:15.9ms, probe:2.03ms, fetch:76.8ms} | inner join, equal:[eq(Column#8, Column#9)] | 839.3 KB | 0 Bytes |
| ├─Projection_16(Build) | 863.00 | 10000 | root | | time:14.2ms, loops:12, Concurrency:OFF | cast(test.t_int.id, double BINARY)->Column#8 | 8.24 KB | N/A |
| │ └─TableReader_18 | 863.00 | 10000 | root | | time:14.1ms, loops:12, cop_task: {num: 6, max: 2.68ms, min: 2.21ms, avg: 2.44ms, p95: 2.68ms, max_proc_keys: 3248, p95_proc_keys: 3248, tot_proc: 6ms, copr_cache_hit_ratio: 0.00, build_task_duration: 2.56µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:6, total_time:14.6ms}} | data:TableFullScan_17 | 49.6 KB | N/A |
| │ └─TableFullScan_17 | 863.00 | 10000 | cop[tikv] | table:t_int | tikv_task:{proc max:2ms, min:1ms, avg: 1.17ms, p80:1ms, p95:2ms, iters:33, tasks:6}, scan_detail: {total_process_keys: 10000, total_process_keys_size: 528841, total_keys: 10006, get_snapshot_time: 22.5µs, rocksdb: {block: {}}} | keep order:false | N/A | N/A |
| └─Projection_21(Probe) | 10000.00 | 5000 | root | | time:4.57ms, loops:7, Concurrency:5 | cast(test.t_varchar.id, double BINARY)->Column#9 | 95.8 KB | N/A |
| └─IndexReader_25 | 10000.00 | 5000 | root | | time:8.37ms, loops:7, cop_task: {num: 5, max: 2.15ms, min: 1.38ms, avg: 1.67ms, p95: 2.15ms, max_proc_keys: 2016, p95_proc_keys: 2016, copr_cache_hit_ratio: 0.00, build_task_duration: 1.85µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:5, total_time:8.33ms}} | index:IndexFullScan_24 | 38.4 KB | N/A |
| └─IndexFullScan_24 | 10000.00 | 5000 | cop[tikv] | table:t_varchar, index:idx_t_varchar_info(info) | tikv_task:{proc max:1ms, min:0s, avg: 400µs, p80:1ms, p95:1ms, iters:24, tasks:5}, scan_detail: {total_process_keys: 5000, total_process_keys_size: 324109, total_keys: 5005, get_snapshot_time: 17.9µs, rocksdb: {block: {}}} | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------+----------+---------+-----------+-------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+---------+
mysql> EXPLAIN ANALYZE SELECT COUNT(*) FROM t_int JOIN t_varchar ON t_int.id = t_varchar.id;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0) (cost=0.80 rows=1) (actual time=0.043..0.043 rows=1 loops=1)
-> Inner hash join (cast(t_int.id as double) = cast(t_varchar.id as double)) (cost=0.70 rows=1) (actual time=0.039..0.039 rows=0 loops=1)
-> Index scan on t_varchar using PRIMARY (cost=0.35 rows=1) (never executed)
-> Hash
-> Covering index scan on t_int using PRIMARY (cost=0.35 rows=1) (actual time=0.029..0.029 rows=0 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'id' |
| Warning | 1739 | Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'id' |
+---------+------+----------------------------------------------------------------------------------------------+
```
Contributor guide
Assessment
This issue has not been assessed yet.