MySQL Bugs: #28121: INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits

Bug #28121	INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits
Submitted:	26 Apr 2007 13:17	Modified:	19 Jun 2007 0:49
Reporter:	Dan Rucci	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Linux (Fedora Core 6)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
I have searched and looked for answers/solutions but have been unable to find anything. I have marked this as S1 (Critical) because it is critical to my application.

I have a database table where I am storing VERY large numbers.... numbers up to 200 digits long.

I had this working on my MySQL 4 database but we migrated to MySQL 5.0.27 this week and I am having issues.

I have a field set to DOUBLE(200,0).

When I try and insert/update a large number like 99999999999999999999999999999999999999999999999999999999999999999999999999999

I check the data and it is "truncated" to: 9999999999999999635896294965248

How to repeat:
1) Create a new table
2) Create a new field of datatype DOUBLE(200,0)
3) Insert a new record giving that new field a massive value like 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
4) Select from the table and have a look at the value.

I have had a few people repeat this for me as a sanity check and all have had the same result.

Keep in mind this currently works perfectly on MySQL 4 databases I have running.

Suggested fix:
I have no idea, which is the reason for this bug report.

This does work as descibed in the bug report.

Which version of MySQL did you run before? You state 4, but do not specify if it is 4.0.x or 4.1.x.

Since 4.0, the behavior of DOUBLE fields has changed. Quoting the manual on upgrading form 4.0 to 4.1:

"Incompatible change: As of MySQL 4.1.2, handling of the FLOAT and DOUBLE floating-point data types is more strict to follow standard SQL. For example, a data type of FLOAT(3,1) stores a maximum value of 99.9. Before 4.1.2, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. As of 4.1.2, the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 4.1.2 and that contain floating-point data not strictly legal for the data type, you should alter the data types of those columns. For example:

ALTER TABLE tbl_name MODIFY col_name FLOAT(4,1);"

A valid example for you, if you have indeed upgraded from 4.0, would be:

ALTER TABLE tbl_name MODIFY col_name DOUBLE;

Thank you for the report.

Verified as described.

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/commits/26023

ChangeSet@1.2470, 2007-05-03 18:40:34+04:00, kaa@polly.local +4 -0
  Fix for bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits"
  
  When storing a large number to a FLOAT or DOUBLE field with fixed length, it could be incorrectly truncated if the field's length was greater than 31.
  
  This patch also does some code cleanups to be able to reuse code which is common between Field_float::store() and Field_double::store().

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/commits/27210

ChangeSet@1.2470, 2007-05-23 16:19:11+04:00, kaa@polly.local +9 -0
  Fix for bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits"
  
  When storing a large number to a FLOAT or DOUBLE field with fixed length, it could be incorrectly truncated if the field's length was greater than 31.
  
  This patch also does some code cleanups to be able to reuse code which is common between Field_float::store() and Field_double::store().

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/commits/27432

ChangeSet@1.2470, 2007-05-28 12:44:59+04:00, kaa@polly.local +10 -0
  Fix for bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits"
  
  When storing a large number to a FLOAT or DOUBLE field with fixed length, it could be incorrectly truncated if the field's length was greater than 31.
  
  This patch also does some code cleanups to be able to reuse code which is common between Field_float::store() and Field_double::store().

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/commits/27440

ChangeSet@1.2504, 2007-05-28 14:22:21+04:00, kaa@polly.local +1 -0
  Changed the warning messages in the testcase to match 5.1 text.
  
  This is for bug #28121.

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/commits/27497

ChangeSet@1.2503, 2007-05-28 21:43:31+04:00, kaa@polly.local +1 -0
  Don't use log_01[] in my_strtod() to avoid loss of precision.
  
  This is for bug #28121.

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/commits/27581

ChangeSet@1.2504, 2007-05-29 16:35:55+04:00, kaa@polly.local +1 -0
  Moved size_of() from Field_double to Field_real in order to properly clone Field_float and Field_double. This is to fix PushBuild failures introduced by the patch for bug #28121.

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/commits/27722

ChangeSet@1.2505, 2007-05-30 22:47:52+04:00, kaa@polly.local +3 -0
  Got rid of log_01[], because we don't really need it. Division and log_10[] can always be used instead, which is also a more precise way.
  
  This is for bug #28121.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.

Storing a large number into a FLOAT or DOUBLE column with a fixed
length could result in incorrect truncation of the number if the
columns's length was greater than 31.

hi 
i want to insert a field data like this
137.7 136.6 138.9 123.5 143.0
123.2 135.2 156.3 187.9 156.3
140.8 211.1 901.1 892.1 327.1
.............................

Like this as 65 rows how to insert this field into a table and what is the size i have to give for this field.

Looking through this,  I'm still not sure what the solution is for storing very large numbers like the Original person who started this report wanted to do.

I'm running into the same thing right now.