Bug #8060 Incorrect INSERT for DOUBLE values on MySQL 5.0.2
Submitted: 21 Jan 2005 3:16 Modified: 21 Jan 2005 5:22
Reporter: David Nugent Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 OS:FreeBSD (FreeBSD 5.3)
Assigned to: Dean Ellis CPU Architecture:Any

[21 Jan 2005 3:16] David Nugent
Description:
Insertion of values into DOUBLE fields in 5.0.1 and 5.0.2 consistently results in the insertion of a 9.99999 value or -9.99999 (depending on the +/- sign of the value inserted) instead of the actual value inserted.

This works fine in 5.0.0 and previous.

How to repeat:
Previous (correct) behaviour:

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37 to server version: 5.0.0-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> create table t ( value double(5,5) );
Query OK, 0 rows affected (0.61 sec)

mysql> insert into t set value=55.43210;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----------+
| value    |
+----------+
| 55.43210 |
+----------+
1 row in set (0.00 sec)

Behaviour in 5.0.2 (using FreeBSD 4.7 binaries downloaded from mysql.com):

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.2-alpha-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> create table t ( value double(5,5) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t set value=55.43210;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into t set value=-55.43210;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t;
+----------+
| value    |
+----------+
|  9.99999 |
| -9.99999 |
+----------+
2 rows in set (0.00 sec)

Suggested fix:
Unknown
[21 Jan 2005 4:17] David Nugent
Ok, I now believe this to be my error and it simply highlights a difference between versions and how they each cope with and compensate for the error.

Specification in the table creation of the width is incorrect and too small; 5.0.1 is truncating the value whereas earlier MySQL versions allowed it without warning.
[21 Jan 2005 5:22] Dean Ellis
Yes, this is expected.  The 5.0.0 behavior you noted looks like an old bug that has (as you see) already been fixed, as you noted.  Thanks for the update.