Last week I received a bug from Yahoo! search team that when LOAD DATA INFILE is used to load data, few values were inserted incorrectly in to the MySQL 5.1.21 table where as the same one inserts right values when used with MySQL 4.0 or 4.1.
After debugging found that the regression is in the new strntoull10rnd function which was replaced by old strtoull which was present in the old versions of MySQL and introduced recently. The new function was not properly tested as it just appends the digits after the dot without proper verification.
Here is the simple case which fails with the new MySQL server version 5.0.45 or 5.1.21:
case 1:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
mysql> create table t1 (a bigint); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values ("1.0000000000"); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values ("1.000000000000000000"); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values ("1.0000000000000000000000"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +---------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +---------------------+ | a | +---------------------+ | 1 | | 1 | | 9223372036854775807 | +---------------------+ 3 rows in set (0.00 sec) |
case 2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> create table t(c1 bigint); Query OK, 0 rows affected (0.02 sec) mysql> insert into t values('15449237462.0000000000'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'c1' at row 1 | +---------+------+---------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t; +---------------------+ | c1 | +---------------------+ | 9223372036854775807 | +---------------------+ 1 row in set (0.00 sec) |
So, I made a simple patch by using the old strtoull code instead of fixing the new function. Lets wait for MySQL to have a fix for this. The bug is reported in the mysql bugs database.