MySQL Bugs: #67529: GROUP BY treats +0 and -0 as different values

Bug #67529	GROUP BY treats +0 and -0 as different values
Submitted:	9 Nov 2012 0:20	Modified:	9 Nov 2012 18:29
Reporter:	Igor T.	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.41, 5.1.67, 5.5.29, 5.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY, round

Description:
For some reason round() seems to return +0 or -0 which may be a feature rather than a bug, except group by then treats them as different values.

How to repeat:
(Using an arbitrary smallish input table)
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 that shouldn't be required.

Suggested fix:
Either round() or group by seems broken.

Please, check if the same problem still happens with a recent version, 5.1.65+. If it does, please, send the exact CREATE TABLE and INSERT statements used to reproduce this.

Thank you for the report.

Verified as described.

Test case for MTR:

create table tab(id int not null auto_increment primary key);
insert into tab values(null);
insert into tab select null from tab;
insert into tab select null from tab;
insert into tab select null from tab;
insert into tab select null from tab;
insert into tab select null from tab;
insert into tab select null from tab;
insert into tab select null from tab;
insert into tab select null from tab;

select x, count(1) from (select round(rand()-0.5,1) x from tab) t group by x;

Here is a simpler example.

create table x (x float);
insert into x(x) values (-0.1), (0.1);

mysql> select round(x), count(1) from x group by round(x);
+----------+----------+
| round(x) | count(1) |
+----------+----------+
|       -0 |        1 |
|        0 |        1 |
+----------+----------+
2 rows in set (0.00 sec)

Simple example that does not involve ROUND()

MySQL [test]> create table x(x float);
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> insert into x values (+0e0),(-0e0);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test]> select x from x group by x;
+------+
| x    |
+------+
|    0 |
|   -0 |
+------+
2 rows in set (0.00 sec)