db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6890) INSERT error after PK altered to GENERATED
Date Sun, 05 Jun 2016 18:12:59 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6890?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15315971#comment-15315971
] 

Bryan Pendleton commented on DERBY-6890:
----------------------------------------

OK, I think I see the problem now, though I don't yet know how to fix it.

The test performs an ALTER TABLE DROP COLUMN ID statement. Before
that statement, the Module table has columns (ID, TITLE, ID_TEMP).
After that statement, the Module table will have columns (TITLE, ID_TEMP).

Near the end of ALTER TABLE DROP COLUMN ID, we are going to
rebuild all the indexes on table Module. At this point, since we are dropping the
primary key column ID, we are also discarding the system-generated index
for the primary key, so there is only 1 index on table Module, namely the
Module_title index on Module(title).

This index processing sets up a sorter for each remaining index, in
preparation to scanning the base table and reloading each index. This
work is done by AlterTableConstantAction.setUpAllSorts().

But the data structures at this point are confused. The table descriptor
(td.getColumnDescriptorList) is still showing (ID,TITLE,ID_TEMP), but
the collation ids are expecting to work with the columns (TITLE,ID_TEMP).
When we go to set up the sorter for the Module_title index, we look at
the information about the first column, expecting that to be the information
for the TITLE column, but instead the column we get from the column
descriptor list is the first column in the **old** table, which is the ID column,
and hence we rebuild the Module_title index using the collation information
from the ID column, rather than the TITLE column.

Since ID is a BIGINT, not a string, it has no collation information, so the
new index that we build says that the first column has no collation
information, but then when the test later tries to update the index, the
store (rightfully) complains because column TITLE is a string column
and so it **should** have had collation information.

So either, at this point, we should be using collation information that
is still based off the original table column description list, or we should
be referencing the new (post-DROP COLUMN) column descriptor list.

We call

> INSERT error after PK altered to GENERATED
> ------------------------------------------
>
>                 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
>         Attachments: CollationTest.diff, Test.groovy, Test.java, testRepro.diff
>
>
> 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)

Mime
View raw message