Bug #43953 wrong calculation with unsigned integers
Submitted: 30 Mar 2009 8:06 Modified: 30 Mar 2009 9:09
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.32 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[30 Mar 2009 8:06] Peter Laursen
Description:
When substracting two unsigned integer columns and result is returning a negative value the result variables is also handled as unsigned what results in a very high and wrong number. (I the example (maxint - 7), I guess!

Reproduced with 5.1.32 but also reported with older version (not specified) by a customer of ours.

How to repeat:
CREATE TABLE `tab1` ( 
  `id` INT(11) NOT NULL AUTO_INCREMENT, 
  `priority` INT(11) DEFAULT NULL, 
  `day` INT(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8; 

INSERT INTO `tab1`(`id`,`priority`,`day`) VALUES (86,1,8); 

SELECT (tab1.priority-tab1.day) FROM tab1 WHERE id=86; -- returns '7' 

-- changing operands from SIGNED to UNSIGNED integers.
ALTER TABLE `test`.`tab1` CHANGE `priority` `priority` INT(11) UNSIGNED NULL , CHANGE `day` `day` INT(11) UNSIGNED NULL ; 

SELECT (tab1.priority-tab1.day) FROM tab1 WHERE id=86; -- returns '18446744073709551609'

Suggested fix:
a UNSIGNED INT (and btw. also the longest INT type supported by system)should be used for the result.
[30 Mar 2009 8:23] Peter Laursen
Correction: the 1st subtract operation returns (-7) of course!  That was the point actually!
[30 Mar 2009 9:06] Sveta Smirnova
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

According to http://dev.mysql.com/doc/refman/5.1/en/arithmetic-functions.html:

"If one of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer. "

So this is expected behavior.
[30 Mar 2009 9:07] Peter Laursen
With hindsight this can be a little tricky!

Consider that columns were BIGINT UNSIGNED and the calculation goes like (1 - <highest possible value for BIGINT UNSIGNED> (not to mention a caluclation of the form a-b-c-d!).
[30 Mar 2009 9:09] Peter Laursen
Sorry for reopening. It was not intentional. We 'cross-answered' here.  Thanks for the clarification!
[30 Mar 2009 10:56] Sergei Golubchik
Just to add that it's configurable, check the manual for NO_UNSIGNED_SUBTRACTION