Issue Details (XML | Word | Printable)

Key: CORE-4307
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 1
Operations

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

Fields present only in WHERE clause of views WITH CHECK OPTION causes invalid CHECK CONSTRAINT violation

Created: 30/Dec/13 01:18 AM   Updated: 23/Sep/15 12:44 PM
Component/s: Engine
Affects Version/s: 3.0 Alpha 1, 3.0 Alpha 2
Fix Version/s: 3.0 Beta 1

Issue Links:
Relate
 

QA Status: Done successfully


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


 All   Comments   Change History   Subversion Commits      Sort Order: Descending order - Click to sort in ascending order
Pavel Cisar added a comment - 23/Sep/15 12:44 PM
Test created.

Adriano dos Santos Fernandes added a comment - 26/Mar/15 06:54 PM
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.

Pavel Zotov added a comment - 26/Mar/15 05:39 PM
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