Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13596

CHECK constraints disallow NULL to pass through, violating SQL

    Details

      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

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                zzzeek Mike Bayer
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: