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

            Ascending order - Click to sort in descending order
            zzzeek Mike Bayer created issue -
            zzzeek Mike Bayer made changes -
            Field Original Value New Value
            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.
            		Given a table as follows:


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

            {code}


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


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

            {code}


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


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



            {code}
            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:


            {code:java}
            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



            {code}
            Oracle:


            {code:java}
            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.

            {code}
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Affects Version/s 10.2.1 [ 22012 ]
            alice Alice Sherepa made changes -
            serg Sergei Golubchik added a comment -

            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.

            serg Sergei Golubchik added a comment - 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.
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2 [ 14601 ]
            serg Sergei Golubchik made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2.9 [ 22611 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 82187 ] MariaDB v4 [ 152670 ]

            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.