Join result is inconsistent with TiDB #6393
Assignees
Labels
Comments
|
tiflash explain analyze: +------------------------------------------+----------+---------+--------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------+----------+---------+--------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashAgg_20 | 1.00 | 1 | root | | time:128.8ms, loops:2, partial_worker:{wall_time:128.735061ms, concurrency:5, task_num:13, tot_wait:629.996924ms, tot_exec:767.82µs, tot_time:630.777872ms, max:126.160113ms, p95:126.160113ms}, final_worker:{wall_time:0s, concurrency:5, task_num:5, tot_wait:630.837598ms, tot_exec:15.427µs, tot_time:630.85755ms, max:126.176158ms, p95:126.176158ms} | funcs:count(1)->Column#70 | 153.3 KB | N/A |
| └─HashJoin_25 | 54000.00 | 7902 | root | | time:128.5ms, loops:14, build_hash_table:{total:59.2ms, fetch:59.2ms, build:21.5µs}, probe:{concurrency:5, total:582.7ms, max:125.7ms, probe:21.6ms, fetch:561.1ms} | CARTESIAN right outer join, other cond:ge(test.table_20_latin1_undef.col_double_undef_unsigned, cast(test.table_30_latin1_6.col_enum_key_signed, double BINARY)) | 20.8 KB | 0 Bytes |
| ├─PartitionUnion_40(Build) | 30.00 | 30 | root | | time:59.2ms, loops:7 | | N/A | N/A |
| │ ├─TableReader_43 | 5.00 | 5 | root | | time:25.3ms, loops:2, cop_task: {num: 1, max: 25.1ms, proc_keys: 0, rpc_num: 1, rpc_time: 25.1ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_42 | 274 Bytes | N/A |
| │ │ └─TableFullScan_42 | 5.00 | 5 | cop[tiflash] | table:t3, partition:p0 | tiflash_task:{time:18ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
| │ ├─TableReader_45 | 5.00 | 5 | root | | time:45.2ms, loops:2, cop_task: {num: 1, max: 44.9ms, proc_keys: 0, rpc_num: 1, rpc_time: 44.9ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_44 | 246 Bytes | N/A |
| │ │ └─TableFullScan_44 | 5.00 | 5 | cop[tiflash] | table:t3, partition:p1 | tiflash_task:{time:39.3ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
| │ ├─TableReader_47 | 5.00 | 5 | root | | time:45.2ms, loops:2, cop_task: {num: 1, max: 44.8ms, proc_keys: 0, rpc_num: 1, rpc_time: 44.8ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_46 | 274 Bytes | N/A |
| │ │ └─TableFullScan_46 | 5.00 | 5 | cop[tiflash] | table:t3, partition:p2 | tiflash_task:{time:37.5ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
| │ ├─TableReader_49 | 5.00 | 5 | root | | time:45.2ms, loops:2, cop_task: {num: 1, max: 44.8ms, proc_keys: 0, rpc_num: 1, rpc_time: 44.8ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_48 | 264 Bytes | N/A |
| │ │ └─TableFullScan_48 | 5.00 | 5 | cop[tiflash] | table:t3, partition:p3 | tiflash_task:{time:39.7ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
| │ ├─TableReader_51 | 5.00 | 5 | root | | time:45ms, loops:2, cop_task: {num: 1, max: 44.6ms, proc_keys: 0, rpc_num: 1, rpc_time: 44.5ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_50 | 282 Bytes | N/A |
| │ │ └─TableFullScan_50 | 5.00 | 5 | cop[tiflash] | table:t3, partition:p4 | tiflash_task:{time:36.8ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
| │ └─TableReader_53 | 5.00 | 5 | root | | time:33.7ms, loops:2, cop_task: {num: 1, max: 33.6ms, proc_keys: 0, rpc_num: 1, rpc_time: 33.6ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_52 | 274 Bytes | N/A |
| │ └─TableFullScan_52 | 5.00 | 5 | cop[tiflash] | table:t3, partition:p5 | tiflash_task:{time:8.8ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_35(Probe) | 1800.00 | 1194 | root | | time:110.5ms, loops:3, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_34 | N/A | N/A |
| └─ExchangeSender_34 | 1800.00 | 1194 | mpp[tiflash] | | tiflash_task:{time:89.8ms, loops:1, threads:4} | ExchangeType: PassThrough | N/A | N/A |
| └─HashJoin_27 | 1800.00 | 1194 | mpp[tiflash] | | tiflash_task:{time:67.2ms, loops:1, threads:4} | CARTESIAN left outer join, other cond:div(cast(test.table_90_utf8_undef.col_bigint_undef_unsigned, decimal(20,0) UNSIGNED BINARY), test.table_20_latin1_undef.col_decimal(40, 20)_key_signed) | N/A | N/A |
| ├─ExchangeReceiver_33(Build) | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{time:39.2ms, loops:1, threads:4} | | N/A | N/A |
| │ └─ExchangeSender_32 | 20.00 | 20 | mpp[tiflash] | | tiflash_task:{time:17.4ms, loops:1, threads:1} | ExchangeType: Broadcast | N/A | N/A |
| │ └─TableFullScan_31 | 20.00 | 20 | mpp[tiflash] | table:t2 | tiflash_task:{time:17.3ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableFullScan_30(Probe) | 90.00 | 90 | mpp[tiflash] | table:t1 | tiflash_task:{time:40.3ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------------------+----------+---------+--------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
|
|
tidb explain analyze +----------------------------------+----------+---------+-----------+------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+----------+---------+-----------+------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
| HashAgg_17 | 1.00 | 1 | root | | time:46.4ms, loops:2, partial_worker:{wall_time:46.341991ms, concurrency:5, task_num:14, tot_wait:202.451298ms, tot_exec:821.265µs, tot_time:203.286524ms, max:40.662391ms, p95:40.662391ms}, final_worker:{wall_time:0s, concurrency:5, task_num:5, tot_wait:203.342074ms, tot_exec:14.165µs, tot_time:203.360693ms, max:40.676411ms, p95:40.676411ms} | funcs:count(1)->Column#70 | 153.3 KB | N/A |
| └─HashJoin_19 | 54000.00 | 8334 | root | | time:40.9ms, loops:15, build_hash_table:{total:20.2ms, fetch:20.2ms, build:18.4µs}, probe:{concurrency:5, total:147.1ms, max:40.1ms, probe:23.7ms, fetch:123.4ms} | CARTESIAN right outer join, other cond:ge(test.table_20_latin1_undef.col_double_undef_unsigned, cast(test.table_30_latin1_6.col_enum_key_signed, double BINARY)) | 20.8 KB | 0 Bytes |
| ├─PartitionUnion_27(Build) | 30.00 | 30 | root | | time:20.2ms, loops:7 | | N/A | N/A |
| │ ├─IndexReader_31 | 5.00 | 5 | root | | time:20ms, loops:2, cop_task: {num: 1, max: 19.8ms, proc_keys: 5, tot_proc: 8ms, tot_wait: 5ms, rpc_num: 1, rpc_time: 19.7ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | index:IndexFullScan_30 | 380 Bytes | N/A |
| │ │ └─IndexFullScan_30 | 5.00 | 5 | cop[tikv] | table:t3, partition:p0, index:col_enum_key_signed(col_enum_key_signed) | tikv_task:{time:9ms, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 221, total_keys: 6, get_snapshot_time: 5.13ms, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 2}}} | keep order:false, stats:pseudo | N/A | N/A |
| │ ├─IndexReader_35 | 5.00 | 5 | root | | time:9.25ms, loops:2, cop_task: {num: 1, max: 8.71ms, proc_keys: 0, rpc_num: 1, rpc_time: 8.69ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15} | index:IndexFullScan_34 | 321 Bytes | N/A |
| │ │ └─IndexFullScan_34 | 5.00 | 5 | cop[tikv] | table:t3, partition:p1, index:col_enum_key_signed(col_enum_key_signed) | tikv_task:{time:2ms, loops:1}, scan_detail: {get_snapshot_time: 5.08ms, rocksdb: {block: {}}} | keep order:false, stats:pseudo | N/A | N/A |
| │ ├─IndexReader_39 | 5.00 | 5 | root | | time:9ms, loops:2, cop_task: {num: 1, max: 8.51ms, proc_keys: 0, rpc_num: 1, rpc_time: 8.35ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15} | index:IndexFullScan_38 | 349 Bytes | N/A |
| │ │ └─IndexFullScan_38 | 5.00 | 5 | cop[tikv] | table:t3, partition:p2, index:col_enum_key_signed(col_enum_key_signed) | tikv_task:{time:2ms, loops:1}, scan_detail: {get_snapshot_time: 4.55ms, rocksdb: {block: {}}} | keep order:false, stats:pseudo | N/A | N/A |
| │ ├─IndexReader_43 | 5.00 | 5 | root | | time:8.83ms, loops:2, cop_task: {num: 1, max: 8.14ms, proc_keys: 0, rpc_num: 1, rpc_time: 8.12ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15} | index:IndexFullScan_42 | 340 Bytes | N/A |
| │ │ └─IndexFullScan_42 | 5.00 | 5 | cop[tikv] | table:t3, partition:p3, index:col_enum_key_signed(col_enum_key_signed) | tikv_task:{time:2ms, loops:1}, scan_detail: {get_snapshot_time: 5.39ms, rocksdb: {block: {}}} | keep order:false, stats:pseudo | N/A | N/A |
| │ ├─IndexReader_47 | 5.00 | 5 | root | | time:8.8ms, loops:2, cop_task: {num: 1, max: 8.03ms, proc_keys: 0, rpc_num: 1, rpc_time: 8ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15} | index:IndexFullScan_46 | 357 Bytes | N/A |
| │ │ └─IndexFullScan_46 | 5.00 | 5 | cop[tikv] | table:t3, partition:p4, index:col_enum_key_signed(col_enum_key_signed) | tikv_task:{time:2ms, loops:1}, scan_detail: {get_snapshot_time: 4.69ms, rocksdb: {block: {}}} | keep order:false, stats:pseudo | N/A | N/A |
| │ └─IndexReader_51 | 5.00 | 5 | root | | time:11ms, loops:2, cop_task: {num: 1, max: 10.9ms, proc_keys: 5, tot_proc: 1ms, tot_wait: 8ms, rpc_num: 1, rpc_time: 10.9ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | index:IndexFullScan_50 | 379 Bytes | N/A |
| │ └─IndexFullScan_50 | 5.00 | 5 | cop[tikv] | table:t3, partition:p5, index:col_enum_key_signed(col_enum_key_signed) | tikv_task:{time:1ms, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 221, total_keys: 6, get_snapshot_time: 2.1ms, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 2}}} | keep order:false, stats:pseudo | N/A | N/A |
| └─HashJoin_21(Probe) | 1800.00 | 1290 | root | | time:3.18ms, loops:3, build_hash_table:{total:1.66ms, fetch:1.65ms, build:7.66µs}, probe:{concurrency:5, total:14.3ms, max:3.16ms, probe:1.51ms, fetch:12.8ms} | CARTESIAN left outer join, other cond:div(cast(test.table_90_utf8_undef.col_bigint_undef_unsigned, decimal(20,0) UNSIGNED BINARY), test.table_20_latin1_undef.col_decimal(40, 20)_key_signed) | 49.7 KB | 0 Bytes |
| ├─TableReader_26(Build) | 20.00 | 20 | root | | time:1.61ms, loops:2, cop_task: {num: 1, max: 7.65ms, proc_keys: 0, rpc_num: 1, rpc_time: 7.63ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15} | data:TableFullScan_25 | 1.21 KB | N/A |
| │ └─TableFullScan_25 | 20.00 | 20 | cop[tikv] | table:t2 | tikv_task:{time:2ms, loops:1}, scan_detail: {get_snapshot_time: 127.9µs, rocksdb: {block: {}}} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_24(Probe) | 90.00 | 90 | root | | time:1.08ms, loops:2, cop_task: {num: 1, max: 7.27ms, proc_keys: 0, rpc_num: 1, rpc_time: 7.23ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15} | data:TableFullScan_23 | 1006 Bytes | N/A |
| └─TableFullScan_23 | 90.00 | 90 | cop[tikv] | table:t1 | tikv_task:{time:2ms, loops:2}, scan_detail: {get_snapshot_time: 158.7µs, rocksdb: {block: {}}} | keep order:false, stats:pseudo | N/A | N/A |
+----------------------------------+----------+---------+-----------+------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
|
old reproduce with join
minimal reproduce: drop table i1;
drop table i2;
create table i1(a int);
create table i2(a decimal(5,0));
alter table i1 set tiflash replica 1;
alter table i2 set tiflash replica 1;
insert into i1 values (1);
insert into i2 values (10001), (1);tikv correct set tidb_isolation_read_engines = tikv;
select * from i1 join i2 on i1.a / i2.a;
+------+-------+
| a | a |
+------+-------+
| 1 | 10001 |
| 1 | 1 |
+------+-------+tiflash wrong answer set tidb_isolation_read_engines=tiflash;
set tidb_enforce_mpp=on;
select * from i1 join i2 on i1.a / i2.a;
+------+------+
| a | a |
+------+------+
| 1 | 1 |
+------+------+tiflash explain analyze mysql> explain analyze select * from i1 join i2 on i1.a / i2.a;
+--------------------------------------+---------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------+---------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------+--------+------+
| TableReader_24 | 2.00 | 1 | root | | time:34.9ms, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_23 | N/A | N/A |
| └─ExchangeSender_23 | 2.00 | 1 | mpp[tiflash] | | tiflash_task:{time:30.4ms, loops:1, threads:4} | ExchangeType: PassThrough | N/A | N/A |
| └─HashJoin_22 | 2.00 | 1 | mpp[tiflash] | | tiflash_task:{time:29.3ms, loops:1, threads:4} | CARTESIAN inner join, other cond:div(cast(test.i1.a, decimal(10,0) BINARY), test.i2.a) | N/A | N/A |
| ├─ExchangeReceiver_14(Build) | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:15.9ms, loops:1, threads:4} | | N/A | N/A |
| │ └─ExchangeSender_13 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:15.3ms, loops:1, threads:1} | ExchangeType: Broadcast | N/A | N/A |
| │ └─TableFullScan_12 | 1.00 | 1 | mpp[tiflash] | table:i1 | tiflash_task:{time:15.2ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableFullScan_15(Probe) | 2.00 | 2 | mpp[tiflash] | table:i2 | tiflash_task:{time:21.6ms, loops:1, threads:1} | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------------+---------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------+--------+------+ |
root causeThis bug is because of the compatibility issue of DecimalDivide between TiFlash and TiDB/TiKV. Two reasons for this issue:
versionreproduced in 6.3.0, 6.2.0, 6.1.0, 6.0.0, ...(not tested), 5.3.0, ...(not tested), 5.0.0, ...(not tested) minimal reproducedrop table ish;
create table ish(a int, b decimal(40, 20));
alter table ish set tiflash replica 1;
insert into ish values (1, 10000), (1, 10001), (1, 20000), (1, 20001);
select a, b, a/b from ish where a/b order by b;tiflash set tidb_isolation_read_engines=tiflash;
+------+----------------------------+--------+
| a | b | a/b |
+------+----------------------------+--------+
| 1 | 10000.00000000000000000000 | 0.0001 |
+------+----------------------------+--------+
1 row in set (0.06 sec)tikv set tidb_isolation_read_engines=tikv;
+------+----------------------------+--------+
| a | b | a/b |
+------+----------------------------+--------+
| 1 | 10000.00000000000000000000 | 0.0001 |
| 1 | 10001.00000000000000000000 | 0.0001 |
| 1 | 20000.00000000000000000000 | 0.0001 |
| 1 | 20001.00000000000000000000 | 0.0000 |
+------+----------------------------+--------+
4 rows in set (0.01 sec) |
12 tasks
ti-chi-bot
pushed a commit
that referenced
this issue
Feb 9, 2023
12 tasks
ti-chi-bot
pushed a commit
to ti-chi-bot/tiflash
that referenced
this issue
Feb 9, 2023
12 tasks
ti-chi-bot
pushed a commit
to ti-chi-bot/tiflash
that referenced
this issue
Feb 9, 2023
guo-shaoge
pushed a commit
to guo-shaoge/tiflash
that referenced
this issue
Feb 10, 2023
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
use go-randgen generate data.
cmd: go-randgen gendata --dsns "root:@tcp(127.0.0.1:4000)/test"
2. What did you expect to see? (Required)
3. What did you see instead (Required)
4. What is your TiFlash version? (Required)
nightly
The text was updated successfully, but these errors were encountered: