Wrong result for a hash index look-up if the index is unique and the key is NULL
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Oleksandr "Sanja" Byelkin |
Bug Description
The MySQL reference (http://
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.
According to the above a look-up into a unique index over a nullable column may return several records if the key value is null.
However iMariaDB 5.1/5.2/5.3/5.5 always returns not more than one row when using a null key to perform a look-up into an unique hash index. This can be seen from the following:
MariaDB [test]> CREATE TABLE t1
-> (
-> pk INT PRIMARY KEY,
-> val INT,
-> UNIQUE KEY USING HASH(val)
-> ) ENGINE=MEMORY;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> INSERT INTO t1 VALUES (1, NULL);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> INSERT INTO t1 VALUES (2, NULL);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> INSERT INTO t1 VALUES (3, 1);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> INSERT INTO t1 VALUES (4, NULL);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> EXPLAIN SELECT * FROM t1 WHERE val IS NULL;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | t1 | ref | val | val | 5 | const | 1 | Using where |
+----+-
1 row in set (0.01 sec)
MariaDB [test]> SELECT * FROM t1 WHERE val IS NULL;
+----+------+
| pk | val |
+----+------+
| 4 | NULL |
+----+------+
1 row in set (0.00 sec)
MariaDB [test]> SELECT * FROM t1;
+----+------+
| pk | val |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 3 | 1 |
| 4 | NULL |
+----+------+
4 rows in set (0.00 sec)
(see also http://
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
assignee: | nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin) |
milestone: | none → 5.2 |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |