mariadb 10.2 CHECK constraints don't allow NULL to pass, need to revise BOOLEAN, ENUM

will seek reporting this upstream as this likely breaks the SQL standard:

CREATE TABLE ck_cons_test (
    -> b1 INT NULL, 
    -> b2 INT NULL,
    -> CHECK (b1 IN (0, 1)), 
    -> CHECK (b2 IN (0, 1, NULL))
    -> );
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> INSERT INTO ck_cons_test (b1, b2) VALUES (1, 0);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> 
MariaDB [test]> INSERT INTO ck_cons_test (b1, b2) VALUES (1, NULL);
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_2` failed for `test`.`ck_cons_test`
MariaDB [test]> 
MariaDB [test]> INSERT INTO ck_cons_test (b1, b2) VALUES (NULL, 0);
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `test`.`ck_cons_test`

Comments (2)