MySQL Bugs: #13573: wrong data inserted for too big values for INSERT into DECIMAL column

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

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

test file cs19.test

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

result file cs019.result

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

just detected that the last added commands in the test file (inserts with ...E..) shows differences between Windows and Linux:

! 6412345600000000899492411331745085252813067451936489276033728512
--- 
! 6412345600000000900000000000000000000000000000000000000000000000

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

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

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

Noted in 5.0.16 changelog.