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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With