db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1547585 - 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 Tue, 03 Dec 2013 20:42:27 GMT
Author: rhillegas
Date: Tue Dec  3 20:42:26 2013
New Revision: 1547585

URL: http://svn.apache.org/r1547585
Log:
DERBY-3155: Improve handling of correlation names for the target tables in MERGE statements;
commit derby-3155-09-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=1547585&r1=1547584&r2=1547585&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 Dec  3 20:42:26 2013
@@ -186,6 +186,27 @@ public class MatchingClauseNode extends 
     //
     ///////////////////////////////////////////////////////////////////////////////////
 
+    /**
+     * <p>
+     * Replace references to the correlation name with the underlying table name
+     * in all ColumnReferences under all expressions. This replacement is
+     * done before the ColumnReferences are bound.
+     * </p>
+     */
+    public  void    replaceCorrelationName
+        (
+         MergeNode  parent,
+         String correlationName,
+         TableName  newTableName
+         )
+        throws StandardException
+    {
+        parent.replaceCorrelationName( correlationName, newTableName, _matchingRefinement
);
+        parent.replaceCorrelationName( correlationName, newTableName, _updateColumns );
+        parent.replaceCorrelationName( correlationName, newTableName, _insertColumns );
+        parent.replaceCorrelationName( correlationName, newTableName, _insertValues );
+    }
+    
     /** Bind this WHEN [ NOT ] MATCHED clause against the parent JoinNode */
     void    bind
         (

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=1547585&r1=1547584&r2=1547585&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 Tue Dec
 3 20:42:26 2013
@@ -198,6 +198,54 @@ public final class MergeNode extends DML
 	{
         DataDictionary  dd = getDataDictionary();
 
+        // source table must be a vti or base table
+        if (
+            !(_sourceTable instanceof FromVTI) &&
+            !(_sourceTable instanceof FromBaseTable)
+            )
+        {
+            throw StandardException.newException( SQLState.LANG_SOURCE_NOT_BASE_VIEW_OR_VTI
);
+        }
+
+        // source and target may not have the same correlation names
+        if ( getExposedName( _targetTable ).equals( getExposedName( _sourceTable ) ) )
+        {
+            throw StandardException.newException( SQLState.LANG_SAME_EXPOSED_NAME );
+        }
+
+        //
+        // Replace all references to a target correlation name 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
+                     );
+            }
+
+            _targetTable.correlationName = null;
+        }
+
         FromList    dummyFromList = new FromList( getContextManager() );
         FromBaseTable   dummyTargetTable = new FromBaseTable
             (
@@ -209,17 +257,12 @@ public final class MergeNode extends DML
              );
         FromTable       dummySourceTable = cloneSourceTable();
         
-        // source and target may not have the same correlation names
-        if ( getExposedName( dummyTargetTable ).equals( getExposedName( dummySourceTable
) ) )
-        {
-            throw StandardException.newException( SQLState.LANG_SAME_EXPOSED_NAME );
-        }
-
         dummyFromList.addFromTable( dummySourceTable );
         dummyFromList.addFromTable( dummyTargetTable );
         dummyFromList.bindTables( dd, new FromList( getOptimizerFactory().doJoinOrderOptimization(),
getContextManager() ) );
         
-        if ( !targetIsBaseTable( dummyTargetTable ) ) { notBaseTable(); }
+        // target table must be a base table
+        if ( !targetIsBaseTable( _targetTable ) ) { notBaseTable(); }
 
         for ( MatchingClauseNode mcn : _matchingClauses )
         {
@@ -236,6 +279,68 @@ public final class MergeNode extends DML
         }
 	}
 
+    /**
+     * <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>
+     */
+    public  void    replaceCorrelationName
+        (
+         String correlationName,
+         TableName  newTableName,
+         ResultColumnList   rcl
+         )
+        throws StandardException
+    {
+        if ( rcl == null ) { return; }
+        
+        for ( int i = 0; i < rcl.size(); i++ )
+        {
+            replaceCorrelationName( correlationName, newTableName, rcl.elementAt( i ) );
+        }
+    }
+    
+    /**
+     * <p>
+     * Replace references to the correlation name with the underlying table name
+     * in all ColumnReferences in the indicated expression. This replacement is
+     * done before the ColumnReferences are bound.
+     * </p>
+     */
+    public  void    replaceCorrelationName
+        (
+         String correlationName,
+         TableName  newTableName,
+         ValueNode  expression
+         )
+        throws StandardException
+    {
+        if ( expression == null ) { return; }
+        
+        CollectNodesVisitor<ColumnReference> getCRs =
+            new CollectNodesVisitor<ColumnReference>(ColumnReference.class);
+
+        expression.accept(getCRs);
+        List<ColumnReference> colRefs = getCRs.getList();
+
+        for ( ColumnReference cr : colRefs )
+        {
+            TableName   origTableName = cr.tableName;
+            if ( origTableName != null )
+            {
+                if (
+                    (origTableName.getSchemaName() == null) &&
+                    correlationName.equals( origTableName.getTableName() )
+                    )
+                {
+                    cr.setTableNameNode( newTableName );
+                }
+            }
+        }
+    }
+
     /** Get the exposed name of a FromTable */
     private String  getExposedName( FromTable ft ) throws StandardException
     {

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=1547585&r1=1547584&r2=1547585&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 Dec  3 20:42:26 2013
@@ -3421,7 +3421,7 @@ public class MergeStatementTest extends 
      * The CHECK constraint is satisfied but the foreign key is not.
      * </p>
      */
-    public  void    test_021_updateWithForeignPrimaryAndCheckConstraint()
+    public  void    test_022_updateWithForeignPrimaryAndCheckConstraint()
         throws Exception
     {
         Connection  dboConnection = openUserConnection( TEST_DBO );
@@ -3521,6 +3521,361 @@ public class MergeStatementTest extends 
     }
     
     ///////////////////////////////////////////////////////////////////////////////////
+
+    /**
+     * <p>
+     * Test correlation names in MERGE statements.
+     * </p>
+     */
+    public  void    test_023_correlationNames()
+        throws Exception
+    {
+        Connection  dboConnection = openUserConnection( TEST_DBO );
+
+        //
+        // create schema
+        //
+        goodStatement
+            (
+             dboConnection,
+             "create table t1_023\n" +
+             "(\n" +
+             "    a_public int primary key,\n" +
+             "    b_select_t1_ruth int,\n" +
+             "    c_select_t1_alice int,\n" +
+             "    d_select_t1_frank int,\n" +
+             "    e_update_t1_ruth int,\n" +
+             "    f_update_t1_alice int,\n" +
+             "    g_update_t1_frank int,\n" +
+             "    h_select_t1_ruth generated always as ( a_public )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create table t2_023\n" +
+             "(\n" +
+             "    a_public int primary key,\n" +
+             "    b_select_t2_ruth int,\n" +
+             "    c_select_t2_alice int,\n" +
+             "    d_select_t2_frank int,\n" +
+             "    e_select_t2_ruth generated always as ( a_public )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create table t3_023\n" +
+             "(\n" +
+             "    a int primary key,\n" +
+             "    b int,\n" +
+             "    c int,\n" +
+             "    d int,\n" +
+             "    e int,\n" +
+             "    f int,\n" +
+             "    g int,\n" +
+             "    h generated always as ( a )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create table t4_023\n" +
+             "(\n" +
+             "    a int primary key,\n" +
+             "    b int,\n" +
+             "    c int,\n" +
+             "    d int,\n" +
+             "    e generated always as ( a )\n" +
+             ")\n"
+             );
+
+        //
+        // Correlation names in DELETE actions
+        //
+        populate_023( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 a using test_dbo.t2_023 b\n" +
+             "on a.a_public = b.a_public\n" +
+             "when matched and a.b_select_t1_ruth = 11 then delete\n",
+             1
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "2", "12", "102", "1002", "10002", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+             },
+             false
+             );
+
+        //
+        // Correlation names in UPDATE actions
+        //
+        populate_023( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 a using test_dbo.t2_023 b\n" +
+             "on a.a_public = b.a_public\n" +
+             "when matched and a.b_select_t1_ruth = 12 then update set e_update_t1_ruth =
a.g_update_t1_frank + b.c_select_t2_alice\n",
+             1
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "1", "11", "101", "1001", "10001", "100001", "1000001", "1" },
+                 { "2", "12", "102", "1002", "1000104", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+             },
+             false
+             );
+
+        //
+        // Correlation names in INSERT actions
+        //
+        populate_023( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 a using test_dbo.t2_023 b\n" +
+             "on a.a_public = b.a_public\n" +
+             "when not matched and b.b_select_t2_ruth = 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 ( b.a_public, 18, 108, 1008, 10008, 100008, 1000008 )\n",
+             1
+             );
+        assertResults
+            (
+             dboConnection,
+             "select * from t1_023 order by a_public",
+             new String[][]
+             {
+                 { "1", "11", "101", "1001", "10001", "100001", "1000001", "1" },
+                 { "2", "12", "102", "1002", "10002", "100002", "1000002", "2" },
+                 { "3", "13", "103", "1003", "10003", "100003", "1000003", "3" },
+                 { "4", "18", "108", "1008", "10008", "100008", "1000008", "4" },
+             },
+             false
+             );
+
+        //
+        // Correlation names in all actions
+        //
+        populate_023( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 a using test_dbo.t2_023 b\n" +
+             "on a.a_public = b.a_public\n" +
+             "when matched and a.b_select_t1_ruth = 11 then delete\n" +
+             "when matched and a.b_select_t1_ruth = 12 then update set e_update_t1_ruth =
a.g_update_t1_frank + b.c_select_t2_alice\n" +
+             "when not matched and b.b_select_t2_ruth = 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 ( b.a_public, 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
+             );
+
+        //
+        // Correlation names only where needed.
+        //
+        populate_023( dboConnection );
+        goodUpdate
+            (
+             dboConnection,
+             "merge into test_dbo.t1_023 a using test_dbo.t2_023 b\n" +
+             "on a.a_public = b.a_public\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_select_t2_alice\n" +
+             "when not matched and b_select_t2_ruth = 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 ( b.a_public, 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
+             );
+
+        //
+        // Correlation names to remove ambiguities.
+        //
+        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
+             );
+
+        //
+        // drop schema
+        //
+        goodStatement( dboConnection, "drop table t4_023" );
+        goodStatement( dboConnection, "drop table t3_023" );
+        goodStatement( dboConnection, "drop table t2_023" );
+        goodStatement( dboConnection, "drop table t1_023" );
+    }
+    private void    populate_023( Connection conn ) throws Exception
+    {
+        goodStatement( conn, "delete from t2_023" );
+        goodStatement( conn, "delete from t1_023" );
+
+        goodStatement
+            (
+             conn,
+             "insert into t1_023\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\n" +
+             "( 1, 11, 101, 1001, 10001, 100001, 1000001 ),\n" +
+             "( 2, 12, 102, 1002, 10002, 100002, 1000002 ),\n" +
+             "( 3, 13, 103, 1003, 10003, 100003, 1000003 )\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t2_023\n" +
+             "(\n" +
+             "    a_public,\n" +
+             "    b_select_t2_ruth,\n" +
+             "    c_select_t2_alice,\n" +
+             "    d_select_t2_frank\n" +
+             ")\n" +
+             "values\n" +
+             "( 1, 11, 101, 1001 ),\n" +
+             "( 2, 12, 102, 1002 ),\n" +
+             "( 3, 13, 103, 1003 ),\n" +
+             "( 4, 14, 104, 1004 )\n"
+             );
+    }
+    private void    populate_023_2( Connection conn ) throws Exception
+    {
+        goodStatement( conn, "delete from t4_023" );
+        goodStatement( conn, "delete from t3_023" );
+
+        goodStatement
+            (
+             conn,
+             "insert into t3_023\n" +
+             "(\n" +
+             "    a,\n" +
+             "    b,\n" +
+             "    c,\n" +
+             "    d,\n" +
+             "    e,\n" +
+             "    f,\n" +
+             "    g\n" +
+             ")\n" +
+             "values\n" +
+             "( 1, 11, 101, 1001, 10001, 100001, 1000001 ),\n" +
+             "( 2, 12, 102, 1002, 10002, 100002, 1000002 ),\n" +
+             "( 3, 13, 103, 1003, 10003, 100003, 1000003 )\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t4_023\n" +
+             "(\n" +
+             "    a,\n" +
+             "    b,\n" +
+             "    c,\n" +
+             "    d\n" +
+             ")\n" +
+             "values\n" +
+             "( 1, 11, 101, 1001 ),\n" +
+             "( 2, 12, 102, 1002 ),\n" +
+             "( 3, 13, 103, 1003 ),\n" +
+             "( 4, 14, 104, 1004 )\n"
+             );
+    }
+    
+    ///////////////////////////////////////////////////////////////////////////////////
     //
     // ROUTINES
     //



Mime
View raw message