Consider this table schema and data:

CREATE TABLE `foo` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `up` INT UNSIGNED NOT NULL DEFAULT '0',
    `down` INT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);
INSERT INTO `foo` (`up`, `down`) VALUES(0, 1);

Now, consider this query:

mysql> SELECT (`up` - `down`) FROM `foo`;

You’d expect to get back -1, right? Wrong:

+----------------------+
| (up - down)          |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.06 sec)

Whoops. What’s happening here? MySQL is subtracting from two columns which are both unsigned, so it’s treating the result as unsigned. The solution? Ugliness:

mysql> SELECT CAST(up - down AS SIGNED) FROM `foo`;
+---------------------------+
| CAST(up - down AS SIGNED) |
+---------------------------+
|                        -1 |
+---------------------------+
1 row in set (0.06 sec)

Lame.

This entry was posted on Tuesday, March 18th, 2008 at 5:59 pm and is filed under Code, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.