Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Join result is inconsistent with TiDB #6393

Closed
mengxin9014 opened this issue Nov 30, 2022 · 4 comments · Fixed by #6471
Closed

Join result is inconsistent with TiDB #6393

mengxin9014 opened this issue Nov 30, 2022 · 4 comments · Fixed by #6471
Assignees

Comments

@mengxin9014
Copy link
Contributor

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"

set tidb_isolation_read_engines=tikv;
SELECT /*+ inl_hash_join (t1,t2) */ count(*) FROM table_90_utf8_undef t1 left outer join table_20_latin1_undef t2 on t1. `col_bigint_undef_unsigned` / t2. `col_decimal(40, 20)_key_signed` right join table_30_latin1_6 t3 on t2. `col_double_undef_unsigned` >= t3. `col_enum_key_signed`;

set tidb_isolation_read_engines=tiflash;
set tidb_enforce_mpp=on;
SELECT /*+ inl_hash_join (t1,t2) */ count(*) FROM table_90_utf8_undef t1 left outer join table_20_latin1_undef t2 on t1. `col_bigint_undef_unsigned` / t2. `col_decimal(40, 20)_key_signed` right join table_30_latin1_6 t3 on t2. `col_double_undef_unsigned` >= t3. `col_enum_key_signed`;

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

+----------+
| count(*) |
+----------+
|     8334 |
+----------+

+----------+
| count(*) |
+----------+
|     8334 |
+----------+

3. What did you see instead (Required)

+----------+
| count(*) |
+----------+
|     8334 |
+----------+
+----------+
| count(*) |
+----------+
|     7902 |
+----------+

4. What is your TiFlash version? (Required)

nightly

@mengxin9014 mengxin9014 added the type/bug The issue is confirmed as a bug. label Nov 30, 2022
@mengxin9014
Copy link
Contributor Author

mengxin9014 commented Dec 1, 2022

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     |
+------------------------------------------+----------+---------+--------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+

@mengxin9014
Copy link
Contributor Author

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     |
+----------------------------------+----------+---------+-----------+------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+

@LittleFall
Copy link
Contributor

LittleFall commented Dec 2, 2022

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  |
+--------------------------------------+---------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------+--------+------+

@LittleFall
Copy link
Contributor

LittleFall commented Dec 2, 2022

root cause

This bug is because of the compatibility issue of DecimalDivide between TiFlash and TiDB/TiKV.

Two reasons for this issue:

  1. The behavior of TiFlash Decimal calculation is different from TiDB #1682, need discuss, won't fixed.
  2. The result of decimal divide won't round at some precision. #6462, will be fixed soon.

version

reproduced 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 reproduce

drop 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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants