Bug #38344 UNIQUE key is not working when there is a default value on a field

• Submitted: 24 Jul 2008 11:58
• Modified: 24 Jul 2008 12:27
• Reporter: Jerome Despatis
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.45
• OS: Linux (Kubuntu)
• CPU Architecture: Any
• Tags: default value, duplicate, key, unique

[24 Jul 2008 11:58] Jerome Despatis

Description:
I've created a table with a unique index on several fields.

However, i can add many identical records...

How to repeat:

CREATE TABLE `capture_cache2` (
  `ref_movie` int(10) unsigned default NULL,
  `ref_member` int(10) unsigned default NULL,
  `ref_target` tinyint(3) unsigned NOT NULL,
  `ref_resolution` tinyint(3) unsigned NOT NULL,
  UNIQUE KEY `uniq` (`ref_movie`,`ref_member`,`ref_target`,`ref_resolution`),
  KEY `ref_member` (`ref_member`),
  KEY `ref_target` (`ref_target`),
  KEY `ref_resolution` (`ref_resolution`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And then, i can do many queries like:

INSERT INTO `capture_cache2` SET ref_movie = 1, ref_target = 1, ref_resolution = 1

Here is the content of the table, after 2 inserts:

mysql> select * from capture_cache2;
+-----------+------------+------------+----------------+
| ref_movie | ref_member | ref_target | ref_resolution |
+-----------+------------+------------+----------------+
|         1 |       NULL |          1 |              1 |
|         1 |       NULL |          1 |              1 |
+-----------+------------+------------+----------------+
2 rows in set (0.00 sec)

[24 Jul 2008 12:27] Susanne Ebrecht

Many thanks for reporting a bug. This is not a bug.
You inserted two times: 1, NULL, 1, 1

(NULL != NULL) always is true. Means the systems thinks that NULL is different in every row.