Bug #71152 Inside of Trigger can not set negative value of declared signed int variable

• Submitted: 16 Dec 2013 14:18
• Modified: 31 Jan 2014 19:25
• Reporter: Angel Tsvetkov
• Status: No Feedback
• Category: MySQL Server: Stored Routines
• Severity: S1 (Critical)
• Version: 5.1.41 Source distribution
• OS: Windows
• CPU Architecture: Any
• Tags: trigger negative value stored program

[16 Dec 2013 14:18] Angel Tsvetkov

Description:
Inside of Trigger can not set negative value of declared signed int variable

How to repeat:
delimiter //
DROP TRIGGER IF EXISTS ins;
CREATE TRIGGER ins BEFORE UPDATE ON `.Tables`
FOR EACH ROW
BEGIN
	DECLARE actual INT SIGNED DEFAULT 0;
	IF NEW.`Count Contained Items` <> OLD.`Count Contained Items` THEN	
		IF	NEW.`Count Contained Items` < OLD.`Count Contained Items` THEN
			SET actual = (OLD.`Count Contained Items` - NEW.`Count Contained Items`) * -1;
		ELSEIF NEW.`Count Contained Items` > OLD.`Count Contained Items` THEN
			SET actual = (NEW.`Count Contained Items` - OLD.`Count Contained Items`);
		END IF;
		SET NEW.`Count Contained Items`=(OLD.`Count Contained Items` + actual);
	END IF;
END;//
delimiter ;

mysql> select Id,`Count Contained Items` from `.Tables`;
+----+-----------------------------+
| id 	| Count Contained Items |
+----+-----------------------------+
|  1   |     							15 |
+-----------------------------------+
mysql> UPDATE `.Tables` SET `Count Contained Items`=16 WHERE Id=1;
mysql> select Id,`Count Contained Items` from `.Tables`;
+----+-----------------------------+
| id 	| Count Contained Items |		=>	ADDITION is CORRECT:
+----+-----------------------------+
|  1   |     						    16 |		 -		correct as expected
+-----------------------------------+
mysql> UPDATE `.Tables` SET `Count Contained Items`=15 WHERE Id=1;
mysql> select Id,`Count Contained Items` from `.Tables`;
+----+-----------------------------+
| id 	| Count Contained Items |		=>	Subtraction is INCORRECT:
+----+-----------------------------+
|  1   |     			  2147483663 |		 -    	ERROR as expected, but must be 15 !!!!!
+-----------------------------------+
Server version:         5.1.41 Source distribution

Suggested fix:
Haven't got any!

[31 Dec 2013 19:25] Sveta Smirnova

Thank you for the report.

But version 5.1.41 is very old and many bugs were fixed since. Please try with current version 5.1.73 and if problem still exists send us output of SHOW CREATE TABLE `.Tables`

[1 Feb 2014 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".