Surprising MySQL behaviour using unsigned int

2008-02-29

Today I saw some strange behaviour in my MySQL database. A simple ABS( myval - 600 ) gave me very big numbers whenever myval was less than 600. This happens on my MacBook with MySQL 5.0.45 from the Mac version of Xampp. My Ubuntu box running 5.0.22 does not show this very surprising feature. Yes, it’s a feature, not a bug, MySQL says.

But let’s have a look at my code to reproduce this nice feature:

mysql> select * from test;
+-------+
| myval |
+-------+
|     1 |
|   156 |
|     3 |
|   674 |
|   848 |
|   123 |
+-------+
6 rows in set (0.00 sec)

mysql> select myval, abs(myval - 600) from test;
+-------+----------------------+
| myval | abs(myval - 600)     |
+-------+----------------------+
|     1 | 18446744073709551017 |
|   156 | 18446744073709551172 |
|     3 | 18446744073709551019 |
|   674 |                   74 |
|   848 |                  248 |
|   123 | 18446744073709551139 |
+-------+----------------------+
6 rows in set (0.00 sec)

Of course, there is nothing wrong with the ABS function. It’s the unsigned integer field, that causes this behaviour:

mysql> select myval, myval - 600 from test;
+-------+----------------------+
| myval | myval - 600          |
+-------+----------------------+
|     1 | 18446744073709551017 |
|   156 | 18446744073709551172 |
|     3 | 18446744073709551019 |
|   674 |                   74 |
|   848 |                  248 |
|   123 | 18446744073709551139 |
+-------+----------------------+
6 rows in set (0.00 sec)

Because myval is unsigned, a calculation that would result in a negative value causes some kind of overflow. The correct way to deal with this situation is to use the CAST function:

mysql> select myval, cast(myval as signed) - 600 from test;
+-------+-----------------------------+
| myval | cast(myval as signed) - 600 |
+-------+-----------------------------+
|     1 |                        -599 |
|   156 |                        -444 |
|     3 |                        -597 |
|   674 |                          74 |
|   848 |                         248 |
|   123 |                        -477 |
+-------+-----------------------------+
6 rows in set (0.00 sec)

I hope you’re warned now and don’t make the same mistake. Perhaps it’ll save you some time.