MySQL Bugs: #61410: an unsigned column can't subtract a bigger value than before

Bug #61410	an unsigned column can't subtract a bigger value than before
Submitted:	5 Jun 2011 6:47	Modified:	5 Jun 2011 16:58
Reporter:	甲胄未名	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.5.11	OS:	Linux (suse)
Assigned to:		CPU Architecture:	Any
Tags:	an unsigned column, parser

Description:
when i try to compute the value from a unsigned(int/bigint) column subtract a bigger value, and plus a any one, the mysql will return a error:ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in***.

How to repeat:
use test;
create table a (id int unsigned not null, value int unsigned not null);
insert into a values(1, 2);
select id - 2 from a;

######
#this same statment was running well in mysql 5.0.45...

Suggested fix:
avoid use the unsigned column, or do not subtract from a unsigned column.

I find 5.1.57 to behave same way as reporter describes that 5.0 does. So there is a change with 5.5

The example (below) will fail even though the result ends up as a positive value (the *final* result is not 'out of range' but it never gets calculated):

-- 5.5.13
SELECT (id - 2 + 2) FROM a;
-- Error Code : 1690
-- BIGINT UNSIGNED value is out of range in '(`test`.`a`.`id` - 2)'

This could break applications. But it may have been a choice to implement like this is 5.5 though. It also does not look as any SQL_mode controls this behavior (above error is returned in '' (empty) mode).

Peter
(not a MySQL person)

This is intentional change in behavior in 5.5. For those who needs this kind of statements to work and produce meaningful (unlike in pre-5.5) results, there is NO_UNSIGNED_SUBTRACTION SQL mode (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtractio...).

Look:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.14-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table a (id int unsigned not null, value int unsigned not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into a values(1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select id - 2 from a;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`a`.`id` - 2)'
mysql> set session sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select id - 2 from a;
+--------+
| id - 2 |
+--------+
|     -1 |
+--------+
1 row in set (0.00 sec)

It is still (at least a documentation) bug in my opinion.

The documentation does not adress the problem with my example:
SELECT (id - 2 + 2) FROM a;

The error fires when evaluating the inner paranthesis of ((id - 2) + 2) and not with the result of the complete formula/calculation.

See

SELECT (id  + 2 - 2) FROM a; -- 1
SELECT (id  - (2 - 2)) FROM a; -- 1
SELECT (id  + (2 - 2)) FROM a; -- 1
SELECT (id  - 2 + 2) FROM a; -- Error Code: 1690

In common algebra the four statements are idencital.  So MySQL invents it own private algebra here! 

It is actually a practical matter with lots of administrative systems.   Consider I have a 'no_of_items_in stock' -column and as an unsigned INT (as you cannot have less tha ZERO items in stock).  I sell two items and buy two (or whatever) new items the same day.  My maintenance job that updates the database will faill with an error. I do not want to use NO_UNSIGNED_SUBTRACTION SQL_mode as it will effecctively reduce the range of the result to half of the value that can be stored in the table using an unsigned integer.

I have continued here: 
http://bugs.mysql.com/bug.php?id=61410

Status changed to not a bug which, to my mind, makes it a design flaw. MySQL has introduced a constraint on common algebra which, at the very least, will break many previously perfectly functional queries and views. How is this not a bug???