Bug #2454 unsigned int 0 value -1 results into (max bigint)-1
Submitted: 20 Jan 2004 4:21 Modified: 4 Feb 2004 16:13
Reporter: Arjen lastname Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.16, 4.0.17 OS:Linux (Linux)
Assigned to: Michael Widenius CPU Architecture:Any

[20 Jan 2004 4:21] Arjen lastname
When you select a unsigned integer field from the database and substract something, it is wrapped to a unsigned bigint's maximum value.

It is either a bug because: since you use bigints to calculate, you could easily use signed bigint arithmetics even if the int is unsigned.
Or: a unsigned integer should never be transformed into a bigint as a result, for internal calculations the user doesn't care, but requesting a integer should either return an integer or a NULL, not a bigint, right?

How to repeat:
mysql> select messagecount, messagecount -1 from F_Topics where messagecount = 0 limit 1;
| messagecount | messagecount -1      |
|            0 | 18446744073709551615 |
1 row in set (0.00 sec)
[4 Feb 2004 16:13] Michael Widenius
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:

The given behavior is not a bug. In MySQL, the result type when using
subtraction with an unsigned integer is an unsigned integer.  (Just
like you would get in a language like C)

The above is notied in the manual sections:


Note that if you don't want to use unsigned arithmetic, you should
either not use the 'unsigned bigint' type or use the



[24 Jun 2004 2:48] Ted Cui
But why does this only happen for "bigint unsigned" instead of "int unsigned"? Does MySQL internally convert "unsigned int" into "bigint" instead of "unsigned bigint"?

mysql> create table a(i  int unsigned);
Query OK, 0 rows affected (0.24 sec)

mysql> insert into a(i) values (1);
Query OK, 1 row affected (0.19 sec)

mysql> select * from a;
| i    |
|    1 |
1 row in set (0.19 sec)

mysql> update a set i = i - 3;
Query OK, 1 row affected (0.27 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from a;
| i    |
|    0 |
1 row in set (0.20 sec)