NULLable column is rejected when domain definition includes a check with select
-------------------------------------------------------------------------------
Key: CORE-6327
URL: http://tracker.firebirdsql.org/browse/CORE-6327
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.9
Environment: Windows 2012 R2
Reporter: Alvaro Castiello
I have a table for several constants: (oversimplified)
create table values (
id integer not nulll primary key, // will never change
category varchar(100) not null, // all rows of the same category
hold the values they can have
value varchar(30) not null // displayed value
);
"values" could have something like:
1 sex male
2 sex femenine
3 color blue
4 color red
5 color yellow
now, a table that uses "values"
create table models as ( //
clothes
model_id integer not null primary key,
name varchar(100) not null,
suitable_for sex_map references values(id),
factory_color color_map references values(id)
)
suitable_for and factory_color can be null because: suitable_for is NULL when
the model is unisex and factory_color is NULL because it could be unknown.
In order to restrict the values so a color won´t be confused with sex, I
defined two domains as
create domain sex_map integer check (value in (1,2));
create domain color_map integer check (value in (3,4,5));
so
insert into models (model_id,name,suitable_for,factory_color) values (1,'model
1',null,null);
runs fine
but...
later I realized that more colors could exists and also more sex values:
6 sex kid
7 sex girl
8 color green
this would also implies to modify the domains every time a new record is
inserted in values so, they are better defined as
create domain sex_map integer check (value in select id from values where
category='sex');
create domain color_map integer check (value in select id from values where
category='color');
so here is the bug:
Given such domain definitions this insert is rejected
insert into models (model_id,name,suitable_for,factory_color) values (1,'model
1',null,null);
with message
The insert failed because a column definition includes validation constraints.
validation error for column SUITABLE_FOR, value "*** null ***". (is nullable by
definition)
My guess is that the value NULL for SUITABLE_FOR causes the domain restriction
to be evaluated as
NULL in select id from values where category='color'
which in turn is a NULL expression (to my knowledge)
Finally, the logic of the constraint should be something like this
if (SUITABLE_FOR.thevalue = result of the constraint) then
accept the row
the value SUITABLE_FOR is also NULL so this will evaluate as
if (NULL = NULL) then
accept the row
which causes the error
conclusion: not always a constraint restriction in a domain returns TRUE or
FALSE. It may also evaluate to NULL
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
