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

          relates to

          Task - A task that needs to be done. MDEV-7563 Support CHECK constraint as in (or close to) SQL Standard

          • Major - Major loss of function.
          • Closed

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.