ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default" identity column constraint

• Key: DERBY-1645
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Bryan Pendleton
• Reporter: Alan Baldwin
• Component/s: SQL
• Affects Version/s: 10.1.3.1
• Fix Version/s: 10.2.2.0, 10.3.1.4

Description

I have a table which has an auto-generated key:

create table MyTable (
   TableId INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   StringValue VARCHAR(20) not null,
   constraint PK_MyTable primary key (TableId)
)

I verify that GENERATED BY DEFAULT is set:

SELECT * FROM
sys.syscolumns col
INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
WHERE tab.tableName = 'MYTABLE' AND ColumnName = 'TABLEID'

I'm pulling in data for which I need to preserve the ID's:

INSERT INTO MYTABLE (TableId, StringValue) VALUES (1, 'test1')
INSERT INTO MYTABLE (TableId, StringValue) VALUES (2, 'test2')
INSERT INTO MYTABLE (TableId, StringValue) VALUES (3, 'test3')

In the absense of the Derby 10.2 feature (ALTER TABLE WITH RESTART X), I try to just change the INCREMENT BY value and insert a row so that I can reset the "next" key value:

ALTER TABLE MyTable ALTER TableId SET INCREMENT BY 50

Then I insert a "dummy" record (which I will delete later...) to move the key upwards:

INSERT INTO MYTABLE (StringValue) VALUES ('test53')

However, I can now no longer insert explicit values into the primary key like this:

INSERT INTO MYTABLE (TableId, StringValue) VALUES (-999, 'test3')

I get this error: SQL Exception: Attempt to modify an identity column 'TABLEID'.

Upon checking the sys.syscolumns table again, it verifies that the table no longer has an auto-generated key, but the TableId is still an identity column.

Comments

[Saurabh Vyas]

I have investigated on this issue and found out that when we create a table with 'GENERATED BY DEFAULT AS IDENTITY' , the 'columnDefaultInfo' attribute for ColumnDescriptor gets set to GENERATED_BY_DEFAULT. When we alter the table and set a new increment by:

 - ALTER TABLE MyTable ALTER TableId SET INCREMENT BY 50
we override the value for 'columnDefaultInfo' and set it to 'null'. as 'GENERATED BY DEFAULT....' clause is not allowed here. Now when we try to insert a row with specifying value of 'TableId' as :

 - INSERT INTO MYTABLE (TableId, StringValue) VALUES (-999, 'test3')
the ResultColumnList.checkAutoincrement() throws an exception

if ((sourceRC != null) &&
    (sourceRC.isAutoincrementGenerated()))
    {
          sourceRC.setColumnDescriptor(cd.getTableDescriptor(), cd);
    }else{
          if(cd.isAutoincAlways()) // <----- SEE HERE !!
              throw StandardException.newException(SQLState.LANG_AI_CANNOT_MODIFY_AI,
                  rc.getName());
    }

Here, isAutoincAlways() is called for the ColumnDescriptor 'cd' for TableId :

public boolean isAutoincAlways(){
        return (columnDefaultInfo == null) && isAutoincrement();
    }

it returns true as 'columnDefaultInfo' is now 'null' and hence the exception.

For this case to work correctly, ALTER TABLE should change the INCREMENT BY clause without setting the 'columnDefaultInfo' to 'null'. Correct me if I am wrong or if I am missing anything ??

Comments / Suggestions please.

Thanks in advance,
Saurabh

[Bryan Pendleton]

Possible duplicate of DERBY-1495?

[Alan Baldwin]

They are definitely closely related. Same symptom, different version of Derby. 1495 was logged against 10.1.3, 1645 was logged against 10.2. Derby 10.2 introduced the "ALTER TABLE WITH RESTART X" clause, and 10.1.3 did not have this. That is the only real difference.

Disclaimer: I'm not familiar with the code base, but I'm guessing that one fix may solve both issues.

[Saurabh Vyas]

I further investigated on this and following is the summary for that :
 - when alter table is executed, ColumnDefinitionNode.validateDefault(DataDictionary dd, TableDescriptor td) get called.void
- - - - - - - - - - -
validateDefault(DataDictionary dd, TableDescriptor td)
throws StandardException
{

if (defaultNode == null ) // <--- See here
return;

//Examin whether default value is autoincrement.
if (isAutoincrement){
defaultInfo = createDefaultInfoOfAutoInc();
return;
}
                ........................
                ........................
- - - - - - - - - -

While the alter table statement does not support 'GENERATED BY' clause, the ModifiedColumnNode which gets created by the alter statement has defaultNode=null. Whereas for the original tree (before alter table), the defaultNode is not null and its value is available to ColumnDefinitionNode.validateDefault(DataDictionary dd, TableDescriptor td) in 'td' but is not used in the current code.

Thus leaving defaultInfo unset and hence throws exception in ResultColumnList.checkAutoincrement() while checking for cd.isAutoincAlways()

I tried to populate the defaultInfo for the alter table case as follows :

void validateDefault(DataDictionary dd, TableDescriptor td)
throws StandardException
{
//Check for defalutInfo from the exisiting TableData td
//and set defaultInfo
if (defaultNode == null ) {
ColumnDescriptorList cdl = td.getColumnDescriptorList();
ColumnDescriptor cd = cdl.getColumnDescriptor(td.getUUID(), this.getColumnName());

// Get the defaultInfo for the particular column from the exixiting values itself
// and set it for the modified column.
if (cd != null)
defaultInfo = (DefaultInfoImpl)cd.getDefaultInfo();
return;
}

//Examin whether default value is autoincrement.
if (isAutoincrement){
defaultInfo = createDefaultInfoOfAutoInc();
return;
}
                ................
                ................
This works fine for the following : (For the same table MYTABLE)
 - ALTER TABLE MyTable ALTER TableId SET INCREMENT BY 50
 - INSERT INTO MYTABLE (TableId, StringValue) VALUES (123, 'NewTest')

Well I could not provide a patch as derbyall was failing for lang/autoincrement.sql , it seems some where else it breaks the regression. I am not too clear with the sceniro. Correct me if I am wrong or if I am missing anything ??

Comments / Suggestions please.

- Saurabh

[Kristian Waagan]

Saurabh, your analysis seem to be identical to what
I reported in DERBY-1495. I also ran into trouble when trying to fix the problem.
In my case, Derby failed when clearing dependencies for the table when I dropped it.

[Bryan Pendleton]

I have attached a proposed patch to DERBY-1495 which also addresses this problem, I believe.

The proposed patch is slightly different from that suggested by Saurabh; I modified the
bindAndValidateDefault method in ModifyColumnNode to populate the defaultInfo as needed.

[Bryan Pendleton]

I've committed the patch attached to DERBY-1495 to subversion
as revision 474502. DERBY-1495 and DERBY-1645 are not
precisely speaking duplicates, but as was noted in the comments above,
a single fix resolves both problems. The patch changes the ALTER TABLE
handling so that the parts of the identify column that you aren't altering
are preserved and not incorrectly reset.

[Alan Baldwin]

Since DERBY-1495 was backported to version 10.2, can we assume that this issue was as well?

[Bryan Pendleton]

Thanks Alan for catching this. Yes, this fix is now in the 10.2 branch as well. Updated Fix Version to reflect this.

[Kristian Waagan]

Yes, since the patch is a fix for both issues, you should not see the bug with the head of the 10.2 branch anymore (or with trunk).
I assume the Fix Version field will be updated properly.

Note that to obtain the fix, you will have to build Derby yourself until a new release is out.
If you try it out, we would appreciate a confirmation that the patch fixes the problem you saw.

[Andrew McIntyre]

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