TiKV and TiFlash behave differently when performing division.

1. Minimal reproduce step (Required)

create table t1 ( 
c1 int not null ,
c2 int not null ,
primary key(c1) CLUSTERED);

alter table t1 set tiflash replica 1;

insert into t1 (c1,c2) values (1486109909, -1113200806);

SELECT /*+ read_from_storage(tiflash[t1]) */ c2, c1, cast( (c2 / cast(c1 as signed)) as decimal) as c2 FROM t1;

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

mysql> SELECT /*+ read_from_storage(tikv[t1]) */ c2, c1, cast( (c2 / cast(c1 as signed)) as decimal) as c2 FROM t1;
+-------------+------------+------+
| c2          | c1         | c2   |
+-------------+------------+------+
| -1113200806 | 1486109909 |   -1 |
+-------------+------------+------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '-0.7491' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT /*+ read_from_storage(tiflash[t1]) */ c2, c1, cast( (c2 / cast(c1 as signed)) as decimal) as c2 FROM t1;
+-------------+------------+------+
| c2          | c1         | c2   |
+-------------+------------+------+
| -1113200806 | 1486109909 |    1 |
+-------------+------------+------+
1 row in set (0.10 sec)

4. What is your TiDB version? (Required)

Release Version: v8.2.0-alpha-292-g7629a0d
Edition: Community
Git Commit Hash: 7629a0d68595c4d11c25fc059208f3efd838c2c1
Git Branch: HEAD
UTC Build Time: 2024-06-04 03:27:25
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

topology:

distributed.yaml:

global:
  user: "tidb"
  ssh_port: 22
  deploy_dir: "/tidb-deploy"
  data_dir: "/tidb-data"

pd_servers:
  - host: 10.0.2.31

tidb_servers:
  - host: 10.0.2.21

tikv_servers:
  - host: 10.0.2.11
  - host: 10.0.2.12
  - host: 10.0.2.13

monitoring_servers:
  - host: 10.0.2.8

grafana_servers:
  - host: 10.0.2.8

alertmanager_servers:
  - host: 10.0.2.8

tiflash_servers:
  - host: 10.0.2.32

about us

We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned vulnerabilities in TiDB that may lead to database logic error.

Reproduction simplified:

create table t2(d decimal(11, 4));
alter table t2 set tiflash replica 1;
insert into t2 values(-0.741);

SELECT /*+ read_from_storage(tiflash[t2]) */ * from t2;
+---------+
| d       |
+---------+
| -0.7410 |
+---------+

SELECT /*+ read_from_storage(tiflash[t2]) */ cast(d as decimal) from t2;
+--------------------+
| cast(d as decimal) |
+--------------------+
|                  1 |
+--------------------+

However if we cast to a decimal type with fraction part, the result is correct:

SELECT /*+ read_from_storage(tiflash[t2]) */ cast(d as decimal(10, 1)) from t2;
+---------------------------+
| cast(d as decimal(10, 1)) |
+---------------------------+
|                      -0.7 |
+---------------------------+

Might be something wrong in tiflash decimal casting implementation.

Lower severity to major.

pingcap / tidb