db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3502) Unique Constraint's backing index when shared with existing indexes doesn't behave as expected
Date Thu, 06 Mar 2008 22:04:58 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3502?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575915#action_12575915
] 

A B commented on DERBY-3502:
----------------------------

Thanks for v2, Anurag, the test changes look good now.

As for the engine changes, I think what you have so far is good, and it does indeed solve
the different scenarios that I described in DERBY-3456.

There is, however, one more scenario that is still not quite right.  It is as follows:

create table t1 (i int, j int not null, k int);
insert into t1 values (1, -1, 1), (2, -2, 4), (4, -4, 16), (3, -3, 9);

create table t2 (a int not null, b int not null);
alter table t2 add constraint pkt2 primary key(a,b);
insert into t2 values (1, -1), (2, -2), (4, -4), (3, -3);

-- First create a unique conglomerate on columns (i,j).
create unique index uix on t1(i,j);

-- Now create a uniqueWhenNotNull constraint on the same columns.
-- This uniqueWhenNotNull constraint will share UIX's conglomerate.
alter table t1 add constraint uc unique(i,j);

-- And finally, create a foreign key on the same columns.  This foreign
-- key will *also* share UIX's conglomerate.
alter table t1 add constraint fkt1 foreign key (i,j) references t2;

-- Should fail due to UIX (and it does).
insert into t1(i,j) values (1, -1);

-- Drop the unique index UIX. The conglomerate for UC and FKT1 should
-- be re-created as non-unique with uniqueWhenNotNull set to true.  But
-- this does not currently happen: instead, the replacement conglomerate
-- is non-unique with uniqueWhenNotNull set to FALSE.
drop index uix;

-- Should work.
insert into t1(i,j) values (null, 2);

-- Should also work since UIX is no longer around.
insert into t1(i,j) values (null, 2);

-- Should fail due to UC, but since the replacement conglomerate created
-- above is NOT uniqueWhenNotNull, this statement will succeed.
insert into t1 values (1, -1, 1);

-- End result is that we have two rows with (1, -1, 1), which should not have
-- been allowed to due UC.
select * from t1;

I think what's needed is additional logic in the "describeSharedConglomerate()" method of
ConglomerateDescriptor.java.  In the absence of a unique conglomerate, that method will currently
return the last non-unique conglomerate that it finds in the "descriptors" array.  Depending
on the order in which the constraints are created, this might be UC and it might be FKT1.
 In the above case it's FKT1, which means the method returns a non-unique conglomerate with
uniqueWhenNotNull set to false.

So I think you have to change describeSharedConglomerate() so that it will return a uniqueWhenNotNull
conglomerate over normal non-unique conglomerate, similar to how it will currently return
a unique conglomerate over a non-unique conglomerate.  Or put differently:

  - If the array contains a unique conglomerate, return that (already implemented).
  - Else if the array contains a non-unique conglomerate with uniqueWhenNotNull set to true,
return that.
  - Else return any non-unique conglomerate.

If you can do that, I think the above scenario will behave correctly.

> Unique Constraint's backing index when shared with existing indexes doesn't behave as
expected
> ----------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3502
>                 URL: https://issues.apache.org/jira/browse/DERBY-3502
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.0.0
>            Reporter: Anurag Shekhar
>            Assignee: Anurag Shekhar
>         Attachments: derby-3502v1.diff, derby-3502v2.diff
>
>
> Unique Constraint now uses non unique backing indexes with new attribute UniqueWithDuplicateNulls.
This index has following sharing properties
> 1. Can use an existing unique index.
> 2. Non Unique indexes (and foreign key) can use this index.
> While dropping a unique index a new index for unique constraint should be created.
> when a unique constraint is dropped a new index for a non unique index or foreign key
should be created. 
> Army has found several issues in actual behavior. This problems are listed in DERBY-3456.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message