firebird-automations
commented
9 years ago Open firebird-automations opened 9 years ago
firebird-automations
commented
9 years ago firebird-automations
commented
9 years ago
Submitted by: @pavel-zotov
Relate to CORE4307
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.