10 Replies Latest reply on Feb 10, 2012 10:17 AM by Jonathan Lewis

    Check constraint with NULL check, evaluates to TRUE for all values

    895463
      Hi,

      We are using Oracle 10.2.0.4 on RHEL4 64 bit OS.
      Recently we encountered something surprising related to check constraints. Our check constraint accepts all values!

      For example,
      CREATE TABLE EMPLOYEE
      (
      ENO NUMBER,
      NAME VARCHAR2(500 CHAR),
      GROUP_TYPE CHAR(1 CHAR)
      )

      ALTER TABLE EMPLOYEE ADD (
      CONSTRAINT EMPLOYEE_CK01
      CHECK (group_type IN ('A','B','C','D',NULL)));

      Above check constraint accepts any character. This is surprising us.
      For example,

      If we execute below simple SELECT, it doesn't returns any value i.e. the WHERE condition becomes FALSE and no records are returned.
      select 1 from dual where 1 in (NULL)

      Whereas, in case of below SQL, the WHERE condition becomes TRUE and 1 record is returned.

      Hence we think that, similarly the IN (NULL) condition for check constraint should become FALSE and gives CHECK Constraint error, "ORA-02290: check constraint (COB.EMPLOYEE_CK01) violated".
      But the check constraint IN clause becomes TRUE and allows insertion of record in the table.

      Is this expected behaviour? or is it a known BUG?

      Regards
      Shailendra
        • 1. Re: Check constraint with NULL check, evaluates to TRUE for all values
          SomeoneElse
          CHECK (group_type in ('A','B','C','D')))

          Yes of course, just leave out the null in the in list.

          Edited by: SomeoneElse on Dec 13, 2011 10:31 AM
          • 2. Re: Check constraint with NULL check, evaluates to TRUE for all values
            Centinul
            That does seem odd, as I was able to replicate it on my 11.2.0.1 system.

            However, including NULL in an IN list does not make any sense. An in-list is expanded to something like this:
            group_type = 'A'
            OR group_type = 'B'
            OR group_type = 'C'
            OR group_type = 'D'
            OR group_type = NULL
            group_type = NULL will always be false.

            There is no need to have a NULL condition in a check constraint in this example, just let the column be nullable.
            • 3. Re: Check constraint with NULL check, evaluates to TRUE for all values
              mbobak
              As others have mentioned, you don't need for your check constraint to check for NULL, simply allow nulls in the column, by not setting a NOT NULL constraint.

              However, why would Oracle allow for any value when you have a condition such as:
              column in('A','B','C','D',NULL)
              The answer is the way in which NULLs are evaluated in Oracle.

              The above in condition is expanded to:
              column = 'A' or
              column = 'B' or
              column = 'C' or
              column = 'D' or
              column =  NULL
              The problem there comes with the last ORed condition:
              column = NULL
              By definition of a NULL in Oracle, NULL is never equal to or not equal to anything.

              That is:
              :b1 = NULL
              :b2 != NULL
              for any value of b1 or b2, will never return TRUE or FALSE. Testing equality or inequality to NULL always returns UNKNOWN.

              For more information, see this link:
              http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm

              Hope that helps,

              -Mark
              • 4. Re: Check constraint with NULL check, evaluates to TRUE for all values
                Centinul
                To add to Mark's response:
                To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null).
                Source: Check constraints
                • 5. Re: Check constraint with NULL check, evaluates to TRUE for all values
                  905172
                  we can't compare any two NULL values.
                  • 6. Re: Check constraint with NULL check, evaluates to TRUE for all values
                    Jonathan Lewis
                    892460 wrote:
                    Hence we think that, similarly the IN (NULL) condition for check constraint should become FALSE and gives CHECK Constraint error, "ORA-02290: check constraint (COB.EMPLOYEE_CK01) violated".
                    But the check constraint IN clause becomes TRUE and allows insertion of record in the table.

                    Is this expected behaviour? or is it a known BUG?
                    As others have stated, it's expected behaviour - here's a little note I wrote a few months ago about the difference between constraints and predicates: http://jonathanlewis.wordpress.com/2011/02/21/constraints-2/


                    Regards
                    Jonathan Lewis
                    http://jonathanlewis.wordpress.com
                    Author: <b><em>Oracle Core</em></b>
                    • 7. Re: Check constraint with NULL check, evaluates to TRUE for all values
                      895463
                      Hi All,

                      Thanks for your reply.
                      But still my question in not answered yet.

                      If I use below SQL, the output is 1 row.

                      select 1 from dual where 1 in (1,NULL)

                      But when I use below SQL, the output is zero rows selected,

                      select 1 from dual where 1 in (2,NULL)

                      Similarly when a column defined with check constraint and having NULL as one of the value in the IN clause should evaluate to FALSE (as per second SQL) in case the corresponding value doesn't present in the IN clause.
                      But in case of check constraints it is evaluating TRUE and allowing that value to be inserted.

                      Regards
                      Shailendra
                      • 8. Re: Check constraint with NULL check, evaluates to TRUE for all values
                        895463
                        Hi Jonathan,

                        I have gone thorugh your example.
                        Even if you don't create CHECK constraint on column v1, the output of below command will always be 3 rows.
                        select count(*) from t1 where v1 in (1,2,3)

                        Regards
                        Shailendra
                        • 9. Re: Check constraint with NULL check, evaluates to TRUE for all values
                          Jonathan Lewis
                          892460 wrote:

                          If I use below SQL, the output is 1 row.
                          select 1 from dual where 1 in (1,NULL)
                          Your predicate expands to: (1 = 1 ) OR ( 1 = NULL), which evaluates to (TRUE) OR NULL which evaluates to TRUE, so you see one row output.

                          >
                          But when I use below SQL, the output is zero rows selected,
                          select 1 from dual where 1 in (2,NULL)
                          This predicate expands to (1 = 2) OR (1 = NULL), which evaluates to (FALSE) or NULL which evaluates to NULL, which is not the same as TRUE, so you see no rows.
                          Similarly when a column defined with check constraint and having NULL as one of the value in the IN clause should evaluate to FALSE (as per second SQL) in case the corresponding value doesn't present in the IN clause.
                          But the predicate in the second SQL doesn't evaluate to FALSE, it evaluates to NULL, which is not the same as TRUE.
                          But in case of check constraints it is evaluating TRUE and allowing that value to be inserted.
                          No, it's evaluating to NULL, which is not the same as FALSE.

                          As it said in the article - a query returns rows when the predicate evaluates to TRUE, a constraint allows rows where the check does not evaluate to FALSE.

                          Regards
                          Jonathan Lewis
                          http://jonathanlewis.wordpress.com
                          Author: <b><em>Oracle Core</em></b>
                          • 10. Re: Check constraint with NULL check, evaluates to TRUE for all values
                            Jonathan Lewis
                            >
                            I have gone thorugh your example.
                            Even if you don't create CHECK constraint on column v1, the output of below command will always be 3 rows.
                            select count(*) from t1 where v1 in (1,2,3)
                            >



                            That is correct - but the point of the note was to show that the constraint allows rows that do not appear when you use a predicate that seems to be identical in intent to the constraint. The predicate behave in the way that everyone would expect - the constraint seems (to those who forget about null effects) to be doing something wrong.





                            Regards

                            Jonathan Lewis

                            http://jonathanlewis.wordpress.com

                            Author: <b><em>Oracle Core</em></b>