MySQL Bugs: #12750: Incorrect storage of 9999999999999999999 in DECIMAL(19, 0)

Bug #12750	Incorrect storage of 9999999999999999999 in DECIMAL(19, 0)
Submitted:	23 Aug 2005 10:31	Modified:	13 Sep 2005 22:42
Reporter:	Michael G. Zinner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.10 & 5.0.12-debug/BK source	OS:	Windows (Windows/Linux)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
The value 9999999999999999999 is stored incorrectly in a DECIMAL(19, 0) column. 

It does not even help to change the column type to DECIMAL(20, 0) which still results in an incorrect storage.

How to repeat:
DROP DATABASE IF EXISTS `scott`;

CREATE DATABASE `scott`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;

CREATE TABLE `scott`.`test007_number` (
  `col1` INT(1) NULL,
  `col2` INT(2) NULL,
  `col3` INT(3) NULL,
  `col4` INT(4) NULL,
  `col5` INT(5) NULL,
  `col6` INT(6) NULL,
  `col7` INT(7) NULL,
  `col8` INT(8) NULL,
  `col9` INT(9) NULL,
  `col10` BIGINT(10) NULL,
  `col11` BIGINT(11) NULL,
  `col12` BIGINT(12) NULL,
  `col13` BIGINT(13) NULL,
  `col14` BIGINT(14) NULL,
  `col15` BIGINT(15) NULL,
  `col16` BIGINT(16) NULL,
  `col17` BIGINT(17) NULL,
  `col18` BIGINT(18) NULL,
  `col19` DECIMAL(19, 0) NULL,
  `col20` DECIMAL(20, 0) NULL,
  `col21` DECIMAL(21, 0) NULL,
  `col22` DECIMAL(22, 0) NULL,
  `col23` DECIMAL(23, 0) NULL,
  `col24` DECIMAL(24, 0) NULL,
  `col25` DECIMAL(25, 0) NULL,
  `col26` DECIMAL(26, 0) NULL,
  `col27` DECIMAL(27, 0) NULL,
  `col28` DECIMAL(28, 0) NULL,
  `col29` DECIMAL(29, 0) NULL,
  `col30` DECIMAL(30, 0) NULL,
  `col31` DECIMAL(31, 0) NULL,
  `col32` DECIMAL(32, 0) NULL,
  `col33` DECIMAL(33, 0) NULL,
  `col34` DECIMAL(34, 0) NULL,
  `col35` DECIMAL(35, 0) NULL,
  `col36` DECIMAL(36, 0) NULL,
  `col37` DECIMAL(37, 0) NULL,
  `col38` DECIMAL(38, 0) NULL,
  `fix1` DECIMAL(38, 1) NULL,
  `fix2` DECIMAL(38, 2) NULL,
  `fix3` DECIMAL(38, 3) NULL,
  `fix4` DECIMAL(38, 4) NULL,
  `fix5` DECIMAL(38, 5) NULL,
  `fix6` DECIMAL(38, 6) NULL,
  `fix7` DECIMAL(38, 7) NULL,
  `fix8` DECIMAL(38, 8) NULL,
  `fix9` DECIMAL(38, 9) NULL,
  `fix10` DECIMAL(38, 10) NULL,
  `fix11` DECIMAL(38, 11) NULL,
  `fix12` DECIMAL(38, 12) NULL,
  `fix13` DECIMAL(38, 13) NULL,
  `fix14` DECIMAL(38, 14) NULL,
  `fix15` DECIMAL(38, 15) NULL,
  `fix16` DECIMAL(38, 16) NULL,
  `fix17` DECIMAL(38, 17) NULL,
  `fix18` DECIMAL(38, 18) NULL,
  `fix19` DECIMAL(38, 19) NULL,
  `fix20` DECIMAL(38, 20) NULL,
  `fix21` DECIMAL(38, 21) NULL,
  `fix22` DECIMAL(38, 22) NULL,
  `fix23` DECIMAL(38, 23) NULL,
  `fix24` DECIMAL(38, 24) NULL,
  `fix25` DECIMAL(38, 25) NULL,
  `fix26` DECIMAL(38, 26) NULL,
  `fix27` DECIMAL(38, 27) NULL,
  `fix28` DECIMAL(38, 28) NULL,
  `fix29` DECIMAL(38, 29) NULL,
  `fix30` DECIMAL(38, 30) NULL
)
ENGINE = INNODB;

INSERT INTO `scott`.`test007_number`(`col1`, `col2`, `col3`, `col4`, `col5`, `col6`, `col7`, `col8`, `col9`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`, `col31`, `col32`, `col33`, `col34`, `col35`, `col36`, `col37`, `col38`, `fix1`, `fix2`, `fix3`, `fix4`, `fix5`, `fix6`, `fix7`, `fix8`, `fix9`, `fix10`, `fix11`, `fix12`, `fix13`, `fix14`, `fix15`, `fix16`, `fix17`, `fix18`, `fix19`, `fix20`, `fix21`, `fix22`, `fix23`, `fix24`, `fix25`, `fix26`, `fix27`, `fix28`, `fix29`, `fix30`)
VALUES (9, 99, 999, 9999, 99999, 999999, 9999999, 99999999, 999999999, 9999999999, 99999999999, 999999999999, 9999999999999, 99999999999999, 999999999999999, 9999999999999999, 99999999999999999, 999999999999999999, 9999999999999999999, 99999999999999999999, 999999999999999999999, 9999999999999999999999, 99999999999999999999999, 999999999999999999999999, 9999999999999999999999999, 99999999999999999999999999, 999999999999999999999999999, 9999999999999999999999999999, 99999999999999999999999999999, 999999999999999999999999999999, 9999999999999999999999999999999, 99999999999999999999999999999999, 999999999999999999999999999999999, 9999999999999999999999999999999999, 99999999999999999999999999999999999, 999999999999999999999999999999999999, 9999999999999999999999999999999999999, 99999999999999999999999999999999999999, 9999999999999999999999999999999999999.9, 999999999999999999999999999999999999.99, 99999999999999999999999999999999999.999, 9999999999999999999999999999999999.9999, 999999999999999999999999999999999.99999, 99999999999999999999999999999999.999999, 9999999999999999999999999999999.9999999, 999999999999999999999999999999.99999999, 99999999999999999999999999999.999999999, 9999999999999999999999999999.9999999999, 999999999999999999999999999.99999999999, 99999999999999999999999999.999999999999, 9999999999999999999999999.9999999999999, 999999999999999999999999.99999999999999, 99999999999999999999999.999999999999999, 9999999999999999999999.9999999999999999, 999999999999999999999.99999999999999999, 99999999999999999999.999999999999999999, 9999999999999999999.9999999999999999999, 999999999999999999.99999999999999999999, 99999999999999999.999999999999999999999, 9999999999999999.9999999999999999999999, 999999999999999.99999999999999999999999, 99999999999999.999999999999999999999999, 9999999999999.9999999999999999999999999, 999999999999.99999999999999999999999999, 99999999999.999999999999999999999999999, 9999999999.9999999999999999999999999999, 999999999.99999999999999999999999999999, 99999999.999999999999999999999999999999);

mysql> SELECT * FROM `scott`.`test007_number` \G
*************************** 1. row ************************
 col1: 9
 col2: 99
 col3: 999
 col4: 9999
 col5: 99999
 col6: 999999
 col7: 9999999
 col8: 99999999
 col9: 999999999
col10: 9999999999
col11: 99999999999
col12: 999999999999
col13: 9999999999999
col14: 99999999999999
col15: 999999999999999
col16: 9999999999999999
col17: 99999999999999999
col18: 999999999999999999
col19: -8446744073709551617
col20: 99999999999999999999
col21: 999999999999999999999
col22: 9999999999999999999999
col23: 99999999999999999999999
col24: 999999999999999999999999
col25: 9999999999999999999999999
col26: 99999999999999999999999999
col27: 999999999999999999999999999
col28: 9999999999999999999999999999
col29: 99999999999999999999999999999
col30: 999999999999999999999999999999
col31: 9999999999999999999999999999999
col32: 99999999999999999999999999999999
col33: 999999999999999999999999999999999
col34: 9999999999999999999999999999999999
col35: 99999999999999999999999999999999999
col36: 999999999999999999999999999999999999
col37: 9999999999999999999999999999999999999
col38: 99999999999999999999999999999999999999
 fix1: 9999999999999999999999999999999999999.9
 fix2: 999999999999999999999999999999999999.99
 fix3: 99999999999999999999999999999999999.999
 fix4: 9999999999999999999999999999999999.9999
 fix5: 999999999999999999999999999999999.99999
 fix6: 99999999999999999999999999999999.999999
 fix7: 9999999999999999999999999999999.9999999
 fix8: 999999999999999999999999999999.99999999
 fix9: 99999999999999999999999999999.999999999
fix10: 9999999999999999999999999999.9999999999
fix11: 999999999999999999999999999.99999999999
fix12: 99999999999999999999999999.999999999999
fix13: 9999999999999999999999999.9999999999999
fix14: 999999999999999999999999.99999999999999
fix15: 99999999999999999999999.999999999999999
fix16: 9999999999999999999999.9999999999999999
fix17: 999999999999999999999.99999999999999999
fix18: 99999999999999999999.999999999999999999
fix19: 9999999999999999999.9999999999999999999
fix20: 999999999999999999.99999999999999999999
fix21: 99999999999999999.999999999999999999999
fix22: 9999999999999999.9999999999999999999999
fix23: 999999999999999.99999999999999999999999
fix24: 99999999999999.999999999999999999999999
fix25: 9999999999999.9999999999999999999999999
fix26: 999999999999.99999999999999999999999999
fix27: 99999999999.999999999999999999999999999
fix28: 9999999999.9999999999999999999999999999
fix29: 999999999.99999999999999999999999999999
fix30: 99999999.999999999999999999999999999999
1 row in set (0.00 sec)

Thanks for bug report. I was able to reproduce the bug on Windows.

col17: 99999999999999999
col18: 999999999999999999
col19: -8446744073709551617

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 5.0.13

For documentation team:
This fixes the old problem with "integer wrapping" when storing unsigned longlong values (integers between 9223372036854775808 and 18446744073709551615) in a field.  Instead of getting a
'complement' value on when updating a numeric field to a bigint in this range, one will now get the maximum allowed number value for the field type (or error in strict mode).