Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 1007981