Skip to content
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

Open
firebird-automations opened this issue Jun 8, 2020 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Should not it be: check (value is null or value in (select ...
?

Looks like support question.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alvaro Castiello (acastiello)

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

@firebird-automations
Copy link
Collaborator Author

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.
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.

@firebird-automations
Copy link
Collaborator Author

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.

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Mark, you are correct. Pull request for CORE6322 fixes this bug too.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE6322 [ CORE6322 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 4.0 Beta 2 [ 10888 ]

Version: 3.0.5 [ 10885 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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...

@acastiello
Copy link

I regret to inform that the bug still exists, but in a strange manner. FB 64 bits 3.0.7.33374
The insert is still rejected
Reason:
SQL Error [335544347] [23000]: validation error for column "SERVICIOS"."SERVICIO_CALIFICACION_ID", value "*** null ***" [SQLState:23000, ISC error code:335544347]
(SERVICIOS.SERVICIO_CALIFICACION_ID is kind of "suitable_for" field and domain CALIFICACION_MAP exists)

After doing some digging I found some strange behavior:
a) the insertion is rejected but the update is not. If any (valid) value for CALIFICACION_MAP is inserted, and later nulled, the update is accepted
b) select null in (select id from values where category='sex') from RDB$DATABASE (incorrectly) returns false!
c) in fact
select null in (select id from values where category='nonexistant') from RDB$DATABASE also returns false!
d) in fact
select null in (select id from values) from RDB$DATABASE also returns false!
e) in fact
select null in (select anyfield from anytable) from RDB$DATABASE also returns false!
f) however
select null in (1,2,3) from RDB$DATABASE
correctly returns NULL

So, the insertion is "correctly" rejected, but according to Mark, the expression should evaluate to NULL and therefore, accepted

A couple of notes:
1.- Since the field is nullable, the domain constraint should not even be triggered. A nullable field with a check should be accepted if:
(fields is null) or (check constraint is distinct from false)
so only if the field is not null, the constraint should be checked
2.- It seems (not sure) that NULL is converted to zero on the above select statements

@mrotteveel
Copy link
Member

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants