db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
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 GMT
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" );
     }
     
+    /**
+     * <p>
+     * Test MERGE statements which read BLOBs multiple times when running triggers.
+     * </p>
+     */
+    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" );
+    }
+    
+    /**
+     * <p>
+     * Test MERGE statements which read CLOBs multiple times when running triggers.
+     * </p>
+     */
+    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 )
     {



Mime
View raw message