[
https://issues.apache.org/jira/browse/DERBY-3299?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
A B updated DERBY-3299:
-----------------------
Attachment: d3299_dropSharedConglom_v1.patch
d3299_caUtilMethods_v1.patch
d3299_createIxAction_v1.patch
I broke the single _v1 patch down into 4 smaller, relatively modular patches in
hopes of making things easier for any potential reviewers. The patches are:
1. d3299_createIxAction_v1.patch: Changes/additions to
CreateIndexConstantAction.java so that it can support the creation of
"replacement" conglomerates, which will be necessary when the physical
conglomerate for an index has been dropped but the index descriptor still
exists. In that case we need to create a new physical conglomerate to support
the existing descriptor. This can happen if multiple indexes share a physical
conglomerate but then the conglomerate is dropped as part of "drop index"
processing for one of the indexes. (Note that "indexes" here includes indexes
which were created to back constraints).
2. d3299_caUtilMethods_v1.patch: Addition of utility methods to
DDLSingleTableConstantAction.java that can be used by any ConstantAction
classes which drop constraint or conglomerate descriptors. The methods
recognize when a "replacement" conglomerate is needed and make the necessary
calls to create such a conglomerate. See previous comment for details.
3. d3299_dropSharedConglom_v1.patch: Changes to ConglomerateDescriptor that
include logic for detecting a "replacement" scenario, and logic to drop the old
(shared) conglomerate in those scenarios. Also changes the various
ConstantAction classes which rely on the "drop()" methods of
ConglomerateDescriptor and ConstraintDescriptor to use the utility methods
added as part of caUtilMethods_v1.patch. This is the patch which ultimately
fixes this issue and "activates" the preceding two patches--and thus is the
only one which changes the functional behavior of Derby.
4. d3299_tests_v1.patch: New JUnit tests for verifying the correct behavior
when constraints share a physical conglomerate and then one of them is dropped.
I'm attaching all 4 patches here, but plan to commit them one at a time, a day
or two apart, to allow for any review comments/suggestions. In the absence of
review comments I'll just go ahead and commit each one in its own time.
As each patch is committed, the subsequent patches may need to be tweaked to
allow for minor conflicts that exist between them. I will posted updated
patches as necessary--but the _v1 patches should at least allow reviewers to
see the changes and make comments.
Note to any reviewers: the patches build on each other: i.e. attempts to apply
and build patch #2 will fail unless patch #1 has been applied first.
I ran derbyall with d3288_createIxAction_v1.patch and it ran cleanly; I'm
currently running suites.All. If the latter passes, then I plan to commit that
patch tomorrow (Thursday, Feb 14, PST).
> Uniqueness violation error (23505) occurs after dropping a PK constraint if
> there exists a foreign key on the same columns.
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3299
> URL: https://issues.apache.org/jira/browse/DERBY-3299
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1,
> 10.4.0.0
> Reporter: A B
> Assignee: A B
> Priority: Minor
> Attachments: case_2.sql, d3299_caUtilMethods_v1.patch,
> d3299_createIxAction_v1.patch, d3299_dropSharedConglom_v1.patch,
> d3299_tests_v1.patch, d3299_v1.patch
>
>
> When there are multiple constraints on a single table and the constraints
> have the same set of columns (in the same order), Derby tries to optimize
> things by re-using a single backing index for all of the relevant
> constraints. See the "executeConstantAction()" method of
> CreateIndexConstantAction.java (search for "duplicate").
> But there is a bug in Derby where, if one of the constraints is unique and is
> dropped, the uniqueness "attribute" of the backing index is not updated
> accordingly. This means that uniqueness may be incorrectly enforced where it
> is not required.
> Take the following example ("Case 2" from DERBY-2204):
> ALTER TABLE NEWORDERS ADD CONSTRAINT
> NEWORDERS_PK PRIMARY KEY(NO_W_ID, NO_D_ID, NO_O_ID);
> ALTER TABLE NEWORDERS ADD CONSTRAINT
> NO_O_FK FOREIGN KEY (NO_W_ID, NO_D_ID, NO_O_ID) REFERENCES ORDERS;
> For these statements Derby will use a single backing index for both the
> primary constraint NEWORDERS_PK and the foreign key constraint NO_O_FK. That
> backing index will be unique because the primary key must itself be unique.
> If later we drop the primary key:
> ALTER TABLE NEWORDERS DROP CONSTRAINT NEWORDERS_PK;
> then the backing index needs to be converted from a unique index to a
> non-unique index (because a foreign key is not inherently unique). But in
> Derby the uniqueness attribute remains unchanged, so attempts to insert a
> duplicate (NO_W_ID, NO_D_ID, NO_O_ID) row into NEWORDERS will fail with error
> 23505, when it should really succeed.
> I tried this out on 10.1.3.1 and the same behavior occurs there, so marking
> "Affects" versions for everything back to that...
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
