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/27 14:36:12 UTC
svn commit: r1561671 - 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: Mon Jan 27 13:36:12 2014
New Revision: 1561671
URL: http://svn.apache.org/r1561671
Log:
DERBY-3155: Forbid subqueries in the WHEN [ NOT ] MATCHED clauses of MERGE statements; commit derby-3155-19-aa-forbidSubqueriesInMatchedClauses.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/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/MatchingClauseNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java?rev=1561671&r1=1561670&r2=1561671&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 Mon Jan 27 13:36:12 2014
@@ -248,6 +248,15 @@ public class MatchingClauseNode extends
)
throws StandardException
{
+ //
+ // Although the SQL Standard allows subqueries in the WHEN [ NOT ] MATCHED clauses,
+ // we don't support them yet. That is because code-generation for those clauses breaks
+ // if they contain subqueries. That, in turn, is because we don't completely optimize those
+ // clauses. If we improve Derby so that we do completely optimize the WHEN [ NOT ] MATCHED clauses,
+ // then we can consider enabling subqueries in them.
+ //
+ forbidSubqueries();
+
_thenColumns = new ResultColumnList( getContextManager() );
if ( isDeleteClause() ) { bindDelete( dd, fullFromList, targetTable ); }
@@ -1116,6 +1125,45 @@ public class MatchingClauseNode extends
return -1;
}
+ /**
+ * <p>
+ * Forbid subqueries in WHEN [ NOT ] MATCHED clauses.
+ * </p>
+ */
+ private void forbidSubqueries()
+ throws StandardException
+ {
+ forbidSubqueries( _matchingRefinement );
+ forbidSubqueries( _updateColumns );
+ forbidSubqueries( _insertColumns );
+ forbidSubqueries( _insertValues );
+ }
+ private void forbidSubqueries( ResultColumnList rcl )
+ throws StandardException
+ {
+ if ( rcl != null )
+ {
+ for ( int i = 0; i < rcl.size(); i++ )
+ {
+ forbidSubqueries( rcl.elementAt( i ) );
+ }
+ }
+ }
+ private void forbidSubqueries( ValueNode expr )
+ throws StandardException
+ {
+ if ( expr != null )
+ {
+ CollectNodesVisitor<SubqueryNode> getSubqueries =
+ new CollectNodesVisitor<SubqueryNode>(SubqueryNode.class);
+ expr.accept( getSubqueries );
+ if ( getSubqueries.getList().size() > 0 )
+ {
+ throw StandardException.newException( SQLState.LANG_NO_SUBQUERIES_IN_MATCHED_CLAUSE );
+ }
+ }
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// optimize() BEHAVIOR
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=1561671&r1=1561670&r2=1561671&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 Mon Jan 27 13:36:12 2014
@@ -2281,11 +2281,16 @@ Guide.
<text>The source and target tables of a MERGE statement may not have the same correlation name.</text>
</msg>
- <msg>
+ <msg>
<name>42XAN</name>
<text>Constraint characteristics not allowed for NOT NULL.</text>
</msg>
+ <msg>
+ <name>42XAO</name>
+ <text>Subqueries are not allowed in the WHEN [ NOT ] MATCHED clauses of MERGE statements.</text>
+ </msg>
+
<msg>
<name>42Y00</name>
<text>Class '{0}' does not implement org.apache.derby.iapi.db.AggregateDefinition and thus cannot be used as an aggregate expression.</text>
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=1561671&r1=1561670&r2=1561671&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 Mon Jan 27 13:36:12 2014
@@ -950,6 +950,7 @@ public interface SQLState {
String LANG_SOURCE_NOT_BASE_VIEW_OR_VTI = "42XAL";
String LANG_SAME_EXPOSED_NAME = "42XAM";
String LANG_NOT_NULL_CHARACTERISTICS = "42XAN";
+ String LANG_NO_SUBQUERIES_IN_MATCHED_CLAUSE = "42XAO";
String LANG_INVALID_USER_AGGREGATE_DEFINITION2 = "42Y00";
String LANG_INVALID_CHECK_CONSTRAINT = "42Y01";
// String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE = "42Y02";
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=1561671&r1=1561670&r2=1561671&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 Mon Jan 27 13:36:12 2014
@@ -71,6 +71,7 @@ public class MergeStatementTest extends
private static final String MISSING_TABLE = "42X05";
private static final String NO_ROWS_AFFECTED = "02000";
private static final String NO_DML_IN_BEFORE_TRIGGERS = "42Z9D";
+ private static final String NO_SUBQUERIES_IN_MATCHED_CLAUSE = "42XAO";
private static final String[] TRIGGER_HISTORY_COLUMNS = new String[] { "ACTION", "ACTION_VALUE" };
@@ -4825,6 +4826,78 @@ public class MergeStatementTest extends
goodStatement( dboConnection, "drop table tv_031" );
}
+ /**
+ * <p>
+ * For the time being, forbid subqueries in WHEN [ NOT ] MATCHED clauses.
+ * </p>
+ */
+ public void test_032_noSubqueriesInMatchedClauses()
+ throws Exception
+ {
+ Connection dboConnection = openUserConnection( TEST_DBO );
+
+ //
+ // create schema
+ //
+ goodStatement
+ (
+ dboConnection,
+ "create table t1_032( a int )"
+ );
+ goodStatement
+ (
+ dboConnection,
+ "create table t2_032( a int )"
+ );
+ goodStatement
+ (
+ dboConnection,
+ "create table t3_032( a int )"
+ );
+
+ //
+ // All of these statements should be rejected because they carry
+ // subqueries in the WHEN [ NOT ] MATCHED clause.
+ //
+ expectCompilationError
+ ( dboConnection, NO_SUBQUERIES_IN_MATCHED_CLAUSE,
+ "merge into t1_032\n" +
+ "using t2_032 on t1_032.a = t2_032.a\n" +
+ "when matched and t1_032.a > (select max( a ) from t3_032 ) then update set a = t2_032.a * 2\n"
+ );
+ expectCompilationError
+ ( dboConnection, NO_SUBQUERIES_IN_MATCHED_CLAUSE,
+ "merge into t1_032\n" +
+ "using t2_032 on t1_032.a = t2_032.a\n" +
+ "when matched then update set a = (select max( a ) from t3_032 )\n"
+ );
+ expectCompilationError
+ ( dboConnection, NO_SUBQUERIES_IN_MATCHED_CLAUSE,
+ "merge into t1_032\n" +
+ "using t2_032 on t1_032.a = t2_032.a\n" +
+ "when matched and t1_032.a > (select max( a ) from t3_032 ) then delete\n"
+ );
+ expectCompilationError
+ ( dboConnection, NO_SUBQUERIES_IN_MATCHED_CLAUSE,
+ "merge into t1_032\n" +
+ "using t2_032 on t1_032.a = t2_032.a\n" +
+ "when not matched and t1_032.a > (select max( a ) from t3_032 ) then insert values ( 1 )\n"
+ );
+ expectCompilationError
+ ( dboConnection, NO_SUBQUERIES_IN_MATCHED_CLAUSE,
+ "merge into t1_032\n" +
+ "using t2_032 on t1_032.a = t2_032.a\n" +
+ "when not matched then insert values ( (select max( a ) from t3_032 ) )\n"
+ );
+
+ //
+ // drop schema
+ //
+ goodStatement( dboConnection, "drop table t3_032" );
+ goodStatement( dboConnection, "drop table t2_032" );
+ goodStatement( dboConnection, "drop table t1_032" );
+ }
+
///////////////////////////////////////////////////////////////////////////////////
//
// ROUTINES