Hi all,

When i check for "IN (null, ...)" in a check constraint, it allows to store any value in the field.
I presume the cause is the IN behavior when handling nulls. Is it incorrect to use NULLs inside INs?

Regards,

Fabiano

FB 2.1.2 CS, Windows

create domain boolean1 as
char(1) character set iso8859_1 check (value in (NULL, 'S', 'N')) collate iso8859_1;

create domain boolean2 as
char(1) character set iso8859_1 check (value in ('S', 'N')) collate iso8859_1;

create table test1 (
field1 integer not null,
field2 boolean1);

create table test2 (
field1 integer not null,
field2 boolean2);

insert into test1 values (1, null);
-- OK
insert into test1 values (1, '?');
-- OK
insert into test1 values (1, 'S');
-- OK

insert into test2 values (1, null);
-- OK
insert into test2 values (1, '?');
-- ERROR
insert into test2 values (1, 'S');
-- ERROR

select * from test1 a where a.field2 in (null, 'S', 'N');
FIELD1 FIELD2
1 S