View can not be subject of DML if it is declared WITH CHECK OPTION and
COMPUTED-BY column present in the this view expression
-----------------------------------------------------------------------------------------------------------------------------
Key: CORE-6535
URL: http://tracker.firebirdsql.org/browse/CORE-6535
Project: Firebird Core
Issue Type: Bug
Components: Engine
Reporter: Pavel Zotov
Priority: Minor
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
