round function on exact number produces different result from MySQL

[r33s3n6]

1. Minimal reproduce step (Required)

select round(-717754013, 24);

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

In MySQL:

mysql> select round(-717754013, 24);
+-----------------------+
| round(-717754013, 24) |
+-----------------------+
|            -717754013 |
+-----------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

But TiDB output -717754012. And TiDB produces correct result -717754013 sometimes.

mysql> select round(-717754013, 24);
+-----------------------+
| round(-717754013, 24) |
+-----------------------+
|            -717754012 |
+-----------------------+
1 row in set (0.00 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.

[zanmato1984]

When rounding an integer, we're converting it to float64 and leveraging the floating-rounding function:

tidb/pkg/expression/builtin_math.go

Line 447 in 0b54071

return int64(types.Round(float64(val), int(frac))), false, nil

The error is resulted from the glitch of floating point representation of value -717754013 (-717754012.9999999).

We should following MySQL's specialized integer rounding path:
https://github.com/mysql/mysql-server/blob/824e2b4064053f7daf17d7f3f84b7a3ed92e5fb4/sql/item_func.cc#L3536

Changing severity to major as this is relative corner.

[dveeden]

Also happens for large positive numbers.

mysql-8.0.11-TiDB-v8.4.0-alpha-364-gd5796776c1> select round(12345678901234567890, 24);
+---------------------------------+
| round(12345678901234567890, 24) |
+---------------------------------+
|            12345678901234568192 |
+---------------------------------+
1 row in set (0.00 sec)

[r33s3n6]

Another testcase:
In TiDB:

mysql> select round(-5, -1), round(-15, -1), round(-25, -1);
+---------------+----------------+----------------+
| round(-5, -1) | round(-15, -1) | round(-25, -1) |
+---------------+----------------+----------------+
|             0 |            -20 |            -20 |
+---------------+----------------+----------------+
1 row in set (0.00 sec)

In MySQL:

mysql> select round(-5, -1), round(-15, -1), round(-25, -1);
+---------------+----------------+----------------+
| round(-5, -1) | round(-15, -1) | round(-25, -1) |
+---------------+----------------+----------------+
|           -10 |            -20 |            -30 |
+---------------+----------------+----------------+
1 row in set (0.00 sec)