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.
