db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1576027 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Mon, 10 Mar 2014 18:04:44 GMT
Author: rhillegas
Date: Mon Mar 10 18:04:43 2014
New Revision: 1576027

URL: http://svn.apache.org/r1576027
Log:
DERBY-3155: Add datatype tests for MERGE statement and fix bug involving xml types in MERGE statements; tests passed cleanly on derby-3155-38-aa-datatypes.diff.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MergeNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntArray.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/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=1576027&r1=1576026&r2=1576027&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java Mon Mar 10 18:04:43 2014
@@ -78,6 +78,9 @@ public class CursorNode extends DMLState
 	//At generate time, we save this position in activation for easy access to session table names list from compiler context
 	private int indexOfSessionTableNamesInSavedObjects = -1;
 
+    // true if this CursorNode is the driving left-join of a MERGE statement
+    private boolean forMergeStatement;
+    
 	/**
      * Constructor for a CursorNode
 	 *
@@ -99,6 +102,7 @@ public class CursorNode extends DMLState
      *                           updatable columns specified.  May only be
      *                           provided if the updateMode parameter is
      *                           CursorNode.UPDATE.
+     * @param forMergeStatement True if this cursor is the driving left-join of a MERGE statement
      * @param cm                 The context manager
 	 */
     CursorNode(String         statementType,
@@ -110,6 +114,7 @@ public class CursorNode extends DMLState
                boolean        hasJDBClimitClause,
                int            updateMode,
                String[]       updatableColumns,
+               boolean        forMergeStatement,
                ContextManager cm)
 	{
         super(resultSet, cm);
@@ -123,6 +128,7 @@ public class CursorNode extends DMLState
         this.updatableColumns =
                 updatableColumns == null ?
                 null : Arrays.asList(updatableColumns);
+        this.forMergeStatement = forMergeStatement;
 
 		/*
 		** This is a sanity check and not an error since the parser
@@ -286,7 +292,7 @@ public class CursorNode extends DMLState
 
 			// Reject any XML values in the select list; JDBC doesn't
 			// define how we bind these out, so we don't allow it.
-			resultSet.rejectXMLValues();
+			if ( !forMergeStatement ) { resultSet.rejectXMLValues(); }
 
 			/* Verify that all underlying ResultSets reclaimed their FromList */
 			if (SanityManager.DEBUG) {

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MergeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MergeNode.java?rev=1576027&r1=1576026&r2=1576027&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MergeNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MergeNode.java Mon Mar 10 18:04:43 2014
@@ -426,6 +426,7 @@ public final class MergeNode extends DML
                  false,
                  CursorNode.READ_ONLY,
                  null,
+                 true,
                  getContextManager()
                  );
             

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1576027&r1=1576026&r2=1576027&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Mon Mar 10 18:04:43 2014
@@ -3374,6 +3374,7 @@ preparableSelectStatement(boolean checkP
                 forUpdateState == CursorNode.READ_ONLY ?
                     null :
                     updateColumns.toArray(new String[updateColumns.size()]),
+                false,
 				getContextManager());
 
 		if (checkParams)
@@ -3633,6 +3634,7 @@ bareCallStatement() throws StandardExcep
                                                   false,
                                                   CursorNode.READ_ONLY,
                                                   null,
+                                                  false,
                                                   getContextManager());
 
 		// set the 0th param to be a RETURN param

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntArray.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntArray.java?rev=1576027&r1=1576026&r2=1576027&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntArray.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntArray.java Mon Mar 10 18:04:43 2014
@@ -64,6 +64,11 @@ public class IntArray implements Externa
     //
     ///////////////////////////////////////////////////////////////////////////////////
 
+    public static IntArray makeIntArray( int... values )
+    {
+        return new IntArray( values );
+    }
+
     public static IntArray makeIntArray( int length )
     {
         return new IntArray( new int[ length ] );

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=1576027&r1=1576026&r2=1576027&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 10 18:04:43 2014
@@ -21,6 +21,8 @@
 
 package org.apache.derbyTesting.functionTests.tests.lang;
 
+import java.sql.Blob;
+import java.sql.Clob;
 import java.sql.SQLException;
 import java.sql.SQLWarning;
 import java.sql.Connection;
@@ -28,11 +30,14 @@ import java.sql.Statement;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.DriverManager;
+import java.util.Arrays;
 import java.util.ArrayList;
 import junit.framework.Test;
 import junit.framework.TestSuite;
 import org.apache.derby.iapi.util.StringUtil;
 import org.apache.derby.catalog.DefaultInfo;
+import org.apache.derby.iapi.types.HarmonySerialBlob;
+
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.JDBC;
 import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
@@ -79,6 +84,9 @@ public class MergeStatementTest extends 
 
     private static  final   String[]    TRIGGER_HISTORY_COLUMNS = new String[] { "ACTION", "ACTION_VALUE" };
 
+    private static  final   String      BEGIN_HTML = "<html>";
+    private static  final   String      END_HTML = "</html>";
+
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // STATE
@@ -7200,6 +7208,713 @@ public class MergeStatementTest extends 
         goodStatement( dboConnection, "drop type SourceOnClauseType_049 restrict" );
     }
     
+    /**
+     * <p>
+     * Test all datatypes in ON clauses, matching restrictions, and as INSERT/UPDATE values.
+     * </p>
+     */
+    public  void    test_050_allDatatypes()
+        throws Exception
+    {
+        Connection  dboConnection = openUserConnection( TEST_DBO );
+
+        //
+        // Schema
+        //
+        goodStatement
+            (
+             dboConnection,
+             "create function lv_equals_050( leftV long varchar, rightV long varchar ) 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 clob_equals_050( 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 vb( inputVal int... ) returns varchar( 10 ) for bit data\n" +
+             "language java parameter style derby deterministic no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.makeByteArray'\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create function vb_reverse( inputVal varchar( 10 ) for bit data ) returns varchar( 10 ) for bit data\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 vb_add( leftV varchar( 10 ) for bit data, rightV varchar( 10 ) for bit data ) returns varchar( 10 ) for bit data\n" +
+             "language java parameter style java deterministic no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.add'\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create function ch( inputVal int... ) returns char( 10 ) for bit data\n" +
+             "language java parameter style derby deterministic no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.makeByteArray'\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create function ch_reverse( inputVal char( 10 ) for bit data ) returns char( 10 ) for bit data\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 ch_add( leftV char( 10 ) for bit data, rightV char( 10 ) for bit data ) returns char( 10 ) for bit data\n" +
+             "language java parameter style java deterministic no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.add'\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create function vbl( inputVal 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 vbl_reverse( inputVal 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 vbl_add( 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 function vbl_equals( 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 xmlX( val varchar( 32672 ) ) returns int\n" +
+             "language java parameter style java deterministic no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.xmlX'\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create type IntArray external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray' language java"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create function mia( vals int... ) returns IntArray\n" +
+             "language java parameter style derby deterministic no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray.makeIntArray'\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create function gc( val IntArray, cellNumber int ) returns int\n" +
+             "language java parameter style java deterministic no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray.getCell'\n"
+             );
+
+        //
+        // Numeric types
+        //
+        
+        String  intTargetValues =
+            "( 1, 'orig', 1, 100, 1000 ), ( 2, 'orig: will delete', 1, 101, 1001 ), ( 3, 'orig: will update', 2, 200, 2000 ),\n" +
+            "( 4, 'orig', 2, 201, 2000 ), ( 5, 'orig', 4, 400, 4000 )\n";
+        String  intSourceValues =
+            "( 1, 101, 10000 ), ( 2, -200, -20000 ), ( 3, 300, 30000 ), ( 5, 5, -5 )\n";
+        String  intMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on t.onClauseColumn = s.onClauseColumn\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn\n" +
+            "     then delete\n" +
+            "when matched and t.matchingClauseColumn = -s.matchingClauseColumn\n" +
+            "     then update set valueColumn = t.valueColumn + s.valueColumn, description = 'updated'\n" +
+            "when not matched and s.onClauseColumn = s.matchingClauseColumn\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  intExpectedValues = new String[][]
+        {
+            { "1", "orig", "1", "100", "1000" },
+            { "3", "updated", "2", "200", "-18000" },
+            { "4", "orig", "2", "201", "2000" },
+            { "5", "orig", "4", "400", "4000" },
+            { "6", "inserted", "5", "5", "-5" },
+        };
+
+        vet_050( dboConnection, "int", true, intTargetValues, intSourceValues, intMergeStatement, intExpectedValues );
+        vet_050( dboConnection, "bigint", true, intTargetValues, intSourceValues, intMergeStatement, intExpectedValues );
+        vet_050( dboConnection, "smallint", true, intTargetValues, intSourceValues, intMergeStatement, intExpectedValues );
+        vet_050( dboConnection, "decimal", true, intTargetValues, intSourceValues, intMergeStatement, intExpectedValues );
+        vet_050( dboConnection, "numeric", true, intTargetValues, intSourceValues, intMergeStatement, intExpectedValues );
+
+        String[][]  doubleExpectedValues = new String[][]
+        {
+            { "1", "orig", "1.0", "100.0", "1000.0" },
+            { "3", "updated", "2.0", "200.0", "-18000.0" },
+            { "4", "orig", "2.0", "201.0", "2000.0" },
+            { "5", "orig", "4.0", "400.0", "4000.0" },
+            { "6", "inserted", "5.0", "5.0", "-5.0" },
+        };
+
+        vet_050( dboConnection, "double", true, intTargetValues, intSourceValues, intMergeStatement, doubleExpectedValues );
+        vet_050( dboConnection, "float", true, intTargetValues, intSourceValues, intMergeStatement, doubleExpectedValues );
+        vet_050( dboConnection, "real", true, intTargetValues, intSourceValues, intMergeStatement, doubleExpectedValues );
+        
+        //
+        // String types
+        //
+        
+        String  stringTargetValues =
+            "( 1, 'orig', 'b', 'baa', 'baaa' ), ( 2, 'orig: will delete', 'b', 'bab', 'baab' ), ( 3, 'orig: will update', 'c', 'caa', 'caaa' ),\n" +
+            "( 4, 'orig', 'c', 'cab', 'caaa' ), ( 5, 'orig', 'e', 'eaa', 'eaaa' )\n";
+        String  stringSourceValues =
+            "( 'b', 'bab', 'baaaa' ), ( 'c', '-caa', '-caaaa' ), ( 'd', 'daa', 'daaaa' ), ( 'f', 'f', '-f' )\n";
+        String  stringMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on t.onClauseColumn = s.onClauseColumn\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn\n" +
+            "     then delete\n" +
+            "when matched and '-' || trim( t.matchingClauseColumn ) = trim( s.matchingClauseColumn )\n" +
+            "     then update set valueColumn = trim( t.valueColumn ) || trim( s.valueColumn ), description = 'updated'\n" +
+            "when not matched and s.onClauseColumn = s.matchingClauseColumn\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  stringExpectedValues = new String[][]
+        {
+            { "1", "orig", "b", "baa", "baaa" },
+            { "3", "updated", "c", "caa", "caaa-caaaa" },
+            { "4", "orig", "c", "cab", "caaa" },
+            { "5", "orig", "e", "eaa", "eaaa" },
+            { "6", "inserted", "f", "f", "-f" },
+        };
+
+        vet_050
+            ( dboConnection, "varchar( 10 )", true, stringTargetValues, stringSourceValues, stringMergeStatement, stringExpectedValues );
+        vet_050
+            ( dboConnection, "char( 10 )", true, stringTargetValues, stringSourceValues, stringMergeStatement, stringExpectedValues );
+
+        String  lvMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on lv_equals_050( t.onClauseColumn, s.onClauseColumn )\n" +
+            "when matched and lv_equals_050( t.matchingClauseColumn, s.matchingClauseColumn )\n" +
+            "     then delete\n" +
+            "when matched and lv_equals_050( '-' || t.matchingClauseColumn, s.matchingClauseColumn )\n" +
+            "     then update set valueColumn = t.valueColumn || s.valueColumn, description = 'updated'\n" +
+            "when not matched and lv_equals_050( s.onClauseColumn, s.matchingClauseColumn )\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        
+        vet_050
+            ( dboConnection, "long varchar", false, stringTargetValues, stringSourceValues, lvMergeStatement, stringExpectedValues );
+
+        String  clobMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on clob_equals_050( t.onClauseColumn, s.onClauseColumn )\n" +
+            "when matched and clob_equals_050( t.matchingClauseColumn, s.matchingClauseColumn )\n" +
+            "     then delete\n" +
+            "when matched and clob_equals_050( '-' || t.matchingClauseColumn, s.matchingClauseColumn )\n" +
+            "     then update set valueColumn = t.valueColumn || s.valueColumn, description = 'updated'\n" +
+            "when not matched and clob_equals_050( s.onClauseColumn, s.matchingClauseColumn )\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        
+        vet_050
+            ( dboConnection, "clob", false, stringTargetValues, stringSourceValues, clobMergeStatement, stringExpectedValues );
+        
+        
+        //
+        // Binary types
+        //
+        
+        String  varbinaryTargetValues =
+            "( 1, 'orig', vb( 1 ), vb( 1, 0, 0 ), vb( 1, 0, 0, 0 ) ),\n" +
+            "( 2, 'orig: will delete', vb( 1 ), vb( 1, 0, 1 ), vb( 1, 0, 0, 1 ) ),\n" +
+            "( 3, 'orig: will update', vb( 2 ), vb( 2, 0, 0 ), vb( 2, 0, 0, 0 ) ),\n" +
+            "( 4, 'orig', vb( 2 ), vb( 2, 0, 1 ), vb( 2, 0, 0, 0 ) ),\n" +
+            "( 5, 'orig', vb( 4 ), vb( 4, 0, 0 ), vb( 4, 0, 0, 0 ) )\n";
+        String  varbinarySourceValues =
+            "( vb( 1 ), vb( 1, 0, 1 ), vb( 1, 0, 0, 0, 0 ) ),\n" +
+            "( vb( 2 ), vb( 0, 0, 2 ), vb( -2, 0, 0, 0, 0 ) ),\n" +
+            "( vb( 3 ), vb( 3, 0, 0 ), vb( 3, 0, 0, 0, 0 ) ),\n" +
+            "( vb( 5 ), vb( 5 ), vb( -5 ) )\n";
+        String  varbinaryMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on t.onClauseColumn = s.onClauseColumn\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn\n" +
+            "     then delete\n" +
+            "when matched and t.matchingClauseColumn = vb_reverse( s.matchingClauseColumn )\n" +
+            "     then update set valueColumn = vb_add( t.valueColumn, s.valueColumn ), description = 'updated'\n" +
+            "when not matched and s.onClauseColumn = s.matchingClauseColumn\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  varbinaryExpectedValues = new String[][]
+        {
+            { "1", "orig", "01", "010000", "01000000" },
+            { "3", "updated", "02", "020000", "00000000" },
+            { "4", "orig", "02", "020001", "02000000" },
+            { "5", "orig", "04", "040000", "04000000" },
+            { "6", "inserted", "05", "05", "fb" },
+        };
+
+        vet_050
+            ( dboConnection, "varchar( 10 ) for bit data", true, varbinaryTargetValues, varbinarySourceValues,
+              varbinaryMergeStatement, varbinaryExpectedValues );
+        
+        String  binaryTargetValues =
+            "( 1, 'orig', ch( 1 ), ch( 1, 0, 0 ), ch( 1, 0, 0, 0 ) ),\n" +
+            "( 2, 'orig: will delete', ch( 1 ), ch( 1, 0, 1 ), ch( 1, 0, 0, 1 ) ),\n" +
+            "( 3, 'orig: will update', ch( 2 ), ch( 2, 0, 0, 0, 0, 0, 0, 0, 0, 0 ), ch( 2, 0, 0, 0 ) ),\n" +
+            "( 4, 'orig', ch( 2 ), ch( 2, 0, 1 ), ch( 2, 0, 0, 0 ) ),\n" +
+            "( 5, 'orig', ch( 4 ), ch( 4, 0, 0 ), ch( 4, 0, 0, 0 ) )\n";
+        String  binarySourceValues =
+            "( ch( 1 ), ch( 1, 0, 1 ), ch( 1, 0, 0, 0, 0 ) ),\n" +
+            "( ch( 2 ), ch( 0, 0, 0, 0, 0, 0, 0, 0, 0, 2 ), ch( -2, 0, 0, 0, 0 ) ),\n" +
+            "( ch( 3 ), ch( 3, 0, 0 ), ch( 3, 0, 0, 0, 0 ) ),\n" +
+            "( ch( 5 ), ch( 5 ), ch( -5 ) )\n";
+        String  binaryMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on t.onClauseColumn = s.onClauseColumn\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn\n" +
+            "     then delete\n" +
+            "when matched and t.matchingClauseColumn = ch_reverse( s.matchingClauseColumn )\n" +
+            "     then update set valueColumn = ch_add( t.valueColumn, s.valueColumn ), description = 'updated'\n" +
+            "when not matched and s.onClauseColumn = s.matchingClauseColumn\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  binaryExpectedValues = new String[][]
+        {
+            { "1", "orig", "01202020202020202020", "01000020202020202020", "01000000202020202020" },
+            { "3", "updated", "02202020202020202020", "02000000000000000000", "00000000204040404040" },
+            { "4", "orig", "02202020202020202020", "02000120202020202020", "02000000202020202020" },
+            { "5", "orig", "04202020202020202020", "04000020202020202020", "04000000202020202020" },
+            { "6", "inserted", "05202020202020202020", "05202020202020202020", "fb202020202020202020" },
+        };
+
+        vet_050
+            ( dboConnection, "char( 10 ) for bit data", true, binaryTargetValues, binarySourceValues,
+              binaryMergeStatement, binaryExpectedValues );
+        
+        String  blobTargetValues =
+            "( 1, 'orig', vbl( 1 ), vbl( 1, 0, 0 ), vbl( 1, 0, 0, 0 ) ),\n" +
+            "( 2, 'orig: will delete', vbl( 1 ), vbl( 1, 0, 1 ), vbl( 1, 0, 0, 1 ) ),\n" +
+            "( 3, 'orig: will update', vbl( 2 ), vbl( 2, 0, 0 ), vbl( 2, 0, 0, 0 ) ),\n" +
+            "( 4, 'orig', vbl( 2 ), vbl( 2, 0, 1 ), vbl( 2, 0, 0, 0 ) ),\n" +
+            "( 5, 'orig', vbl( 4 ), vbl( 4, 0, 0 ), vbl( 4, 0, 0, 0 ) )\n";
+        String  blobSourceValues =
+            "( vbl( 1 ), vbl( 1, 0, 1 ), vbl( 1, 0, 0, 0, 0 ) ),\n" +
+            "( vbl( 2 ), vbl( 0, 0, 2 ), vbl( -2, 0, 0, 0, 0 ) ),\n" +
+            "( vbl( 3 ), vbl( 3, 0, 0 ), vbl( 3, 0, 0, 0, 0 ) ),\n" +
+            "( vbl( 5 ), vbl( 5 ), vbl( -5 ) )\n";
+        String  blobMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on vbl_equals( t.onClauseColumn, s.onClauseColumn )\n" +
+            "when matched and vbl_equals( t.matchingClauseColumn, s.matchingClauseColumn )\n" +
+            "     then delete\n" +
+            "when matched and vbl_equals( t.matchingClauseColumn, vbl_reverse( s.matchingClauseColumn ) )\n" +
+            "     then update set valueColumn = vbl_add( t.valueColumn, s.valueColumn ), description = 'updated'\n" +
+            "when not matched and vbl_equals( s.onClauseColumn, s.matchingClauseColumn )\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+
+        vet_050
+            ( dboConnection, "blob", false, blobTargetValues, blobSourceValues,
+              blobMergeStatement, varbinaryExpectedValues );
+
+        //
+        // Boolean
+        //
+        
+        String  booleanTargetValues =
+            "( 1, 'orig', true, true, false ), ( 2, 'orig: will delete', true, true, true ), ( 3, 'orig: will update', false, false, true )\n";
+        String  booleanSourceValues =
+            "( true, true, true ), ( null, true, false ), ( false, false, false )\n";
+        String  booleanMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on t.onClauseColumn = s.onClauseColumn and t.valueColumn\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn and s.valueColumn\n" +
+            "     then delete\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn\n" +
+            "     then update set valueColumn = t.valueColumn and s.valueColumn, description = 'updated'\n" +
+            "when not matched and s.matchingClauseColumn\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  booleanExpectedValues = new String[][]
+        {
+            { "1", "orig", "true", "true", "false" },
+            { "3", "updated", "false", "false", "false" },
+            { "6", "inserted", null, "true", "false" },
+        };
+
+        vet_050
+            ( dboConnection, "boolean", true, booleanTargetValues, booleanSourceValues,
+              booleanMergeStatement, booleanExpectedValues );
+        
+        //
+        // Date
+        //
+        
+        String  dateTargetValues =
+            "( 1, 'orig', date( '0001-02-23' ), date( '0100-02-23' ), date( '1000-02-23' ) ),\n" +
+            "( 2, 'orig: will delete', date( '0001-02-23' ), date( '0101-02-23' ), date( '1001-02-23' ) ),\n" +
+            "( 3, 'orig: will update', date( '0002-02-23' ), date( '0200-02-23' ), date( '2000-02-23' ) ),\n" +
+            "( 4, 'orig', date( '0002-02-23' ), date( '0201-02-23' ), date( '2000-02-23' ) ),\n" +
+            "( 5, 'orig', date( '0004-02-23' ), date( '0400-02-23' ), date( '4000-02-23' ) )\n";
+        String  dateSourceValues =
+            "( date( '0001-02-23' ), date( '0101-02-23' ), date( '1000-01-23' ) ),\n" +
+            "( date( '0002-02-23' ), date( '0202-02-23' ), date( '2000-12-23' ) ),\n" +
+            "( date( '0003-02-23' ), date( '0300-02-23' ), date( '3000-01-23' ) ),\n" +
+            "( date( '0005-02-23' ), date( '0005-02-23' ), date( '5000-01-23' ) )\n";
+        String  dateMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on t.onClauseColumn = s.onClauseColumn\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn\n" +
+            "     then delete\n" +
+            "when matched and year( t.matchingClauseColumn ) = year( s.matchingClauseColumn ) - 2\n" +
+            "     then update set valueColumn = date( month( t.valueColumn ) + month( s.valueColumn ) ), description = 'updated'\n" +
+            "when not matched and s.onClauseColumn = s.matchingClauseColumn\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  dateExpectedValues = new String[][]
+        {
+            { "1", "orig", "0001-02-23", "0100-02-23", "1000-02-23" },
+            { "3", "updated", "0002-02-23", "0200-02-23", "1970-01-14" },
+            { "4", "orig", "0002-02-23", "0201-02-23", "2000-02-23" },
+            { "5", "orig", "0004-02-23", "0400-02-23", "4000-02-23" },
+            { "6", "inserted", "0005-02-23", "0005-02-23", "5000-01-23" },
+        };
+
+        vet_050
+            ( dboConnection, "date", true, dateTargetValues, dateSourceValues,
+              dateMergeStatement, dateExpectedValues );
+        
+        //
+        // Time
+        //
+        
+        String  timeTargetValues =
+            "( 1, 'orig', time( '01:00:01' ), time( '01:01:00' ), time( '01:10:00' ) ),\n" +
+            "( 2, 'orig: will delete', time( '01:00:01' ), time( '01:01:01' ), time( '01:10:01' ) ),\n" +
+            "( 3, 'orig: will update', time( '01:00:02' ), time( '01:02:00' ), time( '01:20:00' ) ),\n" +
+            "( 4, 'orig', time( '01:00:02' ), time( '01:02:01' ), time( '01:20:00' ) ),\n" +
+            "( 5, 'orig', time( '01:00:04' ), time( '01:04:00' ), time( '01:40:00' ) )\n";
+        String  timeSourceValues =
+            "( time( '01:00:01' ), time( '01:01:01' ), time( '01:00:00' ) ),\n" +
+            "( time( '01:00:02' ), time( '01:02:02' ), time( '20:02:00' ) ),\n" +
+            "( time( '01:00:03' ), time( '01:03:00' ), time( '03:00:00' ) ),\n" +
+            "( time( '01:00:05' ), time( '01:00:05' ), time( '05:00:00' ) )\n";
+        String  timeMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on t.onClauseColumn = s.onClauseColumn\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn\n" +
+            "     then delete\n" +
+            "when matched and second( t.matchingClauseColumn ) = second( s.matchingClauseColumn ) - 2\n" +
+            "     then update set valueColumn = coalesce( t.valueColumn, s.valueColumn ), description = 'updated'\n" +
+            "when not matched and s.onClauseColumn = s.matchingClauseColumn\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  timeExpectedValues = new String[][]
+        {
+            { "1", "orig", "01:00:01", "01:01:00", "01:10:00" },
+            { "3", "updated", "01:00:02", "01:02:00", "01:20:00" },
+            { "4", "orig", "01:00:02", "01:02:01", "01:20:00" },
+            { "5", "orig", "01:00:04", "01:04:00", "01:40:00" },
+            { "6", "inserted", "01:00:05", "01:00:05", "05:00:00" },
+        };
+
+        vet_050
+            ( dboConnection, "time", true, timeTargetValues, timeSourceValues,
+              timeMergeStatement, timeExpectedValues );
+        
+        //
+        // Timestamp
+        //
+        
+        String  timestampTargetValues =
+            "( 1, 'orig', timestamp( '1960-01-01 01:00:01' ), timestamp( '1960-01-01 01:01:00' ), timestamp( '1960-01-01 01:10:00' ) ),\n" +
+            "( 2, 'orig: will delete', timestamp( '1960-01-01 01:00:01' ), timestamp( '1960-01-01 01:01:01' ), timestamp( '1960-01-01 01:10:01' ) ),\n  " +
+            "( 3, 'orig: will update', timestamp( '1960-01-01 01:00:02' ), timestamp( '1960-01-01 01:02:00' ), timestamp( '1960-01-01 01:20:00' ) ),\n" +
+            "( 4, 'orig', timestamp( '1960-01-01 01:00:02' ), timestamp( '1960-01-01 01:02:01' ), timestamp( '1960-01-01 01:20:00' ) ),\n" +
+            "( 5, 'orig', timestamp( '1960-01-01 01:00:04' ), timestamp( '1960-01-01 01:04:00' ), timestamp( '1960-01-01 01:40:00' ) )\n";
+        String  timestampSourceValues =
+            "( timestamp( '1960-01-01 01:00:01' ), timestamp( '1960-01-01 01:01:01' ), timestamp( '1960-01-01 01:00:00' ) ),\n" +
+            "( timestamp( '1960-01-01 01:00:02' ), timestamp( '1960-01-01 01:02:02' ), timestamp( '1960-01-01 20:02:00' ) ),\n" +
+            "( timestamp( '1960-01-01 01:00:03' ), timestamp( '1960-01-01 01:03:00' ), timestamp( '1960-01-01 03:00:00' ) ),\n" +
+            "( timestamp( '1960-01-01 01:00:05' ), timestamp( '1960-01-01 01:00:05' ), timestamp( '1960-01-01 05:00:00' ) )\n";
+        String  timestampMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on t.onClauseColumn = s.onClauseColumn\n" +
+            "when matched and t.matchingClauseColumn = s.matchingClauseColumn\n" +
+            "     then delete\n" +
+            "when matched and second( t.matchingClauseColumn ) = second( s.matchingClauseColumn ) - 2\n" +
+            "     then update set valueColumn = coalesce( t.valueColumn, s.valueColumn ), description = 'updated'\n" +
+            "when not matched and s.onClauseColumn = s.matchingClauseColumn\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  timestampExpectedValues = new String[][]
+        {
+            { "1", "orig", "1960-01-01 01:00:01.0", "1960-01-01 01:01:00.0", "1960-01-01 01:10:00.0" },
+            { "3", "updated", "1960-01-01 01:00:02.0", "1960-01-01 01:02:00.0", "1960-01-01 01:20:00.0" },
+            { "4", "orig", "1960-01-01 01:00:02.0", "1960-01-01 01:02:01.0", "1960-01-01 01:20:00.0" },
+            { "5", "orig", "1960-01-01 01:00:04.0", "1960-01-01 01:04:00.0", "1960-01-01 01:40:00.0" },
+            { "6", "inserted", "1960-01-01 01:00:05.0", "1960-01-01 01:00:05.0", "1960-01-01 05:00:00.0" },
+        };
+
+        vet_050
+            ( dboConnection, "timestamp", true, timestampTargetValues, timestampSourceValues,
+              timestampMergeStatement, timestampExpectedValues );
+        
+        //
+        // XML
+        //
+        
+        String  xmlTargetValues =
+            "(\n" +
+            "  1,\n" +
+            "  'orig',\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>1</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>100</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>1000</html>' preserve whitespace )\n" +
+            "),\n" +
+            "(\n" +
+            "  2,\n" +
+            "  'orig: will delete',\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>1</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>101</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>1001</html>' preserve whitespace )\n" +
+            "),\n" +
+            "(\n" +
+            "  3,\n" +
+            "  'orig: will update',\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>2</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>200</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>2000</html>' preserve whitespace )\n" +
+            "),\n" +
+            "(\n" +
+            "  4,\n" +
+            "  'orig',\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>2</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>201</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>2000</html>' preserve whitespace )\n" +
+            "),\n" +
+            "(\n" +
+            "  5,\n" +
+            "  'orig',\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>4</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>400</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>4000</html>' preserve whitespace )\n" +
+            ")\n";
+        String  xmlSourceValues =
+            "(\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>1</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>101</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>10000</html>' preserve whitespace )\n" +
+            "),\n" +
+            "(\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>2</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>-200</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>-20000</html>' preserve whitespace )\n" +
+            "),\n" +
+            "(\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>3</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>300</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>30000</html>' preserve whitespace )\n" +
+            "  ),\n" +
+            "(\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>5</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>5</html>' preserve whitespace ),\n" +
+            "  xmlparse( document '<?xml version=\"1.0\" encoding=\"UTF-8\"?> <html>50000</html>' preserve whitespace )\n" +
+            ")\n";
+        String  xmlMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on xmlserialize( t.onClauseColumn as varchar(1000) ) = xmlserialize( s.onClauseColumn as varchar(1000) )\n" +
+"when matched and xmlserialize( t.matchingClauseColumn as varchar(1000) ) = xmlserialize( s.matchingClauseColumn as varchar(1000) )\n" +
+            "     then delete\n" +
+            "when matched\n" +
+            "     and xmlX( xmlserialize( t.matchingClauseColumn as varchar(1000) ) ) =\n" +
+            "       -xmlX( xmlserialize( s.matchingClauseColumn as varchar(1000) ) )\n" +
+            "     then update set valueColumn = coalesce( t.valueColumn, s.valueColumn ), description = 'updated'\n" +
+"when not matched and xmlserialize( s.onClauseColumn as varchar(1000) ) = xmlserialize( s.matchingClauseColumn as varchar(1000) )\n"    +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String[][]  xmlExpectedValues = new String[][]
+        {
+            { "1", "orig", "1", "100", "1000" },
+            { "3", "updated", "2", "200", "2000" },
+            { "4", "orig", "2", "201", "2000" },
+            { "5", "orig", "4", "400", "4000" },
+            { "6", "inserted", "5", "5", "50000" },
+        };
+
+        vet_050
+            ( dboConnection, "xml", false, xmlTargetValues, xmlSourceValues,
+              xmlMergeStatement, xmlExpectedValues,
+              "select\n" +
+              "  primaryKey,\n" +
+              "  description,\n" +
+              "  xmlX( xmlserialize( onClauseColumn as varchar(1000) ) ),\n" +
+              "  xmlX( xmlserialize( matchingClauseColumn as varchar(1000) ) ),\n" +
+              "  xmlX( xmlserialize( valueColumn as varchar(1000) ) )\n" +
+              "from targetTable_050 order by primaryKey\n"
+              );
+        
+        //
+        // UDT
+        //
+        
+        String  udtTargetValues =
+            "( 1, 'orig', mia( 1 ), mia( 100 ), mia( 1000 ) ),\n" +
+            "( 2, 'orig: will delete', mia( 1 ), mia( 101 ), mia( 1001 ) ),\n" +
+            "( 3, 'orig: will update', mia( 2 ), mia( 200 ), mia( 2000 ) ),\n" +
+            "( 4, 'orig', mia( 2 ), mia( 201 ), mia( 2000 ) ),\n" +
+            "( 5, 'orig', mia( 4 ), mia( 400 ), mia( 4000 ) )\n";
+        String  udtSourceValues =
+            "( mia( 1 ), mia( 101 ), mia( 10000 ) ),\n" +
+            "( mia( 2 ), mia( -200 ), mia( -20000 ) ),\n" +
+            "( mia( 3 ), mia( 300 ), mia( 30000 ) ),\n" +
+            "( mia( 5 ), mia( 5 ), mia( -5 ) )\n";
+        String  udtMergeStatement =
+            "merge into targetTable_050 t\n" +
+            "using sourceTable_050 s on gc( t.onClauseColumn, 0 ) = gc( s.onClauseColumn, 0 )\n" +
+            "when matched and gc( t.matchingClauseColumn, 0 ) = gc( s.matchingClauseColumn, 0 )\n" +
+            "     then delete\n" +
+            "when matched and gc( t.matchingClauseColumn, 0 ) = -gc( s.matchingClauseColumn, 0 )\n" +
+            "     then update set valueColumn = mia( gc( t.valueColumn, 0 ) + gc( s.valueColumn, 0 ) ), description = 'updated'\n" +
+            "when not matched and gc( s.onClauseColumn, 0 ) = gc( s.matchingClauseColumn, 0 )\n" +
+            "     then insert values ( 6, 'inserted', s.onClauseColumn, s.matchingClauseColumn, s.valueColumn )\n";
+        String  udtSelectStatement =
+            "select\n" +
+            "  primaryKey,\n" +
+            "  description,\n" +
+            "  gc( onClauseColumn, 0 ),\n" +
+            "  gc( matchingClauseColumn, 0 ),\n" +
+            "  gc( valueColumn, 0 )\n" +
+            "from targetTable_050 order by primaryKey\n";
+
+        vet_050
+            ( dboConnection, "IntArray", false, udtTargetValues, udtSourceValues,
+              udtMergeStatement, intExpectedValues, udtSelectStatement );
+        
+        //
+        // drop schema
+        //
+        goodStatement( dboConnection, "drop function lv_equals_050" );
+        goodStatement( dboConnection, "drop function clob_equals_050" );
+        goodStatement( dboConnection, "drop function vb" );
+        goodStatement( dboConnection, "drop function vb_reverse" );
+        goodStatement( dboConnection, "drop function vb_add" );
+        goodStatement( dboConnection, "drop function ch" );
+        goodStatement( dboConnection, "drop function ch_reverse" );
+        goodStatement( dboConnection, "drop function ch_add" );
+        goodStatement( dboConnection, "drop function vbl" );
+        goodStatement( dboConnection, "drop function vbl_reverse" );
+        goodStatement( dboConnection, "drop function vbl_add" );
+        goodStatement( dboConnection, "drop function vbl_equals" );
+        goodStatement( dboConnection, "drop function xmlX" );
+        goodStatement( dboConnection, "drop function mia" );
+        goodStatement( dboConnection, "drop function gc" );
+        goodStatement( dboConnection, "drop type IntArray restrict" );
+    }
+    private void    vet_050
+        (
+         Connection conn,
+         String datatype,
+         boolean    indexable,
+         String initialTargetValues,
+         String initialSourceValues,
+         String     mergeStatement,
+         String[][] expectedResults
+         )
+        throws Exception
+    {
+        String  selectStatement = "select * from targetTable_050 order by primaryKey";
+        vet_050( conn, datatype, indexable, initialTargetValues, initialSourceValues, mergeStatement, expectedResults, selectStatement );
+    }
+    private void    vet_050
+        (
+         Connection conn,
+         String datatype,
+         boolean    indexable,
+         String initialTargetValues,
+         String initialSourceValues,
+         String     mergeStatement,
+         String[][] expectedResults,
+         String selectStatement
+         )
+        throws Exception
+    {
+        goodStatement
+            (
+             conn,
+             "create table targetTable_050\n" +
+             "(\n" +
+             "    primaryKey int,\n" +
+             "    description varchar( 20 ),\n" +
+             "    onClauseColumn " + datatype + ",\n" +
+             "    matchingClauseColumn " + datatype + ",\n" +
+             "    valueColumn " + datatype + "\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "create table sourceTable_050\n" +
+             "(\n" +
+             "    onClauseColumn " + datatype + ",\n" +
+             "    matchingClauseColumn " + datatype + ",\n" +
+             "    valueColumn " + datatype + "\n" +
+             ")\n"
+             );
+
+        // no index
+        vet_050( conn, initialTargetValues, initialSourceValues, mergeStatement, expectedResults, selectStatement );
+
+        // add indexes and repeat
+        if ( indexable )
+        {
+            goodStatement( conn, "create index tt_050_idx on targetTable_050( onClauseColumn )" );
+            goodStatement( conn, "create index st_050_idx on sourceTable_050( onClauseColumn )" );
+            vet_050( conn, initialTargetValues, initialSourceValues, mergeStatement, expectedResults, selectStatement );
+        }
+
+        goodStatement( conn, "drop table targetTable_050" );
+        goodStatement( conn, "drop table sourceTable_050" );
+    }
+    private void    vet_050
+        (
+         Connection conn,
+         String     initialTargetValues,
+         String     initialSourceValues,
+         String     mergeStatement,
+         String[][]     expectedResults,
+         String     selectStatement
+         )
+        throws Exception
+    {
+        populate_050( conn, initialTargetValues, initialSourceValues );
+        goodUpdate( conn, mergeStatement, 3 );
+        assertResults( conn, selectStatement, expectedResults, true );
+    }
+    private void    populate_050
+        (
+         Connection conn,
+         String     initialTargetValues,
+         String     initialSourceValues
+         )
+        throws Exception
+    {
+        goodStatement( conn, "delete from targetTable_050" );
+        goodStatement( conn, "delete from sourceTable_050" );
+        goodStatement( conn, "insert into targetTable_050 values " + initialTargetValues );
+        goodStatement( conn, "insert into sourceTable_050 values " + initialSourceValues );
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // ROUTINES
@@ -7312,9 +8027,121 @@ public class MergeStatementTest extends 
         _triggerHistory.add( new String[] { actionString, Integer.toString( actionValue ) } );
     }
 
+    /** Function for comparing two long varchar values */
+    public  static  boolean equals( String left, String right )
+    {
+        if ( left == null ) { return false; }
+        else { return left.equals( right ); }
+    }
+
+    /** Function for comparing two clob values */
+    public  static  boolean equals( Clob left, Clob right )
+        throws SQLException
+    {
+        if ( left == null ) { return false; }
+        else if ( right == null ) { return false; }
+        else
+        {
+            String  leftString = left.getSubString( 1L, (int) left.length() );
+            String  rightString = right.getSubString( 1L, (int) right.length() );
+            return leftString.equals( rightString );
+        }
+    }
+
+    /** Function for comparing two blob values */
+    public  static  boolean equals( Blob left, Blob right )
+        throws SQLException
+    {
+        if ( left == null ) { return false; }
+        else if ( right == null ) { return false; }
+        else
+        {
+            return Arrays.equals( left.getBytes( 1L, (int) left.length() ), right.getBytes( 1L, (int) right.length() ) );
+        }
+    }
+
+    /** flip the order of bytes in an array */
+    public  static  byte[]  reverse( byte[] input )
+    {
+        if ( input == null ) { return null; }
+
+        int count = input.length;
+        byte[]  output = new byte[ count ];
+
+        for ( int i = 0; i < count; i++ ) { output[ ( count - i ) - 1 ] = input[ i ]; }
+
+        return output;
+    }
+
+    /** flip the order of bytes in a blob */
+    public  static  Blob  reverse( Blob inputBlob )
+        throws SQLException
+    {
+        if ( inputBlob == null ) { return null; }
+
+        return new HarmonySerialBlob( reverse( inputBlob.getBytes( 1L, (int) inputBlob.length() ) ) );
+    }
+
+    /** add the values of two byte arrays */
+    public  static  byte[]  add( byte[] left, byte[] right )
+    {
+        if ( left ==  null ) { return null; }
+        if ( right == null ) { return null; }
+
+        int     count = Integer.min( left.length, right.length );
+        byte[]  retval = new byte[ count ];
+
+        for ( int i = 0; i < count; i++ ) { retval[ i ] = (byte) (left[ i ] + right[ i ]); }
+
+        return retval;
+    }
+
+    /** add the values of two blobs */
+    public  static  Blob  add( Blob left, Blob right )
+        throws SQLException
+    {
+        if ( left ==  null ) { return null; }
+        if ( right == null ) { return null; }
+
+        return new HarmonySerialBlob
+            ( add( left.getBytes( 1L, (int) left.length() ), right.getBytes( 1L, (int) right.length() ) ) );
+    }
+
+    /** Function for making a byte array from an array of ints */
+    public  static  byte[]  makeByteArray( Integer... inputs )
+    {
+        if ( inputs == null )   { return null; }
+
+        byte[]  retval = new byte[ inputs.length ];
+        for ( int i = 0; i < inputs.length; i++ ) { retval[ i ] = (byte) inputs[ i ].intValue(); }
+
+        return retval;
+    }
+
+    /** Function for making a byte array from an array of ints */
+    public  static  Blob  makeBlob( Integer... inputs )
+    {
+        if ( inputs == null )   { return null; }
+
+        byte[]  retval = new byte[ inputs.length ];
+        for ( int i = 0; i < inputs.length; i++ ) { retval[ i ] = (byte) inputs[ i ].intValue(); }
+
+        return new HarmonySerialBlob( retval );
+    }
+
     /** Function for returning an arbitrary integer value */
     public  static  Integer nop( Integer value ) { return value; }
 
+    /** Extract the contents of an html document as an integer */
+    public  static  int xmlX( String doc ) throws Exception
+    {
+        int     startIdx = doc.indexOf( BEGIN_HTML ) + BEGIN_HTML.length();
+        int     endIdx = doc.indexOf( END_HTML );
+        String  number = doc.substring( startIdx, endIdx );
+
+        return Integer.parseInt( number );
+    }
+
     public  static  Connection  getNestedConnection()   throws SQLException
     {
         return DriverManager.getConnection( "jdbc:default:connection" );



Mime
View raw message