MySQL Bugs: #67978: Signed zero is handled incorrectly by HASH indexes and GROUP BY

Bug #67978	Signed zero is handled incorrectly by HASH indexes and GROUP BY
Submitted:	27 Dec 2012 6:13	Modified:	29 Dec 2012 16:21
Reporter:	Alexey Kopytov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Negative and positive zeroes are considered different values by HASH
indexes in the MEMORY storage engine:

mysql> SELECT -0e0 = 0e0;
+------------+
| -0e0 = 0e0 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE h(a DOUBLE, UNIQUE KEY USING BTREE (a)) ENGINE=MEMORY;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO h VALUES(-0e0), (0e0);
ERROR 1062 (23000): Duplicate entry '0' for key 'a'

mysql> ALTER TABLE h DROP KEY a, ADD UNIQUE KEY USING HASH (a);
Query OK, 1 row affected (1.24 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO h VALUES(0e0);
Query OK, 1 row affected (1.45 sec)

mysql> SELECT * FROM h;
+------+
| a    |
+------+
|   -0 |
|    0 |
+------+
2 rows in set (0.00 sec)

As a result, when GROUP BY is executed using a HEAP temporary table,
spurious groups occur in the results:

mysql> CREATE TABLE t(a DOUBLE);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t VALUES(-0e0), (0e0);
Query OK, 2 rows affected (1.93 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t GROUP BY a;
+------+
| a    |
+------+
|   -0 |
|    0 |
+------+
2 rows in set (2.42 sec)

Compare this with the case when a MyISAM temporary table is used:

mysql> ALTER TABLE t ADD COLUMN b BLOB;
Query OK, 2 rows affected (1.79 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t GROUP BY a;
+------+------+
| a    | b    |
+------+------+
|   -0 | NULL |
+------+------+
1 row in set (1.12 sec)

How to repeat:
See the description.

This is really easy to verify:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.29    |
+-----------+
1 row in set (0.14 sec)

mysql> SELECT -0e0 = 0e0;
+------------+
| -0e0 = 0e0 |
+------------+
|          1 |
+------------+
1 row in set (0.08 sec)

mysql> CREATE TABLE h(a DOUBLE, UNIQUE KEY USING BTREE (a)) ENGINE=MEMORY;
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO h VALUES(-0e0), (0e0);
ERROR 1062 (23000): Duplicate entry '0' for key 'a'
mysql> ALTER TABLE h DROP KEY a, ADD UNIQUE KEY USING HASH (a);
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>  INSERT INTO h VALUES(0e0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from h;
+------+
| a    |
+------+
|   -0 |
|    0 |
+------+
2 rows in set (0.20 sec)

mysql> drop table t;
ERROR 1051 (42S02): Unknown table 't'
mysql> CREATE TABLE t(a DOUBLE);
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO t VALUES(-0e0), (0e0);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t GROUP BY a;
+------+
| a    |
+------+
|   -0 |
|    0 |
+------+
2 rows in set (0.08 sec)

mysql> ALTER TABLE t ADD COLUMN b BLOB;
Query OK, 2 rows affected (0.67 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t GROUP BY a;
+------+------+
| a    | b    |
+------+------+
|   -0 | NULL |
+------+------+
1 row in set (0.16 sec)

Verified as described.