Bug #54699 Signed Unsigned Subtraction Out of Range Error

• Submitted: 22 Jun 2010 16:14
• Modified: 22 Jun 2010 17:23
• Reporter: Syed Hashmi
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.1.47
• OS: Linux
• CPU Architecture: Any
• Tags: Signed Unsigned Subtraction

[22 Jun 2010 16:14] Syed Hashmi

Description:
I cannot store -3000 in a signed BIGINT if the subtraction operation involves an UNSIGNED number.

I get out of range value for column v_Signed which is not at all an out of range value for a SIGNED BIGINT.

How to repeat:
BEGIN
	DECLARE v_Unsigned BIGINT UNSIGNED DEFAULT 3000;
	DECLARE v_Signed BIGINT DEFAULT NULL;
	SET v_Signed = v_Unsigned - 6000;
	SELECT v_Signed;
END;

[22 Jun 2010 17:23] Valeriy Kravchuk

Actually, I'd say that this is documented in http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html:

"MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned. You can override this by using the SIGNED and UNSIGNED cast operators to cast the operation to a signed or unsigned 64-bit integer, respectively."

Also good workaround exists:

mysql> create procedure proc3() BEGIN DECLARE v_Unsigned BIGINT UNSIGNED DEFAULT 3000; DECLARE v_Signed BIGINT DEFAULT NULL; SET v_Signed = cast(v_Unsigned as signed) - 6000; SELECT v_Signed; END;//
Query OK, 0 rows affected (0.00 sec)

mysql> call proc3();//
+----------+
| v_Signed |
+----------+
|    -3000 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)