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 2014/01/16 21:46:00 UTC
svn commit: r1558912 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/MergeNode.java
testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Author: rhillegas
Date: Thu Jan 16 20:46:00 2014
New Revision: 1558912
URL: http://svn.apache.org/r1558912
Log:
DERBY-3155: Allow system and temp tables in MERGE statements; commit derby-3155-13-aa-allowSystemAndTempTables.diff.
Modified:
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/MergeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MergeNode.java?rev=1558912&r1=1558911&r2=1558912&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 Jan 16 20:46:00 2014
@@ -520,7 +520,15 @@ public final class MergeNode extends DML
TableDescriptor desc = fbt.getTableDescriptor();
if ( desc == null ) { return false; }
- return ( desc.getTableType() == TableDescriptor.BASE_TABLE_TYPE );
+ switch( desc.getTableType() )
+ {
+ case TableDescriptor.BASE_TABLE_TYPE:
+ case TableDescriptor.GLOBAL_TEMPORARY_TABLE_TYPE:
+ return true;
+
+ default:
+ return false;
+ }
}
/** Return true if the source table is a base table, view, or table function */
@@ -536,6 +544,8 @@ public final class MergeNode extends DML
switch( desc.getTableType() )
{
case TableDescriptor.BASE_TABLE_TYPE:
+ case TableDescriptor.SYSTEM_TABLE_TYPE:
+ case TableDescriptor.GLOBAL_TEMPORARY_TABLE_TYPE:
case TableDescriptor.VIEW_TYPE:
return true;
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=1558912&r1=1558911&r2=1558912&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 Jan 16 20:46:00 2014
@@ -4375,6 +4375,132 @@ public class MergeStatementTest extends
goodStatement( dboConnection, "drop table t1_025" );
}
+ /**
+ * <p>
+ * Verify that MERGE works with system tables and global temporary tables.
+ * </p>
+ */
+ public void test_026_otherTableTypes()
+ throws Exception
+ {
+ Connection dboConnection = openUserConnection( TEST_DBO );
+
+ //
+ // create schema
+ //
+ goodStatement
+ (
+ dboConnection,
+ "create table t1_026( a int )"
+ );
+ goodStatement
+ (
+ dboConnection,
+ "declare global temporary table session.t2_temp_026( a int )\n" +
+ "on commit preserve rows not logged\n"
+ );
+
+ // allow system tables as source tables
+ goodStatement
+ (
+ dboConnection,
+ "merge into t1_026 t\n" +
+ "using sys.syscolumns c on t.a = c.columnNumber\n" +
+ "when not matched then insert ( a ) values ( c.columnNumber )\n"
+ );
+
+ // but don't allow system tables as target tables
+ expectCompilationError
+ ( dboConnection, TARGET_MUST_BE_BASE,
+ "merge into sys.syscolumns c\n" +
+ "using t1_026 t on t.a = c.columnNumber\n" +
+ "when not matched then insert ( columnNumber ) values ( t.a )\n"
+ );
+
+ // allow temporary tables as source tables
+ goodStatement
+ (
+ dboConnection,
+ "delete from t1_026"
+ );
+ goodStatement
+ (
+ dboConnection,
+ "insert into t1_026 values ( 1 )"
+ );
+ goodStatement
+ (
+ dboConnection,
+ "insert into session.t2_temp_026 values ( 1 ), ( 2 )"
+ );
+ goodUpdate
+ (
+ dboConnection,
+ "merge into t1_026\n" +
+ "using session.t2_temp_026 s on s.a = t1_026.a\n" +
+ "when not matched then insert values ( s.a )\n",
+ 1
+ );
+ assertResults
+ (
+ dboConnection,
+ "select * from t1_026 order by a",
+ new String[][]
+ {
+ { "1" },
+ { "2" },
+ },
+ false
+ );
+
+ // allow temporary tables as target tables
+ goodStatement
+ (
+ dboConnection,
+ "delete from t1_026"
+ );
+ goodStatement
+ (
+ dboConnection,
+ "delete from session.t2_temp_026"
+ );
+ goodStatement
+ (
+ dboConnection,
+ "insert into t1_026 values ( 1 ), ( 2 )"
+ );
+ goodStatement
+ (
+ dboConnection,
+ "insert into session.t2_temp_026 values ( 1 )"
+ );
+ goodUpdate
+ (
+ dboConnection,
+ "merge into session.t2_temp_026 s\n" +
+ "using t1_026 on t1_026.a = s.a\n" +
+ "when not matched then insert values ( t1_026.a )\n",
+ 1
+ );
+ assertResults
+ (
+ dboConnection,
+ "select * from session.t2_temp_026 order by a",
+ new String[][]
+ {
+ { "1" },
+ { "2" },
+ },
+ false
+ );
+
+ //
+ // drop schema
+ //
+ goodStatement( dboConnection, "drop table session.t2_temp_026" );
+ goodStatement( dboConnection, "drop table t1_026" );
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// ROUTINES