Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.18, 10.4.7
-
None
-
both Windows and Linux
Description
NULLs in the index allow for duplicate keys. NULLs are required due to foreign keys.
Demo Script to show that values with zero are unique but records with NULL are in the table twice:-
DROP TABLE IF EXISTS `test_table`; |
CREATE TABLE `test_table` ( |
`id1` INT(10) NOT NULL, |
`id2` INT(10) NULL DEFAULT NULL, |
`id3` INT(10) NULL DEFAULT NULL, |
`id4` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), |
`data` TEXT,
|
UNIQUE INDEX `main` (`id1`, `id2`, `id3`, `id4`) |
)
|
COLLATE='latin1_swedish_ci' |
ENGINE=InnoDB
|
;
|
INSERT INTO `test_table` (`id1`,`id2`,`id3`,`id4`,`data`) |
VALUES (1,NULL,NULL,'2020-02-20 10:00:00','data1'), |
(1,NULL,NULL,'2020-02-20 10:00:00','data2'), |
(2,0,0,'2020-02-20 10:00:00','data1'), |
(2,0,0,'2020-02-20 10:00:00','data2') |
ON DUPLICATE KEY UPDATE `data` = VALUE(`data`); |
Attachments
Issue Links
- relates to
-
MDEV-31397 Implement DISTINCT and NOT DISTINCT for unique NULL handling
-
- Open
-