MySQL Bugs: #2454: unsigned int 0 value -1 results into (max bigint)-1

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.16, 4.0.17	OS:	Linux (Linux)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
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)

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:

http://www.mysql.com/doc/en/Cast_Functions.html

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

--sql-mode=NO_UNSIGNED_SUBTRACTION

option.

Regards,
Monty

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)