[MDEV-31397] Implement DISTINCT and NOT DISTINCT for unique NULL handling Created: 2023-06-03  Updated: 2023-06-03

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Ian Gilfillan Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: sql2023

Issue Links:
Relates
relates to MDEV-21527 NULL values allowing duplicate keys i... Closed

 Description   

In MariaDB, unique indexes permit duplicate NULLs.

CREATE TABLE t2 (
 
    a int,
 
    b int,
 
    c int,
 
    UNIQUE (a, b, c) 
);
 
 
 
INSERT INTO t1 VALUES (1, NULL, NULL);
 
INSERT INTO t1 VALUES (1, NULL, NULL);
 
 
 
SELECT * FROM t1;
 
+------+------+------+
 
| a    | b    | c    |
 
+------+------+------+
 
|    1 | NULL | NULL |
 
|    1 | NULL | NULL |
 
+------+------+------+

Previously, the SQL standard has not been clear about the behaviour, leaving it to be implemented in multiple ways. For example, SQL Server does not permit the second row to be inserted, while PostgreSQL does.

Now, with the SQL 2023 standard, the behaviour is made explicit:

CREATE TABLE t2 (
 
    a int,
 
    b int,
 
    c int,
 
    UNIQUE NULLS DISTINCT (a, b, c)
 
);

allows the duplicate NULL to be inserted, while 

CREATE TABLE t3 (
 
    a int,
 
    b int,
 
    c int,
 
    UNIQUE NULLS NOT DISTINCT (a, b, c)
 
);

does not.
Generated at Thu Feb 08 10:23:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.