Issue Details (XML | Word | Printable)

Key: CORE-6327
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Alvaro Castiello
Votes: 0
Watchers: 6
Operations

If you were logged in you would be able to see more operations.
Firebird Core

NULLable column is rejected when domain definition includes a check with select

Created: 08/Jun/20 07:10 PM   Updated: 10/Jun/20 06:10 AM
Component/s: None
Affects Version/s: 2.5.9, 3.0.5, 4.0 Beta 2
Fix Version/s: None

Environment: Windows 2012 R2
Issue Links:
Relate
 

QA Status: No test


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 08/Jun/20 07:40 PM
Should not it be: check (value is null or value in (select ...
?

Looks like support question.

Alvaro Castiello added a comment - 08/Jun/20 08:01 PM
Maybe the constraint should be defined as you say Adriano but...
a) I don´t think is a good practice to define nullability in the domain but in the table. Some tables may accept null some others don´t. But in any case, they must honor the domain restriction in case the value is not null
b) If the column is defined as nullable, it should accept null without regarding the constraint (and domain) definition. I guess this is SQL standard



Apart, something like this:
create domain SOMETHING as integer check (value is not null)
and a column defined as
 somecolumn SOMETHING, (without "not null")
will lead to some phylosophical discussion

Karol Bieniaszewski added a comment - 09/Jun/20 05:57 AM
>> If the column is defined as nullable, it should accept null without regarding the constraint (and domain) definition

i am really opposite to what you say.
If you declare Field Integer;
then you have chack constraint (VALUE IN (1,2,3))

then it should accept 4,5,6,7..?
No, as you have restricted Field more by check constraint.

And in your constraint you limit values to only from select.

Mark Rotteveel added a comment - 09/Jun/20 08:48 AM - edited
A CHECK constraint should only reject a value if the result is FALSE, not when it is TRUE or NULL[1], so given `(NULL in (non-empty list or select)` is NULL, the CHECK constraint should allow the value. This may be related to the 'false null' issue (CORE-6322) I reported recently for Firebird 3.

 [1]: https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-ddl-tbl-constraints-check

Adriano dos Santos Fernandes added a comment - 09/Jun/20 10:26 AM
Mark, you are correct. Pull request for CORE-6322 fixes this bug too.

Karol Bieniaszewski added a comment - 10/Jun/20 06:10 AM
I see that i never understand the sql standard logic.
Maybe becouse of that i always put
(VALUE IS NULL OR VALUE IN (1,2,3))
or
(VALUE IS NOT NULL OR VALUE IN (1,2,3))

to not depend on some strange logic behind.
For me if i write explicit values then only accepted are these values not one more like null. But this is sql standard logic...