Bug #52715 Assigning negative value to unsigned bigint does not produce any error/warning
Submitted: 9 Apr 2010 9:48 Modified: 9 Apr 2010 11:04
Reporter: Szymon Sobczak Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.1.37-community OS:Microsoft Windows (2003 server & 7)
Assigned to: CPU Architecture:Any
Tags: BIGINT, out of bounds, overlap, UNSIGNED

[9 Apr 2010 9:48] Szymon Sobczak
Description:
Negative value assigned to unsigned integer is being caught as error.

For unsigned bigint assigning negative value to it overlaps and produces big positive value without any warning nor error.

How to repeat:
DELIMITER |

DROP PROCEDURE IF EXISTS IntDupa|
CREATE PROCEDURE IntDupa()
BEGIN
	DECLARE IntUnsig INTEGER UNSIGNED DEFAULT 1;
	DECLARE IntSig INTEGER DEFAULT -2;
	
	SET IntUnsig := IntUnsig + IntSig;
	
	SELECT IntUnsig AS IntResult;
END |

DROP PROCEDURE IF EXISTS BigDupa|
CREATE PROCEDURE BigDupa()
BEGIN
	DECLARE BigUnsig BIGINT UNSIGNED DEFAULT 1;
	DECLARE BigSig BIGINT DEFAULT -2;
	
	SET BigUnsig := BigUnsig + BigSig;
	
	SELECT BigUnsig AS BigintResult;
END |

DELIMITER ;

mysql> set session sql_mode = "";
Query OK, 0 rows affected (0.00 sec)

mysql> CALL IntDupa();
ERROR 1264 (22003): Out of range value for column 'IntUnsig' at row 1
mysql> CALL BigDupa();
+----------------------+
| BigintResult         |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> set session sql_mode = "NO_UNSIGNED_SUBTRACTION,STRICT_ALL_TABLES";
Query OK, 0 rows affected (0.00 sec)

mysql> CALL IntDupa();
ERROR 1264 (22003): Out of range value for column 'IntUnsig' at row 1
mysql> CALL BigDupa();
+----------------------+
| BigintResult         |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@version;
+------------------+
| @@version        |
+------------------+
| 5.1.37-community |
+------------------+
1 row in set (0.01 sec)

Suggested fix:
Warning / error should be produced always when result becomes negative.
[9 Apr 2010 9:51] Szymon Sobczak
Changed title - I think it's more about lack of warning than inconsistency.
[9 Apr 2010 11:04] Sveta Smirnova
Thank you for the report.

This is duplicate of bug #8433. Fixed in version 5.5.5