db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1087049 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/UpdateNode.java testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_8.java
Date Wed, 30 Mar 2011 19:23:12 GMT
Author: mamta
Date: Wed Mar 30 19:23:11 2011
New Revision: 1087049

URL: http://svn.apache.org/viewvc?rev=1087049&view=rev
Log:
DERBY-5121 Data corruption when executing an UPDATE trigger

With the earlier checkin for DERBY-5121, DERBY-1482 changes weren't completely backed out
on trunk and 10.7. We have backed out 
the code for the triggers so that now triggers look for the columns in their actual column
positions at execution time. But 
DERBY-1482 also made changes to UPDATE code to read only the colunms needed by it and the
triggers that it is going to fire. 
We need to backout the changes to UPDATE code to make sure that it reads all the columns from
the trigger table and not do 
selective column reading.

Also adding an upgrade case testing the behavior of UPDATE reading correct columns from the
trigger table so that trigger 
finds the columns it needs.

derbyall and junit suite runs fine with these changes


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_8.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java?rev=1087049&r1=1087048&r2=1087049&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java Wed
Mar 30 19:23:11 2011
@@ -1087,53 +1087,28 @@ public final class UpdateNode extends DM
 		if (relevantTriggers.size() > 0)
 		{
 			needsDeferredProcessing[0] = true;
+			
+			boolean needToIncludeAllColumns = false;
 			Enumeration descs = relevantTriggers.elements();
 			while (descs.hasMoreElements())
 			{
 				TriggerDescriptor trd = (TriggerDescriptor) descs.nextElement();
 				
-				int[] referencedColsInTriggerAction = trd.getReferencedColsInTriggerAction();
-				int[] triggerCols = trd.getReferencedCols();
-				if (triggerCols == null || triggerCols.length == 0) {
-					for (int i=0; i < columnCount; i++) {
-						columnMap.set(i+1);
-					}
-					//no need to go through the test of the trigger because
-					//we have already decided to read all the columns 
-					//because no trigger action columns were found for the
-					//trigger that we are considering right now.
-					break; 
-				} else {
-					if (referencedColsInTriggerAction == null || 
-							referencedColsInTriggerAction.length == 0) {
-						//Does this trigger have REFERENCING clause defined on it
-						if (!trd.getReferencingNew() && !trd.getReferencingOld()) {
-							for (int ix = 0; ix < triggerCols.length; ix++)
-							{
-								columnMap.set(triggerCols[ix]);
-							}
-						} else {
-							for (int i=0; i < columnCount; i++) {
-								columnMap.set(i+1);
-							}							
-							//no need to go through the test of the trigger because
-							//we have already decided to read all the columns 
-							//because no trigger action columns were found for the
-							//trigger that we are considering right now.
-							break; 
-						}
-					} else {
-						for (int ix = 0; ix < triggerCols.length; ix++)
-						{
-							columnMap.set(triggerCols[ix]);
-						}
-						for (int ix = 0; ix < referencedColsInTriggerAction.length; ix++)
-						{
-							columnMap.set(referencedColsInTriggerAction[ix]);
-						}
-					}
-				}			
+				//Does this trigger have REFERENCING clause defined on it
+				if (!trd.getReferencingNew() && !trd.getReferencingOld())
+					continue;
+				else
+				{
+					needToIncludeAllColumns = true;
+					break;
+				}
+			}
 
+			if (needToIncludeAllColumns) {
+				for (int i = 1; i <= columnCount; i++)
+				{
+					columnMap.set(i);
+				}
 			}
 		}
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_8.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_8.java?rev=1087049&r1=1087048&r2=1087049&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_8.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_8.java
Wed Mar 30 19:23:11 2011
@@ -22,12 +22,8 @@ package org.apache.derbyTesting.function
 
 import org.apache.derbyTesting.junit.SupportFilesSetup;
 
-import java.sql.SQLException;
-import java.sql.SQLWarning;
 import java.sql.Statement;
 import java.sql.ResultSet;
-import java.util.HashSet;
-import java.util.Set;
 
 import junit.framework.Test;
 import junit.framework.TestSuite;
@@ -96,6 +92,70 @@ public class Changes10_8 extends Upgrade
     // TESTS
     //
     ///////////////////////////////////////////////////////////////////////////////////
+    public void testDERBY5121TriggerTest2() throws Exception
+    {
+        Statement s = createStatement();
+        boolean modeDb2SqlOptional = oldAtLeast(10, 3);
+    	String updateSQL = "update media "+
+    	"set name = 'Mon Liza', description = 'Something snarky.' " +
+    	"where mediaID = 1";
+        
+        switch ( getPhase() )
+        {
+        case PH_CREATE: // create with old version
+        	s.execute("create table folder ( "+
+        			"folderType	int	not null, folderID	int	not null, "+
+        			"folderParent int, folderName varchar(50) not null)");
+        	s.execute("create table media ( " +
+        			"mediaID int not null, name varchar(50)	not null, "+
+        			"description clob not null, mediaType varchar(50), "+
+        			"mediaContents	blob, folderID int not null	default 7)");
+        	s.execute("create trigger mediaInsrtDupTrgr " +
+        			"after INSERT on media referencing new as nr "+
+        			"for each ROW "+
+        			(modeDb2SqlOptional?"":"MODE DB2SQL ") +
+        			"values( nr.folderID, 7, nr.name)");
+        	s.execute("create trigger mediaUpdtDupTrgr " +
+        			"after UPDATE of folderID, name on media " +
+        			"referencing new as nr "+
+        			"for each ROW "+
+        			(modeDb2SqlOptional?"":"MODE DB2SQL ") +
+        			"values( nr.folderID, 7, nr.name)");
+        	s.executeUpdate("insert into folder(folderType, folderID, "+
+        			"folderParent, folderName ) "+
+        			"values ( 7, 7, null, 'media' )");
+        	s.executeUpdate("insert into media(mediaID, name, description)"+
+        			"values (1, 'Mona Lisa', 'A photo of the Mona Lisa')");
+        	if (oldIs(10,7,1,1))
+                assertStatementError(  "XCL12", s, updateSQL );
+        	else
+        		s.executeUpdate(updateSQL);
+        	break;
+
+        case PH_SOFT_UPGRADE:
+    		s.executeUpdate(updateSQL);
+        	break;
+        	
+        case PH_POST_SOFT_UPGRADE:
+        	//Derby 10.7.1.1 is not going to work because UPDATE sql should
+        	// have read all the columns from the trigger table but it did
+        	// not and hence trigger can't find the column it needs from the
+        	// trigger table
+        	if (oldIs(10,7,1,1))
+                assertStatementError(  "S0022", s, updateSQL );
+        	else
+        		s.executeUpdate(updateSQL);
+        	break;
+        case PH_HARD_UPGRADE:
+    		s.executeUpdate(updateSQL);
+        	break;
+        case PH_POST_HARD_UPGRADE:
+    		s.executeUpdate(updateSQL);
+        	s.executeUpdate("drop table media");
+        	s.executeUpdate("drop table folder");
+        	break;
+        }
+    }
 
     /**
      * Changes made for DERBY-1482 caused corruption which is being logged 



Mime
View raw message