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:
None 
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
Description:
An unsigned bigint column can be decremented below zero, resulting in a very large number.  Possibly the number is wrapping?  This would seem to be a range violation for an unsigned column.  Is this related to bug #5913?

Unsigned int columns, on the other hand, never go below zero.  I believe this is the correct behavior.

This problem did not exist in earlier 4.1.x versions.  I am not sure what version it appeared in, but I discovered it in 4.1.7

Thanks,
Darryl

How to repeat:
mysql> create table bigint_table (bi1 bigint unsigned, i1 int unsigned);
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from bigint_table;
+------+------+
| bi1  | i1   |
+------+------+
|    0 |    0 |
+------+------+
1 row in set (0.00 sec)

mysql> update bigint_table set bi1=(bi1 - 1), i1=(i1 - 1);
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from bigint_table;
+----------------------+------+
| bi1                  | i1   |
+----------------------+------+
| 18446744073709551615 |    0 |
+----------------------+------+
1 row in set (0.00 sec)

This is an incorrect result.  bi1 should never go below zero (or wrap).
[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)