db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject 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...
Date Thu, 15 Nov 2007 19:44:17 GMT
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));



Mime
View raw message