MySQL Bugs: #21497: DOUBLE truncated to unusable value

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	CPU Architecture:	Any
Tags:	double, mysqldump, truncate

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.

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.

Fixed in 6.0 by the patch for WL #2934.

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.

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.

Just a quick question please. Where do i get the version of connector/j with the fix to this bug?

Setting report to NDI pending push to Celosia.

Setting report to Need Merge pending push of Celosia to release tree.

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)

Noted in 5.5.3 changelog.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)

Already fixed in 5.5.x.

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.