MySQL integer unsigned arithmetic problems?

Question

Does MySQL (5.0.45) like to do strange internal typecasts with unsigned maths? I am storing integers unsigned but when selecting basic arithmetic I get outrageous numbers:

mysql> create table tt ( a integer unsigned , b integer unsigned , c float );
Query OK, 0 rows affected (0.41 sec)

mysql> insert into tt values (215731,216774,1.58085);
Query OK, 1 row affected (0.00 sec)

mysql> select a,b,c from tt;
+--------+--------+---------+
| a      | b      | c       |
+--------+--------+---------+
| 215731 | 216774 | 1.58085 |
+--------+--------+---------+
1 row in set (0.02 sec)

mysql> select (a-b)/c from tt;
+---------------------+
| (a-b)/c             |
+---------------------+
| 1.1668876878652e+19 |
+---------------------+
1 row in set (0.00 sec)

mysql> -- WHAT?
mysql> select a-b from tt;
+----------------------+
| a-b                  |
+----------------------+
| 18446744073709550573 |
+----------------------+
1 row in set (0.02 sec)

I assume this has to do with the fact that the subtraction is negative and thus it is trying to map the results into an unsigned and overflowing? I can solve this apparently by changing everything to signed, but I'd prefer to have a little more positive space with my 32-bit integers.

I have not run into this before on MySQL and I'm pretty certain I've done lots with unsigned MySQL arithmetic; is this a common problem?

Duplicate: http://stackoverflow.com/questions/1517556/mysql-query-gone-wild — OMG Ponies, Nov 14 '09 at 06:28

score 7 · Accepted Answer · answered Nov 14 '09 at 06:21

7

If either the left-hand side or the right-hand side of the subtraction operator is unsigned, the result is unsigned as well. You can change this by setting the NO_UNSIGNED_SUBTRACTION SQL mode.

Alternatively, you can also explicitly cast your unsigned values to be signed bigint values and then do the subtraction.

answered Nov 14 '09 at 06:21

James McNellis

348,265
75
913
977

New docs link: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_unsigned_subtraction ....................................................................................................... MariaDB: https://mariadb.com/kb/en/mariadb/sql_mode/ – jave.web Aug 20 '16 at 12:12

score 3 · Answer 2 · answered Nov 14 '09 at 06:51

try this:

mysql> select cast(cast(a-b as unsigned) as signed)/c from tt;

+-----------------------------------------+
| cast(cast(a-b as unsigned) as signed)/c |
+-----------------------------------------+
|                       -659.771639688953 | 
+-----------------------------------------+
1 row in set (0.00 sec)

reference: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

score 2 · Answer 3 · answered Nov 14 '09 at 06:21

2

Yep, the intermediate subtraction did a 64-bit wraparound. Since you expected 32 bit integers, but actually get 64, there's no reason to use unsigned.

answered Nov 14 '09 at 06:21

wallyk

56,922
16
83
148

Yeah imagine my memory graphs when apparently we were able to sustain memory allocation at ~119 thousand billion yobibytes per second. That's Star Trek computing right there. – Jé Queue Nov 14 '09 at 06:35

MySQL integer unsigned arithmetic problems?

3 Answers3

Linked