Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id ECC62106FB for ; Mon, 17 Mar 2014 19:55:11 +0000 (UTC) Received: (qmail 58356 invoked by uid 500); 17 Mar 2014 19:55:11 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 58293 invoked by uid 500); 17 Mar 2014 19:55:10 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 58286 invoked by uid 99); 17 Mar 2014 19:55:10 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 17 Mar 2014 19:55:10 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 17 Mar 2014 19:55:06 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id B369023888E4; Mon, 17 Mar 2014 19:54:43 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1578535 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/MergeResultSet.java testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java Date: Mon, 17 Mar 2014 19:54:43 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.0.9 Message-Id: <20140317195443.B369023888E4@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: rhillegas Date: Mon Mar 17 19:54:43 2014 New Revision: 1578535 URL: http://svn.apache.org/r1578535 Log: DERBY-3155: Materialize LOBs before buffering them up for processing by WHEN [ NOT ] MATCHED clauses; commit derby-3155-44-aa-lobsInTriggers.diff. Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java?rev=1578535&r1=1578534&r2=1578535&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java Mon Mar 17 19:54:43 2014 @@ -24,6 +24,7 @@ package org.apache.derby.impl.sql.execut import org.apache.derby.iapi.error.StandardException; import org.apache.derby.iapi.reference.SQLState; import org.apache.derby.iapi.services.context.ContextManager; +import org.apache.derby.iapi.services.io.StreamStorable; import org.apache.derby.iapi.services.loader.GeneratedMethod; import org.apache.derby.iapi.sql.Activation; import org.apache.derby.iapi.sql.execute.ConstantAction; @@ -252,7 +253,26 @@ class MergeResultSet extends NoRowsResul if ( matchingClause != null ) { + // this will raise an exception if the row is being touched more than once if ( baseRowLocation != null ) { addSubjectRow( baseRowLocation ); } + + // + // This bit of defensive code materializes large streams before they + // are handed off to the WHEN [ NOT ] MATCHED clauses. By the time + // that those clauses operate, the driving left join has been closed and + // the streams can't be materialized. + // + for ( int i = 0; i < _row.nColumns(); i++ ) + { + DataValueDescriptor dvd = _row.getColumn( i + 1 ); + if ( dvd instanceof StreamStorable ) + { + if ( dvd.hasStream() ) + { + _row.setColumn( i + 1, dvd.cloneValue( true ) ); + } + } + } _thenRows[ clauseIdx ] = matchingClause.bufferThenRow( activation, _thenRows[ clauseIdx ], _row ); _rowCount++; Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java?rev=1578535&r1=1578534&r2=1578535&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java Mon Mar 17 19:54:43 2014 @@ -8487,6 +8487,486 @@ public class MergeStatementTest extends goodStatement( dboConnection, "drop procedure truncateTriggerHistory_054" ); } + /** + *

+ * Test MERGE statements which read BLOBs multiple times when running triggers. + *

+ */ + public void test_055_triggersMultiBlob() + throws Exception + { + Connection dboConnection = openUserConnection( TEST_DBO ); + + // + // Schema + // + goodStatement + ( + dboConnection, + "create procedure truncateTriggerHistory_055()\n" + + "language java parameter style java no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.truncateTriggerHistory'\n" + ); + goodStatement + ( + dboConnection, + "create procedure addHistoryRow_055\n" + + "(\n" + + " actionString varchar( 20 ),\n" + + " actionValue int\n" + + ")\n" + + "language java parameter style java reads sql data\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.addHistoryRow'\n" + ); + goodStatement + ( + dboConnection, + "create function history_055()\n" + + "returns table\n" + + "(\n" + + " action varchar( 20 ),\n" + + " actionValue int\n" + + ")\n" + + "language java parameter style derby_jdbc_result_set\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.history'\n" + ); + goodStatement + ( + dboConnection, + "create function mb_055( repeatCount int, vals int... ) returns blob\n" + + "language java parameter style derby deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.makeBlob'\n" + ); + goodStatement + ( + dboConnection, + "create function bequals_055( leftV blob, rightV blob ) returns boolean\n" + + "language java parameter style java deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.equals'\n" + ); + goodStatement + ( + dboConnection, + "create function reverse_055( leftV blob ) returns blob\n" + + "language java parameter style java deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.reverse'\n" + ); + goodStatement + ( + dboConnection, + "create function gc_055( leftV blob, idx bigint ) returns int\n" + + "language java parameter style java deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.getCell'\n" + ); + goodStatement + ( + dboConnection, + "create function add_055( leftV blob, rightV blob ) returns blob\n" + + "language java parameter style java deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.add'\n" + ); + goodStatement + ( + dboConnection, + "create table targetTable_055\n" + + "(\n" + + " primaryKey int,\n" + + " description varchar( 20 ),\n" + + " valueColumn blob,\n" + + " generatedColumn generated always as ( reverse_055( valueColumn ) )\n" + + ")\n" + ); + goodStatement + ( + dboConnection, + "create table sourceTable_055\n" + + "(\n" + + " primaryKey int,\n" + + " valueColumn blob\n" + + ")\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_055_del_before\n" + + "no cascade before delete on targetTable_055\n" + + "referencing old as old\n" + + "for each row\n" + + "call addHistoryRow_055( 'before delete', gc_055( old.generatedColumn, 50153 ) + gc_055( old.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_055_del_after\n" + + "after delete on targetTable_055\n" + + "referencing old as old\n" + + "for each row\n" + + "call addHistoryRow_055( 'after delete', gc_055( old.generatedColumn, 50153 ) + gc_055( old.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_055_ins_after\n" + + "after insert on targetTable_055\n" + + "referencing new as new\n" + + "for each row\n" + + "call addHistoryRow_055( 'after insert', gc_055( new.generatedColumn, 50153 ) + gc_055( new.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_055_upd_before\n" + + "no cascade before update on targetTable_055\n" + + "referencing old as old\n" + + "for each row\n" + + "call addHistoryRow_055( 'before update', gc_055( old.generatedColumn, 50153 ) + gc_055( old.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_055_upd_after\n" + + "after update on targetTable_055\n" + + "referencing new as new\n" + + "for each row\n" + + "call addHistoryRow_055( 'after update', gc_055( new.generatedColumn, 50153 ) + gc_055( new.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "insert into targetTable_055 ( primaryKey, description, valueColumn ) values\n" + + "( 1, 'orig', mb_055( 10000, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 ) ),\n" + + "( 2, 'orig: will delete', mb_055( 20000, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 ) ),\n" + + "( 3, 'orig: will update', mb_055( 30000, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29 ) ),\n" + + "( 4, 'orig', mb_055( 10000, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39 ) ),\n" + + "( 5, 'orig: will update', mb_055( 30000, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29 ) ),\n" + + "( 6, 'orig: will delete', mb_055( 20000, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 ) )\n" + ); + goodStatement + ( + dboConnection, + "insert into sourceTable_055 values\n" + + "( 20, mb_055( 20000, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 ) ),\n" + + "( 21, mb_055( 30000, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29 ) ),\n" + + "( 22, mb_055( 7000, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69 ) ),\n" + + "( 23, mb_055( 6000, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59 ) )\n" + ); + goodStatement + ( + dboConnection, + "call truncateTriggerHistory_055()" + ); + + // + // Verify that rows are correctly updated and that triggers fire correctly. + // + goodUpdate + ( + dboConnection, + "merge into targetTable_055 t\n" + + "using sourceTable_055 s on bequals_055( t.valueColumn, s.valueColumn )\n" + + "when matched and gc_055( t.valueColumn, 1000 ) = 10\n" + + " then delete\n" + + "when matched and gc_055( t.valueColumn, 1001 ) = 21\n" + + " then update set valueColumn = add_055( t.valueColumn, s.valueColumn ), description = 'updated'\n" + + "when not matched and mod( gc_055( s.valueColumn, 3002 ), 10 ) = 2\n" + + " then insert ( primaryKey, description, valueColumn ) values ( s.primarykey, 'inserted', s.valueColumn )\n", + 6 + ); + assertResults + ( + dboConnection, + "select\n" + + " primaryKey,\n" + + " description,\n" + + " gc_055( valueColumn, 2001 ),\n" + + " gc_055( generatedColumn, 2001 )\n" + + "from targetTable_055 order by primaryKey\n", + new String[][] + { + { "1", "orig", "1", "8" }, + { "3", "updated", "42", "56" }, + { "4", "orig", "31", "38" }, + { "5", "updated", "42", "56" }, + { "22", "inserted", "61", "68" }, + { "23", "inserted", "51", "58" }, + }, + false + ); + assertResults + ( + dboConnection, + "select * from table( history_055() ) h", + new String[][] + { + { "before delete", "31" }, + { "before delete", "31" }, + { "after delete", "31" }, + { "after delete", "31" }, + { "before update", "51" }, + { "before update", "51" }, + { "after update", "102" }, + { "after update", "102" }, + { "after insert", "131" }, + { "after insert", "111" }, + }, + false + ); + + // + // Drop schema + // + goodStatement( dboConnection, "drop table sourceTable_055" ); + goodStatement( dboConnection, "drop table targetTable_055" ); + goodStatement( dboConnection, "drop function add_055" ); + goodStatement( dboConnection, "drop function gc_055" ); + goodStatement( dboConnection, "drop function reverse_055" ); + goodStatement( dboConnection, "drop function bequals_055" ); + goodStatement( dboConnection, "drop function mb_055" ); + goodStatement( dboConnection, "drop function history_055" ); + goodStatement( dboConnection, "drop procedure addHistoryRow_055" ); + goodStatement( dboConnection, "drop procedure truncateTriggerHistory_055" ); + } + + /** + *

+ * Test MERGE statements which read CLOBs multiple times when running triggers. + *

+ */ + public void test_056_triggersMultiClob() + throws Exception + { + Connection dboConnection = openUserConnection( TEST_DBO ); + + // + // Schema + // + goodStatement + ( + dboConnection, + "create procedure truncateTriggerHistory_056()\n" + + "language java parameter style java no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.truncateTriggerHistory'\n" + ); + goodStatement + ( + dboConnection, + "create procedure addHistoryRow_056\n" + + "(\n" + + " actionString varchar( 20 ),\n" + + " actionValue varchar( 20 )\n" + + ")\n" + + "language java parameter style java reads sql data\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.addHistoryRow'\n" + ); + goodStatement + ( + dboConnection, + "create function history_056()\n" + + "returns table\n" + + "(\n" + + " action varchar( 20 ),\n" + + " actionValue varchar( 20 )\n" + + ")\n" + + "language java parameter style derby_jdbc_result_set\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.history'\n" + ); + goodStatement + ( + dboConnection, + "create function mc_056( repeatCount int, vals varchar( 32672 )... ) returns clob\n" + + "language java parameter style derby deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.makeClob'\n" + ); + goodStatement + ( + dboConnection, + "create function cequals_056( leftV clob, rightV clob ) returns boolean\n" + + "language java parameter style java deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.equals'\n" + ); + goodStatement + ( + dboConnection, + "create function reverse_056( leftV clob ) returns clob\n" + + "language java parameter style java deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.reverse'\n" + ); + goodStatement + ( + dboConnection, + "create function gc_056( leftV clob, idx bigint ) returns varchar( 1 )\n" + + "language java parameter style java deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.getCell'\n" + ); + goodStatement + ( + dboConnection, + "create function add_056( leftV clob, rightV clob ) returns clob\n" + + "language java parameter style java deterministic no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.add'\n" + ); + goodStatement + ( + dboConnection, + "create table targetTable_056\n" + + "(\n" + + " primaryKey int,\n" + + " description varchar( 20 ),\n" + + " valueColumn clob,\n" + + " generatedColumn generated always as ( reverse_056( valueColumn ) )\n" + + ")\n" + ); + goodStatement + ( + dboConnection, + "create table sourceTable_056\n" + + "(\n" + + " primaryKey int,\n" + + " valueColumn clob\n" + + ")\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_056_del_before\n" + + "no cascade before delete on targetTable_056\n" + + "referencing old as old\n" + + "for each row\n" + + "call addHistoryRow_056( 'before delete', gc_056( old.generatedColumn, 50153 ) || gc_056( old.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_056_del_after\n" + + "after delete on targetTable_056\n" + + "referencing old as old\n" + + "for each row\n" + + "call addHistoryRow_056( 'after delete', gc_056( old.generatedColumn, 50153 ) || gc_056( old.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_056_ins_after\n" + + "after insert on targetTable_056\n" + + "referencing new as new\n" + + "for each row\n" + + "call addHistoryRow_056( 'after insert', gc_056( new.generatedColumn, 50153 ) || gc_056( new.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_056_upd_before\n" + + "no cascade before update on targetTable_056\n" + + "referencing old as old\n" + + "for each row\n" + + "call addHistoryRow_056( 'before update', gc_056( old.generatedColumn, 50153 ) || gc_056( old.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "create trigger t1_056_upd_after\n" + + "after update on targetTable_056\n" + + "referencing new as new\n" + + "for each row\n" + + "call addHistoryRow_056( 'after update', gc_056( new.generatedColumn, 50153 ) || gc_056( new.generatedColumn, 50154 ) )\n" + ); + goodStatement + ( + dboConnection, + "insert into targetTable_056 ( primaryKey, description, valueColumn ) values\n" + + "( 1, 'orig', mc_056( 10000, 'abcdefghij' ) ),\n" + + "( 2, 'orig: will delete', mc_056( 20000, 'klmnopqrst' ) ),\n" + + "( 3, 'orig: will update', mc_056( 30000, 'uvwxyzabcd' ) ),\n" + + "( 4, 'orig', mc_056( 10000, 'efghijklmn' ) ),\n" + + "( 5, 'orig: will update', mc_056( 30000, 'uvwxyzabcd' ) ),\n" + + "( 6, 'orig: will delete', mc_056( 20000, 'klmnopqrst' ) )\n" + ); + goodStatement + ( + dboConnection, + "insert into sourceTable_056 values\n" + + "( 20, mc_056( 20000, 'klmnopqrst' ) ),\n" + + "( 21, mc_056( 30000, 'uvwxyzabcd' ) ),\n" + + "( 22, mc_056( 7000, 'efghijklmn' ) ),\n" + + "( 23, mc_056( 6000, 'opqrstuvwx' ) )\n" + ); + goodStatement + ( + dboConnection, + "call truncateTriggerHistory_056()" + ); + + // + // Verify that rows are correctly updated and that triggers fire correctly. + // + goodUpdate + ( + dboConnection, + "merge into targetTable_056 t\n" + + "using sourceTable_056 s on cequals_056( t.valueColumn, s.valueColumn )\n" + + "when matched and gc_056( t.valueColumn, 1000 ) = 'k'\n" + + " then delete\n" + + "when matched and gc_056( t.valueColumn, 1001 ) = 'v'\n" + + " then update set valueColumn = add_056( t.valueColumn, s.valueColumn ), description = 'updated'\n" + + "when not matched and gc_056( s.valueColumn, 3002 ) = 'g' or gc_056( s.valueColumn, 3003 ) = 'r'\n" + + " then insert ( primaryKey, description, valueColumn ) values ( s.primarykey, 'inserted', s.valueColumn )\n", + 6 + ); + assertResults + ( + dboConnection, + "select\n" + + " primaryKey,\n" + + " description,\n" + + " gc_056( valueColumn, 2001 ),\n" + + " gc_056( generatedColumn, 2001 )\n" + + "from targetTable_056 order by primaryKey\n", + new String[][] + { + { "1", "orig", "b", "i" }, + { "3", "updated", "v", "c" }, + { "4", "orig", "f", "m" }, + { "5", "updated", "v", "c" }, + { "22", "inserted", "f", "m" }, + { "23", "inserted", "p", "w" }, + }, + false + ); + assertResults + ( + dboConnection, + "select * from table( history_056() ) h", + new String[][] + { + { "before delete", "qp" }, + { "before delete", "qp" }, + { "after delete", "qp" }, + { "after delete", "qp" }, + { "before update", "az" }, + { "before update", "az" }, + { "after update", "az" }, + { "after update", "az" }, + { "after insert", "kj" }, + { "after insert", "ut" }, + }, + false + ); + + // + // Drop schema + // + goodStatement( dboConnection, "drop table sourceTable_056" ); + goodStatement( dboConnection, "drop table targetTable_056" ); + goodStatement( dboConnection, "drop function add_056" ); + goodStatement( dboConnection, "drop function gc_056" ); + goodStatement( dboConnection, "drop function reverse_056" ); + goodStatement( dboConnection, "drop function cequals_056" ); + goodStatement( dboConnection, "drop function mc_056" ); + goodStatement( dboConnection, "drop function history_056" ); + goodStatement( dboConnection, "drop procedure addHistoryRow_056" ); + goodStatement( dboConnection, "drop procedure truncateTriggerHistory_056" ); + } + /////////////////////////////////////////////////////////////////////////////////// // // ROUTINES @@ -8599,6 +9079,12 @@ public class MergeStatementTest extends _triggerHistory.add( new String[] { actionString, actionValue == null ? null : actionValue.toString() } ); } + /** Procedure for adding trigger history */ + public static void addHistoryRow( String actionString, String actionValue ) + { + _triggerHistory.add( new String[] { actionString, actionValue } ); + } + /** Function for comparing two long varchar values */ public static boolean equals( String left, String right ) {