New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
NULLable column is rejected when domain definition includes a check with select [CORE6327] #6568
Comments
Commented by: @asfernandes Should not it be: check (value is null or value in (select ... Looks like support question. |
Commented by: Alvaro Castiello (acastiello) Maybe the constraint should be defined as you say Adriano but... Apart, something like this: |
Commented by: @livius2 >> 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. then it should accept 4,5,6,7..? And in your constraint you limit values to only from select. |
Commented by: @mrotteveel 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 (CORE6322) I reported recently for Firebird 3. |
Commented by: @asfernandes Mark, you are correct. Pull request for CORE6322 fixes this bug too. |
Modified by: @asfernandes |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: @livius2 I see that i never understand the sql standard logic. to not depend on some strange logic behind. |
I regret to inform that the bug still exists, but in a strange manner. FB 64 bits 3.0.7.33374 After doing some digging I found some strange behavior: So, the insertion is "correctly" rejected, but according to Mark, the expression should evaluate to NULL and therefore, accepted A couple of notes: |
This issue is still open, as is the linked issue, #6563 (which currently has a pull request to master in draft), so yes, it is not fixed in 3.0.7. |
Submitted by: Alvaro Castiello (acastiello)
Relate to CORE6322
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
The text was updated successfully, but these errors were encountered: