[MDEV-2430] LP:1007981 - Wrong result for a hash index look-up if the index is unique and the key is NULL Created: 2012-06-03  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1007981.xml    

 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)



 Comments   
Comment by Rasmus Johansson (Inactive) [ 2012-10-03 ]

Launchpad bug id: 1007981

Generated at Thu Feb 08 06:41:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.