[DERBY-6890] ALTER TABLE DROP COLUMN corrupts secondary index collation information

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.12.1.1
• Fix Version/s: 10.13.1.0
• Component/s: SQL
• Environment: Mac OS X 10.11.5; JDK: Oracle 1.8.0_92
• Assignee: Bryan Pendleton
• Reporter: Andrei Koiro
• Created: 27/May/16 06:57
• Updated: 15/Sep/16 15:25
• Resolved: 18/Jun/16 16:02

Description

For a database with "collation=/territory=" information configured via
the JDBC Connection URL at database connection time, individual
columns in tables and indexes in the database have collation identification
which is stored as part of the table/index conglomerate.

When an ALTER TABLE DROP COLUMN statement is run against
such a database, the drop column processing performs logic which
re-builds all of the (remaining) secondary indexes for that table
to reflect their new state following the removal of that column.

This index rebuild process does not properly re-configure the
collation information for column(s) in those index(es), leaving
the indexes in a corrupt state.

As a workaround, following the ALTER TABLE DROP COLUMN, the
damaged secondary indexes can be dropped and recreated explicitly.

== Original issue description below ==

After issue https://issues.apache.org/jira/browse/DERBY-3888 was fixed, we want to use the 'GENERATED BY DEFAULT' feature
for our tables.

To migrate our tables, we use this sql:
ALTER TABLE MODULE ADD COLUMN ID_TEMP BIGINT GENERATED BY DEFAULT AS IDENTITY;
UPDATE MODULE SET ID_TEMP = ID;
ALTER TABLE MODULE ALTER COLUMN ID_TEMP NOT NULL;
ALTER TABLE MODULE DROP ID;
RENAME COLUMN MODULE.ID_TEMP TO ID;

But after I applied it, I started to get this exception:
Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED type of inserted column[0] = org.apache.derby.iapi.types.CollatorSQLVarchartype of template column[0] = org.apache.derby.iapi.types.SQLVarchar
at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)
at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)
at org.apache.derby.impl.store.access.btree.OpenBTree.isIndexableRowConsistent(OpenBTree.java:519)
at org.apache.derby.impl.store.access.btree.BTreeController.doIns(BTreeController.java:679)
at org.apache.derby.impl.store.access.btree.BTreeController.insert(BTreeController.java:1372)
at org.apache.derby.impl.store.access.btree.index.B2IController.insert(B2IController.java:210)
at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(IndexChanger.java:565)
at org.apache.derby.impl.sql.execute.IndexChanger.doInsert(IndexChanger.java:393)
at org.apache.derby.impl.sql.execute.IndexChanger.insert(IndexChanger.java:713)
at org.apache.derby.impl.sql.execute.IndexSetChanger.insert(IndexSetChanger.java:268)
at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(RowChangerImpl.java:458)
at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:881)
at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:452)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:473)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:352)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1340)
... 30 more

I attached Test.groovy class which shows this issue.

also I found this workaround:
we need to drop all indexes and create them again, after we applied this pk column update.

Issue Links

• is related to: DERBY-2537 implement pushing collation info to store, storing collation info in store metadata, and creating templates based on store metadata