Attempt to modify an identity column error after resetting identity column

• Key: DERBY-1495
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Bryan Pendleton
• Reporter: Lars Gråmark
• Component/s: SQL
• Affects Version/s: 10.2.1.6
• Fix Version/s: 10.2.2.0, 10.3.1.4

Description

When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
The following statements will reproduce the error.

CREATE TABLE MYTABLE (
  id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
 ,col2 INT NOT NULL
)

-- Insert using an explicit value on the ID-field
INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)

-- Reset the identity field
ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3

-- Try inserting another explicit value and the error below is thrown.
INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)

Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000

-- Although, this works fine
INSERT INTO MYTABLE(COL2) VALUES(4)

Comments

[Suresh Thalamati]

I came across this issue while doing a buddy testing for alter table with RESTART . It is really irritating for an user to try alter table with restart on GENERATED BY DEFAULT COLUMN and find , he/she can not insert values manually. I think , this bug should get fixed for 10.2 , if possible .

[Jimisola Laursen]

Seems as if a lot of people have came across this issue. It was reported more than two months ago and still no action - not even a comment from the developer team.

This issue has minor priority, but it should be considered MAJOR. We currently have to copy the Derby database back-and-forth due to this issue.
I sincerely hope that this will get into 10.2. Especially, since Derby 10.2 will be the JavaDB shipped with the JDK (if am not mistaking).

[Kristian Waagan]

I'm taking a brief look at this. If someone with more knowledge wants to help, or fix the bug, let me know :)

Note that the same problem occur if you try to change the increment of the identity column.

[Bryan Pendleton]

Thanks Kristian for investigating this problem. My hunch is that all the fun happens
in AlterTableConstantAction.modifyColumnDefault. The ALTER TABLE statement
doesn't provide all the information about the column's auto-increment behaviors so
the modifyColumnDefault() method has to merge the new behaviors specified on
the ALTER TABLE statement with the existing behaviors that the column already has.

My guess is that the problem lies in this merging process; the modifyColumnDefault()
method is not being careful enough to use the previous column behaviors in those
cases where the ALTER TABLE statement has not provided new behaviors.

Hope this helps.

[Alan Baldwin]

Note:
The same thing is happening in Derby 10.1.x when you try to change Set Increment By. Please reference issue: http://issues.apache.org/jira/browse/DERBY-1645.

There is no With Restart command in 10.1.x, but I assume the behavior is related.

Like the previous poster, I also had to jump through some hoops to get around this issue.

[Kristian Waagan]

Thanks Bryan.

I have been able to determine what seems to be causing the problem, but have not yet understood how to fix it. This is my first venture into the this area of the code, so things are very unclear to me.

First, after studying the various variables describing an identity column, I noticed that if the DefaultInfo assoiciated with an identity column is null, the column is said to be "generated always as identity". In effect, the column definition is changed after the restart/increment operation, because the defaultInfo is set to null. This can be observed with the new describe command in ij.

Bryan's guess seems to be valid, but if I add the old DefaultInfoImpl instance to the new one, I get a NPE when doing a drop on the table the next time I boot the database. It happens for 'BasicDependencyManager.clearDependencies'. It also happens if I create a new instance (new DefaultInfoImpl(true,null,null)) and add it to the newly created ColumnDescriptor. This piece of the code is in AlterTableConstantAction.modifyColumnDefault, as Bryan mentioned.

Is the error in this method, or does it happen earlier so the input is not as expected?
What step have I forgotten to do?

I'll keep looking into this, but any piece of further advice is appreciated!

I'm going on vacation for a week, so I won't be doing anything on this issue for a while.
Anyone should feel free to take over ;)
The problem should be easy to find if you follow the hint Bryan gave in a comment above.
I don't have a preliminary patch I can post at this time, but what I found out is described in the comment above.

[Bryan Pendleton]

Possible duplicate of DERBY-1645?

[Kristian Waagan]

Although the problem summaries are a bit different, these two issues seem to be caused by the same bug in Derby.

[Bryan Pendleton]

Attached is preserveColDefault_v1.diff, a proposed patch to solve this problem,
as well as to solve DERBY-1645, which is closely related.

Kristian, I was not able to reproduce the problems that you had in the
dependency system when dropping the table. Perhaps you could experiment
with this patch and see if you can reproduce those problems?

[Kristian Waagan]

I have tested/reviewed the patch, and was unable to find any problems. More specifically, I did not see the error I saw when trying to fix the bug. Although my approach were pretty much the same, I suspect I might have done what Bryan did somewhere else in the code path (and in that way made something go wrong).

I ran derbyall and suites.All with both Java SE 5 and 6. Only saw three known problems in derbyall (Compatibility and VerifySignatures).

Patch is small and clean, everything seems to work and testcases have been added.
+1 to commit

[Bryan Pendleton]

Kristian, thank you very much for the review and the testing.

I committed the patch to subversion as revision 474502.

[Rajesh Kartha]

Seems like a useful fix for the 10.2 branch also, is any committer planning to port the fix to 10.2 ?

-Rajesh

[Bryan Pendleton]

Hi Rajesh, I think that is a good suggestion. I will investigate merging this fix to 10.2.

[Jimisola Laursen]

Backporting the fix to 10.2 would be highly appreciated.

[Bryan Pendleton]

The merge to 10.2 was clean, and my testing was successful. Submitted the merged
change to the 10.2 branch as revision 475081

[Kristian Waagan]

Should the Fix Version be updated to reflect that the fix is in the 10.2 branch as well?
If so, this goes for the related Jira issue too.

[Bryan Pendleton]

Adding 10.2.1.8 Fix Version to reflect the merging of this fix to 10.2 branch. Thanks Kristian for catching this.

[Andrew McIntyre]

This issue has been resolved for over a year with no further movement. Closing.