You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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.
The text was updated successfully, but these errors were encountered: