You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by rh...@apache.org on 2013/12/05 21:49:17 UTC
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
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