Out of range value for Integer

Question

Out of range value for Integer column type does not truncate to it's maximum value if there are more than 19 digits. In the following example, there should not be -1 value in the second row. Is it a bug?

drop table if exists test;

CREATE TABLE `test` (
  `col1` int(11) NOT NULL,
  `col2` int(11) NOT NULL,
  `col3` int(11) NOT NULL,
  `col4` int(11) NOT NULL,
  `col5` int(11) NOT NULL,
  `col6` int(11) NOT NULL,
  `col7` int(11) NOT NULL,
  `col8` int(11) NOT NULL,
  `lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `lastupdateid` varchar(100) NOT NULL
) ENGINE=InnoDB;


insert into test values('6022','2123456789012345678', '00','00','00','00','00','00', NULL, 'test');

mysql>select * from test;
+------+------------+------+------+------+------+------+------+---------------------+--------------+
| col1 | col2       | col3 | col4 | col5 | col6 | col7 | col8 | lastupdate          | lastupdateid |
+------+------------+------+------+------+------+------+------+---------------------+--------------+
| 6022 | 2147483647 |    0 |    0 |    0 |    0 |    0 |    0 | 2009-10-28 18:20:30 | test         | 
+------+------------+------+------+------+------+------+------+---------------------+--------------+
1 row in set (0.00 sec)


insert into test values('6022','21234567890123456789', '00','00','00','00','00','00', NULL, 'test');

mysql>select * from test;
+------+------------+------+------+------+------+------+------+---------------------+--------------+
| col1 | col2       | col3 | col4 | col5 | col6 | col7 | col8 | lastupdate          | lastupdateid |
+------+------------+------+------+------+------+------+------+---------------------+--------------+
| 6022 | 2147483647 |    0 |    0 |    0 |    0 |    0 |    0 | 2009-10-28 18:20:30 | test         | 
| 6022 |         -1 |    0 |    0 |    0 |    0 |    0 |    0 | 2009-10-28 18:20:34 | test         | 
+------+------------+------+------+------+------+------+------+---------------------+--------------+
2 rows in set (0.00 sec)

Answer 1

It's not a bug, it's merely a case of integer overflow.

If you need your number-in-string value to max out, you should add code to do that, either when you do the insert, or as a trigger.

See http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html for more information.

You can also experiment with small selects (untested, I don't have MySQL access here):

SELECT CAST('2123456789012345678' AS INT(11))
SELECT CAST('21234567890123456789' AS INT(11))