MySQL Bugs: #58697: Int datatype setting to max value

Bug #58697	Int datatype setting to max value
Submitted:	3 Dec 2010 14:01	Modified:	3 Jan 2011 18:20
Reporter:	Dan Rucci	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.1.50	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Just upgraded to this version and noticed this. I have an unsigned int column. If it is 0 and i subtract any amount from it the value of the field becomes  4294967295 (max value).

It should just remain 0.

I wasn't sure if the was another bug report on this I couldn't find one. This is pretty serious for my application and has caused havoc.

How to repeat:
Crate table.
Create unsigned int colum and set it to 0.
Create an update statement and take 1 from that column.

Looks related to http://bugs.mysql.com/bug.php?id=20093?.

Try the below sql_mode:

C:\DBS>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.92-Win X64-log Source distribution

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

mysql 5.0 > use test
Database changed
mysql 5.0 > create table h (id int unsigned);
Query OK, 0 rows affected (0.09 sec)

mysql 5.0 > insert into h values (0);
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > update h set id = id-1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql 5.0 > select * from h;
+------------+
| id         |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

mysql 5.0 > drop table h;
Query OK, 0 rows affected (0.01 sec)

mysql 5.0 > set sql_mode= 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > create table h (id int unsigned);
Query OK, 0 rows affected (0.07 sec)

mysql 5.0 > insert into h values (0);
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > update h set id = id-1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql 5.0 > select * from h;
+------+
| id   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql 5.0 >

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".