Bug #57339 Max Double value out of range in select and dump
Submitted: 8 Oct 2010 13:11 Modified: 28 Mar 2011 17:12
Reporter: Dominique STOFFEL Email Updates:
Status: Patch pending Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:MySQL Server 5.1.51 OS:Microsoft Windows (Windows XP, 2003, 7)
Assigned to: CPU Architecture:Any
Tags: double, Max value, MySQL

[8 Oct 2010 13:11] Dominique STOFFEL
Description:
With MySQL server 5.1.50 and 5.1.51
Double max value 1.7976931348623157E+308(idem min value) return a wrong value in select and mysqldump. The returned value: 1.79769313486232e+308 is out of range.

When you want to import dump file, you get this error message :
ERROR 1367 (22007) at line 40: Illegal double '1.79769313486232e+308' value found during parsing
...
You can't load your dump file!

N.B: It's ok in 5.5.6 RC

How to repeat:
1) Select
CREATE  TABLE `test`.`T1` (`id` INT NOT NULL ,`val` DOUBLE NULL , PRIMARY KEY (`id`) );

INSERT INTO T1 VALUES (2,1.7976931348623157E+308);

select * from T1;
+----+------------------------+
| id | val                    |
+----+------------------------+
|  2 |  1.79769313486232e+308 |
+----+------------------------+

The value (of val) is not the same (rounded) and out of range 
(> 1.7976931348623157E+308)

2) Dump (mysqldump.exe)

...
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (2,1.79769313486232e+308);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
...

Suggested fix:
For dump file, to replace 1.79769313486232e+308 by 1.7976931348623157E+308
For MySQL Server : to return the good value (not rounded): 1.7976931348623157E+308

It's ok in 5.5.6 RC
[8 Oct 2010 13:27] Alexey Kopytov
Duplicate of bug #21497. The bug is fixed in 5.5. I don't think it will be fixed in 5.1.