Bug #6953 | bigint unsigned wraps below zero | ||
---|---|---|---|
Submitted: | 2 Dec 2004 17:12 | Modified: | 29 Sep 2008 20:34 |
Reporter: | Darryl Rodden | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 4.1.7 | OS: | Linux (Linux (REL 3)) |
Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[2 Dec 2004 17:12]
Darryl Rodden
[8 Feb 2005 19:25]
Paul DuBois
I am unassigning this from myself. This report identifies a difference between BIGINT UNSIGNED and INT UNSIGNED behavior. Seems like a server bug, not a doc bug.
[9 Feb 2005 10:29]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: See http://dev.mysql.com/doc/mysql/en/server-sql-mode.html for NO_UNSIGNED_SUBTRACTION
[23 Feb 2005 15:21]
Darryl Rodden
to Paul: Thank you for your time, but I never indicated this was a documentation bug. You are correct that it is a server bug. ============================================================= to Sergei: You are completely missing the point and focusing on the subtraction. The real point is that the server is not clipping negative values to the minimum valid value, zero, for unsigned bigint columns. Using the same table in my example, try a simple insert of negative values and this is what you get: mysql> insert into bigint_table values (0,0); Query OK, 1 row affected (0.02 sec) mysql> insert into bigint_table values (-1,-1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from bigint_table; +----------------------+------+ | bi1 | i1 | +----------------------+------+ | 0 | 0 | | 18446744073709551615 | 0 | +----------------------+------+ 2 rows in set (0.00 sec) Yes, the insert of negative values gives a warning, but it does modify the unsigned bigint column. An update of that table gives similar results: mysql> update bigint_table set bi1 = -1, i1 = -1; Query OK, 1 row affected, 2 warnings (0.00 sec) Rows matched: 2 Changed: 1 Warnings: 2 mysql> select * from bigint_table; +----------------------+------+ | bi1 | i1 | +----------------------+------+ | 18446744073709551615 | 0 | | 18446744073709551615 | 0 | +----------------------+------+ 2 rows in set (0.00 sec) Again, the SQL gives a warning, but modifies the unsigned bigint column. I will refer you to the documentation for numeric column types (http://dev.mysql.com/doc/mysql/en/numeric-types.html), which says: "When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. For example, the range of an INT column is -2147483648 to 2147483647. If you try to insert -9999999999 into an INT column, MySQL clips the value to the lower endpoint of the range and stores -2147483648 instead. Similarly, if you try to insert 9999999999, MySQL clips the value to the upper endpoint of the range and stores 2147483647 instead. If the INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift up to 0 and 4294967295. If you try to store -9999999999 and 9999999999, the values stored in the column are 0 and 4294967296. Conversions that occur due to clipping are reported as ``warnings'' for ALTER TABLE, LOAD DATA INFILE, UPDATE, and multiple-row INSERT statements." Per that documentation, inserting or updating an unsigned bigint column with a negative value should be clipped to the endpoint zero. This behavior works for all unsigned int column types except bigint. That is why I entered this bug report and it is still broken. Thanks, Darryl
[1 Mar 2005 22:10]
Sergei Golubchik
Ok, you're right (your first example was only about subtraction and that confused me). It's a known bug, listed here: http://dev.mysql.com/doc/mysql/en/open-bugs.html As it's said "The following problems are known and fixing them is a high priority" (it cannot be fixed in 4.1 as the fix would require big structural changes, something we cannot do in the stable branch).
[29 Sep 2008 20:34]
Konstantin Osipov
set @@sql_mode="no_unsigned_subtraction"; drop table if exists bigint_table; create table bigint_table (bi1 bigint unsigned, i1 int unsigned); insert into bigint_table values (0,0); select * from bigint_table; update bigint_table set bi1=(bi1 - 1), i1=(i1 - 1); select * from bigint_table; I can't repeat it in 6.0, seems the bug has been fixed: if sql_mode is no_unsigned_subtraction, the code works as expected: mysql> \e -> ; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.01 sec) +------+------+ | bi1 | i1 | +------+------+ | 0 | 0 | +------+------+ 1 row in set (0.01 sec) Query OK, 0 rows affected, 2 warnings (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 2 +------+------+ | bi1 | i1 | +------+------+ | 0 | 0 | +------+------+ 1 row in set (0.02 sec)