MySQL Bugs: #67680: Data overflow in simple operation with unsigned int field

Bug #67680	Data overflow in simple operation with unsigned int field
Submitted:	22 Nov 2012 22:15	Modified:	23 Nov 2012 17:42
Reporter:	thomas schubarth	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.63-nmm1-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	mathematical operation, unsigned int

Description:
I have a table with a unsigned integer field. If I make a select and subtract a constant number from the field value, which would lead to a negative value as result, there is a data overflow in resultset.

How to repeat:
CREATE TABLE `tbl` (
  `n` int(10) unsigned NOT NULL,
  `score` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`n`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `tbl` (`n`,`score`) VALUES ('1','1344603119');

SELECT score FROM `tbl`;
SELECT score - 1344603119 FROM `tbl`;
SELECT score - 1344603120 FROM `tbl`;

The first two selects work fine.
The third select should give me -1 as result, but a data overflow is returned.
The failure disappears, if a make the score field NOT unsigned!

This is probably not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/out-of-range-and-overflow.html:

"Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default."

Then you probably have strict SQL mode enable, hence the error.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Valeriy is correct: this is not a bug