Bug #114550 ROUND(1.2345, 2) and ROUND(1.2345, @TWO) unexpected different results
Submitted: 4 Apr 11:31 Modified: 4 Apr 20:27
Reporter: Eimantas Jatkonis Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.35, 8.0.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[4 Apr 11:31] Eimantas Jatkonis
Description:
ROUND behaviour changed and returning undocumented output (TRUNCATE too has same problem).

8.0.21 returns OK:

mysql> SET @TWO = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @VALUE = 1.23456;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT ROUND(1.23456, 2),
    ->        ROUND(1.23456, @TWO),
    ->        ROUND(@VALUE, @TWO);
+-------------------+----------------------+---------------------+
| ROUND(1.23456, 2) | ROUND(1.23456, @TWO) | ROUND(@VALUE, @TWO) |
+-------------------+----------------------+---------------------+
|              1.23 |                 1.23 |                1.23 |
+-------------------+----------------------+---------------------+
1 row in set (0.00 sec)

8.0.35 returns strange values:

mysql> SET @TWO = 2;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @VALUE = 1.23456;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SELECT ROUND(1.23456, 2),
    ->        ROUND(1.23456, @TWO),
    ->        ROUND(@VALUE, @TWO);
+-------------------+----------------------+----------------------------------+
| ROUND(1.23456, 2) | ROUND(1.23456, @TWO) | ROUND(@VALUE, @TWO)              |
+-------------------+----------------------+----------------------------------+
|              1.23 |              1.23000 | 1.230000000000000000000000000000 |
+-------------------+----------------------+----------------------------------+
1 row in set (0.01 sec)

This is a bug according to manual examples https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_round.
Manual states:
"When the desired number of decimal places is less than the scale of the argument, the scale and the precision of the result are adjusted accordingly."

But 8.0.35 returns more decimal places then expected.

How to repeat:
SET @TWO = 2;
SET @VALUE = 1.23456;

SELECT ROUND(1.23456, 2), 
       ROUND(1.23456, @TWO),
       ROUND(@VALUE, @TWO);
[4 Apr 11:53] MySQL Verification Team
Hello Eimantas Jatkonis,

Thank you for the report and test case.

Thanks,
Umesh
[4 Apr 20:05] Roy Lyseng
Posted by developer:
 
This is not a bug.
Metadata, such as number of decimals, is calculated based on literal values.
But a user variable may change between executions, so metadata is calculated
as a conservative large value.
The actual result is still rounded according to the value of the user variable at runtime.
[4 Apr 20:27] Eimantas Jatkonis
If code is not bug, then manual seems to be misleading.

Any ideas how to achieve OLD results for
ROUND(1.23456, @TWO) = 1.23
ROUND(1.00007, @TWO) = 1.00
?

I know FORMAT(X, @TWO), but unfortunately it adds thousands separator.
And CAST(ROUND(...) AS DECIMAL(12, @TWO)) doesn't works either.