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 2007/11/15 20:44:17 UTC
svn commit: r595408 - in /db/derby/code/branches/10.3/java:
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/catalog/
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/master/ testing/o...
Author: bpendleton
Date: Thu Nov 15 11:44:15 2007
New Revision: 595408
URL: http://svn.apache.org/viewvc?rev=595408&view=rev
Log:
DERBY-3175: NullPointerException on INSERT after ALTER TABLE DROP COLUMN
Merged change from the trunk via svn merge -r 594726:594727
One conflict resolved in SYSCOLUMNSRowFactory.java.
Modified:
db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java?rev=595408&r1=595407&r2=595408&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java Thu Nov 15 11:44:15 2007
@@ -69,6 +69,7 @@
private UUID defaultUUID;
private long autoincStart;
private long autoincInc;
+ private long autoincValue;
//Following variable is used to see if the user is adding an autoincrement
//column, or if user is altering the existing autoincrement column to change
//the increment value or to change the start value. If none of the above,
@@ -154,6 +155,7 @@
columnDefaultInfo);
this.autoincStart = autoincStart;
+ this.autoincValue = autoincStart;
this.autoincInc = autoincInc;
}
@@ -176,13 +178,14 @@
* @param defaultUUID The UUID for the default, if any.
* @param autoincStart Start value for an autoincrement column.
* @param autoincInc Increment for autoincrement column
+ * @param autoincValue Current value of the autoincrement column
*/
public ColumnDescriptor(String columnName, int columnPosition,
DataTypeDescriptor columnType, DataValueDescriptor columnDefault,
DefaultInfo columnDefaultInfo,
UUID uuid,
UUID defaultUUID,
- long autoincStart, long autoincInc)
+ long autoincStart, long autoincInc, long autoincValue)
{
this.columnName = columnName;
@@ -198,6 +201,7 @@
columnDefaultInfo);
this.autoincStart = autoincStart;
+ this.autoincValue = autoincValue;
this.autoincInc = autoincInc;
}
@@ -380,9 +384,32 @@
return autoincInc;
}
+ /**
+ * Get the current value for an autoincrement column.
+ *
+ * One case in which this is used involves dropping a column
+ * from a table. When ALTER TABLE DROP COLUMN runs, it drops
+ * the column from SYSCOLUMNS, and then must adjust the
+ * column positions of the other subsequent columns in the table
+ * to account for the removal of the dropped columns. This
+ * involves deleting and re-adding the column descriptors to
+ * SYSCOLUMNS, but during that process we must be careful to
+ * preserve the current value of any autoincrement column.
+ *
+ * @return the current value for an autoincrement column
+ */
+ public long getAutoincValue()
+ {
+ return autoincValue;
+ }
+
public long getAutoinc_create_or_modify_Start_Increment()
{
return autoinc_create_or_modify_Start_Increment;
+ }
+ public void setAutoinc_create_or_modify_Start_Increment(int c_or_m)
+ {
+ autoinc_create_or_modify_Start_Increment = c_or_m;
}
/**
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=595408&r1=595407&r2=595408&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Thu Nov 15 11:44:15 2007
@@ -3407,7 +3407,7 @@
(DataValueDescriptor)parameterDefaults[index],
(DefaultInfo) null,
uuid,
- (UUID) null, 0, 0);
+ (UUID) null, 0, 0, 0);
addDescriptor(cd, null, SYSCOLUMNS_CATALOG_NUM,
false, // no chance of duplicates here
@@ -3591,7 +3591,7 @@
(DefaultInfo) null,
uuid,
(UUID) null,
- 0, 0);
+ 0, 0, 0);
updateColumnDescriptor(cd,
cd.getReferencingUUID(),
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java?rev=595408&r1=595407&r2=595408&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java Thu Nov 15 11:44:15 2007
@@ -192,6 +192,7 @@
Object defaultSerializable = null;
long autoincStart = 0;
long autoincInc = 0;
+ long autoincValue = 0;
//The SYSCOLUMNS table's autoinc related columns change with different
//values depending on what happened to the autoinc column, ie is the
//user adding an autoincrement column, or is user changing the existing
@@ -212,6 +213,7 @@
colID = new Integer(column.getPosition() );
autoincStart = column.getAutoincStart();
autoincInc = column.getAutoincInc();
+ autoincValue = column.getAutoincValue();
autoinc_create_or_modify_Start_Increment = column.getAutoinc_create_or_modify_Start_Increment();
if (column.getDefaultInfo() != null)
{
@@ -260,8 +262,15 @@
if (autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.CREATE_AUTOINCREMENT ||
autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE)
{//user is adding an autoinc column or is changing the increment value of autoinc column
+ // This code also gets run when ALTER TABLE DROP COLUMN
+ // is used to drop a column other than the autoinc
+ // column, and the autoinc column gets removed from
+ // SYSCOLUMNS and immediately re-added with a different
+ // column position (to account for the dropped column).
+ // In this case, the autoincValue may have a
+ // different value than the autoincStart.
row.setColumn(SYSCOLUMNS_AUTOINCREMENTVALUE,
- new SQLLongint(autoincStart));
+ new SQLLongint(autoincValue));
row.setColumn(SYSCOLUMNS_AUTOINCREMENTSTART,
new SQLLongint(autoincStart));
row.setColumn(SYSCOLUMNS_AUTOINCREMENTINC,
@@ -360,7 +369,7 @@
UUID defaultUUID = null;
UUID uuid = null;
UUIDFactory uuidFactory = getUUIDFactory();
- long autoincStart, autoincInc;
+ long autoincStart, autoincInc, autoincValue;
DataDescriptorGenerator ddg = dd.getDataDescriptorGenerator();
@@ -445,7 +454,8 @@
DataTypeDescriptor dataTypeServices = new DataTypeDescriptor(typeDescriptor,
wrapperTypeId);
- /* 7th column is AUTOINCREMENTVALUE, not cached in descriptor (long) */
+ /* 7th column is AUTOINCREMENTVALUE (long) */
+ autoincValue = row.getColumn(SYSCOLUMNS_AUTOINCREMENTVALUE).getLong();
/* 8th column is AUTOINCREMENTSTART (long) */
autoincStart = row.getColumn(SYSCOLUMNS_AUTOINCREMENTSTART).getLong();
@@ -461,7 +471,8 @@
colDesc = new ColumnDescriptor(columnName, columnNumber,
dataTypeServices, defaultValue, defaultInfo, uuid,
- defaultUUID, autoincStart, autoincInc);
+ defaultUUID, autoincStart, autoincInc,
+ autoincValue);
return colDesc;
}
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=595408&r1=595407&r2=595408&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Thu Nov 15 11:44:15 2007
@@ -79,6 +79,7 @@
import org.apache.derby.iapi.types.RowLocation;
import org.apache.derby.iapi.types.StringDataValue;
import org.apache.derby.impl.sql.catalog.DDColumnDependableFinder;
+import org.apache.derby.impl.sql.compile.ColumnDefinitionNode;
/**
* This class describes actions that are ALWAYS performed for an
@@ -931,11 +932,22 @@
ColumnDescriptor[] cdlArray =
new ColumnDescriptor[size - columnDescriptor.getPosition()];
+ // For each column in this table with a higher column position,
+ // drop the entry from SYSCOLUMNS, but hold on to the column
+ // descriptor and reset its position to adjust for the dropped
+ // column. Then, re-add all those adjusted column descriptors
+ // back to SYSCOLUMNS
+ //
for (int i = columnDescriptor.getPosition(), j = 0; i < size; i++, j++)
{
ColumnDescriptor cd = (ColumnDescriptor) tab_cdl.elementAt(i);
dd.dropColumnDescriptor(td.getUUID(), cd.getColumnName(), tc);
cd.setPosition(i);
+ if (cd.isAutoincrement())
+ {
+ cd.setAutoinc_create_or_modify_Start_Increment(
+ ColumnDefinitionNode.CREATE_AUTOINCREMENT);
+ }
cdlArray[j] = cd;
}
dd.addDescriptorArray(cdlArray, td,
Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?rev=595408&r1=595407&r2=595408&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out Thu Nov 15 11:44:15 2007
@@ -1642,6 +1642,224 @@
USER2 |s|{0, 1}
SUPER_USER |u|{1}
USER1 |u|{0}
+ij> -- JIRA 3175: Null Pointer Exception or SanityManager ASSERT because
+-- autoincrement properties of generated column are not maintained properly
+-- when a column before it in the table is dropped:
+create table d3175 (x varchar(12), y varchar(12),
+ id int primary key generated by default as identity);
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and t.tablename='D3175';
+REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|ID |3 |INTEGER NOT NU&|GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R
+xxxxFILTERED-UUIDxxxx|X |1 |VARCHAR(12) |NULL |NULL |NULL |NULL |NULL |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R
+xxxxFILTERED-UUIDxxxx|Y |2 |VARCHAR(12) |NULL |NULL |NULL |NULL |NULL |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R
+ij> insert into d3175(x) values 'b';
+1 row inserted/updated/deleted
+ij> alter table d3175 drop column y;
+0 rows inserted/updated/deleted
+ij> insert into d3175(x) values 'a';
+1 row inserted/updated/deleted
+ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and t.tablename='D3175';
+REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|ID |2 |INTEGER NOT NU&|GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|3 |1 |1 |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R
+xxxxFILTERED-UUIDxxxx|X |1 |VARCHAR(12) |NULL |NULL |NULL |NULL |NULL |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R
+ij> -- JIRA 3177 appears to be aduplicate of JIRA 3175, but the reproduction
+-- test script is different. In the interests of additional testing, we
+-- include the JIRA 3177 test script, as it has a number of additional
+-- examples of interesting ALTER TABLE statements
+--
+-- In the original JIRA 3177 bug, by the time we get to the end of the
+-- ALTER TABLE processing, the select from SYS.SYSCOLUMNS retrieves NULL
+-- for the autoinc columns, instead of the correct value (1).
+create table d3177_SchemaVersion ( version INTEGER NOT NULL );
+0 rows inserted/updated/deleted
+ij> insert into d3177_SchemaVersion (version) values (0);
+1 row inserted/updated/deleted
+ij> create table d3177_BinaryData (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ CRC32 BIGINT NOT NULL ,
+ data BLOB NOT NULL ,
+ CONSTRAINT d3177_BinaryData_id_pk PRIMARY KEY(id)
+);
+0 rows inserted/updated/deleted
+ij> create table d3177_MailServers (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ port INTEGER NOT NULL ,
+ username varchar(80) NOT NULL ,
+ protocol varchar(80) NOT NULL ,
+ SSLProtocol varchar(10),
+ emailAddress varchar(80) NOT NULL ,
+ server varchar(80) NOT NULL ,
+ password varchar(80) NOT NULL ,
+ CONSTRAINT d3177_MailServers_id_pk PRIMARY KEY(id)
+);
+0 rows inserted/updated/deleted
+ij> create table d3177_Mailboxes (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ port INTEGER NOT NULL ,
+ folder varchar(80) NOT NULL ,
+ username varchar(80) NOT NULL ,
+ SSLProtocol varchar(10),
+ hostname varchar(80) NOT NULL ,
+ storeType varchar(80) NOT NULL ,
+ password varchar(80) NOT NULL ,
+ timeout INTEGER NOT NULL ,
+ MailServerID INTEGER NOT NULL ,
+ CONSTRAINT d3177_Mailboxes_id_pk PRIMARY KEY(id)
+);
+0 rows inserted/updated/deleted
+ij> create table d3177_MESSAGES (
+ Message_From varchar(1000),
+ Message_Cc varchar(1000),
+ Message_Subject varchar(1000),
+ Message_ID varchar(256) NOT NULL ,
+ Message_Bcc varchar(1000),
+ Message_Date TIMESTAMP,
+ Content_Type varchar(256),
+ MailboxID INTEGER NOT NULL ,
+ Search_Text CLOB NOT NULL ,
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ Message_To varchar(1000),
+ Display_Text CLOB NOT NULL ,
+ Message_Data_ID INTEGER NOT NULL ,
+ CONSTRAINT d3177_MESSAGES_id_pk PRIMARY KEY(id)
+);
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES';
+REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|ID |10 |INTEGER NOT NU&|NULL |NULL |1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3177_MESSAGES |T|xxxxFILTERED-UUIDxxxx|R
+ij> create table D3177_ATTACHMENTS (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ Inline INTEGER,
+ CRC32 BIGINT NOT NULL ,
+ Attachment_Name varchar(256) NOT NULL ,
+ Attachment_File varchar(512) NOT NULL ,
+ Message_ID INTEGER NOT NULL ,
+ Content_Type varchar(256) NOT NULL ,
+ CONSTRAINT D3177_ATTACHMENTS_id_pk PRIMARY KEY(id)
+);
+0 rows inserted/updated/deleted
+ij> alter table D3177_ATTACHMENTS ADD CONSTRAINT ATTACHMENTS_Message_ID_MESSAGES_ID FOREIGN KEY ( Message_ID ) REFERENCES D3177_MESSAGES ( ID );
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES ADD CONSTRAINT MESSAGES_MailboxID_Mailboxes_ID FOREIGN KEY ( MailboxID ) REFERENCES d3177_Mailboxes ( ID );
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES ADD CONSTRAINT MESSAGES_Message_Data_ID_d3177_BinaryData_ID FOREIGN KEY ( Message_Data_ID ) REFERENCES d3177_BinaryData ( ID );
+0 rows inserted/updated/deleted
+ij> alter table d3177_Mailboxes ADD CONSTRAINT Mailboxes_MailServerID_MailServers_ID FOREIGN KEY ( MailServerID ) REFERENCES d3177_MailServers ( ID );
+0 rows inserted/updated/deleted
+ij> update d3177_SchemaVersion set version=1;
+1 row inserted/updated/deleted
+ij> alter table D3177_MESSAGES alter Message_To SET DATA TYPE varchar(10000);
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES alter Message_From SET DATA TYPE varchar(10000);
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES alter Message_Cc SET DATA TYPE varchar(10000);
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES alter Message_Bcc SET DATA TYPE varchar(10000);
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES';
+REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|ID |10 |INTEGER NOT NU&|NULL |NULL |1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3177_MESSAGES |T|xxxxFILTERED-UUIDxxxx|R
+ij> update d3177_SchemaVersion set version=2;
+1 row inserted/updated/deleted
+ij> create table D3177_MailStatistics (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ ProcessedCount INTEGER DEFAULT 0 NOT NULL ,
+ HourOfDay INTEGER NOT NULL ,
+ LastModified TIMESTAMP NOT NULL ,
+ RejectedMailCount INTEGER DEFAULT 0 NOT NULL ,
+ DayOfWeek INTEGER NOT NULL ,
+ CONSTRAINT D3177_MailStatistics_id_pk PRIMARY KEY(id)
+);
+0 rows inserted/updated/deleted
+ij> CREATE INDEX D3177_MailStatistics_HourOfDay_idx ON D3177_MailStatistics(HourOfDay);
+0 rows inserted/updated/deleted
+ij> CREATE INDEX D3177_MailStatistics_DayOfWeek_idx ON D3177_MailStatistics(DayOfWeek);
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES alter CONTENT_TYPE SET DATA TYPE varchar(256);
+0 rows inserted/updated/deleted
+ij> update d3177_SchemaVersion set version=3;
+1 row inserted/updated/deleted
+ij> alter table D3177_messages alter column Message_ID NULL;
+0 rows inserted/updated/deleted
+ij> CREATE INDEX D3177_MESSAGES_Message_ID_idx ON D3177_MESSAGES(Message_ID);
+0 rows inserted/updated/deleted
+ij> update d3177_SchemaVersion set version=4;
+1 row inserted/updated/deleted
+ij> alter table D3177_MESSAGES add filename varchar(256);
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES add CRC32 BIGINT;
+0 rows inserted/updated/deleted
+ij> select id,crc32,data from d3177_BinaryData;
+ID |CRC32 |DATA
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES';
+REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|ID |10 |INTEGER NOT NU&|NULL |NULL |1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3177_MESSAGES |T|xxxxFILTERED-UUIDxxxx|R
+ij> alter table D3177_messages alter column filename NOT NULL;
+0 rows inserted/updated/deleted
+ij> alter table D3177_messages alter column crc32 NOT NULL;
+0 rows inserted/updated/deleted
+ij> alter table D3177_messages alter column mailboxid NULL;
+0 rows inserted/updated/deleted
+ij> ALTER TABLE D3177_MESSAGES DROP CONSTRAINT MESSAGES_message_data_id_BinaryData_id;
+ERROR 42X86: ALTER TABLE failed. There is no constraint 'RENC_SCHEMA_1.MESSAGES_MESSAGE_DATA_ID_BINARYDATA_ID' on table '"RENC_SCHEMA_1"."D3177_MESSAGES"'.
+ij> alter table D3177_messages drop column message_data_id;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint MESSAGES_MESSAGE_DATA_ID_D3177_BINARYDATA_ID on table D3177_MESSAGES has been dropped.
+ij> drop table d3177_BinaryData;
+0 rows inserted/updated/deleted
+ij> update d3177_SchemaVersion set version=6;
+1 row inserted/updated/deleted
+ij> create table D3177_EmailAddresses (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ address varchar(256) NOT NULL ,
+ CONSTRAINT D3177_EmailAddresses_id_pk PRIMARY KEY(id),
+ CONSTRAINT D3177_EmailAddresses_address_uq UNIQUE(address)
+);
+0 rows inserted/updated/deleted
+ij> CREATE UNIQUE INDEX D3177_EmailAddresses_address_idx ON D3177_EmailAddresses(address);
+0 rows inserted/updated/deleted
+WARNING 01504: The new index is a duplicate of an existing index: xxxxGENERATED-IDxxxx.
+ij> create table D3177_EmailAddressesToMessages (
+ MessageID INTEGER NOT NULL ,
+ EmailAddressID INTEGER NOT NULL
+);
+0 rows inserted/updated/deleted
+ij> alter table D3177_EmailAddressesToMessages ADD CONSTRAINT EmailAddressesToMessages_MessageID_Messages_ID FOREIGN KEY ( MessageID ) REFERENCES D3177_Messages ( ID );
+0 rows inserted/updated/deleted
+ij> alter table D3177_EmailAddressesToMessages ADD CONSTRAINT EmailAddressesToMessages_EmailAddressID_EmailAddresses_ID FOREIGN KEY ( EmailAddressID ) REFERENCES D3177_EmailAddresses ( ID );
+0 rows inserted/updated/deleted
+ij> create table AuthenticationServers (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ port INTEGER NOT NULL ,
+ protocol varchar(20) NOT NULL ,
+ hostname varchar(40) NOT NULL ,
+ CONSTRAINT AuthenticationServers_id_pk PRIMARY KEY(id)
+);
+0 rows inserted/updated/deleted
+ij> alter table d3177_Mailboxes add AuthenticationServerID INTEGER;
+0 rows inserted/updated/deleted
+ij> select id,filename from D3177_messages;
+ID |FILENAME
+--------------------------------------------------------------------------------------------------------------------------------------------
+ij> alter table D3177_MESSAGES drop column message_to;
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES drop column message_cc;
+0 rows inserted/updated/deleted
+ij> alter table D3177_MESSAGES drop column message_from;
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES';
+REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |&
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|ID |8 |INTEGER NOT NU&|NULL |NULL |1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3177_MESSAGES |T|xxxxFILTERED-UUIDxxxx|R
+ij> update d3177_SchemaVersion set version=7;
+1 row inserted/updated/deleted
ij> -- JIRA 2371: ensure that a non-numeric, non-autogenerated column can
-- have its default value modified:
create table t2371 ( a varchar(10));
Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?rev=595408&r1=595407&r2=595408&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql Thu Nov 15 11:44:15 2007
@@ -928,6 +928,169 @@
select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
where c.tableid = t.tableid and t.tablename='D1909';
+-- JIRA 3175: Null Pointer Exception or SanityManager ASSERT because
+-- autoincrement properties of generated column are not maintained properly
+-- when a column before it in the table is dropped:
+
+create table d3175 (x varchar(12), y varchar(12),
+ id int primary key generated by default as identity);
+select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and t.tablename='D3175';
+insert into d3175(x) values 'b';
+alter table d3175 drop column y;
+insert into d3175(x) values 'a';
+select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and t.tablename='D3175';
+
+-- JIRA 3177 appears to be aduplicate of JIRA 3175, but the reproduction
+-- test script is different. In the interests of additional testing, we
+-- include the JIRA 3177 test script, as it has a number of additional
+-- examples of interesting ALTER TABLE statements
+--
+-- In the original JIRA 3177 bug, by the time we get to the end of the
+-- ALTER TABLE processing, the select from SYS.SYSCOLUMNS retrieves NULL
+-- for the autoinc columns, instead of the correct value (1).
+
+create table d3177_SchemaVersion ( version INTEGER NOT NULL );
+insert into d3177_SchemaVersion (version) values (0);
+create table d3177_BinaryData (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ CRC32 BIGINT NOT NULL ,
+ data BLOB NOT NULL ,
+ CONSTRAINT d3177_BinaryData_id_pk PRIMARY KEY(id)
+);
+create table d3177_MailServers (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ port INTEGER NOT NULL ,
+ username varchar(80) NOT NULL ,
+ protocol varchar(80) NOT NULL ,
+ SSLProtocol varchar(10),
+ emailAddress varchar(80) NOT NULL ,
+ server varchar(80) NOT NULL ,
+ password varchar(80) NOT NULL ,
+ CONSTRAINT d3177_MailServers_id_pk PRIMARY KEY(id)
+);
+create table d3177_Mailboxes (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ port INTEGER NOT NULL ,
+ folder varchar(80) NOT NULL ,
+ username varchar(80) NOT NULL ,
+ SSLProtocol varchar(10),
+ hostname varchar(80) NOT NULL ,
+ storeType varchar(80) NOT NULL ,
+ password varchar(80) NOT NULL ,
+ timeout INTEGER NOT NULL ,
+ MailServerID INTEGER NOT NULL ,
+ CONSTRAINT d3177_Mailboxes_id_pk PRIMARY KEY(id)
+);
+create table d3177_MESSAGES (
+ Message_From varchar(1000),
+ Message_Cc varchar(1000),
+ Message_Subject varchar(1000),
+ Message_ID varchar(256) NOT NULL ,
+ Message_Bcc varchar(1000),
+ Message_Date TIMESTAMP,
+ Content_Type varchar(256),
+ MailboxID INTEGER NOT NULL ,
+ Search_Text CLOB NOT NULL ,
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ Message_To varchar(1000),
+ Display_Text CLOB NOT NULL ,
+ Message_Data_ID INTEGER NOT NULL ,
+ CONSTRAINT d3177_MESSAGES_id_pk PRIMARY KEY(id)
+);
+ select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES';
+create table D3177_ATTACHMENTS (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ Inline INTEGER,
+ CRC32 BIGINT NOT NULL ,
+ Attachment_Name varchar(256) NOT NULL ,
+ Attachment_File varchar(512) NOT NULL ,
+ Message_ID INTEGER NOT NULL ,
+ Content_Type varchar(256) NOT NULL ,
+ CONSTRAINT D3177_ATTACHMENTS_id_pk PRIMARY KEY(id)
+);
+alter table D3177_ATTACHMENTS ADD CONSTRAINT ATTACHMENTS_Message_ID_MESSAGES_ID FOREIGN KEY ( Message_ID ) REFERENCES D3177_MESSAGES ( ID );
+alter table D3177_MESSAGES ADD CONSTRAINT MESSAGES_MailboxID_Mailboxes_ID FOREIGN KEY ( MailboxID ) REFERENCES d3177_Mailboxes ( ID );
+alter table D3177_MESSAGES ADD CONSTRAINT MESSAGES_Message_Data_ID_d3177_BinaryData_ID FOREIGN KEY ( Message_Data_ID ) REFERENCES d3177_BinaryData ( ID );
+alter table d3177_Mailboxes ADD CONSTRAINT Mailboxes_MailServerID_MailServers_ID FOREIGN KEY ( MailServerID ) REFERENCES d3177_MailServers ( ID );
+
+update d3177_SchemaVersion set version=1;
+
+alter table D3177_MESSAGES alter Message_To SET DATA TYPE varchar(10000);
+alter table D3177_MESSAGES alter Message_From SET DATA TYPE varchar(10000);
+alter table D3177_MESSAGES alter Message_Cc SET DATA TYPE varchar(10000);
+alter table D3177_MESSAGES alter Message_Bcc SET DATA TYPE varchar(10000);
+
+ select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES';
+update d3177_SchemaVersion set version=2;
+
+create table D3177_MailStatistics (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ ProcessedCount INTEGER DEFAULT 0 NOT NULL ,
+ HourOfDay INTEGER NOT NULL ,
+ LastModified TIMESTAMP NOT NULL ,
+ RejectedMailCount INTEGER DEFAULT 0 NOT NULL ,
+ DayOfWeek INTEGER NOT NULL ,
+ CONSTRAINT D3177_MailStatistics_id_pk PRIMARY KEY(id)
+);
+CREATE INDEX D3177_MailStatistics_HourOfDay_idx ON D3177_MailStatistics(HourOfDay);
+CREATE INDEX D3177_MailStatistics_DayOfWeek_idx ON D3177_MailStatistics(DayOfWeek);
+alter table D3177_MESSAGES alter CONTENT_TYPE SET DATA TYPE varchar(256);
+
+update d3177_SchemaVersion set version=3;
+
+alter table D3177_messages alter column Message_ID NULL;
+
+CREATE INDEX D3177_MESSAGES_Message_ID_idx ON D3177_MESSAGES(Message_ID);
+
+update d3177_SchemaVersion set version=4;
+
+alter table D3177_MESSAGES add filename varchar(256);
+alter table D3177_MESSAGES add CRC32 BIGINT;
+select id,crc32,data from d3177_BinaryData;
+
+ select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES';
+alter table D3177_messages alter column filename NOT NULL;
+alter table D3177_messages alter column crc32 NOT NULL;
+alter table D3177_messages alter column mailboxid NULL;
+ALTER TABLE D3177_MESSAGES DROP CONSTRAINT MESSAGES_message_data_id_BinaryData_id;
+alter table D3177_messages drop column message_data_id;
+drop table d3177_BinaryData;
+
+update d3177_SchemaVersion set version=6;
+
+create table D3177_EmailAddresses (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ address varchar(256) NOT NULL ,
+ CONSTRAINT D3177_EmailAddresses_id_pk PRIMARY KEY(id),
+ CONSTRAINT D3177_EmailAddresses_address_uq UNIQUE(address)
+);
+
+CREATE UNIQUE INDEX D3177_EmailAddresses_address_idx ON D3177_EmailAddresses(address);
+
+create table D3177_EmailAddressesToMessages (
+ MessageID INTEGER NOT NULL ,
+ EmailAddressID INTEGER NOT NULL
+);
+alter table D3177_EmailAddressesToMessages ADD CONSTRAINT EmailAddressesToMessages_MessageID_Messages_ID FOREIGN KEY ( MessageID ) REFERENCES D3177_Messages ( ID );
+alter table D3177_EmailAddressesToMessages ADD CONSTRAINT EmailAddressesToMessages_EmailAddressID_EmailAddresses_ID FOREIGN KEY ( EmailAddressID ) REFERENCES D3177_EmailAddresses ( ID );
+
+create table AuthenticationServers (
+ id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
+ port INTEGER NOT NULL ,
+ protocol varchar(20) NOT NULL ,
+ hostname varchar(40) NOT NULL ,
+ CONSTRAINT AuthenticationServers_id_pk PRIMARY KEY(id)
+);
+alter table d3177_Mailboxes add AuthenticationServerID INTEGER;
+select id,filename from D3177_messages;
+
+alter table D3177_MESSAGES drop column message_to;
+alter table D3177_MESSAGES drop column message_cc;
+alter table D3177_MESSAGES drop column message_from;
+
+ select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES';
+
+update d3177_SchemaVersion set version=7;
-- JIRA 2371: ensure that a non-numeric, non-autogenerated column can
-- have its default value modified:
create table t2371 ( a varchar(10));