MySQL Bugs: #114550: ROUND(1.2345, 2) and ROUND(1.2345, @TWO) unexpected different results

Bug #114550	ROUND(1.2345, 2) and ROUND(1.2345, @TWO) unexpected different results
Submitted:	4 Apr 2024 11:31	Modified:	4 Apr 2024 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

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);

Hello Eimantas Jatkonis,

Thank you for the report and test case.

Thanks,
Umesh

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.

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.