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/13 21:36:51 UTC
svn commit: r474502 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bpendleton
Date: Mon Nov 13 12:36:51 2006
New Revision: 474502
URL: http://svn.apache.org/viewvc?view=rev&rev=474502
Log:
DERBY-1495: Error modifying an identity column after altering the column
DERBY-1645: ALTER TABLE SET INCREMENT turns off "Generated By Default"
This patch changes ModifyColumnNode.bindAndValidateDefault so that it
detects the case(s) where the user is altering aspects of an identity column,
and ensures that the other aspects of that identity column are preserved and
not lost. The crucial issue is that if the column is Generated By Default,
then the DefaultInfoImpl column in the SYSCOLUMNS table needs to get
retained when the user uses ALTER TABLE to change either the start value
or the increment value; otherwise the behavior effectively switches from
Generated By Default to Generated Always.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?view=diff&rev=474502&r1=474501&r2=474502
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java Mon Nov 13 12:36:51 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.
@@ -287,6 +288,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/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out?view=diff&rev=474502&r1=474501&r2=474502
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out Mon Nov 13 12:36:51 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql?view=diff&rev=474502&r1=474501&r2=474502
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql Mon Nov 13 12:36:51 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');
+