db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1548298 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java engine/org/apache/derby/impl/sql/compile/MergeNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Date Thu, 05 Dec 2013 20:49:17 GMT
Author: rhillegas
Date: Thu Dec  5 20:49:16 2013
New Revision: 1548298

URL: http://svn.apache.org/r1548298
Log:
DERBY-3155: Improve support for correlation names for the source tables of MERGE statements;
tests passed cleanly for me on derby-3155-10-aa-correlationNames.diff.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MergeNode.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=1548298&r1=1548297&r2=1548298&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
Thu Dec  5 20:49:16 2013
@@ -229,7 +229,19 @@ public class MatchingClauseNode extends 
     {
         if ( _matchingRefinement != null )
         {
-            mergeNode.bindExpression( _matchingRefinement, fullFromList );
+            FromList    fromList = fullFromList;
+
+            //
+            // For an INSERT action, the WHEN NOT MATCHED refinement can only
+            // mention columns in the source table.
+            //
+            if ( isInsertClause() )
+            {
+                fromList = new FromList( getOptimizerFactory().doJoinOrderOptimization(),
getContextManager() );
+                fromList.addElement( fullFromList.elementAt( MergeNode.SOURCE_TABLE_INDEX
) );
+            }
+
+            mergeNode.bindExpression( _matchingRefinement, fromList );
         }
     }
 
@@ -726,13 +738,9 @@ public class MatchingClauseNode extends 
         bindInsertValues( fullFromList, targetTable );
         
         // the VALUES clause may not mention columns in the target table
-        FromList    targetTableFromList = new FromList( getOptimizerFactory().doJoinOrderOptimization(),
getContextManager() );
-        targetTableFromList.addElement( fullFromList.elementAt( 0 ) );
-        bindExpressions( _insertValues, targetTableFromList );
-        if ( _matchingRefinement != null )
-        {
-            mergeNode.bindExpression( _matchingRefinement, targetTableFromList );
-        }
+        FromList    sourceTableFromList = new FromList( getOptimizerFactory().doJoinOrderOptimization(),
getContextManager() );
+        sourceTableFromList.addElement( fullFromList.elementAt( MergeNode.SOURCE_TABLE_INDEX
) );
+        bindExpressions( _insertValues, sourceTableFromList );
         
         SelectNode  selectNode = new SelectNode
             (

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=1548298&r1=1548297&r2=1548298&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 Thu Dec
 5 20:49:16 2013
@@ -130,10 +130,10 @@ public final class MergeNode extends DML
     //
     ///////////////////////////////////////////////////////////////////////////////////
 
-    private static  final   int SOURCE_TABLE_INDEX = 0;
-    private static  final   int TARGET_TABLE_INDEX = 1;
+    public  static  final   int SOURCE_TABLE_INDEX = 0;
+    public  static  final   int TARGET_TABLE_INDEX = 1;
 
-	private static final String TARGET_ROW_LOCATION_NAME = "###TargetRowLocation";
+	private static  final   String  TARGET_ROW_LOCATION_NAME = "###TargetRowLocation";
 
     ///////////////////////////////////////////////////////////////////////////////////
     //
@@ -214,38 +214,27 @@ public final class MergeNode extends DML
         }
 
         //
-        // Replace all references to a target correlation name with the actual
+        // Replace all references to correlation names with the actual
         // resolved table name.
         //
         FromList    dfl = new FromList( getContextManager() );
         dfl.addFromTable( _sourceTable );
         dfl.addFromTable( _targetTable );
         dfl.bindTables( dd, new FromList( getOptimizerFactory().doJoinOrderOptimization(),
getContextManager() ) );
-        if ( _targetTable.correlationName != null )
-        {
-            TableName   targetTableName = _targetTable.tableName;
-            String  correlationName = _targetTable.correlationName;
-            
-            replaceCorrelationName
-                (
-                correlationName,
-                 targetTableName,
-                 _searchCondition
-                 );
-            
-            for ( MatchingClauseNode mcn : _matchingClauses )
-            {
-                mcn.replaceCorrelationName
-                    (
-                     this,
-                     correlationName,
-                     targetTableName
-                     );
-            }
+        
+        replaceCorrelationName( _targetTable.correlationName, _targetTable.tableName );
+        _targetTable.correlationName = null;
 
-            _targetTable.correlationName = null;
+        if ( _sourceTable instanceof FromBaseTable )
+        {
+            TableName   sourceTableName = ((FromBaseTable) _sourceTable).tableName;
+            replaceCorrelationName( _sourceTable.correlationName, sourceTableName );
+            _sourceTable.correlationName = null;
         }
 
+        //
+        // Bind the WHEN [ NOT ] MATCHED clauses.
+        //
         FromList    dummyFromList = new FromList( getContextManager() );
         FromBaseTable   dummyTargetTable = new FromBaseTable
             (
@@ -282,6 +271,42 @@ public final class MergeNode extends DML
     /**
      * <p>
      * Replace references to the correlation name with the underlying table name
+     * in all ColumnReferences under all expressions. If the correlation name is null,
+     * then replace all references to the unqualified table name with the fully
+     * qualified table name. This replacement is
+     * done before the ColumnReferences are bound.
+     * </p>
+     */
+    private void    replaceCorrelationName
+        (
+         String correlationName,
+         TableName  newTableName
+         )
+        throws StandardException
+    {
+        if ( correlationName == null ) { correlationName = newTableName.getTableName(); }
+
+        replaceCorrelationName
+            (
+             correlationName,
+             newTableName,
+             _searchCondition
+             );
+            
+        for ( MatchingClauseNode mcn : _matchingClauses )
+        {
+            mcn.replaceCorrelationName
+                (
+                 this,
+                 correlationName,
+                 newTableName
+                 );
+        }
+    }
+    
+    /**
+     * <p>
+     * Replace references to the correlation name with the underlying table name
      * in all ColumnReferences under the indicated list of ResultColumns. This replacement
is
      * done before the ColumnReferences are bound.
      * </p>

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=1548298&r1=1548297&r2=1548298&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
Thu Dec  5 20:49:16 2013
@@ -3589,6 +3589,16 @@ public class MergeStatementTest extends 
              "    e generated always as ( a )\n" +
              ")\n"
              );
+        goodStatement
+            (
+             dboConnection,
+             "create function integerList_023()\n" +
+             "returns table( a int, b int, c int, d int )\n" +
+             "language java\n" +
+             "parameter style derby_jdbc_result_set\n" +
+             "no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.integerList_023'\n"
+             );
 
         //
         // Correlation names in DELETE actions
@@ -3787,8 +3797,277 @@ public class MergeStatementTest extends 
              );
 
         //
+        // No correlation names.
+        //
+        populate_023( dboConnection );
+        populate_023_2( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 using test_dbo.t4_023\n" +
+             "on a_public = a\n" +
+             "when matched and b_select_t1_ruth = 11 then delete\n" +
+             "when matched and b_select_t1_ruth = 12 then update set e_update_t1_ruth = g_update_t1_frank
+ c\n" +
+             "when not matched and b = 14 then insert\n" +
+             "(\n" +
+             "    a_public,\n" +
+             "    b_select_t1_ruth,\n" +
+             "    c_select_t1_alice,\n" +
+             "    d_select_t1_frank,\n" +
+             "    e_update_t1_ruth,\n" +
+             "    f_update_t1_alice,\n" +
+             "    g_update_t1_frank\n" +
+             ")\n" +
+             "values ( a, 18, 108, 1008, 10008, 100008, 1000008 )\n",
+             3
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "2", "12", "102", "1002", "1000104", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+                 { "4", "18", "108", "1008", "10008", "100008", "1000008", "4" },
+             },
+             false
+             );
+
+        //
+        // No correlation names. Columns are table-qualified, however.
+        //
+        populate_023( dboConnection );
+        populate_023_2( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 using test_dbo.t4_023\n" +
+             "on t1_023.a_public = t4_023.a\n" +
+             "when matched and t1_023.b_select_t1_ruth = 11 then delete\n" +
+             "when matched and t1_023.b_select_t1_ruth = 12 then update set e_update_t1_ruth
= t1_023.g_update_t1_frank + t4_023.c\n" +
+             "when not matched and t4_023.b = 14 then insert\n" +
+             "(\n" +
+             "    a_public,\n" +
+             "    b_select_t1_ruth,\n" +
+             "    c_select_t1_alice,\n" +
+             "    d_select_t1_frank,\n" +
+             "    e_update_t1_ruth,\n" +
+             "    f_update_t1_alice,\n" +
+             "    g_update_t1_frank\n" +
+             ")\n" +
+             "values ( t4_023.a, 18, 108, 1008, 10008, 100008, 1000008 )\n",
+             3
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "2", "12", "102", "1002", "1000104", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+                 { "4", "18", "108", "1008", "10008", "100008", "1000008", "4" },
+             },
+             false
+             );
+
+        //
+        // No correlation names. Tables aren't schema-qualified. Columns are table-qualified,
however.
+        //
+        populate_023( dboConnection );
+        populate_023_2( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into t1_023 using t4_023\n" +
+             "on t1_023.a_public = t4_023.a\n" +
+             "when matched and t1_023.b_select_t1_ruth = 11 then delete\n" +
+             "when matched and t1_023.b_select_t1_ruth = 12 then update set e_update_t1_ruth
= t1_023.g_update_t1_frank + t4_023.c\n" +
+             "when not matched and t4_023.b = 14 then insert\n" +
+             "(\n" +
+             "    a_public,\n" +
+             "    b_select_t1_ruth,\n" +
+             "    c_select_t1_alice,\n" +
+             "    d_select_t1_frank,\n" +
+             "    e_update_t1_ruth,\n" +
+             "    f_update_t1_alice,\n" +
+             "    g_update_t1_frank\n" +
+             ")\n" +
+             "values ( t4_023.a, 18, 108, 1008, 10008, 100008, 1000008 )\n",
+             3
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "2", "12", "102", "1002", "1000104", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+                 { "4", "18", "108", "1008", "10008", "100008", "1000008", "4" },
+             },
+             false
+             );
+
+        //
+        // With correlation names.
+        //
+        populate_023( dboConnection );
+        populate_023_2( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t3_023 a using test_dbo.t4_023 b\n" +
+             "on a.a = b.a\n" +
+             "when matched and a.b = 11 then delete\n" +
+             "when matched and a.b = 12 then update set e = a.g + b.c\n" +
+             "when not matched and b.b = 14 then insert\n" +
+             "(\n" +
+             "    a,\n" +
+             "    b,\n" +
+             "    c,\n" +
+             "    d,\n" +
+             "    e,\n" +
+             "    f,\n" +
+             "    g\n" +
+             ")\n" +
+             "values ( b.a, 18, 108, 1008, 10008, 100008, 1000008 )\n",
+             3
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t3_023 order by a",
+             new String[][]
+             {
+                 { "2", "12", "102", "1002", "1000104", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+                 { "4", "18", "108", "1008", "10008", "100008", "1000008", "4" },
+             },
+             false
+             );
+
+        //
+        // Source is a table function. Column names unambiguous and unqualified.
+        //
+        populate_023( dboConnection );
+        populate_023_2( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 using table( test_dbo.integerList_023() ) i\n" +
+             "on a_public = a\n" +
+             "when matched and b = 11 then delete\n" +
+             "when matched and b = 12 then update set e_update_t1_ruth = g_update_t1_frank
+ c\n" +
+             "when not matched and b = 14 then insert\n" +
+             "(\n" +
+             "    a_public,\n" +
+             "    b_select_t1_ruth,\n" +
+             "    c_select_t1_alice,\n" +
+             "    d_select_t1_frank,\n" +
+             "    e_update_t1_ruth,\n" +
+             "    f_update_t1_alice,\n" +
+             "    g_update_t1_frank\n" +
+             ")\n" +
+             "values ( a, 18, 108, 1008, 10008, 100008, 1000008 )\n",
+             3
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "2", "12", "102", "1002", "1000104", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+                 { "4", "18", "108", "1008", "10008", "100008", "1000008", "4" },
+             },
+             false
+             );
+
+        //
+        // Source is a table function. With correlation names. Tables are schema-qualified.
+        // Column names unambiguous but qualified.
+        //
+        populate_023( dboConnection );
+        populate_023_2( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 a using table( test_dbo.integerList_023() ) i\n"
+
+             "on a.a_public = i.a\n" +
+             "when matched and i.b = 11 then delete\n" +
+             "when matched and i.b = 12 then update set e_update_t1_ruth = a.g_update_t1_frank
+ i.c\n" +
+             "when not matched and i.b = 14 then insert\n" +
+             "(\n" +
+             "    a_public,\n" +
+             "    b_select_t1_ruth,\n" +
+             "    c_select_t1_alice,\n" +
+             "    d_select_t1_frank,\n" +
+             "    e_update_t1_ruth,\n" +
+             "    f_update_t1_alice,\n" +
+             "    g_update_t1_frank\n" +
+             ")\n" +
+             "values ( i.a, 18, 108, 1008, 10008, 100008, 1000008 )\n",
+             3
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "2", "12", "102", "1002", "1000104", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+                 { "4", "18", "108", "1008", "10008", "100008", "1000008", "4" },
+             },
+             false
+             );
+
+        //
+        // Source is a table function. With correlation names. Tables are not schema-qualified.
+        // Column names unambiguous but qualified.
+        //
+        populate_023( dboConnection );
+        populate_023_2( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into t1_023 a using table( integerList_023() ) i\n" +
+             "on a.a_public = i.a\n" +
+             "when matched and i.b = 11 then delete\n" +
+             "when matched and i.b = 12 then update set e_update_t1_ruth = a.g_update_t1_frank
+ i.c\n" +
+             "when not matched and i.b = 14 then insert\n" +
+             "(\n" +
+             "    a_public,\n" +
+             "    b_select_t1_ruth,\n" +
+             "    c_select_t1_alice,\n" +
+             "    d_select_t1_frank,\n" +
+             "    e_update_t1_ruth,\n" +
+             "    f_update_t1_alice,\n" +
+             "    g_update_t1_frank\n" +
+             ")\n" +
+             "values ( i.a, 18, 108, 1008, 10008, 100008, 1000008 )\n",
+             3
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "2", "12", "102", "1002", "1000104", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+                 { "4", "18", "108", "1008", "10008", "100008", "1000008", "4" },
+             },
+             false
+             );
+
+        //
         // drop schema
         //
+        goodStatement( dboConnection, "drop function integerList_023" );
         goodStatement( dboConnection, "drop table t4_023" );
         goodStatement( dboConnection, "drop table t3_023" );
         goodStatement( dboConnection, "drop table t2_023" );
@@ -3906,6 +4185,27 @@ public class MergeStatementTest extends 
         return new StringArrayVTI( TRIGGER_HISTORY_COLUMNS, rows );
     }
 
+    /** Table function for returning some tuples of ints */
+    public static IntegerArrayVTI integerList_023()
+    {
+        // A
+        // B
+        // C
+        // D
+        return new IntegerArrayVTI
+            (
+             new String[] { "A", "B", "C", "D" },
+             new int[][]
+             {
+                 new int[] { 1, 11, 101, 1001 },
+                 new int[] { 2, 12, 102, 1002 },
+                 new int[] { 3, 13, 103, 1003 },
+                 new int[] { 4, 14, 104, 1004 },
+                 new int[] { 5, 15, 105, 1005 },
+             }
+             );
+    }
+    
     /**
      * <p>
      * Trigger-called procedure for counting rows in a candidate table and then inserting



Mime
View raw message