[DERBY-1343] It is possible to have duplicate entries in conglomerateId of sysconglomerates before DERBY-655 was fixed in 10.0 or 10.1 databases. It is desirable to patch these databases on upgrade to 10.2 so conglomerateId becomes unique again.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 10.0.2.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Mamta A. Satoor
• Created: 25/May/06 02:00
• Updated: 18/Jun/13 16:15
• Resolved: 30/Nov/09 15:20

Description

Because of an optimization implemented in before Derby 10.0 release, it is possible to have duplicate entries in conglomerateId column. It would be good to patch these entries during upgrade to 10.2 or later so that conglomerateIds become unique again. See the discussion and proposed solutions at:
http://www.nabble.com/-Derby-655-+%3A+getImportedKeys+returns+duplicate+rows+in+some+cases-t1673189.html#a4535887

When a user defines a constraint, Derby checks to see if it's backing index is a duplicate of an existing index and if yes, then it shares the same conglomerates for both such indexes. Code for this is in org.apache.derby.impl.sql.execute.CreateIndexConstantAction.executeConstantAction. This causes Derby to have duplicate rows in sysconglomerates with same conglomerateid. (More information on this can be found in thread http://www.nabble.com/-Derby-655-+%3A+getImportedKeys+returns+duplicate+rows+in+some+cases-t1673189.html#a4535887 titled "DERBY-655 : getImportedKeys returns duplicate rows in some cases".

During drop constraint, it looks like Derby is not able to identify the correct row in SYSCONGLOMERATES, if there are duplicate rows with same conglomerateid and as a consequence, wrong row gets dropped in SYSCONGLOMERATES. More information with an example on this can be found in thread http://www.nabble.com/When+foreign+key+is+dropped%2C+is+Derby+dropping+the+wrong+row+from+SYS.SYSCONGLOMERATES--t1654121.html#a4481463 titled "When foreign key is dropped, is Derby dropping the wrong row from SYS.SYSCONGLOMERATES?"

Issue Links

• is related to: DERBY-6269 Misleading warning text if you create an index duplicating a constraint.
• relates to: DERBY-655 getImportedKeys returns duplicate rows in some cases
• relates to: DERBY-5249 A table created with 10.0.2.1 with constraints cannot be dropped with 10.5 due to NullPointerException with insane build or ASSERT FAILED Failed to find sharable conglomerate descriptor for index conglomerate with sane build