MySQL Bugs: #21497: DOUBLE truncated to unusable value

Bug #21497	DOUBLE truncated to unusable value
Submitted:	8 Aug 2006 2:12	Modified:	7 Jan 17:32
Reporter:	Kolbe Kegel
Status:	Need Doc Info
Category:	Server	Severity:	S2 (Serious)
Version:	5.0,5.1	OS:	Any
Assigned to:	Alexey Kopytov	Target Version:
Tags:	truncate, mysqldump, double
Triage:	D3 (Medium)

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.