MySQL Bugs: #59426: unsigned bug...

Bug #59426	unsigned bug...
Submitted:	11 Jan 2011 18:48	Modified:	17 Jan 2011 21:10
Reporter:	dobs U	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.1.52	OS:	Linux (Fedora 13)
Assigned to:		CPU Architecture:	Any

Description:
UPDATE `smartcopy_site`.`core_projects` SET `total_hidden_comments` = (total_hidden_comments-1),`total_comments` = (total_comments-1) WHERE `core_projects`.`id` =4

How to repeat:
INT with UNSIGNED

Hi dobs U,

Unfortunately it's not clear what exactly do you mean..
Could you please provide more detailed description and testcase in mysql console client? (not video from 3rd party php program)

[root@wifi dobs]# mysql --password=***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE `demo`.`test` (
    -> `id` INT( 3 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    -> `int1` INT( 4 ) UNSIGNED NOT NULL ,
    -> `int2` INT( 4 ) UNSIGNED NOT NULL 
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO `demo`.`test` (`id` ,`int1` ,`int2` ) VALUES (NULL , '0', '10');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `demo`.`test`;
+----+------+------+
| id | int1 | int2 |
+----+------+------+
|  1 |    0 |   10 |
+----+------+------+
1 row in set (0.00 sec)

mysql> UPDATE `demo`.`test` SET `int2` = '9' WHERE `test`.`id` =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `demo`.`test`;
+----+------+------+
| id | int1 | int2 |
+----+------+------+
|  1 |    0 |    9 |
+----+------+------+
1 row in set (0.00 sec)

mysql> UPDATE `demo`.`test` SET `int1` = (`int1`-1), `int2` = (`int2`-1) WHERE `test`.`id` =1;
Query OK, 1 row affected, 1 warning (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SELECT * FROM `demo`.`test`;
+----+------------+------+
| id | int1       | int2 |
+----+------------+------+
|  1 | 4294967295 |    8 |
+----+------------+------+
1 row in set (0.00 sec)

Why in result we have 4294967295?

Thank you for the feedback.

UNSIGNED column can not contain negative values. This is not a bug. Please read at http://dev.mysql.com/doc/refman/5.1/en/out-of-range-and-overflow.html how MySQL server handles overflow.

But on mysql 5.0.19 I dont have 4294967295 I have 0, why so?
Thank you