Behaviour of VIEW which DDL has "WITH CHECK OPTION" differs depending on fields
in its select list
--------------------------------------------------------------------------------------------------
Key: CORE-4724
URL: http://tracker.firebirdsql.org/browse/CORE-4724
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
DDL:
====
recreate view v1 as select 1 n1 from rdb$database; commit; -- drop dependencies
(if any)
recreate table t1 (id int, n1 int, n2 int);
insert into t1 values (1, 1, 2);
insert into t1 values (2, 1, 3);
insert into t1 values (3, 1, 4);
insert into t1 values (4, 2, 2);
insert into t1 values (5, 2, 3);
insert into t1 values (6, 2, 4);
insert into t1 values (7, 3, 2);
insert into t1 values (8, 3, 3);
insert into t1 values (9, 3, 4);
commit;
Hereafter note on the list in SELECT section of each view and result of DMLs:
CASE-1
=======
recreate view v1 as
select
n1
from t1
where n1 < n2 with check option;
commit;
update v1 set n1 = n1 - 1;
update v1 set n1 = n1 + 1;
Result:
----------
Statement failed, SQLSTATE = 23000
Operation violates CHECK constraint on view or table V1
-At trigger 'CHECK_13'
CASE-2
=======
recreate view v1 as
select
n1,
n2 ------------- add field which is also DOES participate in `WHERE`
clause of view as field `n1`
from t1 where n1 < n2 with check option;
commit;
update v1 set n1 = n1 - 1;
update v1 set n1 = n1 + 1;
Result:
----------
PASSED, no errors.
CASE-3
=======
recreate view v1 as
select
id, ------------- add field which does NOT participate in `WHERE` clause of
view as field `n1`
n1
from t1 where n1 < n2 with check option;
commit;
update v1 set n1 = n1 - 1;
update v1 set n1 = n1 + 1;
Result:
----------
PASSED, no errors.
CASE-4:
-----------
recreate view v1 as
select
n1,
n1 as nx ---------------- yes, the SAME field of table but with adding
alias for it
from t1 where n1 < n2 with check option;
commit;
update v1 set n1 = n1 - 1;
update v1 set n1 = n1 + 1;
Result:
----------
PASSED, no errors.
--
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
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
