MySQL Bugs: #75133: MySQL ignores unique index on multiple columns when one column si null.

Bug #75133	MySQL ignores unique index on multiple columns when one column si null.
Submitted:	6 Dec 2014 16:30	Modified:	6 Jan 2015 21:57
Reporter:	Ondrej Flidr	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.38	OS:	Linux (Debian Wheezy)
Assigned to:		CPU Architecture:	Any
Tags:	unique index multiple column

Description:
I have table defined

CREATE TABLE categories
(
    category_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    nazev VARCHAR(255) NOT NULL,
    url VARCHAR(255) NOT NULL,
    eshop INT NOT NULL,
    is_default TINYINT DEFAULT 0 NOT NULL,
    parent INT,
    FOREIGN KEY (eshop) REFERENCES eshop (eshop_id)
);
CREATE UNIQUE INDEX eshop_url_parent_index ON categories (eshop, parent, url);

with some dataset:
+-------------+-------+-------+-------+------------+--------+
| category_id | nazev | url   | eshop | is_default | parent |
+-------------+-------+-------+-------+------------+--------+
|           1 | cat 1 | cat-1 |     1 |          0 |   NULL |
+-------------+-------+-------+-------+------------+--------+
|           1 | cat 2 | cat-2 |     1 |          0 |   NULL |
+-------------+-------+-------+-------+------------+--------+
|           1 | cat 3 | cat-3 |     1 |          0 |   NULL |
+-------------+-------+-------+-------+------------+--------+
|           1 | cat 4 | cat-4 |     1 |          0 |      1 |
+-------------+-------+-------+-------+------------+--------+

this query:
insert into categories values(null, 'cat 1', 'cat-1', 1, 0, NULL);

works, but it should raise Duplicate entry error (it's duplicate in eshop, parent and url column). 

this query:
insert into categories values(null, 'cat 4', 'cat-1', 1, 0, 1);

raise error correctly.

How to repeat:
1) create table with one nullable column and multiple not null columns
2) create unique index on multiple columns, one of those is nullable
3) insert not uniq data with NULL in nullable column

Suggested fix:
Duplicate entry error should be raised

Please try version 5.5.41 if still the issue happens provide the complete test case scrip with create, insert data, queries, real result and expected result. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".