CODEWITHSUNDEEP
mysql
Sara
Sara

Reputation: 14628

mysql database save minus zero in a unsigned double field

I want to save currency values in a mysql db - curreny_data table. so I created a sale_price field which is a double unsigned. When I am going to save minus values db generate a error and save default value zero(0). But when I am going to save minus zero(-0) it saves successfully. I am confused. Please I need some help.

Upvotes: 3

Views: 3690

Answers (4)

phil_w
phil_w

Reputation: 1254

Note a few oddities: -0 = 0 in a where clause but not a group by. and -0 is transformed in 0 via a mysqldump / restore (may cause issues with slaves... )

create table testnegzero  ( n double);
insert into testnegzero  values (0.001), (-0.001);
update testnegzero  set n = round(n);
select * from testnegzero  ;
+------+
| n    |
+------+
|    0 |
|   -0 |
+------+

(you can also come up with -0 through some calculations)

select count(*) from testnegzero where n=0;
+----------+
| count(*) |
+----------+
|        2 |
+----------+

select count(*) from testnegzero group by n;
+----------+
| count(*) |
+----------+
|        1 |
|        1 |
+----------+

I found myself trying to clear these up, this is not easy since you can't search "where COL= -0 " Eventually I did it that way:

identify affected columns:

select * from testnegzero where cast(n as char(20))="-0";
+------+
| n    |
+------+
|   -0 |
+------+

clear them up:

update  testnegzero set n=0 where cast(n as char(20))="-0";   
select * from testnegzero  ;
+------+
| n    |
+------+
|    0 |
|    0 |
+------+

Upvotes: 1

Matteo
Matteo

Reputation: 14930

Generally unsigned doubles do not exists: see IEEE 754. I would suggest you to use a standard format (and in general money can also have a negative value).

I would check if you get an error with a negative value (e.g., -1): I think that MySQL considers -0 as 0 and allows it for an unsigned double (although -0 and 0 usually have two distinct representations)

EDIT:

The IEE 754 standard defines that -0 and 0 should be considered equal (i.e., 0 == -0 should hold)

Upvotes: 2

pho
pho

Reputation: 25489

Isn't that obvious? an unsigned double (assuming such a type exists) takes only unsigned, i.e. non negative double values. MySQL is smart enough to know that -0 is the same as 0, so it allows you to put -0 in the field. OTOH, negative numbers (by definition of the field) are not allowed, so you get an error.

Negative values will work if you redefine the field as a decimal type

Upvotes: 3

paulsm4
paulsm4

Reputation: 121669

  1. In general, you should probably use "decimal" to store currency, not double:

http://www.stemkoski.com/how-to-properly-store-currencymoney-in-mysql/

  1. I'm curious why you believe -0 "saves successfully":

    a) Are you able to see the sign bit in the double value?

    b) Or did you just fail to see an error exception?

  2. Either way - what's the difference? For purposes of counting money, isn't zero always zero?

Upvotes: 1

Related Questions