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/07/10 14:18:14 UTC
svn commit: r1609450 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
shared/org/apache/derby/shared/common/reference/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: rhillegas
Date: Thu Jul 10 12:18:14 2014
New Revision: 1609450
URL: http://svn.apache.org/r1609450
Log:
DERBY-6652: Disable the use of views as the source data sets of MERGE statements; tests passed cleanly for me on derby-6652-01-aa-disableSourceViews.diff.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MergeNode.java
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.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=1609450&r1=1609449&r2=1609450&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 Jul 10 12:18:14 2014
@@ -346,7 +346,7 @@ public final class MergeNode extends DML
!(_sourceTable instanceof FromBaseTable)
)
{
- throw StandardException.newException( SQLState.LANG_SOURCE_NOT_BASE_VIEW_OR_VTI );
+ throw StandardException.newException( SQLState.LANG_SOURCE_NOT_BASE_OR_VTI );
}
// source and target may not have the same correlation names
@@ -473,7 +473,7 @@ public final class MergeNode extends DML
}
/** Return true if the source table is a base table, view, or table function */
- private boolean sourceIsBase_View_or_VTI() throws StandardException
+ private boolean sourceIsBase_or_VTI() throws StandardException
{
if ( _sourceTable instanceof FromVTI ) { return true; }
if ( !( _sourceTable instanceof FromBaseTable) ) { return false; }
@@ -487,7 +487,6 @@ public final class MergeNode extends DML
case TableDescriptor.BASE_TABLE_TYPE:
case TableDescriptor.SYSTEM_TABLE_TYPE:
case TableDescriptor.GLOBAL_TEMPORARY_TABLE_TYPE:
- case TableDescriptor.VIEW_TYPE:
return true;
default:
@@ -533,9 +532,9 @@ public final class MergeNode extends DML
_leftJoinFromList = _hojn.makeFromList( true, true );
_leftJoinFromList.bindTables( dd, new FromList( getOptimizerFactory().doJoinOrderOptimization(), getContextManager() ) );
- if ( !sourceIsBase_View_or_VTI() )
+ if ( !sourceIsBase_or_VTI() )
{
- throw StandardException.newException( SQLState.LANG_SOURCE_NOT_BASE_VIEW_OR_VTI );
+ throw StandardException.newException( SQLState.LANG_SOURCE_NOT_BASE_OR_VTI );
}
FromList topFromList = new FromList( getOptimizerFactory().doJoinOrderOptimization(), getContextManager() );
@@ -682,7 +681,7 @@ public final class MergeNode extends DML
}
else
{
- throw StandardException.newException( SQLState.LANG_SOURCE_NOT_BASE_VIEW_OR_VTI );
+ throw StandardException.newException( SQLState.LANG_SOURCE_NOT_BASE_OR_VTI );
}
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=1609450&r1=1609449&r2=1609450&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Thu Jul 10 12:18:14 2014
@@ -2318,7 +2318,7 @@ Guide.
<msg>
<name>42XAL</name>
- <text>The source table of a MERGE statement must be a base table, view, or table function.</text>
+ <text>The source table of a MERGE statement must be a base table or table function.</text>
</msg>
<msg>
Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=1609450&r1=1609449&r2=1609450&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Thu Jul 10 12:18:14 2014
@@ -955,7 +955,7 @@ public interface SQLState {
String LANG_SEQUENCE_REFERENCED_TWICE = "42XAI";
String LANG_DUPLICATE_CS_CLAUSE = "42XAJ";
String LANG_TARGET_NOT_BASE_TABLE = "42XAK";
- String LANG_SOURCE_NOT_BASE_VIEW_OR_VTI = "42XAL";
+ String LANG_SOURCE_NOT_BASE_OR_VTI = "42XAL";
String LANG_SAME_EXPOSED_NAME = "42XAM";
String LANG_NOT_NULL_CHARACTERISTICS = "42XAN";
String LANG_NO_SUBQUERIES_IN_MATCHED_CLAUSE = "42XAO";
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=1609450&r1=1609449&r2=1609450&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 Jul 10 12:18:14 2014
@@ -540,13 +540,15 @@ public class MergeStatementTest extends
"on t1.c1 = t2.c1\n" +
"when matched then delete\n"
);
- expectExecutionWarning
- ( dboConnection, NO_ROWS_AFFECTED,
- "merge into t1\n" +
- "using v2\n" +
- "on t1.c1 = v2.c1\n" +
- "when matched then delete\n"
- );
+ // allowed by sql standard, but we have problems using views
+ // as the source for MERGE statements with UPDATE clauses
+ // expectExecutionWarning
+ // ( dboConnection, NO_ROWS_AFFECTED,
+ // "merge into t1\n" +
+ // "using v2\n" +
+ // "on t1.c1 = v2.c1\n" +
+ // "when matched then delete\n"
+ // );
expectExecutionWarning
( dboConnection, NO_ROWS_AFFECTED,
"merge into t1\n" +
@@ -4692,7 +4694,10 @@ public class MergeStatementTest extends
goodStatement
(
dboConnection,
- "create view singlerow_028( x ) as values 1"
+ "create function singlerow_028() returns table\n" +
+ "( x int )\n" +
+ "language java parameter style derby_jdbc_result_set no sql\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.singlerow_028'\n"
);
goodStatement
(
@@ -4711,7 +4716,7 @@ public class MergeStatementTest extends
"referencing new as new\n" +
"for each row\n" +
"merge into t2_028\n" +
- "using singlerow_028 on t2_028.y = new.x\n" +
+ "using table( singlerow_028() ) sr on t2_028.y = new.x\n" +
"when not matched then insert ( y ) values ( new.x )\n"
);
@@ -4742,7 +4747,7 @@ public class MergeStatementTest extends
//
goodStatement( dboConnection, "drop table t1_028" );
goodStatement( dboConnection, "drop table t2_028" );
- goodStatement( dboConnection, "drop view singlerow_028" );
+ goodStatement( dboConnection, "drop function singlerow_028" );
}
/**
@@ -4869,72 +4874,73 @@ public class MergeStatementTest extends
/**
* <p>
- * Verify the fix to a query involving a view.
+ * Verify the fix to a query involving a view. This test was disabled when
+ * we disabled views as sources. See DERBY-6652.
* </p>
*/
- public void test_031_view()
- throws Exception
- {
- Connection dboConnection = openUserConnection( TEST_DBO );
-
- //
- // create schema
- //
- goodStatement
- (
- dboConnection,
- "create table t1_031(x int, y int)"
- );
- goodStatement
- (
- dboConnection,
- "create table tv_031(x int, y int)"
- );
- goodStatement
- (
- dboConnection,
- "create view v_031 as select * from tv_031"
- );
- goodStatement
- (
- dboConnection,
- "insert into t1_031 values ( 1, 100 ), ( 2, 200 ), ( 3, 300 )"
- );
- goodStatement
- (
- dboConnection,
- "insert into tv_031 values ( 1, 1000 ), ( 3, 3000 ), ( 4, 4000 )"
- );
-
- // verify the fix
- goodUpdate
- (
- dboConnection,
- "merge into t1_031\n" +
- "using v_031 on t1_031.x = v_031.x\n" +
- "when matched then update set t1_031.y = v_031.y\n",
- 2
- );
- assertResults
- (
- dboConnection,
- "select * from t1_031 order by x",
- new String[][]
- {
- { "1", "1000" },
- { "2", "200" },
- { "3", "3000" },
- },
- false
- );
-
- //
- // drop schema
- //
- goodStatement( dboConnection, "drop view v_031" );
- goodStatement( dboConnection, "drop table t1_031" );
- goodStatement( dboConnection, "drop table tv_031" );
- }
+ // public void test_031_view()
+ // throws Exception
+ // {
+ // Connection dboConnection = openUserConnection( TEST_DBO );
+ //
+ // //
+ // // create schema
+ // //
+ // goodStatement
+ // (
+ // dboConnection,
+ // "create table t1_031(x int, y int)"
+ // );
+ // goodStatement
+ // (
+ // dboConnection,
+ // "create table tv_031(x int, y int)"
+ // );
+ // goodStatement
+ // (
+ // dboConnection,
+ // "create view v_031 as select * from tv_031"
+ // );
+ // goodStatement
+ // (
+ // dboConnection,
+ // "insert into t1_031 values ( 1, 100 ), ( 2, 200 ), ( 3, 300 )"
+ // );
+ // goodStatement
+ // (
+ // dboConnection,
+ // "insert into tv_031 values ( 1, 1000 ), ( 3, 3000 ), ( 4, 4000 )"
+ // );
+ //
+ // // verify the fix
+ // goodUpdate
+ // (
+ // dboConnection,
+ // "merge into t1_031\n" +
+ // "using v_031 on t1_031.x = v_031.x\n" +
+ // "when matched then update set t1_031.y = v_031.y\n",
+ // 2
+ // );
+ // assertResults
+ // (
+ // dboConnection,
+ // "select * from t1_031 order by x",
+ // new String[][]
+ // {
+ // { "1", "1000" },
+ // { "2", "200" },
+ // { "3", "3000" },
+ // },
+ // false
+ // );
+ //
+ // //
+ // // drop schema
+ // //
+ // goodStatement( dboConnection, "drop view v_031" );
+ // goodStatement( dboConnection, "drop table t1_031" );
+ // goodStatement( dboConnection, "drop table tv_031" );
+ // }
/**
* <p>
@@ -5478,7 +5484,10 @@ public class MergeStatementTest extends
goodStatement
(
dboConnection,
- "create view sr_039( i ) as values 1"
+ "create function sr_039() returns table\n" +
+ "( i int )\n" +
+ "language java parameter style derby_jdbc_result_set no sql\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.singlerow_028'\n"
);
goodStatement
(
@@ -5501,7 +5510,7 @@ public class MergeStatementTest extends
(
dboConnection,
"merge into t1_039\n" +
- "using sr_039 on ( x = 1 )\n" +
+ "using table( sr_039() ) sr on ( x = 1 )\n" +
"when matched then delete\n",
1
);
@@ -5519,7 +5528,7 @@ public class MergeStatementTest extends
//
// drop schema
//
- goodStatement( dboConnection, "drop view sr_039" );
+ goodStatement( dboConnection, "drop function sr_039" );
goodStatement( dboConnection, "drop table t1_039" );
}
@@ -5539,7 +5548,10 @@ public class MergeStatementTest extends
goodStatement
(
dboConnection,
- "create view sr_040( i ) as values 1"
+ "create function sr_040() returns table\n" +
+ "( i int )\n" +
+ "language java parameter style derby_jdbc_result_set no sql\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.singlerow_028'\n"
);
goodStatement
(
@@ -5563,7 +5575,7 @@ public class MergeStatementTest extends
(
dboConnection,
"merge into t1_040\n" +
- "using sr_040 on ( x = 1 )\n" +
+ "using table( sr_040() ) sr on ( x = 1 )\n" +
"when matched and y = 101 then delete\n" +
"when matched and y = 102 then update set z = -1000\n" +
"when not matched and i > 1 then insert values ( -1, i, 0 )\n",
@@ -5587,7 +5599,7 @@ public class MergeStatementTest extends
(
dboConnection,
"merge into t1_040\n" +
- "using sr_040 on ( x = 3 )\n" +
+ "using table( sr_040() ) sr on ( x = 3 )\n" +
"when matched and y = 103 then delete\n" +
"when matched and y = 102 then update set z = -10000\n" +
"when not matched and i = 1 then insert values ( -1, i, 0 )\n",
@@ -5611,7 +5623,7 @@ public class MergeStatementTest extends
//
// drop schema
//
- goodStatement( dboConnection, "drop view sr_040" );
+ goodStatement( dboConnection, "drop function sr_040" );
goodStatement( dboConnection, "drop table t1_040" );
}
@@ -5631,7 +5643,10 @@ public class MergeStatementTest extends
goodStatement
(
dboConnection,
- "create view sr_041( i ) as values ( 1 ), ( 3 )"
+ "create function sr_041() returns table\n" +
+ "( i int )\n" +
+ "language java parameter style derby_jdbc_result_set no sql\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.MergeStatementTest.tworow_041'\n"
);
goodStatement
(
@@ -5688,7 +5703,7 @@ public class MergeStatementTest extends
expectExecutionError
( dboConnection, CARDINALITY_VIOLATION,
"merge into t1_041\n" +
- "using sr_041 on ( x = 1 )\n" +
+ "using table( sr_041() ) sr on ( x = 1 )\n" +
"when matched and y = 101 then delete\n" +
"when matched and y = 102 then update set z = -1000\n" +
"when not matched and i > 1 then insert values ( -1, i, 0 )\n"
@@ -5731,7 +5746,7 @@ public class MergeStatementTest extends
//
// drop schema
//
- goodStatement( dboConnection, "drop view sr_041" );
+ goodStatement( dboConnection, "drop function sr_041" );
goodStatement( dboConnection, "drop table t1_041" );
goodStatement( dboConnection, "drop table t2_041" );
goodStatement( dboConnection, "drop table t3_041" );
@@ -9543,6 +9558,35 @@ public class MergeStatementTest extends
);
}
+ /** Table function returning one row */
+ public static IntegerArrayVTI singlerow_028()
+ {
+ // A
+ return new IntegerArrayVTI
+ (
+ new String[] { "X" },
+ new int[][]
+ {
+ new int[] { 1 },
+ }
+ );
+ }
+
+ /** Table function returning one row */
+ public static IntegerArrayVTI tworow_041()
+ {
+ // A
+ return new IntegerArrayVTI
+ (
+ new String[] { "I" },
+ new int[][]
+ {
+ { 1 },
+ { 3 },
+ }
+ );
+ }
+
/**
* <p>
* Trigger-called procedure for counting rows in a candidate table and then inserting