MySQL Bugs: #103360: Round Function

Bug #103360	Round Function
Submitted:	19 Apr 2021 4:28	Modified:	20 Apr 2021 6:49
Reporter:	Pankaj Chadha	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	round

Description:
The Round function gave different result on two different systems with number of decimal places exceeding the number of digits.

On one system, it padded the required number of zeroes, while on the other it did not!

SYSTEM 1:

mysql> select round(9999.9999,5);
+--------------------+
| round(9999.9999,5) |
+--------------------+
|         9999.99990 |
+--------------------+
1 row in set (0.00 sec)

SYSTEM 2:

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

How to repeat:
.

SYSTEM2

Attachment: WhatsApp Image 2021-04-19 at 09.26.40.jpeg (image/jpeg, text), 16.87 KiB.

SYSTEM1

Attachment: Screenshot 2021-04-19 113224.png (image/png, text), 16.89 KiB.

Hi Mr. Chadha,

Thank you for your bug report.

However, this is not a bug.

Floating point numbers 9999.99990 and 9999.9999 are totally equal in value. If you wish to have a fixed number of digits, you should use some other type, like DECIMAL.

Not a bug.

Thanks for the quick response. However, I consider this as a bug not because it displays/does not display a zero at the end, but because it displays different outputs on different systems. Could yiu please help me understand the difference in outputs.

Thanks

I think the issue is not clear here. 

Could you please help me understand why MySQL displays different outputs on different system. I do understand the datatype issue, but that does not justify different outputs.

HI,

The explanation is simple. Most of  floating point functionality ends in the system libraries of the operating systems. And different systems handle it in different manner. This is known behaviour.