Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Behaviour of VIEW which DDL has "WITH CHECK OPTION" differs depending on fields in its select list [CORE4724] #5031

Open
firebird-automations opened this issue Mar 26, 2015 · 2 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE4307 [ CORE4307 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants