[MDEV-30211] incorrect result for rounding float and double value Created: 2022-12-12  Updated: 2022-12-27  Resolved: 2022-12-26

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.6.10
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: bulepage Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

When we use round function on float or double not working correctly

SELECT ROUND(CAST(118.5 AS FLOAT)),ROUND(CAST(118.5 AS double)) ,ROUND(118.5 )

MariaDB [(none)]> SELECT ROUND(CAST(118.5 AS FLOAT)),ROUND(CAST(118.5 AS double)) , ROUND(118.5 );
 
+-----------------------------+------------------------------+---------------+
 
| ROUND(CAST(118.5 AS FLOAT)) | ROUND(CAST(118.5 AS double)) | ROUND(118.5 ) |
 
+-----------------------------+------------------------------+---------------+
 
|                         118 |                          118 |           119 |
 
+-----------------------------+------------------------------+---------------+

119 is the correct value.

 Comments   
Comment by bulepage [ 2022-12-12 ]

There is only a problem with "even" values.

SELECT ROUND(CAST(seq+0.5 AS FLOAT)) AS _rfloat ,ROUND(seq + 0.5) AS rnm,seq from
 
seq_1_to_200 WHERE ROUND(CAST(seq+0.5 AS FLOAT))<>ROUND(seq + 0.5)

Comment by Sergei Golubchik [ 2022-12-26 ]

See https://mariadb.com/kb/en/floating-point-accuracy/

Did you check that 118.5 can be represented exactly in FLOAT?

Comment by bulepage [ 2022-12-27 ]

The decimal data is the solition!

Generated at Thu Feb 08 10:14:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.