Bug #14142 Round

• Submitted: 19 Oct 2005 14:29
• Modified: 20 Oct 2005 12:44
• Reporter: Sedat Onur Orakoglu
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.15-BK, 5.0.13-rc-nt-max
• OS: Linux (Linux, windows 2000 pro)
• CPU Architecture: Any

[19 Oct 2005 14:29] Sedat Onur Orakoglu

Description:
mysql>
mysql> create table test (field1 double(15,2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test set field1=109.57;
Query OK, 1 row affected (0.00 sec)

mysql> select 109.57 as a1,109.57/2 as a2,truncate(109.57/2,3) as a3,
    -> round(truncate(109.57/2,3),2) as a4,
    -> field1 as b1,
    -> field1/2 as b2,
    -> truncate(field1/2,3) as b3,
    -> round(truncate(field1/2,3),2) as b4
    -> from test;
+--------+-----------+--------+-------+--------+-----------+--------+-------+
| a1     | a2        | a3     | a4    | b1     | b2        | b3     | b4    |
+--------+-----------+--------+-------+--------+-----------+--------+-------+
| 109.57 | 54.785000 | 54.785 | 54.79 | 109.57 | 54.785000 | 54.785 | 54.78 |
+--------+-----------+--------+-------+--------+-----------+--------+-------+
1 row in set (0.00 sec)

here is a4 and b4 must be equal.

How to repeat:
everytime

[19 Oct 2005 15:41] Valeriy Kravchuk

Thank you for a problem report. I was able to repeat it on Linux with 5.0.15-BK (ChangeSet@1.2007.4.1, 2005-10-18 18:51:07-07:00, [e-mail]), but with slightly different result (that is strange too):

mysql> create table test (field1 double(15,2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test set field1=109.57;
Query OK, 1 row affected (0.01 sec)

mysql> select 109.57 as a1,109.57/2 as a2,truncate(109.57/2,3) as a3,
    -> round(truncate(109.57/2,3),2) as a4,
    -> field1 as b1,
    -> field1/2 as b2,
    -> truncate(field1/2,3) as b3,
    -> round(truncate(field1/2,3),2) as b4
    -> from test;
+--------+-----------+--------+-------+--------+-----------+--------+-------+
| a1     | a2        | a3     | a4    | b1     | b2        | b3     | b4    |
+--------+-----------+--------+-------+--------+-----------+--------+-------+
| 109.57 | 54.785000 | 54.785 | 54.79 | 109.57 | 54.785000 | 54.784 | 54.78 |
+--------+-----------+--------+-------+--------+-----------+--------+-------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15    |
+-----------+
1 row in set (0.00 sec)

In my case, the key difference is among a3 and b3 fields. 

Looks like I know the reason for the difference (109.57 is of type decimal really, and for it the precision is guaranteed, unlike double). But I want this to be checked and, if it is the intended behaviour, properly documented (in http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html)

[20 Oct 2005 12:44] Sergei Golubchik

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See http://dev.mysql.com/doc/mysql/en/problems-with-float.html