[MDEV-13596] CHECK constraints disallow NULL to pass through, violating SQL Created: 2017-08-21  Updated: 2017-09-18  Resolved: 2017-09-18

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.2.1, 10.2
Fix Version/s: 10.2.9

Type: Bug Priority: Major
Reporter: Mike Bayer Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7563 Support CHECK constraint as in (or cl... Closed

 Description   

Given a table as follows:

MariaDB [test]> create table ck_test (
 
    ->     x int,
 
    ->     CHECK (x IN (0, 1))
 
    -> );
 
Query OK, 0 rows affected (0.06 sec)

the database disallows the value of "x" to be NULL:

MariaDB [test]> insert into ck_test (x) values (NULL);
 
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `test`.`ck_test`

This despite the fact that the constraint condition here evaluates to NULL:

MariaDB [test]> SELECT NULL IN (0, 1);
 
+----------------+
 
| NULL IN (0, 1) |
 
+----------------+
 
|           NULL |
 
+----------------+
 
1 row in set (0.38 sec)
 
 
 
 

Per SQL standard in Foundations:

"4.6.6.3 Table constraints"
"A table check constraint specifies a search condition. The constraint is violated if the result of the search condition is false for any row of the table (but not if it is unknown)."

the result here is NULL, not false; null is "unknown".

The behavior is also inconsistent with that of other major databases:

Postgresql:

psql (9.5.7)
 
Type "help" for help.
 
 
 
test=# CREATE TABLE ck_test(
 
test(# x int,
 
test(# CHECK (x IN (0, 1))
 
test(# );
 
CREATE TABLE
 
test=# insert into ck_test (x) values (NULL);
 
INSERT 0 1
 
 
 
 

Oracle:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
 
 
SQL> CREATE TABLE ck_test (
 
  x int,
 
  CHECK (x IN (0, 1))
 
)
 
  2    3    4    5  ;
 
 
 
Table created.
 
 
 
SQL> insert into ck_test (x) values (NULL); 
 
 
1 row created.



 Comments   
Comment by Sergei Golubchik [ 2017-08-21 ]

Also, in 4.23.3.4 Table check constraints of part 2, SQL:2016

Let T be a base table that is not a system-versioned table. A table check constraint on T is satisfied if and only if the specified <search condition> evaluates to True or Unknown for every row of T.

Generated at Thu Feb 08 08:06:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.