CoolComputing Logo
Coupons/Deals  ·  New Promo Codes/Coupons  · May 20, 2018

Solution to MySQL Subtraction of Integers Problem Resulting in Negative Number

Posted on Tuesday, September 11, 2012 @ 01:49:49 AM CDT by David Yee [] [read 5247 times]

 
Tips: Linux/Unix world
I ran into a weird problem, so I thought, with MySQL today. I have a MySQL table (let's call it "verizon_fios" for our example) with a pair of integer columns (promotion_code_a and promotion_code_b) that I want to do some calculation with- specifically subtracting one number from another.

server1837>select promotion_code_a from verizon_fios where id = 237;
+------------+
| promotion_code_a |
+------------+
| 0 |
+------------+

server0117>select promotion_code_b from verizon_fios where id = 237;
+----------+
| promotion_code_b |
+----------+
| 1 |
+----------+

Here is the kicker:

server1837>select promotion_code_b - promotion_code_a from verizon_fios where id = 237;
+-----------------------+
| promotion_code_a - promotion_code_b |
+-----------------------+
| 18446744073709551615 |
+-----------------------+

Instead of the result I expected of -1, I got some super-huge number of 18446744073709551615 instead. I looked around in Bing and Google, but didn't find any solution. Then I realized that hey, the integer columns are defined as unsigned, so that could be an issue when I do the subtraction and it results in a negative number? That was indeed it, but I didn't feel like altering the columns to make them signed, so I had to convert the integer types in my query instead. I had to use the CAST() function to convert the result of the subtraction into a signed number.

server0119>select cast(promotion_code_b - promotion_code_a as signed) from verizon_fios where id = 237;
+---------------------------------------+
| cast(promotion_code_b - promotion_code_a as signed) |
+---------------------------------------+
| -1 |
+---------------------------------------+

Now I'm using MySQL 5.0, so maybe they have addressed this problem in the newer versions. But personally I don't think the engine should return the confusing large number, but rather instead throw an error or a warning that subtracting two unsgined numbers is not allowed. But hopefully this tip will help those of you scratching your head as to what the heck is going on when you are expecting a negative number from a subtraction calculation in MySQL.



Please rate this article or post a brief review of Solution to MySQL Subtraction of Integers Problem Resulting in Negative Number or comment on benchmark/performance, features, availability, price or anything else regarding Solution to MySQL Subtraction of Integers Problem Resulting in Negative Number. Thanks!

Solution To MySQL Subtraction Of Integers Problem Resulting In Negative Number Rating: 2.2/5 (38 votes cast)

Your Name:
I have read and agreed to the Review Posting Agreement.
Review Title:
Comment/Review: