MySQL Bugs: #61413: UNSIGNED value is out of range

Bug #61413	UNSIGNED value is out of range
Submitted:	5 Jun 2011 18:41	Modified:	11 Aug 2011 20:01
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.5.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
I am aware of this discussion http://bugs.mysql.com/61410 

I observe different behaviors with different operators (+ and *) on UNSIGNED integers.

How to repeat:
SET SQL_MODE = '';

DROP TABLE IF EXISTS `a`;

CREATE TABLE `a` (`id` INT(10) UNSIGNED NOT NULL);
INSERT INTO `a`VALUES (1);

-- success
SELECT (id  *  2) FROM a; -- 2
SELECT (id  * 2 * 2) FROM a; -- 4

-- all fail with:
-- Error Code : 1690 - -- BIGINT UNSIGNED value is out of range 
SELECT (id  * 2 * (-2)) FROM a; 
SELECT (id  * 2 * 2 * (-2)) FROM a;
SELECT (id  * 2 *  (-2) * 2) FROM a; 

-- replacing operator from "*" to "+" 
SELECT (id  + 2 + (-2)) FROM a; -- 1
SELECT (id  + 2 + 2 + ( -2)) FROM a; -- 3
SELECT (id  + 2 -2 + 2) FROM a; -- 3

Suggested fix:
Please also see my comments and examples in http://bugs.mysql.com/61410 
I have no exact proposal for a fix as I am uncertain I understand what is going on.

It seems that the case with " * " -operator the multiplification first happens with the negative value (no matter the order of operands) whereas with " +/- " -operators the expression is evaluated from left to right. Or something else happens that I cannot figure out.

SELECT (id  * 2 * (-2)) FROM a; 
.. should not raise teh error if calculated as 
SELECT ((id  * 2) * (-2)) FROM a;
.. but it does - even with the additonal set of paranthesis'es added. 
The intermediate results of calculations are 1-->2-->1 what does not make the UNSIGNED go 'out of range'.

This makes the new rule for UNSIGNED overflow (raising an error instead of silently accepting the overflow) in 5.5 non-managable for users that don't want to use NO_UNSIGNED_SUBTRACTION SQL_mode (because it reduces the range of the result to 50%)

I do not see any different behavior here. Multiplication is also done left to right, and all that matters is sign of the value at each stage of evaluation. If value becomes negative you get error. Compare:

mysql> select id*(-2)*(-2) from a;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`a`.`id` * -(2))'
mysql> select id*((-2)*(-2)) from a;
+----------------+
| id*((-2)*(-2)) |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

Note also that there is yet another way to get the reasonable value, cast():

mysql> select cast(id as signed)*(-2)*(-2) from a;
+------------------------------+
| cast(id as signed)*(-2)*(-2) |
+------------------------------+
|                            4 |
+------------------------------+
1 row in set (0.00 sec)

is there anything on this page that should be further clarified?
http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html

I am an idiot of course.  Multipying 2 positive and one negative number will always result in a negative value no matter operands' order.

What remains is this:

SELECT CAST(1 AS UNSIGNED) - 2 + 2; -- Error Code : 1690
SELECT CAST(1 AS UNSIGNED) + 2 - 2; -- returns 1

In 5.1 the first statment was able to deliver perfect (and not overflowing) result and CAST to SIGNED will discard half of the storable range in the output in case the argument is a BIGINT.  Am I right in this? 

I do not see why the check for this error condition needs to be done on *intermediate results* of a calculation.  Doing the check with the *final result* only would be better I think (for backwards compability and in order to make it possible to have the full range of a BIGINT UNSIGNED returned).

If not possible or desirable to change this I think this page http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtractio... should clarify that the error condition may be raised if an intermediate result of a calculation overflows.

Test case showing how large BIGINT UNSIGNED cannot be returned to client if CAST to SIGNED is used.

DROP TABLE IF EXISTS tt;
CREATE TABLE tt (num BIGINT UNSIGNED);
INSERT INTO tt VALUES (18446744073709551615); -- 'MAXBIGINT' value inserted
SELECT * FROM tt; 

SELECT CAST(num AS UNSIGNED) FROM tt; -- 18446744073709551615
SELECT CAST(num AS UNSIGNED) -2 + 2 FROM tt; -- 18446744073709551615
SELECT CAST(num AS UNSIGNED) +2 - 2 FROM tt; -- Error Code : 1690
-- this is what we already discussed.
 
SELECT CAST(num AS SIGNED) FROM tt; -- (-1)
SELECT CAST(num AS SIGNED) -2 + 2 FROM tt; -- (-1)
SELECT CAST(num AS SIGNED) +2 - 2 FROM tt; -- (-1)
-- CAST to UNSIGNED destroys the large value stored.

How can I safely do such simple calculations and have correct results with a large stored BIGINT UNSIGNED?

Hint:

mysql> select cast(-1 as unsigned);
+----------------------+
| cast(-1 as unsigned) |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

Will it always be safe to CAST to SIGNED with an arithmetic operation and next 'CAST back' to UNSIGNED? If not to operate safely on large BIGIN UNSIGNED values it looks to me that the application will need to 'mirror' the calcualtion in order to apply a function order of operands.  Then we may as well drop the calculation if the server and calculate in the client.

We are human users sitting in front of a monitor with a simple test case that we can have in our heads.  In real world it would be applications that face this issue. It cannot choose as intelligently as you (and even not as stupid as I).

Thank you for the feedback.

> Will it always be safe to CAST to SIGNED with an arithmetic operation and next 'CAST back'
to UNSIGNED? 

Not, it would not: if UNSIGNED value is larger than SIGNED you will get overflow issue again. But having data storage limitation we always can choose a compromise. You can also try sql mode NO_UNSIGNED_SUBTRACTION as described at http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html