MySQL Bugs: #32471: Conditional update statement not working as expected

Bug #32471	Conditional update statement not working as expected
Submitted:	17 Nov 2007 16:01	Modified:	29 Nov 2007 15:37
Reporter:	Tomas Valinčius	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45, 4.1, 5.0, 5.1 BK	OS:	Any (Linux, Windows)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	conditional, UPDATE

Description:
Hello,

I have a problem with a conditional update statement.
Here is my example:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`
(
  `ID` int(10) unsigned not null auto_increment,
  `Number` int(10) unsigned not null,
  
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM;
INSERT INTO `test` (`Number`) VALUES (5);

I want to decrease `Number` value by some other value that is not constant.
If decreasing will cause `Number` to be negative I want to set 0 as a value.
Here is my SQL statement:

UPDATE `test` SET `Number` =  IF(`Number` - 3 < 0, 0, `Number` - 3);

First statement results in `Number` value set to 2.
Executing this statement again I get an error "Out of range value adjusted for column 'Number' at row 1".
Every time when `Number`-MyValue becomes negative I get this error.

1) Why does not work my IF statement, which says to set value to 0 on negative subtraction result?

After this experience I modified my UPDATE statement to the following:

UPDATE IGNORE `test` SET `Number` =  IF(`Number` - 3 < 0, 0, `Number` - 3);

I expected this statement to "update to the closest valid value" as written in documentation. I was sure the negative result will be set to 0 as it is in fact the closest value for "int(10) unsigned" column. BUT it sets `Number` to 4294967295 on negative `Number`-MyValue result. Is this an expected behaviour?

Finally I found a way to achieve my goal by modifying UPDATE statement to this:

UPDATE IGNORE `test` SET `Number` =  IF(`Number` - 3 < 0, 0, `Number` - 3), `Number` = IF(`Number` = 4294967295, 0, `Number`);

but I think it's a bad example how to write SQL statements... I'm a bit afraid to use such statement, cause after changing int(10) to, for example, int(9) it won't work because of the hard coded value 4294967295.

How to repeat:
Conditional update statement not working as expected

Thank you for the report.

Verified as described.

Workaround: IF(`Number` < 3, 0, `Number` - 3)

There is also inconsistence with user variables:

set @v1 = 2;
select Number, IF(test.`Number` - 3 < 0, 0, test.`Number` - 3), IF(@v1 - 3 < 0, 0, @v1 - 3) from test;
Number  IF(test.`Number` - 3 < 0, 0, test.`Number` - 3) IF(@v1 - 3 < 0, 0, @v1 - 3)
2       -1      0

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

Binary operations (+, - etc) normally have a result type that is large enough to hold the result. 
Usually the "largest" argument type is used. 
Unsigned int is considered "larger" than signed. So the result of a operation between signed and unsigned int is unsigned int. This is what happens when calculating `Number` - 3 : the result is unsigned, the value wraps around (because the result of the operation is negative) and this explains the behavior.

The correct way to rewrite your query is :
UPDATE `test` SET `Number` =  IF(`Number`< 3, 0, `Number` - 3);