Open wjhuang2016 opened 2 hours ago
Can you also paste the explain anlyze result for the two queries?
mysql> explain analyze SELECT /*+ read_from_storage(tiflash[ t9d8bedfb ]) */ `t4f752381`.`col_84` AS `r0` FROM (`t4f752381`) JOIN `t9d8bedfb` WHERE `t9d8bedfb`.`col_4`<'[4463142022109750732]';
+------------------------------+---------+---------+--------------+----------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+---------+---------+--------------+----------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+-----------+---------+
| HashJoin_13 | 888.00 | 1080 | root | | time:7.48ms, loops:3, RU:0.539676, build_hash_table:{total:688.7µs, fetch:679.6µs, build:9.13µs}, probe:{concurrency:5, total:36.4ms, max:7.38ms, probe:155.4µs, fetch and wait:36.2ms} | CARTESIAN inner join | 9.46 KB | 0 Bytes |
| ├─IndexReader_33(Build) | 30.00 | 30 | root | | time:635.6µs, loops:2, cop_task: {num: 1, max: 620.6µs, proc_keys: 30, tot_proc: 70.9µs, tot_wait: 154.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.88µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:606.9µs}} | index:IndexFullScan_32 | 498 Bytes | N/A |
| │ └─IndexFullScan_32 | 30.00 | 30 | cop[tikv] | table:t4f752381, index:PRIMARY(col_84) | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 30, total_process_keys_size: 1320, total_keys: 31, get_snapshot_time: 121.9µs, rocksdb: {key_skipped_count: 30, block: {}}}, time_detail: {total_process_time: 70.9µs, total_wait_time: 154.3µs, tikv_wall_time: 368.1µs} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_26(Probe) | 29.60 | 36 | root | | time:7.21ms, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_25 | 2.22 KB | N/A |
| └─ExchangeSender_25 | 29.60 | 36 | mpp[tiflash] | | tiflash_task:{time:8.58ms, loops:1, threads:72} | ExchangeType: PassThrough | N/A | N/A |
| └─Selection_24 | 29.60 | 36 | mpp[tiflash] | | tiflash_task:{time:6.08ms, loops:1, threads:72} | lt(test.t9d8bedfb.col_4, cast("[4463142022109750732]", json BINARY)) | N/A | N/A |
| └─TableFullScan_23 | 37.00 | 37 | mpp[tiflash] | table:t9d8bedfb | tiflash_task:{time:5.75ms, loops:1, threads:72}, tiflash_scan:{mvcc_input_rows:37, mvcc_input_bytes:1369, mvcc_output_rows:37, lm_skip_rows:0, local_regions:1, remote_regions:0, tot_learner_read:0ms, region_balance:{instance_num: 1, max/min: 1/1=1.000000}, delta_rows:37, delta_bytes:4931, segments:1, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:0ms, tot_build_inputstream:0ms, min_local_stream:1ms, max_local_stream:3ms, dtfile:{data_scanned_rows:37, data_skipped_rows:0, mvcc_scanned_rows:37, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:0ms}} | pushed down filter:empty, keep order:false, stats:pseudo | N/A | N/A |
+------------------------------+---------+---------+--------------+----------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+-----------+---------+
7 rows in set (0.01 sec)
mysql> explain analyze SELECT /*+ read_from_storage(tikv[ t9d8bedfb ]) */ `t4f752381`.`col_84` AS `r0` FROM (`t4f752381`) JOIN `t9d8bedfb` WHERE `t9d8bedfb`.`col_4`<'[4463142022109750732]';
+-----------------------------+---------+---------+-----------+----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------+---------+---------+-----------+----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+-----------+---------+
| HashJoin_13 | 888.00 | 0 | root | | time:870µs, loops:1, RU:1.104567, build_hash_table:{total:739µs, fetch:727.8µs, build:11.2µs} | CARTESIAN inner join | 9.46 KB | 0 Bytes |
| ├─IndexReader_24(Build) | 30.00 | 30 | root | | time:660.3µs, loops:2, cop_task: {num: 1, max: 656.1µs, proc_keys: 30, tot_proc: 68.5µs, tot_wait: 170.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.5µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:621.6µs}} | index:IndexFullScan_23 | 497 Bytes | N/A |
| │ └─IndexFullScan_23 | 30.00 | 30 | cop[tikv] | table:t4f752381, index:PRIMARY(col_84) | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 30, total_process_keys_size: 1320, total_keys: 31, get_snapshot_time: 111.6µs, rocksdb: {key_skipped_count: 30, block: {}}}, time_detail: {total_process_time: 68.5µs, total_wait_time: 170.9µs, tikv_wall_time: 393.4µs} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_17(Probe) | 29.60 | 0 | root | | time:752.5µs, loops:1, cop_task: {num: 1, max: 795.3µs, proc_keys: 37, tot_proc: 131.2µs, tot_wait: 169.7µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.2µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:750.7µs}} | data:Selection_16 | 245 Bytes | N/A |
| └─Selection_16 | 29.60 | 0 | cop[tikv] | | tikv_task:{time:1ms, loops:2}, scan_detail: {total_process_keys: 37, total_process_keys_size: 4447, total_keys: 38, get_snapshot_time: 118.6µs, rocksdb: {key_skipped_count: 37, block: {}}}, time_detail: {total_process_time: 131.2µs, total_wait_time: 169.7µs, tikv_wall_time: 498.3µs} | lt(test.t9d8bedfb.col_4, cast("[4463142022109750732]", json BINARY)) | N/A | N/A |
| └─TableFullScan_15 | 37.00 | 37 | cop[tikv] | table:t9d8bedfb | tikv_task:{time:0s, loops:2} | keep order:false, stats:pseudo | N/A | N/A |
+-----------------------------+---------+---------+-----------+----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+-----------+---------+
6 rows in set (0.00 sec)
@windtalker
I think the root cause is TiFlash actually not support compare between 2 json object, In TiFlash, it just compare the json using string compare.
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
Empty row.
3. What did you see instead (Required)
4. What is your TiDB version? (Required)
3d42e344f33f32b21162aedb941cab96c3e47422