Bug #13573 wrong data inserted for too big values for INSERT into DECIMAL column
Submitted: 28 Sep 2005 15:53 Modified: 9 Nov 2005 3:51
Reporter: Carsten Segieth Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.14-bk (Linux), 5.0.13-rc (Win) OS:Linux (Linux, Win)
Assigned to: Alexey Botchkov CPU Architecture:Any

[28 Sep 2005 15:53] Carsten Segieth
Description:
Some (not all!) values that are too long to fit the DECIMAL(nn) column specification are not truncated and set to the 'max' value of this colum, but instead '0' is inserted.
It seems to depend on the value, if it contains almost '0' as digits the problem occurs, if it's a random number it is truncated as described in the manual.

Trying to insert a 83digit number (83000...000) results in a '0' inserted, using a 82digit one a 64digit '999..999' (= MAX value in this test) is inserted. 
And trying to add '6.4123456E63' shows a warning but inserts the data (filled with "random" numbers from FLOAT -> DECIMAL conversion, here filling with '0' would be much better).

Also it looks inconsistent when / whether only 

 Warning 1264 Out of range value adjusted for column 'c1' at row 1

or both the warning and 

 Error 1292 Truncated incorrect DECIMAL value: '' 

(without a value in the ' ' filed) is shown.

How to repeat:
create table tt1 (c1 decimal(64));
insert into tt1 values( 89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 );
insert into tt1 values( 89123456789012345678901234567890123456789012345678901234567890123456789012345678900000000 );
insert into tt1 values( 65000000000000000000000000000000000000000000000000000000000000000 );
insert into tt1 values( 65123456789012345678901234567890123456789012345678901234567890000 );
insert into tt1 values( 6412345678901234567890123456789012345678901234567890123456789000 );
insert into tt1 values(  6.4123456E63 );
select * from tt1;

Suggested fix:
- truncate ALL values that are too big to the same 'max' value OR insert into table without warning if value fits
- check showing warnings / errors
[28 Sep 2005 15:56] Carsten Segieth
test file cs19.test

Attachment: cs019.test (application/octet-stream, text), 12.76 KiB.

[28 Sep 2005 15:56] Carsten Segieth
result file cs019.result

Attachment: cs019.result (application/octet-stream, text), 3.82 KiB.

[28 Sep 2005 16:10] Carsten Segieth
just detected that the last added commands in the test file (inserts with ...E..) shows differences between Windows and Linux:

! 6412345600000000899492411331745085252813067451936489276033728512
--- 
! 6412345600000000900000000000000000000000000000000000000000000000
[11 Oct 2005 17:52] Bugs System
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/30928
[15 Oct 2005 16:42] Bugs System
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/31134
[26 Oct 2005 6:40] Bugs System
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/31487
[9 Nov 2005 3:51] Paul Dubois
Noted in 5.0.16 changelog.