Bug #4140 The UNIQUE key error when null

• Submitted: 15 Jun 2004 11:30
• Modified: 15 Jun 2004 19:07
• Reporter: Haitao Jiang
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.x
• OS: Linux (Linux)
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[15 Jun 2004 11:30] Haitao Jiang

Description:
If a UNIQUE key include a NULL column, it cannot run correctly.

How to repeat:
TEST SQL CODE:
==============================================================================
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (
  PID  BIGINT(16) UNSIGNED     NOT NULL AUTO_INCREMENT,
  CNT  SMALLINT(4) UNSIGNED    NOT NULL,
  LDM  CHAR(1) BINARY          NOT NULL DEFAULT '0',
  LDD  DATETIME                NULL,
  PRIMARY KEY (PID),
  UNIQUE UK_TEST_1 (CNT, LDM, LDD)
) TYPE=MyISAM DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1 COMMENT='';
INSERT INTO TEST (PID) VALUES (0);
INSERT INTO TEST (PID) VALUES (0);
SELECT * FROM TEST;
==============================================================================

TEST RESULT: 
==============================================================================
+-----+-----+-----+------+
| PID | CNT | LDM | LDD  |
+-----+-----+-----+------+
|   1 |   0 | 0   | NULL |
|   2 |   0 | 0   | NULL |
+-----+-----+-----+------+
==============================================================================

The UNIQUE key is include CNT, LDM, LDD column, but the insert statement is still work.

Suggested fix:
The Oracle database is correct.
==============================================================================
-- DROP TABLE TEST CASCADE CONSTRAINTS;
CREATE TABLE TEST (
  PID                              NUMBER(16)                                                                 NOT NULL,
  CNT                              NUMBER(4)                DEFAULT 0                                         NOT NULL,
  LDM                              CHAR(1)                  DEFAULT '0'                                       NOT NULL,
  LDD                              DATE                                                                       NULL,
  CONSTRAINT PK_TEST__PID PRIMARY KEY (PID),
  CONSTRAINT UK_TEST__CNT__LDM__LDD UNIQUE (CNT, LDM, LDD)
);

CREATE SEQUENCE SEQ_TEST
  INCREMENT BY 1
  START WITH 1
  MINVALUE 1
  MAXVALUE 9999999999999999
  NOCYCLE
  ORDER
  CACHE 20
;

INSERT INTO TEST (PID) VALUES (SEQ_TEST.NEXTVAL);
INSERT INTO TEST (PID) VALUES (SEQ_TEST.NEXTVAL);
==============================================================================
ORA-00001: unique constraint (SCOTT.UK_TEST__CNT__LDM__LDD) violated

[15 Jun 2004 19:07] Sergei Golubchik

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is the correct behaviour. Oracle does it wrong.
Indeed, you cannot say that LDD column contain identical values, because "NULL=NULL" is not true.

Also, the SQL standard explicitly dictates the behaviour that we have implemented:

2) If there are no two rows in T such that the value of each column in one row is non-null
   and is not distinct from the value of the corresponding column in the other row, then
   the result of the <unique predicate> is True; otherwise, the result of the
   <unique predicate> is False.

(the text above is for <unique predicate> not for <unique constraint>, but it's because the
standard defines <unique constraint> via <unique predicate> - so I skipped intermediate steps for brevity)