Problem with check constraints including in ... null makes oracle fail a query
I think I've just found a bug in oracle. Well, it might be known, but I've crashed on it today.
Testcase:
create table mytab (
mycol varchar2(1) constraint ck_buggy check (mycol in ('A', null))
);
Please, don't ask ... why would you want to do that condition? I found it as is.
insert into mytab values ('B');
-- 1 row inserted!! WOW!
Select * from mytab;
-- 1 row selected.
But you may argue that this is the way check constraints work. They might be implemented in a NEGATIVE way , i.e. instead of checking if condition 'B' in ('A', Null) is or not true, they check if NOT ('B' in ('A', Null)) is false. And it is not, so, the constraint does not stop you from inserting a 'B' value. Simply, don't gamble with nulls in "in" check constraints and problem solved.
Testcase:
create table mytab (
mycol varchar2(1) constraint ck_buggy check (mycol in ('A', null))
);
Please, don't ask ... why would you want to do that condition? I found it as is.
insert into mytab values ('B');
-- 1 row inserted!! WOW!
Select * from mytab;
-- 1 row selected.
But you may argue that this is the way check constraints work. They might be implemented in a NEGATIVE way , i.e. instead of checking if condition 'B' in ('A', Null) is or not true, they check if NOT ('B' in ('A', Null)) is false. And it is not, so, the constraint does not stop you from inserting a 'B' value. Simply, don't gamble with nulls in "in" check constraints and problem solved.
0