[
https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575180#action_12575180
]
A B commented on DERBY-3456:
----------------------------
Thanks for the patch, Anurag. It seems that with the patch applied, some
conglomerate sharing now occurs where it should not, which can lead to
incorrect behavior. For example, the following works as expected:
create table t1 (i int, j int not null);
alter table t1 add constraint uc unique (i,j);
insert into t1 values (2, 2);
-- Should fail due to unique constraint on i, j, and it does.
insert into t1 values (2, 2);
But if I create a non-unique index before creating the unique constraint, the
unique constraint is no longer enforced:
create table t1 (i int, j int not null);
create index ixnu on t1 (i,j);
-- This new constraint UC will incorrectly share a conglomerate with "IXNU",
which is non-unique...
alter table t1 add constraint uc unique (i,j);
insert into t1 values (2, 2);
-- This should fail due to unique constraint on i, j, but it succeeds.
insert into t1 values (2, 2);
ij> select * from t1;
I |J
-----------------------
2 |2
2 |2
2 rows selected
I think this goes back to my Feb 27th comment: namely, I think the criteria for
sharing a conglomerate has to be updated to account for the fact that we can
now have non-unique conglomerates which enforce uniqueness in the absence of
NULLs. In such a case the uniqueWhenNotNull constraint, which is implemented
using a non-unique conglomerate, canNOT share with an existing non-unique
conglomerate UNLESS the existing conglomerate is itself uniqueWhenNotNull. At
least I think that's how it's should work; can you verify?
> 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.
