incorrect query result using tiflash

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t4f752381` (
  `col_84` double NOT NULL DEFAULT '7963.446089530935',
  PRIMARY KEY (`col_84`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `t4f752381` VALUES(7308.317427630863),(7286.125599800909),(5665.897534377155),(1860.600827669093),(5239.924698492964),(5474.786681302416),(7145.109819918936),(4892.91486481474),(3902.3910726741133),(5543.99967209901),(3269.7484327172638),(2512.243623238321),(3903.5329413232585),(1697.3601667906523),(736.2973285027063),(8501.460124553165),(4041.146836592681),(2517.289387520804),(7662.081285351392),(9914.48608993605),(17.02105052235986),(5845.929962163885),(784.1697107396376),(9382.509995531178),(6228.580145158482),(5725.646685741635),(2523.048623301618),(3398.8160070323447),(7824.684029838521),(7738.620129960638);

CREATE TABLE `t9d8bedfb` (
  `col_4` json DEFAULT NULL,
  `col_5` datetime NOT NULL DEFAULT '2031-03-20 00:00:00',
  `col_6` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `col_7` date NOT NULL DEFAULT '2021-02-20',
  PRIMARY KEY (`col_6`(2),`col_5`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_5` (`col_5`,`col_6`(5)),
  KEY `idx_6` (`col_6`(5)),
  UNIQUE KEY `idx_7` (`col_6`(4),`col_5`,`col_7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `t9d8bedfb` VALUES('[3823207007103953128, 3521659414669231622, 603681004249945696]','2008-03-31 00:00:00','%@dQFgiHZq','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','%gKSn','2000-10-23'),('[1258034336034663004, 8519705967827423825, 8440203252631635833, 6777885057568481590]','2029-05-26 00:00:00','&vd','1978-11-30'),('[6124600711957419751, 7435283962184760671, 6955712530425243058]','1974-09-02 00:00:00','(ZuIu~uF%LK!HV+!','2030-10-03'),('[4772518407846627162, 1238027569028631284, 2063390360436765112, 148746451875279651]','2021-12-19 00:00:00','+jUL51X-&L~%$g#p','1971-09-05'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','-37','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','-AMCE9ZxM&u9-3(FoiU','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','3s2zKF+m','2000-10-23'),('[2, 3700628637908574041, 4629163391738847761, 2475065351568534368, 3302061369087435246]','1987-11-05 00:00:00','91nHC#Ceu3l','2019-10-14'),('[2257310573072974120, 5534891517598033450, 749041761366921108]','2003-03-05 00:00:00','=MbjhdMbmU1kur7','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','A(B_lG=UPS^_1!h$1X','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','A+2Opb&(YL!Qzipq','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','ADRYV8B7gMGXc1Wn','2000-10-23'),('[1336585235067508756, 347769040778416317, 1808234183492636802]','1992-06-23 00:00:00','AvY5kIrpq7b_jAXchP2','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','Cb%O7kFLade32ClEo-8','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','El5(+UD3r%Z','2000-10-23'),('[7364323960479768747, 8554247212835118690, -9223372036854775808, 894016727057775158]','1990-02-02 00:00:00','F','2000-10-23'),('[6301734481466030866, 3331061268276480179, 2751705280420411487, 170902329290927303]','1990-12-29 00:00:00','F-fI0+c_HH','2000-10-23'),('[-9223372036854775807, 7341680929414713407, 1]','1984-08-27 00:00:00','GF(!h@X*ag','2000-10-23'),('[1]','2022-08-07 00:00:00','JVBbfk$F','2006-09-13'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','N5LxjU3&WO#@','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','NwNU','2000-10-23'),('[619522859061628475, 5325258998393808522, 3780004921925370218]','2030-05-15 00:00:00','O2A','2000-10-23'),('[6680739630567195494]','2028-02-24 00:00:00','OJ^Fvbku)Z0','1988-07-17'),('[1927916345662391968, 3031157399134681956, 9037265087086485093, 9223372036854775807, 7305829863390571285]','2008-05-22 00:00:00','P9z4A^C=','2000-10-23'),('[4310682512717636261, 3158680371582354758, 6003006996642915917, 1212905306257550674, 3988304947067061314]','1991-02-26 00:00:00','SVPWbP+)','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','SvYcEF2qL7w)yZ63zVq','2000-10-23'),('[8438924761655609198, 6437537405665148658]','2024-01-15 00:00:00','ToYIPQ9_IYbO!5T','2022-09-11'),(NULL,'1988-09-09 00:00:00','i&Tqt*CO#n_tO%3SpX','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','isVmW3%II&2djX','2000-10-23'),('[1471382583821350205, 7473809970765496862, 9127327793696368034, 5057380129306389251, 3374255477286837606]','1997-11-26 00:00:00','kB9!&^Arduea0f','2010-08-04'),('[7392026808512571304, -1, 8383774031805333773, 4166773374314266455]','2006-08-22 00:00:00','lnDP+%NBud*BSTJe','1972-12-02'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','ot_8ioQ#Dx%EAb','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','pFizzPw)_a','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','sUm#m)UP_','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','t6F!xc1bkURAz%','2000-10-23'),('[7258638647232089245, 3865338076838745846, 1401158343473850290]','1988-09-09 00:00:00','zRy$7AK=T2Nw','2000-10-23');

SELECT /*+ read_from_storage(tiflash[ t9d8bedfb ]) */ `t4f752381`.`col_84` AS `r0` FROM (`t4f752381`) JOIN `t9d8bedfb` WHERE `t9d8bedfb`.`col_4`<'[4463142022109750732]';

2. What did you expect to see? (Required)

Empty row.

3. What did you see instead (Required)

mysql> SELECT /*+ read_from_storage(tiflash[ t9d8bedfb ]) */ `t4f752381`.`col_84` AS `r0` FROM (`t4f752381`) JOIN `t9d8bedfb` WHERE `t9d8bedfb`.`col_4`<'[4463142022109750732]';

...

1080 rows in set (0.01 sec)

mysql> SELECT /*+ read_from_storage(tikv[ t9d8bedfb ]) */ `t4f752381`.`col_84` AS `r0` FROM (`t4f752381`) JOIN `t9d8bedfb` WHERE `t9d8bedfb`.`col_4`<'[4463142022109750732]';
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

3d42e34

[windtalker]

Can you also paste the explain anlyze result for the two queries?

[JinheLin]

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

[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.