You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ma...@apache.org on 2011/03/30 21:23:12 UTC
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
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