Positive and negative zeros, and MySQL

In mathematics zero usually is considered signless; it’s neither positive nor negative. In computer floating point numbers there are two zeros: one positive, +0.0, and one negative -0.0, at least if we follow the IEEE standard.

Most of the time you can’t tell the difference between the two: As far as basic arithmetics is concerned both are zero, and the equality operator has been defined so that +0.0 = -0.0. Pretty much the only place where you see a difference between the two is when you divide by zero: 1/-0.0 results in negative infinity.

The MySQL floating point types also have positive and negative zeros, and it treats them as equals for selecting data:

mysql> create table t ( f float );
mysql> insert into t values (0.1), (-0.1);
mysql> update t set f = round(f);
mysql> select f from t where f = 0;
+------+
| f    |
+------+
|    0 |
|   -0 |
+------+

However, GROUP BY considers them distinct!

mysql> select count(*), from t group by f;
+----------+------+
| count(*) | f    |
+----------+------+
|        1 |    0 |
|        1 |   -0 |
+----------+------+

This is something to keep in mind if you are using MySQL floating point types for scientific computing.