MySQL Bugs: #11102: load data infile inserts -9.999999 or 9.999999 when using 4.1 or higher

Bug #11102	load data infile inserts -9.999999 or 9.999999 when using 4.1 or higher
Submitted:	4 Jun 2005 23:29	Modified:	5 Oct 2005 19:14
Reporter:	Jason Rickabaugh	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Fedora Core 3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When using load data infile to incorect data is loaded into the database 
ie -9.999999 or 9.999999.

How to repeat:
Steps to reporduce
create table test(
id int primary key,
frlong          float(3,6)
) type=innodb;

LOAD DATA INFILE '/tsv/test' INTO TABLE objects
 FIELDS TERMINATED BY '\t' optionally ENCLOSED BY '\'' ESCAPED BY '\\'
 LINES TERMINATED BY '\n';

Suggested fix:
?

Work around:
Remove the float parameters.
create table test(
id int primary key,
frlong          float
) type=innodb;

float(3,6) is an odd data type...

Are you really sure you want a float with a display width of 3 but 6 decimal digits?
A display width that is less than the number of decimals usually doesn't make
sense.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. If UNSIGNED is specified, negative values are disallowed. M is the display width and D is the number of significant digits. FLOAT without arguments or FLOAT(p) (where p is in the range from 0 to 24) stands for a single-precision floating-point number.

This is not a bug.
Try SHOW CREATE TABLE test;
you will see that MySQL adjusted column's definition to be FLOAT(7,6),
because having display width less than number of decimals is invalid.

We'll document this behaviour better, though
(in "Silent column changes" and in "Column types" nodes)

Actually, there *is* a bug here.  The float(3,6) declaration
is changed to float(7,6) in 4.1.15, but in 5.0.14, it's not
changed:

mysql> create table t (f float(3,6));
Query OK, 0 rows affected (0.15 sec)

mysql> desc t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| f     | float(3,6) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.25 sec)

Either the column definition should have been rejected with
an error, or the data type should have been changed to
float(7,6). Neither happened, so I believe this is a bug.

Resetting from Docs to Server category, and unassigning
from myself.  After the problem is dealt with, please set the
report to Documenting.  Thanks.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30072

Duplicated by Bug #12694, which is now fixed.