Bug #57740 false result from round() function
Submitted: 26 Oct 2010 15:23 Modified: 26 Oct 2010 16:04
Reporter: youngho park Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.0.77 OS:Linux (CentOS 5)
Assigned to: CPU Architecture:Any
Tags: round

[26 Oct 2010 15:23] youngho park
Description:
this bug is ...

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

mysql> select round("6.325",2);
+------------------+
| round("6.325",2) |
+------------------+
|             6.32 |
+------------------+
1 row in set (0.08 sec)

How to repeat:
I can't ...

Suggested fix:
I can't ...
[26 Oct 2010 15:35] Peter Laursen
1) Seems you are using 'continental decimal notation' (comma as decimal sign). MySQL does not support this. You will need to use 'english decimal notation' (dot as decimal sign).

2) You cannot use 'engineeering notation' (neither '6,325.2' nor '6.325,2' )

This works: SELECT ROUND(6325.2);
.. and I guess that was what you wanted to do?

Peter
(not a MySQL person)
[26 Oct 2010 16:04] Valeriy Kravchuk
The problem you see is related to the fact that whenever you mix string and number in one expression, values are converted to DOUBLE. This is easy to demonstrate:

macbook-pro:5.0 openxs$ bin/mysql -T -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.92-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select round(6.325,2);Field   1:  `round(6.325,2)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     6
Max_length: 4
Decimals:   2
Flags:      NOT_NULL BINARY 

+----------------+
| round(6.325,2) |
+----------------+
|           6.33 | 
+----------------+
1 row in set (0.01 sec)

mysql> select round("6.325",2);
Field   1:  `round("6.325",2)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     19
Max_length: 4
Decimals:   2
Flags:      NOT_NULL BINARY NUM 

+------------------+
| round("6.325",2) |
+------------------+
|             6.32 | 
+------------------+
1 row in set (0.00 sec)

mysql> select round(cast("6.325" as decimal(5,3)),2);
Field   1:  `round(cast("6.325" as decimal(5,3)),2)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     7
Max_length: 4
Decimals:   2
Flags:      NOT_NULL BINARY 

+----------------------------------------+
| round(cast("6.325" as decimal(5,3)),2) |
+----------------------------------------+
|                                   6.33 | 
+----------------------------------------+
1 row in set (0.00 sec)

Last example is a workaround/correct way to implement this if you do NOT want impicit data type conversion to be applied. 

This is documented at http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html.

Calculations with DOUBLE data type are not precise. This is also documented at http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html.