Result of function ROUND(x, d) is different from MySQL

[riteme]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select round(50, -2);

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

+---------------+
| round(50, -2) |
+---------------+
|           100 |
+---------------+

3. What did you see instead (Required)

+---------------+
| round(50, -2) |
+---------------+
|             0 |
+---------------+

4. What is your TiDB version? (Required)

Release Version: v5.2.0-alpha-447-g4f6be1798
Edition: Community
Git Commit Hash: 4f6be1798bc934b090ec8a44ffac5f1f93099a24
Git Branch: origin/master
UTC Build Time: 2021-08-08 03:47:22
GoVersion: go1.16.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[aytrack]

mysql:

MySQL root@172.16.5.103:(none)> select round(49.99999, -2);
+---------------------+
| round(49.99999, -2) |
+---------------------+
| 0                   |
+---------------------+

1 row in set
Time: 0.047s
MySQL root@172.16.5.103:(none)> select round(50, -2);
+---------------+
| round(50, -2) |
+---------------+
| 100           |
+---------------+

1 row in set
Time: 0.061s
MySQL root@172.16.5.103:(none)> select round(50.00001, -2);
+---------------------+
| round(50.00001, -2) |
+---------------------+
| 100                 |
+---------------------+

1 row in set
Time: 0.051s

tidb:

mysql> select round(49.99999, -2);
+---------------------+
| round(49.99999, -2) |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

mysql> select round(50, -2);
+---------------+
| round(50, -2) |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(50.00001, -2);
+---------------------+
| round(50.00001, -2) |
+---------------------+
|                 100 |
+---------------------+
1 row in set (0.00 sec)

[feitian124]

/assign

[dveeden]

I think this should have the type/compatibility label

[espresso98]

I found more test cases to show discrepancies.

select round(-5000111000111000155,-1);
select round(15000111000111000155,-1);
select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614));
select round(1.5, 2147483640), truncate(1.5, 2147483640);

[zanmato1984]

I'm adjusting the severity to moderate.

[wd0517]

Both MySQL and Postgres return 370, but TiDB return 360

mysql> SELECT ROUND(365, -1), ROUND(365.0, -1);
+----------------+------------------+
| ROUND(365, -1) | ROUND(365.0, -1) |
+----------------+------------------+
|            360 |              370 |
+----------------+------------------+
1 row in set (0.00 sec)

Currently, the round function in the doc is linked directly to the MySQL doc. If you believe it is a compatibility issue, it would be better to provide additional details and descriptions.

[dveeden]

This is the result in ClickHouse:

dve-carbon :) SELECT ROUND(365, -1), ROUND(365.0, -1);

SELECT
    ROUND(365, -1),
    ROUND(365., -1)

Query id: 5a30a66b-53a8-4778-9c8b-f4b6ee118018

┌─round(365, -1)─┬─round(365., -1)─┐
│            370 │             360 │
└────────────────┴─────────────────┘

1 row in set. Elapsed: 0.003 sec. 

dve-carbon :) SELECT VERSION();

SELECT VERSION()

Query id: 2f67bbd8-0f50-429c-9d3b-7fe5f7a3224b

┌─version()───┐
│ 23.8.1.1844 │
└─────────────┘

1 row in set. Elapsed: 0.002 sec. 

And SQLite3:

$ sqlite3 :memory:
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> SELECT ROUND(365, -1), ROUND(365.0, -1);
365.0|365.0

DB	ROUND(365, -1)	ROUND(365.0, -1)
MySQL 8.1.0	370	370
SQLite 3.40.1	365.0	365.0
PostgreSQL 15.3	370	370
ClickHouse 23.8.1.1844	370	360
TiDB v6.6.0-serverless	360	370
Oracle 11gR2 (via sqlfiddle.com)	370	370
MS SQL Server 2017 (via sqlfiddle.com)	370	370
DB2 v11.1.4.4 (via dbfiddle.uk)	370	370.0
Firebird v4.0.1 (via dbfiddle.uk)	370	370.0

Note that for Oracle you need to add FROM DUAL and for DB2 you need to add sysibm.sysdummy1 and for Firebird you need to add FROM RDB$DATABASE.

Also tried SQL Server 2022, that gives the same result as SQL Server 2017.

[dveeden]

This is what Python does:

% python3.11
Python 3.11.4 (main, Jun 10 2023, 01:12:00) [Clang 13.0.0 (git@github.com:llvm/llvm-project.git llvmorg-13.0.0-0-gd7b669b3a3 on freebsd13
Type "help", "copyright", "credits" or "license" for more information.
>>> round(365)
365
>>> round(365, -1)
360
>>> round(365.0, -1)
360.0

Not sure what the correct result is but 370/370 seems to be very common.

[dveeden]

Note that for SQLite the manual has this, which explains the unexpected results.

"The round(X,Y) function returns a floating-point value X rounded to Y digits to the right of the decimal point. If the Y argument is omitted or negative, it is taken to be 0."

https://www.sqlite.org/lang_corefunc.html#round

[dveeden]

I also tried Apache Derby... but Derby doesn't have a ROUND() function...
Ref: https://db.apache.org/derby/docs/10.16/ref/rrefsqlj29026.html