Bug #21497 DOUBLE truncated to unusable value
Submitted: 8 Aug 2006 0:12 Modified: 28 Apr 2010 1:52
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0,5.1 OS:Any
Assigned to: Alexey Kopytov
Tags: double, mysqldump, truncate
Triage: D3 (Medium)

[8 Aug 2006 0:12] Kolbe Kegel
Description:
When inserting an extraordinarly large value into a DOUBLE column, the value can be truncated in such a way that the new value cannot be reloaded manually or from the output of mysqldump.

How to repeat:
CREATE TABLE d1 (d DOUBLE);
INSERT INTO d1 VALUES (1.7976931348623157E+308);
SELECT * FROM d1;
INSERT INTO d1 VALUES (1.79769313486232e+308);

mysql 5.0.23-max (root) [test]> CREATE TABLE d1 (d DOUBLE);
Query OK, 0 rows affected (0.03 sec)

mysql 5.0.23-max (root) [test]> INSERT INTO d1 VALUES (1.7976931348623157E+308);
Query OK, 1 row affected (0.00 sec)

mysql 5.0.23-max (root) [test]> SELECT * FROM d1;
+-----------------------+
| d                     |
+-----------------------+
| 1.79769313486232e+308 |
+-----------------------+
1 row in set (0.00 sec)

mysql 5.0.23-max (root) [test]> INSERT INTO d1 VALUES (1.79769313486232e+308);
ERROR 1367 (22007): Illegal double '1.79769313486232e+308' value found during parsing

Also interesting:

INSERT INTO d1 SELECT * FROM d1;
SELECT * FROM d1;

mysql 5.0.23-max (root) [test]> INSERT INTO d1 SELECT * FROM d1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.0.23-max (root) [test]> SELECT * FROM d1;
+-----------------------+
| d                     |
+-----------------------+
| 1.79769313486232e+308 |
| 1.79769313486232e+308 |
+-----------------------+
2 rows in set (0.00 sec)

Suggested fix:
The value displayed by the client, and included in mysqldump output, should be a legal value that can be used in an INSERT statement in such a fashion that mysqldump can permit a lossless restore of all legal values in a DOUBLE column.
[23 Sep 2006 0:46] Timothy Smith
This bug is flagged "To be fixed later" because the fix requires non-trivial development effort.  We must not use C library functions for displaying (and reading) floating-point numbers.  Instead, we must use our own functions for this (like the dtoa() of netlib, etc.).

This will fix a number of related bugs.
[8 Dec 2007 19:47] Alexey Kopytov
Fixed in 6.0 by the patch for WL #2934.
[25 Jan 2008 12:39] Bugs System
Pushed into 6.0.5-alpha
[25 Jan 2008 19:52] Paul Dubois
Noted in 6.0.5 changelog.
[15 Oct 2008 13:20] rol bole
I have the same problem. In my case the truncation happens when i use connenctor/j versions 3.0.17, 5.1.3 and 5.1.6. However i do not get the truncation using connector/j version 5.0.3.
Also when viewing the double value stored in the database when using connector/j version 5.0.3 through the MySQL Query Browser, the value is truncated. Viewing the value using DbVisualizer shows the correct value.
I also tried debugging into the connector/j version 5.1.6 source code but did not get any answer to the problem.
I will try to find the version 6.0 on which this has been fixed and try it out.
[15 Oct 2008 13:30] rol bole
Just a quick question please. Where do i get the version of connector/j with the fix to this bug?
[7 Jan 2010 16:32] Paul Dubois
Setting report to NDI pending push to Celosia.
[24 Feb 2010 20:29] Paul Dubois
Setting report to Need Merge pending push of Celosia to release tree.
[6 Mar 2010 11:08] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@fedora12-20091225154921-x25a5pyw1pxiwobv) (merge vers: 5.5.99) (pib:16)
[6 Mar 2010 19:25] Paul Dubois
Noted in 5.5.3 changelog.
[27 Apr 2010 9:48] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)
[27 Apr 2010 9:51] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)
[28 Apr 2010 1:52] Paul Dubois
Already fixed in 5.5.x.
[20 Mar 2014 0:16] Shari Schutz
This bug is present in MariaDB 5.5.36.  Shouldn't it be fixed?  

I downloaded the rpms for maria db version 5.5.36 from the centos6-amd64/rpms folder and successfully installed them on centos 6.5.