Wrong result for a hash index look-up if the index is unique and the key is NULL

Bug #1007981 reported by Igor Babaev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
High
Oleksandr "Sanja" Byelkin

Bug Description

The MySQL reference (http://dev.mysql.com/doc/refman/5.0/en/create-index.html) says:
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://bugs.mysql.com/bug.php?id=44771)

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
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers