db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1567368 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Date Tue, 11 Feb 2014 20:41:08 GMT
Author: rhillegas
Date: Tue Feb 11 20:41:08 2014
New Revision: 1567368

URL: http://svn.apache.org/r1567368
Log:
DERBY-3155: Add support for ? parameters as INSERT values in MERGE statements; commit derby-3155-25-aa-parametersAsInsertValues.diff.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.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/MatchingClauseNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java?rev=1567368&r1=1567367&r2=1567368&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java
Tue Feb 11 20:41:08 2014
@@ -773,8 +773,17 @@ public class MatchingClauseNode extends 
          )
         throws StandardException
     {
-        bindInsertValues( fullFromList, targetTable );
+        ResultColumnList    selectList = new ResultColumnList( getContextManager() );
+        for ( int i = 0; i < _insertValues.size(); i++ )
+        {
+            ResultColumn    rc = _insertValues.elementAt( i );
+            selectList.addResultColumn( rc.cloneMe() );
+        }
+        selectList.replaceOrForbidDefaults( targetTable.getTableDescriptor(), _insertColumns,
true );
+        bindExpressions( selectList, fullFromList );
         
+        bindInsertValues( fullFromList, targetTable );
+
         // the VALUES clause may not mention columns in the target table
         FromList    sourceTableFromList = new FromList( getOptimizerFactory().doJoinOrderOptimization(),
getContextManager() );
         sourceTableFromList.addElement( fullFromList.elementAt( MergeNode.SOURCE_TABLE_INDEX
) );
@@ -782,7 +791,7 @@ public class MatchingClauseNode extends 
         
         SelectNode  selectNode = new SelectNode
             (
-             _insertValues,      // select list
+             selectList,      // select list
              fullFromList,
              null,      // where clause
              null,      // group by list

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=1567368&r1=1567367&r2=1567368&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
Tue Feb 11 20:41:08 2014
@@ -5244,6 +5244,117 @@ public class MergeStatementTest extends 
         goodStatement( dboConnection, "drop table t1_037" );
     }
     
+   /**
+     * <p>
+     * Verify that you can use ? parameters in all search conditions as well
+     * as in INSERT values and on the left side of SET operators.
+     * </p>
+     */
+    public  void    test_038_parameters()
+        throws Exception
+    {
+        Connection  dboConnection = openUserConnection( TEST_DBO );
+
+        //
+        // create schema
+        //
+        goodStatement
+            (
+             dboConnection,
+             "create table t1_038( x int, y int, z int )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create table t2_038( x int, y int, z int )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "insert into t1_038 values \n" +
+             "( 1, -1, 100 ), ( 2, -2, 200 ), ( 3, -3, 300 ), ( 5, -5, 500 ),\n" +
+             "( 100, -10, 100 ), ( 200, -20, 200 ), ( 300, -30, 300 ), ( 500, -50, 500 )\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "insert into t2_038 values\n" +
+             "( 10, -100, 1000 ), ( 30, -300, 3000), ( 40, -400, 4000 ), ( 40, -401, 4001
), ( 50, -500, 5000 ), ( 50, -501, 5001 ),\n" +
+             "( -1000, -10, 1000 ), ( -3000, -30, 3000), ( -4000, -40, 4000 ), ( -4000, -41,
4001 ),\n" +
+             "( -5000, -50, 5000 ), ( -5000, -51, 5001 )\n"
+             );
+
+        //
+        // now verify the correct operation of ? parameters in all clauses
+        //
+        PreparedStatement    ps = chattyPrepare
+            (
+             dboConnection,
+             "merge into t1_038\n" +
+             "using t2_038 on t2_038.x = t1_038.x * ?\n" +
+             "when not matched and t2_038.y = ? then insert values (  t2_038.x, t2_038.y,
t2_038.z * ? )\n" +
+             "when matched and t2_038.y = ? then delete\n" +
+             "when matched and t2_038.y = ? then update set z = t2_038.z * ?"  
+             );
+        
+        ps.setInt( 1, 10 );
+        ps.setInt( 2, -401 );
+        ps.setInt( 3, 2 );
+        ps.setInt( 4, -300 );
+        ps.setInt( 5, -501 );
+        ps.setInt( 6, 3 );
+        ps.execute();
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_038 order by x, y, z",
+             new String[][]
+             {
+                 { "1", "-1", "100" },
+                 { "2", "-2", "200" },
+                 { "5", "-5", "15003" },
+                 { "40", "-401", "8002" },
+                 { "100", "-10", "100" },
+                 { "200", "-20", "200" },
+                 { "300", "-30", "300" },
+                 { "500", "-50", "500" },
+             },
+             false
+             );
+
+        // verify that you can change the values
+        ps.setInt( 1, -10 );
+        ps.setInt( 2, -41 );
+        ps.setInt( 3, 3 );
+        ps.setInt( 4, -30 );
+        ps.setInt( 5, -51 );
+        ps.setInt( 6, 4 );
+        ps.execute();
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_038 order by x, y, z",
+             new String[][]
+             {
+                 { "-4000", "-41", "12003" },
+                 { "1", "-1", "100" },
+                 { "2", "-2", "200" },
+                 { "5", "-5", "15003" },
+                 { "40", "-401", "8002" },
+                 { "100", "-10", "100" },
+                 { "200", "-20", "200" },
+                 { "500", "-50", "20004" },
+             },
+             false
+             );
+
+        //
+        // drop schema
+        //
+        goodStatement( dboConnection, "drop table t2_038" );
+        goodStatement( dboConnection, "drop table t1_038" );
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // ROUTINES



Mime
View raw message