TiDB v5.4.0 round() function truncates instead of rounding

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB v5.4.0 round()函数出现截断,而不是四舍五入

| username: dengqee

Bug Report
In version v5.4.0, using the round function to retain decimal places for float type columns results in truncation instead of rounding. This was tested successfully in version 4.0.2.
【TiDB Version】
v5.4.0
【Impact of the Bug】
The round() function outputs incorrect values.
【Possible Steps to Reproduce the Issue】

create table t (id int primary key, value float);
insert into t value(1, 1616.625);
select round(value,2) from t where id=1;

【Observed Unexpected Behavior】

mysql> select round(value,2) from t where id=1;
+----------------+
| round(value,2) |
+----------------+
|        1616.62 |
+----------------+
1 row in set (0.00 sec)

【Expected Behavior】

mysql> select round(value,2) from t where id=1;
+----------------+
| round(value,2) |
+----------------+
|        1616.63 |
+----------------+
1 row in set (0.00 sec)

【Related Components and Specific Versions】

【Additional Background Information or Screenshots】

Such as cluster topology, system and kernel version, application app information, etc.; if the issue is related to SQL, please provide SQL statements and related table schema information; if there are critical errors in the node logs, please provide the relevant node log content or files; if some business-sensitive information is inconvenient to provide, please leave contact information, and we will communicate with you privately.

| username: Billmay表妹 | Original post link

Please check the compatibility differences between each version.

| username: tidb菜鸟一只 | Original post link

Just change it to DECIMAL.

| username: Kongdom | Original post link

This should be an issue inherited from MySQL~

| username: zhanggame1 | Original post link

This should be the issue. Normally, databases should not use the float type either.

| username: Kongdom | Original post link

@Youmao Wanshizu The answer from this expert has already raised issues

d92c8b8e162868b0b50847a2865f19d

| username: redgame | Original post link

Try DECIMAL

| username: Kongdom | Original post link

:joy: It’s probably due to historical reasons that float is being used, I can totally relate~

| username: dengqee | Original post link

Because we are migrating data from version 4.0.2 to 5.4.0, we use sync_diff_inspector to verify the data between the source and target. However, sync_diff_inspector uses the round() function to retain 6 and 15 significant digits for float and double types, respectively, causing the data verification to fail. This is because version 4.0.2 uses the round() function to round off, while version 5.4.0 truncates directly.

| username: tidb菜鸟一只 | Original post link

Actually, I executed your example on MySQL 8:

CREATE TABLE t_float (id INT PRIMARY KEY, VALUE FLOAT);
INSERT INTO t_float VALUE(1, 1616.625);
SELECT ROUND(VALUE,2) FROM t_float WHERE id=1;

The data in the table was directly truncated…
image