Bug #33704 Math calculation error: 5-6 = 4294967295

• Submitted: 5 Jan 2008 15:34
• Modified: 5 Jan 2008 16:37
• Reporter: Daniel Drexlmaier
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.0.45 + 5.0.32
• OS: Linux
• CPU Architecture: Any

[5 Jan 2008 15:34] Daniel Drexlmaier

Description:
mysql> UPDATE `test` SET  `test` = 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `test`;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> UPDATE `test` SET  `test` =  `test`+5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `test`;
+------+
| test |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql> UPDATE `test` SET  `test` =  `test`-6;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SELECT * FROM `test`;
+------------+
| test       |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

How to repeat:
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `test` ( `test` int(10) unsigned NOT NULL ) ENGINE=MyISAM;
INSERT INTO `test` (`test`) VALUES (0);
SELECT * FROM `test`;
UPDATE `test` SET  `test` = 0;
SELECT * FROM `test`;
UPDATE `test` SET  `test` =  `test`+5;
SELECT * FROM `test`;
UPDATE `test` SET  `test` =  `test`-6;
SELECT * FROM `test`;

[5 Jan 2008 16:37] MySQL Verification Team

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html

" If you are not using strict mode, then whenever you insert an “incorrect” value into a column, such as a NULL into a NOT NULL  column or a too-large numeric value into a numeric column, MySQL sets the column to the “best possible value” instead of producing an error: The following rules describe in more detail how this works:

    *

      If you try to store an out of range value into a numeric column, MySQL Server instead stores zero, the smallest possible value, or the largest possible value, whichever is closest to the invalid value. "