MySQL Bugs: #10463: float field accepting non-numerical value

Bug #10463	float field accepting non-numerical value
Submitted:	9 May 2005 9:39	Modified:	9 May 2005 13:37
Reporter:	Martijn Korse	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23.41	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
It appears that in 3.x releases fields of the type FLOAT accept any string that starts with 'nan'. Well, it always accepts string-values, but it always converts them to 0; the differece is that with a string that starts with 'nan' it actually saves the value as 'nan'. This is a serious bug, because when trying to use a dump for backup-purposes, the dump fails when it encounters that 'nan' value - as it is a float-field nan is not quoted and thus considered as a (non-existing) field.

Here's some ouput that should further clarify it:

############
mysql> CREATE TABLE test (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> test FLOAT,
    -> PRIMARY KEY(id),
    -> UNIQUE(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO test (id, test) VALUES (NULL, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (id, test) VALUES (NULL, 1.5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (id, test) VALUES (NULL, 0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (id, test) VALUES (NULL, 'nan');
INSERT INTO test (id, test) VALUES (NULL, 'nansomething');Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test (id, test) VALUES (NULL, 'NaN');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (id, test) VALUES (NULL, 'nansomething');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO test (id, test) VALUES (NULL, 'ana');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+------+
| id | test |
+----+------+
|  1 |    1 |
|  2 |  1.5 |
|  3 |    0 |
|  4 |  nan |
|  5 |  nan |
|  6 |  nan |
|  7 |    0 |
+----+------+
7 rows in set (0.00 sec)

mysql>

How to repeat:
create a table with a FLOAT Field, do an insert with 'nan' as the value for the FLOAT field and do a dump of that table. You will see the this field actually contains the value 'nan' now.

This is how you can replicate it:

############
CREATE TABLE test (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	test FLOAT,
	PRIMARY KEY(id),
	UNIQUE(id)
);

INSERT INTO test (id, test) VALUES (NULL, 1);
INSERT INTO test (id, test) VALUES (NULL, 1.5);
INSERT INTO test (id, test) VALUES (NULL, 0);
INSERT INTO test (id, test) VALUES (NULL, 'nan');
INSERT INTO test (id, test) VALUES (NULL, 'NaN');
INSERT INTO test (id, test) VALUES (NULL, 'nansomething');
INSERT INTO test (id, test) VALUES (NULL, 'ana');
############

Now do a SELECT * FROM test and the bug will become visible

Suggested fix:
This bus seems to be fixed in 4.0.18.
I could not test it in other versions

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Thank you for your automated response.

As i indicated myself: this bug HAS, as a matter of fact, already been fixed in 'a' newer version. But because i couldn't find a similar bug report i thought it would
a) be helpfull to get this bug documented for other people that use older versions
b) be helpful for the development team of mysql as the fix might have been more coincidental then an actual fix and therefor the bug might appear again in later versions. And, as i've said also: i couldn't test this in the latest version myself. I'm just a poor freelancer with limited resources.

If the mysql-dev team wishes to completely ignore this bug because i haven't been able to test it in the latest version, that's your problem....

p.s.
I think you should change the Status to Fixed, because it IS a bug in older versions and it WAS a bug in newer versions.