You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2006/11/15 01:14:16 UTC
svn commit: r475081 - in /db/derby/code/branches/10.2/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bpendleton
Date: Tue Nov 14 16:14:16 2006
New Revision: 475081
URL: http://svn.apache.org/viewvc?view=rev&rev=475081
Log:
DERBY-1495: Error modifying an identity column after altering the column
DERBY-1645: ALTER TABLE SET INCREMENT turns off "Generated By Default"
Merged the fix from the trunk by svn merge -r 474501:474502 ../trunk/
Modified:
db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?view=diff&rev=475081&r1=475080&r2=475081
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java Tue Nov 14 16:14:16 2006
@@ -43,6 +43,7 @@
import org.apache.derby.impl.sql.execute.ColumnInfo;
import org.apache.derby.catalog.TypeDescriptor;
import org.apache.derby.catalog.UUID;
+import org.apache.derby.catalog.types.DefaultInfoImpl;
/**
* A ModifyColumnNode represents a modify column in an ALTER TABLE statement.
@@ -285,6 +286,23 @@
{
return;
}
+
+ // If the statement is not setting the column's default, then
+ // recover the old default and re-use it. If the statement is
+ // changing the start value for the auto-increment, then recover
+ // the old increment-by value and re-use it. If the statement is
+ // changing the increment-by value, then recover the old start value
+ // and re-use it. This way, the column alteration only changes the
+ // aspects of the autoincrement settings that it intends to change,
+ // and does not lose the other aspecs.
+ if (defaultNode == null)
+ defaultInfo = (DefaultInfoImpl)cd.getDefaultInfo();
+ if (autoinc_create_or_modify_Start_Increment ==
+ ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE)
+ autoincrementIncrement = cd.getAutoincInc();
+ if (autoinc_create_or_modify_Start_Increment ==
+ ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE)
+ autoincrementStart = cd.getAutoincStart();
/* Fill in the DataTypeServices from the DataDictionary */
dataTypeServices = cd.getType();
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out?view=diff&rev=475081&r1=475080&r2=475081
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out Tue Nov 14 16:14:16 2006
@@ -1855,4 +1855,78 @@
ij(CONN2)> select * from newTable2;
C11 |RESTART
-----------------------
+ij(CONN2)> -- Verify that if we change the START WITH value for a GENERATED_BY_DEFAULT
+-- column, the column is still GENERATED_BY_DEFAULT and its INCREMENT BY
+-- value is preserved
+CREATE TABLE DERBY_1495 (
+ id INT GENERATED BY DEFAULT AS IDENTITY
+ (START WITH 1, INCREMENT BY 1) NOT NULL
+ ,col2 INT NOT NULL);
+0 rows inserted/updated/deleted
+ij(CONN2)> SELECT col.columndefault, col.columndefaultid,
+ col.autoincrementvalue, col.autoincrementstart,
+ col.autoincrementinc
+ FROM sys.syscolumns col
+ INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+ WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID';
+COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC
+-------------------------------------------------------------------------------------------------------------------
+GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|1 |1 |1
+ij(CONN2)> -- Insert using an explicit value on the ID-field
+INSERT INTO DERBY_1495(ID, COL2) VALUES(2, 2);
+1 row inserted/updated/deleted
+ij(CONN2)> -- Reset the identity field
+ALTER TABLE DERBY_1495 ALTER COLUMN id RESTART WITH 3;
+0 rows inserted/updated/deleted
+ij(CONN2)> SELECT col.columndefault, col.columndefaultid,
+ col.autoincrementvalue, col.autoincrementstart,
+ col.autoincrementinc
+ FROM sys.syscolumns col
+ INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+ WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID';
+COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC
+-------------------------------------------------------------------------------------------------------------------
+GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|3 |3 |1
+ij(CONN2)> INSERT INTO DERBY_1495(ID, COL2) VALUES(4, 4);
+1 row inserted/updated/deleted
+ij(CONN2)> INSERT INTO DERBY_1495(COL2) VALUES(4);
+1 row inserted/updated/deleted
+ij(CONN2)> -- Similarly, verify that if we change the INCREMENT BY value for a
+-- GENERATED_BY_DEFAULT column, the column remains GENERATED_BY_DEFAULT
+-- and its START WITH value is preserved.
+create table derby_1645 (
+ TableId INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+ StringValue VARCHAR(20) not null,
+ constraint PK_derby_1645 primary key (TableId));
+0 rows inserted/updated/deleted
+ij(CONN2)> SELECT col.columndefault, col.columndefaultid,
+ col.autoincrementvalue, col.autoincrementstart,
+ col.autoincrementinc
+ FROM sys.syscolumns col
+ INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+ WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID';
+COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC
+-------------------------------------------------------------------------------------------------------------------
+GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|1 |1 |1
+ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (1, 'test1');
+1 row inserted/updated/deleted
+ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (2, 'test2');
+1 row inserted/updated/deleted
+ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (3, 'test3');
+1 row inserted/updated/deleted
+ij(CONN2)> ALTER TABLE derby_1645 ALTER TableId SET INCREMENT BY 50;
+0 rows inserted/updated/deleted
+ij(CONN2)> SELECT col.columndefault, col.columndefaultid,
+ col.autoincrementvalue, col.autoincrementstart,
+ col.autoincrementinc
+ FROM sys.syscolumns col
+ INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+ WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID';
+COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC
+-------------------------------------------------------------------------------------------------------------------
+GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|53 |1 |50
+ij(CONN2)> INSERT INTO derby_1645 (StringValue) VALUES ('test53');
+1 row inserted/updated/deleted
+ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (-999, 'test3');
+1 row inserted/updated/deleted
ij(CONN2)>
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql?view=diff&rev=475081&r1=475080&r2=475081
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql Tue Nov 14 16:14:16 2006
@@ -964,3 +964,66 @@
create table newTable2 (c11 int);
alter table newTable2 add column RESTART int;
select * from newTable2;
+
+-- Verify that if we change the START WITH value for a GENERATED_BY_DEFAULT
+-- column, the column is still GENERATED_BY_DEFAULT and its INCREMENT BY
+-- value is preserved
+CREATE TABLE DERBY_1495 (
+ id INT GENERATED BY DEFAULT AS IDENTITY
+ (START WITH 1, INCREMENT BY 1) NOT NULL
+ ,col2 INT NOT NULL);
+
+SELECT col.columndefault, col.columndefaultid,
+ col.autoincrementvalue, col.autoincrementstart,
+ col.autoincrementinc
+ FROM sys.syscolumns col
+ INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+ WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID';
+
+-- Insert using an explicit value on the ID-field
+INSERT INTO DERBY_1495(ID, COL2) VALUES(2, 2);
+
+-- Reset the identity field
+ALTER TABLE DERBY_1495 ALTER COLUMN id RESTART WITH 3;
+
+SELECT col.columndefault, col.columndefaultid,
+ col.autoincrementvalue, col.autoincrementstart,
+ col.autoincrementinc
+ FROM sys.syscolumns col
+ INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+ WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID';
+
+INSERT INTO DERBY_1495(ID, COL2) VALUES(4, 4);
+INSERT INTO DERBY_1495(COL2) VALUES(4);
+
+-- Similarly, verify that if we change the INCREMENT BY value for a
+-- GENERATED_BY_DEFAULT column, the column remains GENERATED_BY_DEFAULT
+-- and its START WITH value is preserved.
+create table derby_1645 (
+ TableId INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+ StringValue VARCHAR(20) not null,
+ constraint PK_derby_1645 primary key (TableId));
+
+SELECT col.columndefault, col.columndefaultid,
+ col.autoincrementvalue, col.autoincrementstart,
+ col.autoincrementinc
+ FROM sys.syscolumns col
+ INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+ WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID';
+
+INSERT INTO derby_1645 (TableId, StringValue) VALUES (1, 'test1');
+INSERT INTO derby_1645 (TableId, StringValue) VALUES (2, 'test2');
+INSERT INTO derby_1645 (TableId, StringValue) VALUES (3, 'test3');
+
+ALTER TABLE derby_1645 ALTER TableId SET INCREMENT BY 50;
+
+SELECT col.columndefault, col.columndefaultid,
+ col.autoincrementvalue, col.autoincrementstart,
+ col.autoincrementinc
+ FROM sys.syscolumns col
+ INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+ WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID';
+
+INSERT INTO derby_1645 (StringValue) VALUES ('test53');
+INSERT INTO derby_1645 (TableId, StringValue) VALUES (-999, 'test3');
+