Simple mathematical operations in MySQL query

MySQL makes it easy to return the result of simple calculations within the query itself, something as simple as adding a couple of columns together.

Take a table (for the sake of simplicity it can be called Table), with two integer fields (Int1 and Int2).

To return Int1 and Int2, as well as the result of Int1 + Int2, the following query will suffice:

SELECT Int1, Int2, Int1 + Int2 as Total FROM Table

The returned resultset will have both integers, and the total of both of them. Easy!

To subtract, simply do:

SELECT Int1, Int2, Int1 + Int2 as Total FROM Table

Expected results:

 Int1 |  Int2  | Int3
   1 | 0 | 1
   0 | 1 | -1

Actual results:

 Int1 |  Int2  | Int3
   1 | 0 | 1
   0 | 1 | 18446744073709551615

This happens becase MySQL treats the resulting value as an unsigned int. The lowest value a signed int can be is 0, so once it goes below 0 it rolls over to 18446744073709551615 which is the highest value it can hold on a 32 bit system.

The solution is to cast the resulting value to a signed int resolves the issue as follows:

SELECT Int1, Int2, CAST(Int1 - Int2 as signed) as Int3 FROM Table```