[
http://issues.apache.org/jira/browse/DERBY-1645?page=comments#action_12443455 ]
Saurabh Vyas commented on DERBY-1645:
-------------------------------------
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
> ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default"
> identity column constraint
> -----------------------------------------------------------------------------------------------------
>
> Key: DERBY-1645
> URL: http://issues.apache.org/jira/browse/DERBY-1645
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1
> Environment: Both Ubuntu Linux, Windows XP... Java 1.4.2_x and Java
> 1.5
> Reporter: Alan Baldwin
>
> 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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
