3

Tell me if the two zeros make sense:

select x, count(1) from (select round(rand()-0.5,1) x from tab) t group by x;
+------+----------+
| x    | count(1) |
+------+----------+
| -0.5 |     1830 |
| -0.4 |     3726 |
| -0.3 |     3753 |
| -0.2 |     3835 |
| -0.1 |     3828 |
|  0.0 |     1909 |
| -0.0 |     1889 |
|  0.1 |     3831 |
|  0.2 |     3753 |
|  0.3 |     3793 |
|  0.4 |     3690 |
|  0.5 |     1887 |
+------+----------+

There is a workaround (cast as decimal(10,1)) but I am curious if round() is broken. I am using MySQL 5.1

iggy
  • 662
  • 6
  • 14

1 Answers1

7

The IEEE floating point standard has two zeros: positive and negative. It's totally normal for round() to return a negative zero when rounding a negative value up to 0.

Normally the negative zero is treated equal to positive zero, but it looks like on your platform MySQL considers them distinct.

Update: MySQL, at least in 5.5, seems to consider positive and negative zero equal for =, but distinct for GROUP BY.

You can add zero to a value to convert a negative zero to positive with no changes for other values. For example,

select x, count(1) from (select 0+round(rand()-0.5,1) x from tab) t group by x;
Joni
  • 108,737
  • 14
  • 143
  • 193
  • Thanks! Do you think this is a MySQL bug that should be fixed? – iggy Nov 07 '12 at 17:56
  • It's hard to say, certainly it's surprising that sometimes the two zeros are equal and sometimes not. You can try submitting a bug report and see what they say. – Joni Nov 08 '12 at 15:19