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

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 opened this issue Apr 6, 2021 · 0 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

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

1 participant