Issue Details (XML | Word | Printable)

Key: CORE-4724
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Behaviour of VIEW which DDL has "WITH CHECK OPTION" differs depending on fields in its select list

Created: 26/Mar/15 07:33 PM   Updated: 26/Mar/15 08:03 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None

Issue Links:
Relate
 


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.