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

Fields present only in WHERE clause of views WITH CHECK OPTION causes invalid CHECK CONSTRAINT violation [CORE4307] #4630

Closed
firebird-automations opened this issue Dec 30, 2013 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Is related to CORE4724
Is related to QA586

Test case:

recreate table t1 (n1 integer, n2 integer);
insert into t1 values (1, 2);
insert into t1 values (1, 3);
insert into t1 values (1, 4);
insert into t1 values (2, 2);
insert into t1 values (2, 3);
insert into t1 values (2, 4);
insert into t1 values (3, 2);
insert into t1 values (3, 3);
insert into t1 values (3, 4);

--With this one works correctly: recreate view v1 as select n1, n2 from t1 where n1 < n2 with check option;
recreate view v1 as select n1 from t1 where n1 < n2 with check option;

update v1 set n1 = n1 - 1;

Statement failed, SQLSTATE = 23000
Operation violates CHECK constraint on view or table V1
-At trigger 'CHECK_5'

Commits: 980cc3e FirebirdSQL/fbt-repository@dc16e1e

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Component: Engine [ 10000 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA586 [ QA586 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

It seems to me that some problem still DOES exist.

Please look at two scripts:

CASE-1

recreate view v1 as select 1 n1 from rdb$database; commit; -- drop dependencies (if any)

recreate table t1 (id int, n1 int, n2 int); -- field ID has been added to this table in order to distinguish rows when they are displayed
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;
recreate view v1 as
select
n1 -------------------------- note: this view does NOT contain any other fields except this
from t1
where n1 < n2
with check option;
commit;

select * from v1;
update v1 set n1 = n1 - 1;
update v1 set n1 = n1 + 1; ------------------ [ *** ]
select * from v1;

The statement marked with [ *** ] will FAIL with message:

Statement failed, SQLSTATE = 23000
Operation violates CHECK constraint on view or table V1
-At trigger 'CHECK_1'

CASE-2

Code is the same as in case-1 but view's DDL is changed by adding field ID from table T1:

recreate view v1 as
select
id, ------------------- this is added field
n1
from t1
where n1 < n2
with check option;

Result: NO errors when running the same statements:
. . .
select * from v1;

      ID           N1

============ ============
1 1
2 1
3 1
5 2
6 2
9 3

update v1 set n1 = n1 - 1;
select * from v1;

      ID           N1

============ ============
1 0
2 0
3 0
5 1
6 1
9 2

update v1 set n1=n1+1; ------------- now this statement finishes OK
select * from v1;

      ID           N1

============ ============
1 1
2 1
3 1
5 2
6 2
9 3

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Seems you misread this ticket. It talks about "3.0 Alpha 1, 3.0 Alpha 2", and the original test case works in 2.5, so it was about a regression.

So you found a new bug which needs a new ticket.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is related to CORE4724 [ CORE4724 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

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