[ https://issues.apache.org/jira/browse/DERBY-6890?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton updated DERBY-6890:
-----------------------------------
    Attachment: fixIndexCollation.diff

Attached 'fixIndexCollation.diff' is a patch proposal.

It passes both AlterTableTest and CollationTest, as well as
the reproduction script (which is included as a new test in
CollationTest by this patch).

I'm running a more complete set of tests to see what else pops out.

The idea of the fix is as described before, to move the collation
id computation for the indexes from setUpAllSorts into getAffectedIndexes,
with the additional complexity that if an index includes the column
which is being dropped, we must (carefully) omit that column from
the collation id array that we build. This fixup is now performed at
the same point in the code where we fixup the column position data,
which seems more appropriate than where it was before this patch.

This is complex, subtle code. But maybe this patch is viable.

> ALTER TABLE DROP COLUMN corrupts secondary index collation information
> ----------------------------------------------------------------------
>
>                 Key: DERBY-6890
>                 URL: https://issues.apache.org/jira/browse/DERBY-6890
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.12.1.1
>         Environment: Mac OS X 10.11.5
> JDK: Oracle 1.8.0_92
>            Reporter: Andrei Koiro
>            Assignee: Bryan Pendleton
>         Attachments: CollationTest.diff, Test.groovy, Test.java, doesntPassTests.diff, fixIndexCollation.diff, testRepro.diff
>
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)