Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21527

NULL values allowing duplicate keys in unique index

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.3.18, 10.4.7
    • Fix Version/s: N/A
    • Component/s: N/A
    • Labels:
      None
    • Environment:
      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

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            4dallasc Dallas Clarke
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: