Floating Point Arithmetic Behavior Differences Between Unistore and TiKV

[r33s3n6]

1. Minimal reproduce step (Required)

create table t1 (c1 double);
insert into t1 (c1) values (24.5), (25), (18);

SELECT
  c1,
  (c1 - 129) & 55 as w1
FROM t1 ORDER BY w1 asc
limit 2
;

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

with unistore:

mysql> SELECT
    ->   c1,
    ->   (c1 - 129) & 55 as w1
    -> FROM t1 ORDER BY w1 asc
    -> limit 2
    -> ;
+------+------+
| c1   | w1   |
+------+------+
| 24.5 |   16 |
|   25 |   16 |
+------+------+
2 rows in set (0.01 sec)

3. What did you see instead (Required)

with tikv

mysql> SELECT
    ->   c1,
    ->   (c1 - 129) & 55 as w1
    -> FROM t1 ORDER BY w1 asc
    -> limit 2
    -> ;
+------+------+
| c1   | w1   |
+------+------+
|   25 |   16 |
|   18 |   17 |
+------+------+
2 rows in set (0.01 sec)

Actually, if execute it without LIMIT, it is correct:

mysql> SELECT
    ->   c1,
    ->   (c1 - 129) & 55 as w1
    -> FROM t1 ORDER BY w1 asc;
+------+------+
| c1   | w1   |
+------+------+
| 24.5 |   16 |
|   25 |   16 |
|   18 |   17 |
+------+------+
3 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

Release Version: v8.5.0-alpha-184-g1c059a1216
Edition: Community
Git Commit Hash: 1c059a1216db711e2cb56ea9f3d1ad8c23db6327
Git Branch: HEAD
UTC Build Time: 2024-11-21 04:56:22
GoVersion: go1.23.3
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"

tidb_servers:
  - host: 10.0.2.81

pd_servers:
  - host: 10.0.2.82

tikv_servers:
  - host: 10.0.2.83

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.