Description:
On an unsigned int column, setting the value to -1 using different methods has different results.
create table test2 ( `maxnum` int(10) unsigned DEFAULT '0' );
insert into test2 values (0);
select * from test2;
update test2 set maxnum= maxnum-1;
show warnings;
select * From test2;
update test2 set maxnum= -1;
show warnings;
select * From test2;
Observe the different results produced by the two updates.
How to repeat:
Server version: 5.1.51 MySQL Community Server (GPL)
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 [localhost] {msandbox} (test) > create table test2 ( `maxnum` int(10) unsigned DEFAULT '0' );
Query OK, 0 rows affected (0.02 sec)
mysql [localhost] {msandbox} (test) > insert into test2 values (0);
Query OK, 1 row affected (0.00 sec)
mysql [localhost] {msandbox} (test) > select * from test2;
+--------+
| maxnum |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > update test2 set maxnum= maxnum-1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql [localhost] {msandbox} (test) > show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'maxnum' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select * From test2;
+------------+
| maxnum |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > update test2 set maxnum= -1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql [localhost] {msandbox} (test) > show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'maxnum' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select * From test2;
+--------+
| maxnum |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)
Suggested fix:
In older versions, -1 has always been set to 0.
Description: On an unsigned int column, setting the value to -1 using different methods has different results. create table test2 ( `maxnum` int(10) unsigned DEFAULT '0' ); insert into test2 values (0); select * from test2; update test2 set maxnum= maxnum-1; show warnings; select * From test2; update test2 set maxnum= -1; show warnings; select * From test2; Observe the different results produced by the two updates. How to repeat: Server version: 5.1.51 MySQL Community Server (GPL) 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 [localhost] {msandbox} (test) > create table test2 ( `maxnum` int(10) unsigned DEFAULT '0' ); Query OK, 0 rows affected (0.02 sec) mysql [localhost] {msandbox} (test) > insert into test2 values (0); Query OK, 1 row affected (0.00 sec) mysql [localhost] {msandbox} (test) > select * from test2; +--------+ | maxnum | +--------+ | 0 | +--------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > update test2 set maxnum= maxnum-1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql [localhost] {msandbox} (test) > show warnings; +---------+------+-------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------+ | Warning | 1264 | Out of range value for column 'maxnum' at row 1 | +---------+------+-------------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * From test2; +------------+ | maxnum | +------------+ | 4294967295 | +------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > update test2 set maxnum= -1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql [localhost] {msandbox} (test) > show warnings; +---------+------+-------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------+ | Warning | 1264 | Out of range value for column 'maxnum' at row 1 | +---------+------+-------------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * From test2; +--------+ | maxnum | +--------+ | 0 | +--------+ 1 row in set (0.00 sec) Suggested fix: In older versions, -1 has always been set to 0.