db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1071463 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/ testing/org/apache/derbyTesting/functionTests/tests/upgra...
Date Thu, 17 Feb 2011 00:51:56 GMT
Author: mamta
Date: Thu Feb 17 00:51:56 2011
New Revision: 1071463

URL: http://svn.apache.org/viewvc?rev=1071463&view=rev
Log:
DERBY-4984 ALTER TABLE DROP COLUMN may leave triggers invalid even if they are not using the column getting dropped.

With this change, now ALTER TABLE DROP COLUMN will be able to detect the triggers defined on the table which use the columns in it's trigger action. Prior to this, Derby only detected the triggers using the column being dropped in the trigger columns list. In 10.7 and higher, Derby could also detect the triggers using the column being dropped in the trigger action through the REFERENCING clause. With this commit, even the older releases of Derby (prior to 10.7) will be able to detect the triggers using the column being dropped in the trigger action through the REFERENCING clause.

Derby will also be able to fix the internal representation of the trigger action sql which has column references through REFERENCING clause. Such references get converted to reference to the column by their column positions. Drop of a column can change the positions of the columns in the table and hence that can invalidate triggers if they are using the incorrect column numbers in the internal trigger action representation. That problem will be taken care of by regenerating the internal representation of trigger action sql and rebinding that internally generated form of trigger action sql.



Modified:
    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/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java

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=1071463&r1=1071462&r2=1071463&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 Thu Feb 17 00:51:56 2011
@@ -4914,7 +4914,13 @@ public final class	DataDictionaryImpl
 			//We are here because we have come across an invalidated trigger 
 			//which is being fired. This code gets called for such a trigger
 			//only if it is a row level trigger with REFERENCEs clause
-			if (referencedCols != null){
+			//
+			// referencedColsInTriggerAction can be null if trigger action
+			// does not use any columns through REFERENCING clause. This can
+			// happen when we are coming here through ALTER TABLE DROP COLUMN
+			// and the trigger being rebuilt does not use any columns through 
+			// REFERENCING clause. DERBY-4887
+			if (referencedCols != null && referencedColsInTriggerAction != null){
 				for (int i = 0; i < referencedColsInTriggerAction.length; i++)
 				{
 					triggerColsAndTriggerActionCols[referencedColsInTriggerAction[i]-1] = referencedColsInTriggerAction[i];

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=1071463&r1=1071462&r2=1071463&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 Thu Feb 17 00:51:56 2011
@@ -26,6 +26,7 @@ import java.util.Enumeration;
 import java.util.Iterator;
 import java.util.List;
 import java.util.Properties;
+import java.util.Vector;
 
 import org.apache.derby.catalog.DefaultInfo;
 import org.apache.derby.catalog.DependableFinder;
@@ -42,6 +43,8 @@ import org.apache.derby.iapi.sql.Activat
 import org.apache.derby.iapi.sql.PreparedStatement;
 import org.apache.derby.iapi.sql.ResultSet;
 import org.apache.derby.iapi.sql.StatementType;
+import org.apache.derby.iapi.sql.compile.CompilerContext;
+import org.apache.derby.iapi.sql.compile.Parser;
 import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
 import org.apache.derby.iapi.sql.depend.DependencyManager;
 import org.apache.derby.iapi.sql.dictionary.CheckConstraintDescriptor;
@@ -62,6 +65,7 @@ import org.apache.derby.iapi.sql.diction
 import org.apache.derby.iapi.sql.dictionary.StatisticsDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TriggerDescriptor;
+import org.apache.derby.iapi.sql.dictionary.SPSDescriptor;
 import org.apache.derby.iapi.sql.execute.ConstantAction;
 import org.apache.derby.iapi.sql.execute.ExecIndexRow;
 import org.apache.derby.iapi.sql.execute.ExecRow;
@@ -81,7 +85,10 @@ import org.apache.derby.iapi.types.DataV
 import org.apache.derby.iapi.types.RowLocation;
 import org.apache.derby.iapi.util.IdUtil;
 import org.apache.derby.impl.sql.catalog.DDColumnDependableFinder;
+import org.apache.derby.impl.sql.compile.CollectNodesVisitor;
 import org.apache.derby.impl.sql.compile.ColumnDefinitionNode;
+import org.apache.derby.impl.sql.compile.ColumnReference;
+import org.apache.derby.impl.sql.compile.StatementNode;
 
 /**
  *	This class  describes actions that are ALWAYS performed for an
@@ -1384,7 +1391,10 @@ class AlterTableConstantAction extends D
 					}
 				}
 
-				// change triggers to refer to columns in new positions
+				// The following if condition will be true if the column
+				// getting dropped is not a trigger column, but one or more
+				// of the trigge column's position has changed because of
+				// drop column.
 				if (j == refColLen && changed)
 				{
 					dd.dropTriggerDescriptor(trd, tc);
@@ -1399,45 +1409,70 @@ class AlterTableConstantAction extends D
 				}
 			}
 
-			//If the trigger under consideration already got dropped through 
-			//the referencedCols loop above, then move to next trigger
+			// If the trigger under consideration got dropped through the 
+			// loop above, then move to next trigger
 			if (triggerDroppedAlready) continue;
 			
-			//None of the triggers use column being dropped as a trigger 
-			//column. Check if the column being dropped is getting used 
-			//inside the trigger action through REFERENCING clause.
+			// Column being dropped is not one of trigger columns. Check if 
+			// that column is getting used inside the trigger action through 
+			// REFERENCING clause. This can be tracked only for triggers 
+			// created in 10.7 and higher releases. Derby releases prior to
+			// that did not keep track of trigger action columns used 
+			// through the REFERENCING clause.
 			int[] referencedColsInTriggerAction = trd.getReferencedColsInTriggerAction();
-			if (referencedColsInTriggerAction == null)
-				continue;
-
-			int refColInTriggerActionLen = referencedColsInTriggerAction.length, j;
-			boolean changedColPositionInTriggerAction = false;
-			for (j = 0; j < refColInTriggerActionLen; j++)
-			{
-				if (referencedColsInTriggerAction[j] > droppedColumnPosition)
+			if (referencedColsInTriggerAction != null) {
+				int refColInTriggerActionLen = referencedColsInTriggerAction.length, j;
+				boolean changedColPositionInTriggerAction = false;
+				for (j = 0; j < refColInTriggerActionLen; j++)
 				{
-					changedColPositionInTriggerAction = true;
+					if (referencedColsInTriggerAction[j] > droppedColumnPosition)
+					{
+						changedColPositionInTriggerAction = true;
+					}
+					else if (referencedColsInTriggerAction[j] == droppedColumnPosition)
+					{
+						if (cascade)
+						{
+	                        trd.drop(lcc);
+	                        triggerDroppedAlready = true;
+							activation.addWarning(
+								StandardException.newWarning(
+	                                SQLState.LANG_TRIGGER_DROPPED, 
+	                                trd.getName(), td.getName()));
+						}
+						else
+						{	// we'd better give an error if don't drop it,
+							throw StandardException.newException(
+	                            SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
+	                            dm.getActionString(DependencyManager.DROP_COLUMN),
+	                            columnName, "TRIGGER",
+	                            trd.getName() );
+						}
+						break;
+					}
 				}
-				else if (referencedColsInTriggerAction[j] == droppedColumnPosition)
+
+				// change trigger to refer to columns in new positions
+				// The following if condition will be true if the column
+				// getting dropped is not getting used in the trigger action
+				// sql through the REFERENCING clause but one or more of those
+				// column's position has changed because of drop column.
+				// This applies only to triggers created with 10.7 and higher.
+				// Prior to that, Derby did not keep track of the trigger 
+				// action column used through the REFERENCING clause. Such
+				// triggers will be caught later on in this method after the
+				// column has been actually dropped from the table descriptor.
+				if (j == refColInTriggerActionLen && changedColPositionInTriggerAction)
 				{
-					if (cascade)
+					dd.dropTriggerDescriptor(trd, tc);
+					for (j = 0; j < refColInTriggerActionLen; j++)
 					{
-                        trd.drop(lcc);
-						activation.addWarning(
-							StandardException.newWarning(
-                                SQLState.LANG_TRIGGER_DROPPED, 
-                                trd.getName(), td.getName()));
+						if (referencedColsInTriggerAction[j] > droppedColumnPosition)
+							referencedColsInTriggerAction[j]--;
 					}
-					else
-					{	// we'd better give an error if don't drop it,
-						// otherwise there would be unexpected behaviors
-						throw StandardException.newException(
-                            SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
-                            dm.getActionString(DependencyManager.DROP_COLUMN),
-                            columnName, "TRIGGER",
-                            trd.getName() );
-					}
-					break;
+					dd.addDescriptor(trd, sd,
+							 DataDictionary.SYSTRIGGERS_CATALOG_NUM,
+							 false, tc);
 				}
 			}
 		}
@@ -1577,7 +1612,7 @@ class AlterTableConstantAction extends D
 		ColumnDescriptorList tab_cdl = td.getColumnDescriptorList();
 
 		// drop the column from syscolumns 
-		dd.dropColumnDescriptor(td.getUUID(), columnName, tc);
+		dd.dropColumnDescriptor(td.getUUID(), columnName, tc);		
 		ColumnDescriptor[] cdlArray = 
             new ColumnDescriptor[size - columnDescriptor.getPosition()];
 
@@ -1645,6 +1680,173 @@ class AlterTableConstantAction extends D
         // list in case we were called recursively in order to cascade-drop a
         // dependent generated column.
         tab_cdl.remove( td.getColumnDescriptor( columnName ) );
+
+        // By this time, the column has been removed from the table descriptor.
+        // Now, go through all the triggers and regenerate their trigger action
+        // SPS and rebind the generated trigger action sql. If the trigger  
+        // action is using the dropped column, it will get detected here. If 
+        // not, then we will have generated the internal trigger action sql
+        // which matches the trigger action sql provided by the user.
+        //
+        // eg of positive test case
+        // create table atdc_16_tab1 (a1 integer, b1 integer, c1 integer);
+        // create table atdc_16_tab2 (a2 integer, b2 integer, c2 integer);
+        // create trigger atdc_16_trigger_1 
+        //    after update of b1 on atdc_16_tab1
+        //    REFERENCING NEW AS newt
+        //    for each row 
+        //    update atdc_16_tab2 set c2 = newt.c1
+        // The internal representation for the trigger action before the column
+        // is dropped is as follows
+        // 	 update atdc_16_tab2 set c2 = 
+        //   org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
+        //   getONewRow().getInt(3)
+        // After the drop column shown as below
+        //   alter table DERBY4998_SOFT_UPGRADE_RESTRICT drop column c11
+        // The above internal representation of tigger action sql is not 
+        // correct anymore because column position of c1 in atdc_16_tab1 has 
+        // now changed from 3 to 2. Following while loop will regenerate it and
+        // change it to as follows
+        // 	 update atdc_16_tab2 set c2 = 
+        //   org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
+        //   getONewRow().getInt(2)
+        //
+        // We could not do this before the actual column drop, because the 
+        // rebind would have still found the column being dropped in the
+        // table descriptor and hence use of such a column in the trigger
+        // action rebind would not have been caught.
+		GenericDescriptorList tdlAfterColumnDrop = dd.getTriggerDescriptors(td);
+		Enumeration descsAfterColumnDrop = tdlAfterColumnDrop.elements();
+		while (descsAfterColumnDrop.hasMoreElements())
+		{
+			TriggerDescriptor trd = (TriggerDescriptor) descsAfterColumnDrop.nextElement();
+			dd.dropTriggerDescriptor(trd, tc);
+			
+			// For the trigger, get the trigger action sql provided by the user
+			// in the create trigger sql. This sql is saved in the system
+			// table. Since a column has been dropped from the trigger table,
+			// the trigger action sql may not be valid anymore. To establish
+			// that, we need to regenerate the internal representation of that 
+			// sql and bind it again.
+
+			// Here we get the trigger action sql and use the parser to build
+			// the parse tree for it.
+			SchemaDescriptor compSchema;
+			compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
+			CompilerContext newCC = lcc.pushCompilerContext(compSchema);
+			Parser	pa = newCC.getParser();
+			StatementNode stmtnode = (StatementNode)pa.parseStatement(trd.getTriggerDefinition());
+			lcc.popCompilerContext(newCC);
+			// Do not delete following. We use this in finally clause to 
+			// determine if the CompilerContext needs to be popped.
+			newCC = null;
+			
+			try {
+				// We are interested in ColumnReference classes in the parse tree
+				CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
+				stmtnode.accept(visitor);
+				Vector refs = visitor.getList();
+				
+				// Regenerate the internal representation for the trigger action 
+				// sql using the ColumnReference classes in the parse tree. It
+				// will catch dropped column getting used in trigger action sql
+				// through the REFERENCING clause(this can happen only for the
+				// the triggers created prior to 10.7. Trigger created with
+				// 10.7 and higher keep track of trigger action column used
+				// through the REFERENCING clause in system table and hence
+				// use of dropped column will be detected earlier in this 
+				// method for such triggers).
+				//
+				// We might catch errors like following during this step.
+				// Say that following pre-10.7 trigger exists in the system and
+				// user is dropping column c11. During the regeneration of the
+				// internal trigger action sql format, we will catch that 
+				// column oldt.c11 does not exist anymore
+				// CREATE TRIGGER DERBY4998_SOFT_UPGRADE_RESTRICT_tr1 
+				//    AFTER UPDATE OF c12 
+				//    ON DERBY4998_SOFT_UPGRADE_RESTRICT REFERENCING OLD AS oldt
+				//    FOR EACH ROW 
+				//    SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_RESTRICT
+
+				SPSDescriptor triggerActionSPSD = trd.getActionSPS(lcc);
+				int[] referencedColsInTriggerAction = new int[td.getNumberOfColumns()];
+				java.util.Arrays.fill(referencedColsInTriggerAction, -1);
+				triggerActionSPSD.setText(dd.getTriggerActionString(stmtnode, 
+					trd.getOldReferencingName(),
+					trd.getNewReferencingName(),
+					trd.getTriggerDefinition(),
+					trd.getReferencedCols(),
+					referencedColsInTriggerAction,
+					0,
+					trd.getTableDescriptor(),
+					trd.getTriggerEventMask(),
+					true
+					));
+				
+				// Now that we have the internal format of the trigger action sql, 
+				// bind that sql to make sure that we are not using colunm being
+				// dropped in the trigger action sql directly (ie not through
+				// REFERENCING clause.
+				// eg
+				// create table atdc_12 (a integer, b integer);
+				// create trigger atdc_12_trigger_1 after update of a 
+				//     on atdc_12 for each row select a,b from atdc_12
+				// Drop one of the columns used in the trigger action
+				//   alter table atdc_12 drop column b
+				// Following rebinding of the trigger action sql will catch the use
+				// of column b in trigger atdc_12_trigger_1
+				compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
+				newCC = lcc.pushCompilerContext(compSchema);
+			    newCC.setReliability(CompilerContext.INTERNAL_SQL_LEGAL);
+				pa = newCC.getParser();
+				stmtnode = (StatementNode)pa.parseStatement(triggerActionSPSD.getText());
+				// need a current dependent for bind
+				newCC.setCurrentDependent(td);
+				stmtnode.bindStatement();				
+			} catch (StandardException se)
+			{
+				if (se.getMessageId().equals(SQLState.LANG_COLUMN_NOT_FOUND))
+				{
+					if (cascade)
+					{
+                        trd.drop(lcc);
+						activation.addWarning(
+							StandardException.newWarning(
+                                SQLState.LANG_TRIGGER_DROPPED, 
+                                trd.getName(), td.getName()));
+						continue;
+					}
+					else
+					{	// we'd better give an error if don't drop it,
+						throw StandardException.newException(
+                            SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
+                            dm.getActionString(DependencyManager.DROP_COLUMN),
+                            columnName, "TRIGGER",
+                            trd.getName() );
+					}
+				} else
+					throw se;
+			}
+			finally
+			{
+				if (newCC != null)
+					lcc.popCompilerContext(newCC);
+			}
+			
+			// If we are here, then it means that the column being dropped
+			// is not getting used in the trigger action. 
+			//
+			// We have recreated the trigger action SPS and recollected the 
+			// column positions for trigger columns and trigger action columns
+			// getting accessed through REFERENCING clause because
+			// drop column can affect the column positioning of existing
+			// columns in the table. We will save that in the system table.
+			dd.addDescriptor(trd, sd,
+					 DataDictionary.SYSTRIGGERS_CATALOG_NUM,
+					 false, tc);
+
+		}
+		
 	}
 
     private void modifyColumnType(int ix)

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=1071463&r1=1071462&r2=1071463&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java Thu Feb 17 00:51:56 2011
@@ -2256,28 +2256,19 @@ public final class AlterTableTest extend
 
         // Another test
         // drop column restrict should fail because trigger uses the column 
-        // inside the trigger action. DERBY-4887. Currently, Derby does not
-        // look at the columns being used inside the trigger action unless
-        // they are being used through the REFERENCING clause and hence does 
-        // not catch the trigger dependencies
+        // inside the trigger action. 
         st.executeUpdate("create table atdc_12 (a integer, b integer)");
-        //Following is not going to be caught by the information available
-        //in systriggers even in 10.7 and higher. We only keep the information
-        //about the columns used through REFERENCING clause.
         st.executeUpdate(
                 " create trigger atdc_12_trigger_1 after update of a " +
                 "on atdc_12 for each row select a,b from atdc_12");
-        //Following will be caught because of the information available in 
-        //systriggers in 10.7 and higher because we keep the information 
-        //about the columns used through REFERENCING clause.
         st.executeUpdate(
                 " create trigger atdc_12_trigger_2 " +
                 " after update of a on atdc_12" +
                 " REFERENCING NEW AS newt OLD AS oldt "+
                 " for each row select oldt.b from atdc_12");
 
-        // We got an error because Derby detected the dependency on 
-        // atdc_12_trigger_2
+        // We got an error because Derby detected the dependency of 
+        // the triggers
         assertStatementError("X0Y25", st,
         		"alter table atdc_12 drop column b restrict");
         rs =
@@ -2292,25 +2283,15 @@ public final class AlterTableTest extend
         //column list
         st.executeUpdate("alter table atdc_12 drop column b");
         checkWarning(st, "01502");
-        // the 2 triggers should have been dropped as a result of cascade but
-        // only one gets dropped. Derby does not recognize the dependency of 
-        // trigger action column where the column is not getting referenced
-        // through REFERENCING clause
-        rs =
-            st.executeQuery(
-                    " select triggername from sys.systriggers where " +
-                    "triggername = 'ATDC_12_TRIGGER_1'");
-            JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_12_TRIGGER_1"}});
+        // the 2 triggers will get dropped as a result of cascade
+        JDBC.assertEmpty(st.executeQuery(
+        		" select triggername from sys.systriggers where " +
+        		"triggername in ('ATDC_12_TRIGGER_1', 'ATDC_12_TRIGGER_2')"));
 
         // Another test
         // drop column restrict should fail because there is a table level
         // trigger defined with the column being dropped in it's trigger
-        // action. Currently, Derby does not look at the columns being used
-        // inside the trigger action and hence does not catch the trigger 
-        // dependency unless they are being referenced through REFERENCING
-        // clause. Similarly, drop column cascade should drop this table
-        // level trigger because it is using the colunm in it's trigger 
-        // action but Derby does not catch that. DERBY-4887.
+        // action. 
         st.executeUpdate("create table atdc_13 (a integer, b integer)");
         st.executeUpdate(
                 " create trigger atdc_13_trigger_1 after update " +
@@ -2348,24 +2329,53 @@ public final class AlterTableTest extend
             	{"ATDC_13_TRIGGER_4"}, {"ATDC_13_TRIGGER_5"},
             	{"ATDC_13_TRIGGER_6"}});
         
-        // following is not the right behavior. Derby should have dropped
-        // all the 6 triggers but it drops only 3. Other 3 didn't get
-        // dropped because Derby does not recognize the dependency of 
-        // trigger action column where the column is not getting referenced
-        // through REFERENCING clause
+        // Derby will drop all the 6 triggers
         st.executeUpdate("alter table atdc_13 drop column b");
         checkWarning(st, "01502");
-        // the triggers should have been dropped as a result of cascade but
-        // Derby does not recognize the dependency of trigger action column
+        JDBC.assertEmpty(st.executeQuery(
+        		" select triggername from sys.systriggers where " +
+        		"triggername in ('ATDC_13_TRIGGER_1', "+
+        		"'ATDC_13_TRIGGER_2', 'ATDC_13_TRIGGER_3')"));
+        
+        // Another test DERBY-5044
+        // ALTER TABLE DROP COLUMN in following test case causes the column
+        // position of trigger column to change. Derby detects that dependency
+        // and fixes the trigger column position
+        st.executeUpdate("create table atdc_16_tab1 (a1 integer, b1 integer, c1 integer)");
+        st.executeUpdate("create table atdc_16_tab2 (a2 integer, b2 integer, c2 integer)");        
+        st.executeUpdate("insert into atdc_16_tab1 values(1,11,111)");
+        st.executeUpdate("insert into atdc_16_tab2 values(1,11,111)");
+        rs =
+            st.executeQuery(" select * from atdc_16_tab1");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","11","111"}});
+        rs =
+            st.executeQuery(" select * from atdc_16_tab2");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","11","111"}});
+
+        st.executeUpdate(
+                " create trigger atdc_16_trigger_1 " +
+                " after update of b1 on atdc_16_tab1" +
+                " REFERENCING NEW AS newt"+
+                " for each row " +
+                " update atdc_16_tab2 set c2 = newt.c1");
+        st.executeUpdate("update atdc_16_tab1 set b1=22,c1=222");
+        rs =
+            st.executeQuery(" select * from atdc_16_tab1");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","22","222"}});
+        rs =
+            st.executeQuery(" select * from atdc_16_tab2");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","11","222"}});
+        st.executeUpdate("alter table atdc_16_tab1 drop column a1 restrict");
+        st.executeUpdate("update atdc_16_tab1 set b1=33, c1=333");
         rs =
-            st.executeQuery(
-            " select triggername from sys.systriggers where " +
-            "triggername in ('ATDC_13_TRIGGER_1', "+
-            "'ATDC_13_TRIGGER_2', 'ATDC_13_TRIGGER_3')");
-        JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_13_TRIGGER_1"},
-            	{"ATDC_13_TRIGGER_2"}, {"ATDC_13_TRIGGER_3"}});
+            st.executeQuery(" select * from atdc_16_tab1");
+        JDBC.assertFullResultSet(rs, new String[][]{{"33","333"}});
+        rs =
+            st.executeQuery(" select * from atdc_16_tab2");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","11","333"}});
+
         
-        // Another test
+        // Another test DERBY-5044
         //Following test case involves two tables. The trigger is defined 
         //on table 1 and it uses the column from table 2 in it's trigger  
     	//action. This dependency of the trigger on a column from another 
@@ -2404,8 +2414,8 @@ public final class AlterTableTest extend
                 "for each row " +
                 "update atdc_14_tab2 set a2 = newt.a1");
 
-        // following is not the right behavior. we should have gotten an error
-        // because column being dropped is getting used in a trigger action 
+        // following is not the right behavior. we should have dropped 
+        // trigger ATDC_14_TRIGGER_1 because of DROP COLUMN CASCADE
         st.executeUpdate("alter table atdc_14_tab2 drop column a2");
         rs =
                 st.executeQuery(
@@ -2413,6 +2423,43 @@ public final class AlterTableTest extend
                 "triggername = 'ATDC_14_TRIGGER_1' ");
         JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_14_TRIGGER_1"}});
         
+        // Another test
+        // ALTER TABLE DROP COLUMN in following test case causes the column 
+        // positions of trigger action columns to change. Derby detects 
+        // that and regenerates the internal trigger action sql with correct
+        // column positions. The trigger here is defined at the table level
+        st.executeUpdate("create table atdc_15_tab1 (a1 integer, b1 integer)");
+        st.executeUpdate("create table atdc_15_tab2 (a2 integer, b2 integer)");        
+        st.executeUpdate("insert into atdc_15_tab1 values(1,11)");
+        st.executeUpdate("insert into atdc_15_tab2 values(1,11)");
+        rs =
+            st.executeQuery(" select * from atdc_15_tab1");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","11"}});
+        rs =
+            st.executeQuery(" select * from atdc_15_tab2");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","11"}});
+
+        st.executeUpdate(
+                " create trigger atdc_15_trigger_1 after update " +
+                "on atdc_15_tab1 REFERENCING NEW AS newt " +
+                "for each row " +
+                "update atdc_15_tab2 set b2 = newt.b1");
+        st.executeUpdate("update atdc_15_tab1 set b1=22");
+        rs =
+            st.executeQuery(" select * from atdc_15_tab1");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","22"}});
+        rs =
+            st.executeQuery(" select * from atdc_15_tab2");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","22"}});
+        st.executeUpdate("alter table atdc_15_tab1 drop column a1 restrict");
+        st.executeUpdate("update atdc_15_tab1 set b1=33");
+        rs =
+            st.executeQuery(" select * from atdc_15_tab1");
+        JDBC.assertFullResultSet(rs, new String[][]{{"33"}});
+        rs =
+            st.executeQuery(" select * from atdc_15_tab2");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1","33"}});
+        
 
         st.executeUpdate(
                 " create table atdc_7 (a int, b int, c int, primary key (a))");

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java?rev=1071463&r1=1071462&r2=1071463&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_7.java Thu Feb 17 00:51:56 2011
@@ -154,17 +154,21 @@ public class Changes10_7 extends Upgrade
     }
     
     /**
-     * Make sure that DERBY-4998 changes do not break backward compatibility.
+     * This test creates 2 kinds of triggers in old release for each of the
+     * three phase of upgrade. The triggers are of following 2 types
+     * 1)trigger action using columns available through the REFERENCING clause.
+     * 2)trigger action using columns without the REFERENCING clause.
+     * For both kinds of triggers, there is test case which drops the column 
+     * being used in the trigger action column. 
      * 
-     * It creates triggers in old release with trigger action columns getting
-     * used through the REFERENCING clause. Those triggers in soft upgrade
-     * mode, post soft upgrade mode and hard upgrade mode do not get detected 
-     * by ALTER TABLE DROP COLUMN because prior to 10.7, we did not keep 
-     * information about trigger action columns getting used through the 
-     * REFERENCING clause.
+     * In all three modes of upgrade, soft upgrade, post soft upgrade, and 
+     * hard upgrade, ALTER TABLE DROP COLUMN will detect the trigger 
+     * dependency.
      */
     public void testAlterTableDropColumnAndTriggerAction() throws Exception
     {
+    	// ALTER TABLE DROP COLUMN was introduced in 10.3 so no point running
+    	// this test with earlier releases
     	if (!oldAtLeast(10, 3)) return;
     	
         Statement s = createStatement();
@@ -173,232 +177,353 @@ public class Changes10_7 extends Upgrade
         switch ( getPhase() )
         {
         case PH_CREATE: // create with old version
-        	//Create 2 tables for each of the upgrade phases which are 
-        	//a)soft upgrade b)post soft upgrade and c)hard upgrade
-        	//For each of the upgrade phase, one table will be used for 
+        	// Create 4 tables for each of the upgrade phases
+        	//
+        	// There will be 2 tests in each upgrade phase. 
+        	// 1)One test will use the column being dropped as part of the
+        	//   trigger action column through the REFERENCING clause
+        	// 2)Second test will use the column being dropped as part of the
+        	//   trigger action sql without the REFERENCING clause
+        	//
+        	//For each of the two tests, one table will be used for 
         	//ALTER TABLE DROP COLUMN RESTRICT and the second table will
         	//be used for ALTER TABLE DROP COLUMN CASCADE
 
-        	//Following 2 tables and triggers are for soft upgrade mode check
-        	s.execute("CREATE TABLE DERBY4998_SOFT_UPGRADE_RESTRICT(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_SOFT_UPGRADE_RESTRICT VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_SOFT_UPGRADE_RESTRICT_tr1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_SOFT_UPGRADE_RESTRICT REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_RESTRICT");
-            s.executeUpdate("UPDATE DERBY4998_SOFT_UPGRADE_RESTRICT SET c12=c12+1");
-
-            s.execute("CREATE TABLE DERBY4998_soft_upgrade_cascade(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_soft_upgrade_cascade VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_soft_upgrade_cascade_tr1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_soft_upgrade_cascade REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_soft_upgrade_cascade");
-            s.executeUpdate("UPDATE DERBY4998_soft_upgrade_cascade SET c12=c12+1");
+        	//Following 4 tables and triggers will be used in soft upgrade mode
+        	// The trigger actions on following 2 table use a column through 
+        	// REFERENCING clause
+        	createTableAndTrigger("TAB1_SOFT_UPGRADE_RESTRICT", 
+        			"TAB1_SOFT_UPGRADE_RESTRICT_TR1", true);
+        	createTableAndTrigger("TAB1_SOFT_UPGRADE_CASCADE",
+        			"TAB1_SOFT_UPGRADE_CASCADE_TR1", true);
+        	// The trigger actions on following 2 table use a column without
+        	// the REFERENCING clause
+        	createTableAndTrigger("TAB2_SOFT_UPGRADE_RESTRICT",
+        			"TAB2_SOFT_UPGRADE_RESTRICT_TR1", false);
+        	createTableAndTrigger("TAB2_SOFT_UPGRADE_CASCADE",
+        			"TAB2_SOFT_UPGRADE_CASCADE_TR1", false);
+
+        	//Following 4 tables and triggers will be used in post-soft 
+        	// upgrade mode
+        	// The trigger actions on following 2 table use a column through 
+        	// REFERENCING clause
+        	createTableAndTrigger("TAB1_POSTSOFT_UPGRADE_RESTRICT", 
+        			"TAB1_POSTSOFT_UPGRADE_RESTRICT_TR1", true);
+        	createTableAndTrigger("TAB1_POSTSOFT_UPGRADE_CASCADE",
+        			"TAB1_POSTSOFT_UPGRADE_CASCADE_TR1", true);
+        	// The trigger actions on following 2 table use a column without
+        	// the REFERENCING clause
+        	createTableAndTrigger("TAB2_POSTSOFT_UPGRADE_RESTRICT",
+        			"TAB2_POSTSOFT_UPGRADE_RESTRICT_TR1", false);
+        	createTableAndTrigger("TAB2_POSTSOFT_UPGRADE_CASCADE",
+        			"TAB2_POSTSOFT_UPGRADE_CASCADE_TR1", false);
+
+        	//Following 4 tables and triggers will be used in hard 
+        	// upgrade mode
+        	// The trigger actions on following 2 table use a column through 
+        	// REFERENCING clause
+        	createTableAndTrigger("TAB1_HARD_UPGRADE_RESTRICT", 
+        			"TAB1_HARD_UPGRADE_RESTRICT_TR1", true);
+        	createTableAndTrigger("TAB1_HARD_UPGRADE_CASCADE",
+        			"TAB1_HARD_UPGRADE_CASCADE_TR1", true);
+        	// The trigger actions on following 2 table use a column without
+        	// the REFERENCING clause
+        	createTableAndTrigger("TAB2_HARD_UPGRADE_RESTRICT",
+        			"TAB2_HARD_UPGRADE_RESTRICT_TR1", false);
+        	createTableAndTrigger("TAB2_HARD_UPGRADE_CASCADE",
+        			"TAB2_HARD_UPGRADE_CASCADE_TR1", false);
 
-        	//Following 2 tables and triggers are for post-soft upgrade mode
-            //check
-            s.execute("CREATE TABLE DERBY4998_postsoft_upgrade_restrict(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_postsoft_upgrade_restrict VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_postsoft_upgrade_restrict_tr1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_postsoft_upgrade_restrict REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_postsoft_upgrade_restrict");
-            s.executeUpdate("UPDATE DERBY4998_postsoft_upgrade_restrict SET c12=c12+1");
+            break;
 
-            s.execute("CREATE TABLE DERBY4998_postsoft_upgrade_cascade(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_postsoft_upgrade_cascade VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_postsoft_upgrade_cascade_tr1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_postsoft_upgrade_cascade REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_postsoft_upgrade_cascade");
-            s.executeUpdate("UPDATE DERBY4998_postsoft_upgrade_cascade SET c12=c12+1");
+        case PH_SOFT_UPGRADE: // boot with new version and soft-upgrade
+        	// The trigger has trigger action using the column being dropped
+            // through the REFERENCING clause. Because of this, 
+        	// DROP COLUMN RESTRICT will fail.
+            assertStatementError("X0Y25", s,
+            		" alter table TAB1_SOFT_UPGRADE_RESTRICT " +
+            		" drop column c11 restrict");
+            //Verify that trigger still exists in the system
+            rs = s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB1_SOFT_UPGRADE_RESTRICT_TR1'");
+            JDBC.assertFullResultSet(rs, 
+               		new String[][]{{"TAB1_SOFT_UPGRADE_RESTRICT_TR1"}});
+                           	
+        	// The trigger has trigger action using the column being dropped
+            // through the REFERENCING clause. Because of this, 
+        	// DROP COLUMN CASCADE will drop the dependent trigger.
+            s.executeUpdate("alter table TAB1_SOFT_UPGRADE_CASCADE " +
+            		" drop column c11 CASCADE");
+            checkWarning(s, "01502");
+            //Verify that the trigger does not exist in the system anymore
+            JDBC.assertEmpty(s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB1_SOFT_UPGRADE_CASCADE_TR1'"));
 
-        	//Following 2 tables and triggers are for hard upgrade mode check
-            s.execute("CREATE TABLE DERBY4998_hard_upgrade_restrict(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_hard_upgrade_restrict VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_hard_upgrade_restrict_tr1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_hard_upgrade_restrict REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_hard_upgrade_restrict");
-            s.executeUpdate("UPDATE DERBY4998_hard_upgrade_restrict SET c12=c12+1");
+        	// The trigger has trigger action using the column being dropped
+        	// (not through the REFERENCING clause). Because of this, 
+        	// DROP COLUMN RESTRICT will fail.
+            assertStatementError("X0Y25", s,
+            		" alter table TAB2_SOFT_UPGRADE_RESTRICT " +
+            		" drop column c11 restrict");
+            //Verify that trigger still exists in the system
+            rs = s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB2_SOFT_UPGRADE_RESTRICT_TR1'");
+            JDBC.assertFullResultSet(rs, 
+               		new String[][]{{"TAB2_SOFT_UPGRADE_RESTRICT_TR1"}});
+                           	
+        	// The trigger has trigger action using the column being dropped
+            // (not through the REFERENCING clause). Because of this, 
+        	// DROP COLUMN CASCADE will drop the dependent trigger.
+            s.executeUpdate("alter table TAB2_SOFT_UPGRADE_CASCADE " +
+            		" drop column c11 CASCADE");
+            checkWarning(s, "01502");
+            //Verify that the trigger does not exist in the system anymore
+            JDBC.assertEmpty(s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB2_SOFT_UPGRADE_CASCADE_TR1'"));
 
-            s.execute("CREATE TABLE DERBY4998_hard_upgrade_cascade(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_hard_upgrade_cascade VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_hard_upgrade_cascade_tr1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_hard_upgrade_cascade REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_hard_upgrade_cascade");
-            s.executeUpdate("UPDATE DERBY4998_hard_upgrade_cascade SET c12=c12+1");
-            break;
+            // Same behavior can be seen with tables and triggers created
+            // in soft upgrade mode using Derby 10.7 release,
+            // The trigger actions in this test case uses a column through 
+        	// REFERENCING clause. Because of this, 
+        	// DROP COLUMN RESTRICT will fail.
+        	createTableAndTrigger("TAB1_SOFT_UPGRADE_NEW_TABLE_RESTRICT", 
+        			"TAB1_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", true);
+            assertStatementError("X0Y25", s,
+            		" alter table TAB1_SOFT_UPGRADE_NEW_TABLE_RESTRICT " +
+            		" drop column c11 restrict");
+            //Verify that trigger still exists in the system
+            rs = s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB1_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1'");
+            JDBC.assertFullResultSet(rs,
+            		new String[][]{{"TAB1_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1"}});
+            
+            // Same behavior can be seen with tables and triggers created
+            // in soft upgrade mode using Derby 10.7 release,
+            // The trigger actions in this test case uses a column through 
+        	// REFERENCING clause. Because of this, 
+        	// DROP COLUMN CASCADE will drop the dependent trigger.
+        	createTableAndTrigger("TAB1_SOFT_UPGRADE_NEW_TABLE_CASCADE",
+        			"TAB1_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", true);
+            s.executeUpdate("alter table TAB1_SOFT_UPGRADE_NEW_TABLE_CASCADE " +
+            		" drop column c11 CASCADE");
+            checkWarning(s, "01502");
+            //Verify that the trigger does not exist in the system anymore
+            JDBC.assertEmpty(s.executeQuery(
+            		" select triggername from sys.systriggers where " +
+            		"triggername='TAB1_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1'"));
 
-        case PH_SOFT_UPGRADE: // boot with new version and soft-upgrade
-        	//The tables created with 10.6 and prior versions will exhibit
-        	//incorrect behavoir
-        	incorrectBehaviorForDropColumn("DERBY4998_SOFT_UPGRADE_RESTRICT",
-        			"DERBY4998_SOFT_UPGRADE_RESTRICT_TR1", "RESTRICT");
-        	incorrectBehaviorForDropColumn("DERBY4998_SOFT_UPGRADE_CASCADE",
-        			"DERBY4998_SOFT_UPGRADE_CASCADE_TR1", "CASCADE");
-
-        	//Even though we are in soft upgrade mode using Derby 10.7 release,
-        	//newly created triggers will still not keep track of trigger
-        	//action columns referenced through REFERENCING clause because
-        	//that will break the backward compatibility when this db gets
-        	//used with earlier Derby version again after soft upgrade.
-        	//Show this with an example
-            s.execute("CREATE TABLE DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT_tr1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT");
-            s.executeUpdate("UPDATE DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT SET c12=c12+1");
-        	incorrectBehaviorForDropColumn("DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT",
-        			"DERBY4998_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", "RESTRICT");
-        	
-            s.execute("CREATE TABLE DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE");
-            s.executeUpdate("UPDATE DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE SET c12=c12+1");
-        	incorrectBehaviorForDropColumn("DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE",
-        			"DERBY4998_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", "RESTRICT");
+            // Same behavior can be seen with tables and triggers created
+            // in soft upgrade mode using Derby 10.7 release,
+            // The trigger actions in this test case uses a column  
+        	// (not through the REFERENCING clause). Because of this, 
+        	// DROP COLUMN RESTRICT will fail.
+        	createTableAndTrigger("TAB2_SOFT_UPGRADE_NEW_TABLE_RESTRICT", 
+        			"TAB2_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", false);
+            assertStatementError("X0Y25", s,
+            		" alter table TAB2_SOFT_UPGRADE_NEW_TABLE_RESTRICT " +
+            		" drop column c11 restrict");
+            //Verify that trigger still exists in the system
+            rs = s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB2_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1'");
+            JDBC.assertFullResultSet(rs,
+            		new String[][]{{"TAB2_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1"}});
+
+            // Same behavior can be seen with tables and triggers created
+            // in soft upgrade mode using Derby 10.7 release,
+            // The trigger actions in this test case uses a column  
+        	// (not through the REFERENCING clause). Because of this, 
+        	// DROP COLUMN RESTRICT will fail.
+        	createTableAndTrigger("TAB2_SOFT_UPGRADE_NEW_TABLE_CASCADE", 
+        			"TAB2_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", false);
+            s.executeUpdate("alter table TAB2_soft_upgrade_NEW_TABLE_cascade " +
+            		" drop column c11 CASCADE");
+            checkWarning(s, "01502");
+            //Verify that the trigger does not exist in the system anymore
+            JDBC.assertEmpty(s.executeQuery(
+            		" select triggername from sys.systriggers where " +
+            		"triggername='TAB2_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1'"));
             break;
 
         case PH_POST_SOFT_UPGRADE: 
         	// soft-downgrade: boot with old version after soft-upgrade
 
-        	//The tables created with 10.6 and prior versions will continue to 
-        	//exhibit incorrect behavoir
-        	incorrectBehaviorForDropColumn("DERBY4998_POSTSOFT_UPGRADE_RESTRICT",
-        			"DERBY4998_POSTSOFT_UPGRADE_RESTRICT_TR1", "RESTRICT");
-        	incorrectBehaviorForDropColumn("DERBY4998_POSTSOFT_UPGRADE_CASCADE",
-        			"DERBY4998_POSTSOFT_UPGRADE_CASCADE_TR1", "CASCADE");
-        	
+        	//The tables created with 10.6 and prior versions will exhibit
+        	// incorrect behavior because changes for DERBY-4887/DERBY-4984 
+        	// have not been backported to 10.6 and earlier yet
+        	//
+        	//ALTER TABLE DROP COLUMN will not detect column being dropped
+        	// in trigger action of dependent triggers.
+        	incorrectBehaviorForDropColumn("TAB1_POSTSOFT_UPGRADE_RESTRICT",
+        			"TAB1_POSTSOFT_UPGRADE_RESTRICT_TR1", "RESTRICT");
+        	incorrectBehaviorForDropColumn("TAB1_POSTSOFT_UPGRADE_CASCADE",
+        			"TAB1_POSTSOFT_UPGRADE_CASCADE_TR1", "CASCADE");
+        	incorrectBehaviorForDropColumn("TAB2_POSTSOFT_UPGRADE_RESTRICT",
+        			"TAB2_POSTSOFT_UPGRADE_RESTRICT_TR1", "RESTRICT");
+        	incorrectBehaviorForDropColumn("TAB2_POSTSOFT_UPGRADE_CASCADE",
+        			"TAB2_POSTSOFT_UPGRADE_CASCADE_TR1", "CASCADE");
+
         	//We are back to pre-10.7 version after the soft upgrade. 
         	//ALTER TABLE DROP COLUMN will continue to behave incorrectly
         	//and will not detect the trigger actions referencing the column
         	//being dropped through the REFERENCING clause
-            s.execute("CREATE TABLE DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT");
-            s.executeUpdate("UPDATE DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT SET c12=c12+1");
-        	incorrectBehaviorForDropColumn("DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT",
-        			"DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", "RESTRICT");
+        	createTableAndTrigger("TAB1_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT",
+        			"TAB1_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", true);
+        	incorrectBehaviorForDropColumn("TAB1_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT",
+        			"TAB1_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", "RESTRICT");
+        	createTableAndTrigger("TAB1_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE",
+        			"TAB1_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", true);
+        	incorrectBehaviorForDropColumn("TAB1_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE",
+        			"TAB1_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", "RESTRICT");
+
+        	//We are back to pre-10.7 version after the soft upgrade. 
+        	//ALTER TABLE DROP COLUMN will continue to behave incorrectly
+        	//and will not detect the trigger actions referencing the column
+        	//being dropped directly (ie without the REFERENCING clause)
+        	createTableAndTrigger("TAB2_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT",
+        			"TAB2_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", false);
+        	incorrectBehaviorForDropColumn("TAB2_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT",
+        			"TAB2_POST_SOFT_UPGRADE_NEW_TABLE_RESTRICT_TR1", "RESTRICT");
         	
-            s.execute("CREATE TABLE DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE");
-            s.executeUpdate("UPDATE DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE SET c12=c12+1");
-        	incorrectBehaviorForDropColumn("DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE",
-        			"DERBY4998_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", "RESTRICT");
-            break;
+        	createTableAndTrigger("TAB2_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE",
+        			"TAB2_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", false);
+        	incorrectBehaviorForDropColumn("TAB2_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE",
+        			"TAB2_POST_SOFT_UPGRADE_NEW_TABLE_CASCADE_TR1", "RESTRICT");
+        	
+        	break;
 
         case PH_HARD_UPGRADE: // boot with new version and hard-upgrade
-        	//The tables created with 10.6 and prior versions will exhibit
-        	//incorrect behavior. Even though the database is at 10.7 level,
-        	//the triggers created with prior Derby releases did not keep
-        	//track of trigger action columns referenced through REFERENCING
-        	//clause.
-        	incorrectBehaviorForDropColumn("DERBY4998_HARD_UPGRADE_RESTRICT",
-        			"DERBY4998_HARD_UPGRADE_RESTRICT_TR1", "RESTRICT");
-        	incorrectBehaviorForDropColumn("DERBY4998_HARD_UPGRADE_CASCADE",
-        			"DERBY4998_HARD_UPGRADE_CASCADE_TR1", "CASCADE");
+        	// The trigger has trigger action using the column being dropped
+            // through the REFERENCING clause. Because of this, 
+        	// DROP COLUMN RESTRICT will fail.
+            assertStatementError("X0Y25", s,
+            		" alter table TAB1_HARD_UPGRADE_RESTRICT " +
+            		" drop column c11 restrict");
+            //Verify that trigger still exists in the system
+            rs = s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB1_HARD_UPGRADE_RESTRICT_TR1'");
+            JDBC.assertFullResultSet(rs, 
+                    new String[][]{{"TAB1_HARD_UPGRADE_RESTRICT_TR1"}});
+
+        	// The trigger has trigger action using the column being dropped
+            // through the REFERENCING clause. Because of this, 
+        	// DROP COLUMN CASCADE will drop the dependent trigger.
+            s.executeUpdate("alter table TAB1_HARD_UPGRADE_CASCADE " +
+                    " drop column c11 CASCADE");
+            checkWarning(s, "01502");
+            //Verify that the trigger does not exist in the system anymore
+            JDBC.assertEmpty(s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB1_HARD_UPGRADE_CASCADE_TR1'"));
         	
-        	//Create 2 new tables now that the database has been upgraded to 
-        	//10.7 Notice that newly created tables will be able to detect
+        	// The trigger has trigger action using the column being dropped
+        	// (not through the REFERENCING clause). Because of this, 
+        	// DROP COLUMN RESTRICT will fail.
+            assertStatementError("X0Y25", s,
+            		" alter table TAB2_HARD_UPGRADE_RESTRICT " +
+            		" drop column c11 restrict");
+            //Verify that trigger still exists in the system
+            rs = s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB2_HARD_UPGRADE_RESTRICT_TR1'");
+            JDBC.assertFullResultSet(rs, 
+                    new String[][]{{"TAB2_HARD_UPGRADE_RESTRICT_TR1"}});
+
+        	// The trigger has trigger action using the column being dropped
+            // (not through the REFERENCING clause). Because of this, 
+        	// DROP COLUMN CASCADE will drop the dependent trigger.
+            s.executeUpdate("alter table TAB2_HARD_UPGRADE_CASCADE " +
+                    " drop column c11 CASCADE");
+            checkWarning(s, "01502");
+            //Verify that the trigger does not exist in the system anymore
+            JDBC.assertEmpty(s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB2_HARD_UPGRADE_CASCADE_TR1'"));
+
+            //Create 2 new tables now that the database has been upgraded.
+        	//Notice that newly created tables will be able to detect
         	//trigger action reference to column through REFERENCING clause.
-        	//This is because starting 10.7, for new triggers, we have
-        	//started keeping track of trigger action columns referenced 
-        	//through REFERENCING clause.
-            s.execute("CREATE TABLE DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT_tr1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT");
-            s.executeUpdate("UPDATE DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT SET c12=c12+1");
+        	createTableAndTrigger("TAB1_HARD_UPGRADE_NEW_TABLE_RESTRICT",
+        			"TAB1_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1", true);
             assertStatementError("X0Y25", s,
-            		" alter table DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT " +
+            		" alter table TAB1_HARD_UPGRADE_NEW_TABLE_RESTRICT " +
             		" drop column c11 restrict");
+            //Verify that trigger still exists in the system
             rs = s.executeQuery(
             " select triggername from sys.systriggers where " +
-            "triggername='DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1'");
+            "triggername='TAB1_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1'");
             JDBC.assertFullResultSet(rs, 
-            		new String[][]{{"DERBY4998_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1"}});
-            s.execute("CREATE TABLE DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE(c11 int, c12 int)");
-            s.execute("INSERT INTO DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE VALUES (1,10)");
-            //Create a trigger in the older release where the database has been
-            //created. The trigger action uses a column in trigger action
-            //through REFERENCING clause
-            s.execute("CREATE TRIGGER DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE_TR1 " +
-            		"AFTER UPDATE OF c12 " +
-            		"ON DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE REFERENCING OLD AS oldt " +
-            		"FOR EACH ROW " +
-                    "SELECT oldt.c11 from DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE");
-            s.executeUpdate("UPDATE DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE SET c12=c12+1");
-            s.executeUpdate("alter table DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE " +
+            		new String[][]{{"TAB1_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1"}});
+
+        	// The trigger has trigger action using the column being dropped
+            // through the REFERENCING clause. Because of this, 
+        	// DROP COLUMN CASCADE will drop the dependent trigger.
+        	createTableAndTrigger("TAB1_HARD_UPGRADE_NEW_TABLE_CASCADE",
+        			"TAB1_HARD_UPGRADE_NEW_TABLE_CASCADE_TR1", true);
+            s.executeUpdate("alter table TAB1_HARD_UPGRADE_NEW_TABLE_CASCADE " +
+            		" drop column c11 CASCADE");
+            checkWarning(s, "01502");
+            //Verify that the trigger does not exist in the system anymore
+            JDBC.assertEmpty(s.executeQuery(
+                    " select triggername from sys.systriggers where " +
+                    "triggername='TAB1_HARD_UPGRADE_NEW_TABLE_CASCADE_TR1'"));
+
+            //Create 2 new tables now that the database has been upgraded.
+        	// Notice that newly created tables will be able to detect
+        	// trigger action column (without the REFERENCING clause.)
+        	//Because of this, DROP COLUMN RESTRICT will fail.
+        	createTableAndTrigger("TAB2_HARD_UPGRADE_NEW_TABLE_RESTRICT",
+        			"TAB2_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1", false);
+            //Verify that trigger still exists in the system
+            assertStatementError("X0Y25", s,
+            		" alter table TAB2_HARD_UPGRADE_NEW_TABLE_RESTRICT " +
+            		" drop column c11 restrict");
+            rs = s.executeQuery(
+            " select triggername from sys.systriggers where " +
+            "triggername='TAB2_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1'");
+            JDBC.assertFullResultSet(rs, 
+            		new String[][]{{"TAB2_HARD_UPGRADE_NEW_TABLE_RESTRICT_TR1"}});
+
+        	// The trigger has trigger action using the column being dropped
+            // (not through the REFERENCING clause). Because of this, 
+        	// DROP COLUMN CASCADE will drop the dependent trigger.
+        	createTableAndTrigger("TAB2_HARD_UPGRADE_NEW_TABLE_CASCADE",
+        			"TAB2_HARD_UPGRADE_NEW_TABLE_CASCADE_TR1", false);
+            s.executeUpdate("alter table TAB2_HARD_UPGRADE_NEW_TABLE_CASCADE " +
             		" drop column c11 CASCADE");
             checkWarning(s, "01502");
+            //Verify that the trigger does not exist in the system anymore
             JDBC.assertEmpty(s.executeQuery(
                     " select triggername from sys.systriggers where " +
-                    "triggername='DERBY4998_HARD_UPGRADE_NEW_TABLE_CASCADE_TR1'"));
+                    "triggername='TAB2_HARD_UPGRADE_NEW_TABLE_CASCADE_TR1'"));
             break;
         }
     }
 
+    //Create the table and trigger necessary for ALTER TABLE DROP COLUMN test
+    private void createTableAndTrigger(String tableName,
+    		String triggerName, boolean usesReferencingClause) 
+    throws SQLException {
+        Statement s = createStatement();
+        ResultSet rs;
+        
+        s.execute("CREATE TABLE " + tableName + " (c11 int, c12 int) ");
+        s.execute("INSERT INTO " + tableName + " VALUES (1,10)");
+        s.execute("CREATE TRIGGER " + triggerName + 
+        		" AFTER UPDATE OF c12 ON " + tableName +
+        		(usesReferencingClause ? " REFERENCING OLD AS oldt" : "" )+
+        		" FOR EACH ROW SELECT " +
+        		(usesReferencingClause ? "oldt.c11 " : "c11 " )+
+                "FROM " + tableName);
+        s.executeUpdate("UPDATE " + tableName + " SET c12=c12+1");
+    }
+    
+
     //ALTER TABLE DROP COLUMN in not detected the trigger column dependency for
     //columns being used through the REFERENCING clause for triggers created
     //prior to 10.7 release



Mime
View raw message