Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.1, 10.2(EOL)
-
None
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. |
|
Attachments
Issue Links
- relates to
-
MDEV-7563 Support CHECK constraint as in (or close to) SQL Standard
-
- Closed
-
Also, in 4.23.3.4 Table check constraints of part 2, SQL:2016