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:

[27 Dec 2012 6:13] Alexey Kopytov
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.
[27 Dec 2012 14:49] Valeriy Kravchuk
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)
[29 Dec 2012 16:21] Shane Bester
Verified as described.