Skip to content

View can not be subject of DML if it is declared WITH CHECK OPTION and COMPUTED-BY column present in the this view expression [CORE6535] #6762

Open
@firebird-automations

Description

@firebird-automations

Submitted by: @pavel-zotov

Consider script:

shell del C:\temp\tmp.fdb 2>nul;
create database 'localhost:C:\temp\tmp.fdb';

recreate table test_nums(
id int generated by default as identity primary key
,n1 int
,n2 int
,calc_n1_n2_sum computed by( n1 + n2 )
);

recreate view v_check_sum_of_normal_columns as select n1,n2 from test_nums where n1 + n2 = 5
with check option
;

recreate view v_check_value_of_computed_col as select n1,n2 from test_nums where calc_n1_n2_sum = 5
with check option
;

recreate view v_check_of_mixed_columns_sum as select n1,n2 from test_nums where n1 < calc_n1_n2_sum
with check option
;

commit;

set echo on;

insert into v_check_sum_of_normal_columns(n1, n2) values(2, 3);

insert into v_check_value_of_computed_col(n1, n2) values(2, 3);

insert into v_check_of_mixed_columns_sum(n1, n2) values(2, 3);

Its output will be:

insert into v_check_sum_of_normal_columns(n1, n2) values(2, 3);

insert into v_check_value_of_computed_col(n1, n2) values(2, 3);
Statement failed, SQLSTATE = 23000
Operation violates CHECK constraint on view or table V_CHECK_VALUE_OF_COMPUTED_COL
-At trigger 'CHECK_4'

insert into v_check_of_mixed_columns_sum(n1, n2) values(2, 3);
Statement failed, SQLSTATE = 23000
Operation violates CHECK constraint on view or table V_CHECK_OF_MIXED_COLUMNS_SUM
-At trigger 'CHECK_6'

Checked on WI-V4.0.0.2406; WI-V3.0.8.33435.

PS.
I could not find apropriate info in SQL:2011 about this case. Perhaps this is not a bug, so I've set priority to minor.

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions