db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject 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/
Date Mon, 27 Jan 2014 13:36:12 GMT
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



Mime
View raw message