db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r594727 - in /db/derby/code/trunk/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/org/apach...
Date Wed, 14 Nov 2007 00:12:46 GMT
Author: bpendleton
Date: Tue Nov 13 16:12:45 2007
New Revision: 594727

URL: http://svn.apache.org/viewvc?rev=594727&view=rev
Log:
DERBY-3175: NullPointerException on INSERT after ALTER TABLE DROP COLUMN

The implementation of ALTER TABLE DROP COLUMN needs to adjust the
SYSCOLUMNS information for some of the columns in the table. Specifically,
columns with a higher column position than the dropped column need to
have their column position adjusted (decremented by 1).

The algorithm which did this column position adjustment was flawed,
because it was losing the auto-increment information for generated columns.

The code adjusts the column position by:
 - reading the old column information into a ColumnDescriptor
 - deleting the old column information from SYSCOLUMNS
 - modifying the ColumnDescriptor to set the new column position
 - adding the new column information to SYSCOLUMNS

But the generated column information in the ColumnDescriptor was not being
preserved properly in this process.

The fix involved ensuring that the ColumnDescriptor information was
accurate for generated columns, and ensuring that SYSCOLUMNSRowFactory.makeRow
was correctly loading the generated column information into the new row.



Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java?rev=594727&r1=594726&r2=594727&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
Tue Nov 13 16:12:45 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/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=594727&r1=594726&r2=594727&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Tue Nov 13 16:12:45 2007
@@ -3678,7 +3678,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
@@ -3862,7 +3862,7 @@
 										  (DefaultInfo) null,
 										  uuid,
 										  (UUID) null,
-										  0, 0);
+										  0, 0, 0);
 										
 				updateColumnDescriptor(cd,
 									   cd.getReferencingUUID(), 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java?rev=594727&r1=594726&r2=594727&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
Tue Nov 13 16:12:45 2007
@@ -154,6 +154,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 
@@ -174,6 +175,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)
 			{
@@ -222,8 +224,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, 
@@ -320,7 +329,7 @@
 		UUID				defaultUUID = null;
 		UUID				uuid = null;
 		UUIDFactory			uuidFactory = getUUIDFactory();
-		long autoincStart, autoincInc;
+		long autoincStart, autoincInc, autoincValue;
 
 		DataDescriptorGenerator	ddg = dd.getDataDescriptorGenerator();
 
@@ -393,7 +402,8 @@
 		DataTypeDescriptor dataTypeServices = 
 			DataTypeDescriptor.getType(catalogType);
 
-		/* 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();
@@ -409,7 +419,8 @@
 
 		colDesc = new ColumnDescriptor(columnName, columnNumber,
 							dataTypeServices, defaultValue, defaultInfo, uuid, 
-							defaultUUID, autoincStart, autoincInc);
+							defaultUUID, autoincStart, autoincInc,
+                            autoincValue);
 		return colDesc;
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=594727&r1=594726&r2=594727&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Tue Nov 13 16:12:45 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/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?rev=594727&r1=594726&r2=594727&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
Tue Nov 13 16:12:45 2007
@@ -1643,6 +1643,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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?rev=594727&r1=594726&r2=594727&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
Tue Nov 13 16:12:45 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