MySQL Bugs: #2082: mysqldump converts "inf" to NULL

Bug #2082	mysqldump converts "inf" to NULL
Submitted:	10 Dec 2003 12:12	Modified:	5 Mar 2004 11:22
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Victor Vagin	CPU Architecture:	Any

Description:
MySQLdump converts "nan" "inf" "-inf" to NULL during the dump, however this is not equivalent conversion so restoring mysqldump dump will result in wrong data restored.

It is possible one can use NULL and "inf" for different needs in applications.

How to repeat:
mysql> create table mdtest(f double);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into mdtest values(1e+111111111111111111111);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mdtest;
+------+
| f    |
+------+
|  inf |
+------+
1 row in set (0.01 sec)

pz@abyss:/tmp> mysqldump test mdtest
-- MySQL dump 8.22
--
-- Host: localhost    Database: test
---------------------------------------------------------
-- Server version       4.0.17-log

--
-- Table structure for table 'mdtest'
--

CREATE TABLE mdtest (
  f double default NULL
) TYPE=MyISAM;

--
-- Dumping data for table 'mdtest'
--

INSERT INTO mdtest VALUES (NULL);

Suggested fix:
We perhaps shall convert inf/-inf to appropriate numbers out of range which would result in appropriate value stored back. Alternatively we can just add handling of 
"inf" "-inf"

We however need option for current behavior as well as other databases may not allow to insert such out of range values.

Behaviour is proper, but has to be documented.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

This behavior is now documented.

Re-opened. While Docs can document the current behaviour (and has done so), the argument "it is proper" appears odd.
If a regular select somehow returns inf, and after mysqldump and restore that same select suddenly returns NULL, then the backup is incorrect. The idea of a backup is to be able to reproduce an exact copy of the original. The current behaviour breaks that behaviour.

And More test cases to show this behavior is inconsistent on the top of everything

1) Try to dump and restore this table.  Too large number which grew large enough
   to be inf magically becomes 0.

  create table tmp(c double not null); 
  insert into tmp values(1e+500);
  select * from tmp;

2) Try backup and restore of this table using SELECT INTO OUTFILE /LOAD DATA INFILE or "mysqldump --tab"  it works !

3) Try changing "double" to "float" in this case to get behavior more inline with MySQL behavior in other cases - You'll get maximum allowed float value 
instead of inf.

1. I failed to repeat "becomes 0" here
2. it doesn't. (at least on FreeBSD) It's bug#2595 - atof() behavoir is not portable. On some systems it reads "inf", on others - it doesn't. See bug#2595.
3. architecture-specific behaviour, not portable either

bk commit - 4.1 tree (vva:1.1706)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

we forbidded to store inf in fields
fix will be in mysql-4.1.2