[
https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575212#action_12575212
]
A B commented on DERBY-3456:
----------------------------
As follow-up to my previous comment I ran various "conglomerate sharing"
scenarios with uniqueWhenNotNull constraints. After applying
derby-3456v3.diff, I see the following:
1. uniqueWhenNotNull constraint can share a conglomerate with an existing
non-unique index, which renders the uniqueWhenNotNull constraint ineffective
(per my previous comment).
2. uniqueWhenNotNull constraint can share a conglomerate with an existing
unique index. This seems okay from the "sharing" perspective, but if the
unique index is dropped, the "replacement" conglomerate that is created must
correctly enforce uniqueWhenNotNull. That is not currently the case; instead,
the replacement conglomerate ends up being a normal non-unique conglomerate.
Ex:
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 unique index uix on t1(i,j);
insert into t1 values (null, 1, -1);
-- This will share a conglomerate with UIX.
alter table t1 add constraint uc unique(i,j);
-- Should drop shared conglomerate and re-create a new one
-- that is uniqueWhenNotNull. But currently we just end up
-- with a non-unique index where uniqueWhenNotNull is false.
drop index uix;
-- Should be okay.
insert into t1 values (null, 1, -1);
insert into t1 values (null, 1, -1);
-- Should fail due to uniqueWhenNotNull, but currently succeeds.
insert into t1 values (1, -1, 1);
I think the problem here is that the IndexRowGenerator() which is created for a
"sharing" conglomerates does not account for the uniqueWhenNotNull attribute.
See line ~492 in CreateIndexConstantAction.java.
3. A foreign key constraint can share a conglomerate with a uniqueWhenNotNull
constraint. This seems okay from the "sharing" perspective, but if the
uniqueWhenNotNull constraint is dropped, Derby should create a "replacement"
conglomerate that is a normal, non-unique conglomerate. That is not currently
the case, though; instead, the uniqueWhenNotNull conglomerate remains in tact
and incorrectly enforces uniqueness where it should not be enforced (similar to
DERBY-3299). Ex:
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);
alter table t1 add constraint uc unique(i,j);
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);
-- This foreign key will share a conglomerate with UC, which is
uniqueWhenNotNull.
alter table t1 add constraint fkt1 foreign key (i,j) references t2;
-- Should fail due to UC (and it does).
insert into t1(i,j) values (1, -1);
-- Drop UC, the conglomerate for the foreign key should be re-created as
non-unique.
-- But this doesn't currently happen.
alter table t1 drop constraint uc;
-- This should now succeed because UC has been dropped, but it still fails,
claiming
-- (incorrectly) that FKT1 is a unique constraint.
insert into t1(i,j) values (1, -1);
I think the problem here is that the "drop(...)" and
"describeSharedConglomerate(...)" methods of ConglomerateDescriptor.java do not
recognize the "uniqueWhenNotNull" attribute, and thus are not able to detect
that a new (non-unique) conglomerate is necessary.
As a summary of all three issues, I think it's safe to say that the changes
which were made for DERBY-3299 need to be enhanced to account for the new
"uniqueWhenNotNull" type of conglomerate, and to do the correct thing when such
a conglomerate is in play.
> Allow removing not null from collumns particpating in unique constraint.
> ------------------------------------------------------------------------
>
> Key: DERBY-3456
> URL: https://issues.apache.org/jira/browse/DERBY-3456
> Project: Derby
> Issue Type: Sub-task
> Components: SQL, Store
> Affects Versions: 10.4.0.0
> Environment: all
> Reporter: Anurag Shekhar
> Assignee: Anurag Shekhar
> Attachments: altertable.diff, derby-3456-Tests.diff,
> derby-3456v1.diff, derby-3456v2.diff, derby-3456v3.diff, setnulltest.diff,
> upgradetests.diff, upgradetests_v2.diff
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
