db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r908627 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functi...
Date Wed, 10 Feb 2010 18:51:51 GMT
Author: rhillegas
Date: Wed Feb 10 18:49:57 2010
New Revision: 908627

URL: http://svn.apache.org/viewvc?rev=908627&view=rev
Log:
DERBY-4513: Prevent NEXT VALUE FOR expressions from being used in various contexts.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    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/SequencePermsTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
Wed Feb 10 18:49:57 2010
@@ -33,6 +33,7 @@
 
 import org.apache.derby.iapi.sql.dictionary.AliasDescriptor;
 import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
+import org.apache.derby.iapi.sql.dictionary.SequenceDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 import org.apache.derby.iapi.sql.dictionary.PrivilegedSQLObject;
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
@@ -103,6 +104,8 @@
 	public  static  final   int			NON_DETERMINISTIC_ILLEGAL		=	0x00001000;
 	public  static  final   int			SQL_IN_ROUTINES_ILLEGAL		=	0x00002000;
 
+	public  static  final   int			NEXT_VALUE_FOR_ILLEGAL		=	0x00004000;
+
 	/** Standard SQL is legal */
 	public	static	final	int			SQL_LEGAL					=	(INTERNAL_SQL_ILLEGAL);
 
@@ -116,7 +119,8 @@
 																		    SUBQUERY_ILLEGAL |
 																			USER_ILLEGAL |
 																			SCHEMA_ILLEGAL |
-																			INTERNAL_SQL_ILLEGAL
+																			INTERNAL_SQL_ILLEGAL |
+                                                                            NEXT_VALUE_FOR_ILLEGAL
 																		  );
 
 	public	static	final	int			DEFAULT_RESTRICTION		= (
@@ -129,9 +133,16 @@
 	public	static	final	int			GENERATION_CLAUSE_RESTRICTION		= (
 		                                                                    CHECK_CONSTRAINT |
 																			NON_DETERMINISTIC_ILLEGAL |
-                                                                            SQL_IN_ROUTINES_ILLEGAL
+                                                                            SQL_IN_ROUTINES_ILLEGAL
|
+                                                                            NEXT_VALUE_FOR_ILLEGAL
 																			);
 
+	public	static	final	int			WHERE_CLAUSE_RESTRICTION		= NEXT_VALUE_FOR_ILLEGAL;
+	public	static	final	int			HAVING_CLAUSE_RESTRICTION		= NEXT_VALUE_FOR_ILLEGAL;
+	public	static	final	int			ON_CLAUSE_RESTRICTION		= NEXT_VALUE_FOR_ILLEGAL;
+	public	static	final	int			AGGREGATE_RESTRICTION		= NEXT_VALUE_FOR_ILLEGAL;
+	public	static	final	int			CONDITIONAL_RESTRICTION		= NEXT_VALUE_FOR_ILLEGAL;
+	public	static	final	int			GROUP_BY_RESTRICTION		= NEXT_VALUE_FOR_ILLEGAL;
 
 	/////////////////////////////////////////////////////////////////////////////////////
 	//
@@ -583,4 +594,15 @@
 	 * @return The list of required privileges.
 	 */
 	public List getRequiredPermissionsList();
+    
+	/**
+	 * Add a sequence descriptor to the list of referenced sequences.
+	 */
+	public void addReferencedSequence( SequenceDescriptor sd );
+
+	/**
+	 * Report whether the given sequence has been referenced already.
+	 */
+    public boolean isReferenced( SequenceDescriptor sd );
+
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java Wed
Feb 10 18:49:57 2010
@@ -278,10 +278,14 @@
 		/* Add ourselves to the aggregateVector before we do anything else */
 		aggregateVector.addElement(this);
 
+        CompilerContext cc = getCompilerContext();
+        
         // operand being null means a count(*)
 		if (operand != null)
 		{
+            int previousReliability = orReliability( CompilerContext.AGGREGATE_RESTRICTION
);
             bindOperand(fromList, subqueryList, aggregateVector);
+            cc.setReliability( previousReliability );
             
 			/*
 			** Make sure that we don't have an aggregate 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java
Wed Feb 10 18:49:57 2010
@@ -36,6 +36,7 @@
 
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
 import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
+import org.apache.derby.iapi.sql.dictionary.SequenceDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 import org.apache.derby.iapi.sql.dictionary.AliasDescriptor;
 import org.apache.derby.iapi.sql.dictionary.PermDescriptor;
@@ -162,6 +163,7 @@
 		returnParameterFlag = false;
 		initRequiredPriv();
 		defaultSchemaStack = null;
+        referencedSequences = null;
 	}
 
 	//
@@ -994,6 +996,23 @@
 		return list;
 	} // end of getRequiredPermissionsList
 
+	public void addReferencedSequence( SequenceDescriptor sd )
+    {
+        if ( referencedSequences == null ) { referencedSequences = new HashMap(); }
+
+        referencedSequences.put( sd.getUUID(), sd );
+    }
+
+	/**
+	 * Report whether the given sequence has been referenced already.
+	 */
+    public boolean isReferenced( SequenceDescriptor sd )
+    {
+        if ( referencedSequences == null ) { return false; }
+
+        return referencedSequences.containsKey( sd.getUUID() );
+    }
+
 	/*
 	** Context state must be reset in restContext()
 	*/
@@ -1049,4 +1068,5 @@
 	private HashMap requiredRoutinePrivileges;
 	private HashMap requiredUsagePrivileges;
 	private HashMap requiredRolePrivileges;
+    private HashMap referencedSequences;
 } // end of class CompilerContextImpl

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
Wed Feb 10 18:49:57 2010
@@ -29,6 +29,7 @@
 
 import org.apache.derby.iapi.error.StandardException;
 
+import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 
 import org.apache.derby.iapi.types.TypeId;
@@ -379,6 +380,10 @@
 		Vector	aggregateVector) 
 			throws StandardException
 	{
+        CompilerContext cc = getCompilerContext();
+        
+        int previousReliability = orReliability( CompilerContext.CONDITIONAL_RESTRICTION
);
+        
 		testCondition = testCondition.bindExpression(fromList,
 			subqueryList,
 			aggregateVector);
@@ -545,6 +550,8 @@
 			thenElseList.setElementAt(cast, 1);
 		}
 
+        cc.setReliability( previousReliability );
+        
 		return this;
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java Wed
Feb 10 18:49:57 2010
@@ -24,6 +24,7 @@
 import org.apache.derby.iapi.error.StandardException;
 
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.compile.Visitor;
 import org.apache.derby.iapi.sql.compile.Visitable;
 
@@ -103,9 +104,11 @@
 				throws StandardException
 	{
 		/* Bind the ColumnReference to the FromList */
+        int previousReliability = orReliability( CompilerContext.GROUP_BY_RESTRICTION );
 		columnExpression = (ValueNode) columnExpression.bindExpression(fromList,
 							  subqueryList,
 							  aggregateVector);
+        getCompilerContext().setReliability( previousReliability );
 
 		// Verify that we can group on the column
 		if (columnExpression.isParameterNode()) 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java Wed Feb
10 18:49:57 2010
@@ -29,6 +29,7 @@
 
 import org.apache.derby.iapi.error.StandardException;
 
+import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.compile.Optimizable;
 import org.apache.derby.iapi.sql.compile.OptimizablePredicate;
 import org.apache.derby.iapi.sql.compile.OptimizablePredicateList;
@@ -806,6 +807,8 @@
 											getContextManager());
 		aggregateVector = new Vector();
 
+        CompilerContext cc = getCompilerContext();
+        
 		/* ON clause */
 		if (joinClause != null)
 		{
@@ -821,9 +824,11 @@
 			fromList.addElement((FromTable) leftResultSet);
 			fromList.addElement((FromTable) rightResultSet);
 
+            int previousReliability = orReliability( CompilerContext.ON_CLAUSE_RESTRICTION
);
 			joinClause = joinClause.bindExpression(
 									  fromList, subqueryList,
 									  aggregateVector);
+            cc.setReliability( previousReliability );
 
 			// SQL 2003, section 7.7 SR 5
 			SelectNode.checkNoWindowFunctions(joinClause, "ON");

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
Wed Feb 10 18:49:57 2010
@@ -26,6 +26,7 @@
 import org.apache.derby.iapi.services.sanity.SanityManager;
 import org.apache.derby.iapi.services.compiler.MethodBuilder;
 import org.apache.derby.iapi.services.compiler.LocalField;
+import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.services.classfile.VMOpcode;
 import org.apache.derby.iapi.sql.dictionary.SequenceDescriptor;
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
@@ -66,7 +67,14 @@
     public ValueNode bindExpression(
             FromList fromList, SubqueryList subqueryList,
             Vector aggregateVector, boolean forQueryRewrite)
-            throws StandardException {
+            throws StandardException
+    {
+        CompilerContext cc = getCompilerContext();
+        
+        if ( (cc.getReliability() & CompilerContext.NEXT_VALUE_FOR_ILLEGAL) != 0 )
+        {
+            throw StandardException.newException( SQLState.LANG_NEXT_VALUE_FOR_ILLEGAL );
+        }
 
         // lookup sequence object in the data dictionary
         SchemaDescriptor sd = getSchemaDescriptor(sequenceName.getSchemaName());
@@ -80,6 +88,17 @@
         // set the datatype of the value node
         this.setType(sequenceDescriptor.getDataType());
 
+        //
+        // The statement is only allowed to refer to a given sequence once.
+        // See DERBY-4513.
+        //
+        if ( cc.isReferenced( sequenceDescriptor ) )
+        {
+            throw StandardException.newException
+                ( SQLState.LANG_SEQUENCE_REFERENCED_TWICE, sequenceName.getFullTableName()
);
+        }
+        cc.addReferencedSequence( sequenceDescriptor );
+
         ValueNode returnNode = this;
 
         // set up dependency on sequence and compile a check for USAGE

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Wed
Feb 10 18:49:57 2010
@@ -1768,6 +1768,20 @@
 		throw StandardException.newException(sqlState, fragmentType);
     }
 
+    /**
+     * OR in more reliability bits and return the old reliability value.
+     */
+    public int orReliability( int newBits )
+    {
+        CompilerContext cc = getCompilerContext();
+        
+        int previousReliability = cc.getReliability();
+
+        cc.setReliability( previousReliability | newBits );
+
+        return previousReliability;
+    }
+
 
     /**
      * Bind the parameters of OFFSET n ROWS and FETCH FIRST n ROWS ONLY, if

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Wed
Feb 10 18:49:57 2010
@@ -30,6 +30,7 @@
 
 import org.apache.derby.iapi.sql.conn.Authorizer;
 
+import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 
@@ -573,12 +574,18 @@
 		whereSubquerys = (SubqueryList) getNodeFactory().getNode(
 												C_NodeTypes.SUBQUERY_LIST,
 												getContextManager());
+        
+        CompilerContext cc = getCompilerContext();
+        
 		if (whereClause != null)
 		{
-			getCompilerContext().pushCurrentPrivType( Authorizer.SELECT_PRIV);
+			cc.pushCurrentPrivType( Authorizer.SELECT_PRIV);
+
+            int previousReliability = orReliability( CompilerContext.WHERE_CLAUSE_RESTRICTION
);
 			whereClause = whereClause.bindExpression(fromListParam, 
 										whereSubquerys,
 										whereAggregates);
+            cc.setReliability( previousReliability );
 			
 			/* RESOLVE - Temporarily disable aggregates in the HAVING clause.
 			** (We may remove them in the parser anyway.)
@@ -605,7 +612,10 @@
 			checkNoWindowFunctions(whereClause, "WHERE");
 		}
 
-		if (havingClause != null) {
+		if (havingClause != null)
+        {
+            int previousReliability = orReliability( CompilerContext.HAVING_CLAUSE_RESTRICTION
);
+
 			havingAggregates = new Vector();
 			havingSubquerys = (SubqueryList) getNodeFactory().getNode(
 					C_NodeTypes.SUBQUERY_LIST,
@@ -614,6 +624,8 @@
 					fromListParam, havingSubquerys, havingAggregates);
 			havingClause = havingClause.checkIsBoolean();
 			checkNoWindowFunctions(havingClause, "HAVING");
+            
+            cc.setReliability( previousReliability );
 		}
 		
 		/* Restore fromList */

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Wed Feb
10 18:49:57 2010
@@ -341,6 +341,15 @@
 											"TRUE");
 	}
 
+    /**
+		Prevent NEXT VALUE FOR clauses when we get to the bind() phase.
+    */
+	private void forbidNextValueFor()
+	{
+        CompilerContext cc = getCompilerContext();
+
+        cc.setReliability( cc.getReliability() | CompilerContext.NEXT_VALUE_FOR_ILLEGAL );
+	}
 
 	/**
 		Check that the current mode supports internal extensions.
@@ -4855,6 +4864,8 @@
 				queryExpression(term,
 								(tok != null) ? UNION_ALL_OP : UNION_OP)
 	{
+        if ( (tok != null) && (tok.kind == DISTINCT) ) { forbidNextValueFor(); }
+        
 		return expression;
 	}
 |
@@ -4862,6 +4873,8 @@
 				queryExpression(term,
 								(tok != null) ? EXCEPT_ALL_OP : EXCEPT_OP)
 	{
+        if ( (tok != null) && (tok.kind == DISTINCT) ) { forbidNextValueFor(); }
+        
 		return expression;
 	}
 }
@@ -4975,6 +4988,8 @@
 	<INTERSECT> [ tok = <ALL> | <DISTINCT> ] expression =
 				nonJoinQueryTerm(term, (tok != null) ? INTERSECT_ALL_OP : INTERSECT_OP)
 	{
+        if ( (tok != null) && (tok.kind == DISTINCT) ) { forbidNextValueFor(); }
+        
 		return expression;
 	}
 }
@@ -5061,6 +5076,8 @@
 	)
 	<DISTINCT>
 	{
+        forbidNextValueFor();
+        
 		return true;
 	}
 |
@@ -8086,6 +8103,8 @@
 {
 	<ORDER> <BY> orderCols = sortSpecificationList()
 	{
+        forbidNextValueFor();
+        
 		return orderCols;
 	}
 }

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=908627&r1=908626&r2=908627&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 Wed Feb 10 18:49:57
2010
@@ -2117,6 +2117,17 @@
             </msg>
 
             <msg>
+                <name>42XAH</name>
+                <text>A NEXT VALUE FOR expression may not appear in many contexts,
including WHERE, ON, HAVING, ORDER BY, DISTINCT, CASE, GENERATION, and AGGREGATE clauses as
well as WINDOW functions and CHECK constraints.</text>
+            </msg>
+
+            <msg>
+                <name>42XAI</name>
+                <text>The statement references the following sequence more than once:
'{0}'.</text>
+                <arg>sequenceName</arg>
+            </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>
                 <arg>className</arg>

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=908627&r1=908626&r2=908627&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
Wed Feb 10 18:49:57 2010
@@ -917,6 +917,8 @@
     String LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE                          = "42XAE";
     String LANG_SEQ_MIN_EXCEEDS_MAX                                    = "42XAF";
     String LANG_SEQ_INVALID_START                                      = "42XAG";    
+    String LANG_NEXT_VALUE_FOR_ILLEGAL                                      = "42XAH";  
 
+    String LANG_SEQUENCE_REFERENCED_TWICE                                      = "42XAI";
   
     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/SequencePermsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
Wed Feb 10 18:49:57 2010
@@ -286,19 +286,22 @@
              );
 
         // constraint
-        createStatement = "create table t_02( c int check ( ( next value for ruth.seq_02
) < c ) )\n";
-        dropStatement = "drop table t_02\n";
-        badRevokeSQLState = OPERATION_FORBIDDEN;
-        verifyRevokePrivilege
-            (
-             ruthConnection,
-             frankConnection,
-             grantUsage,
-             revokeUsage,
-             createStatement,
-             dropStatement,
-             badRevokeSQLState
-             );
+        //
+        // no longer possible because syntax is illegal. see DERBY-4513
+        //
+        //        createStatement = "create table t_02( c int check ( ( next value for ruth.seq_02
) < c ) )\n";
+        //        dropStatement = "drop table t_02\n";
+        //        badRevokeSQLState = OPERATION_FORBIDDEN;
+        //        verifyRevokePrivilege
+        //            (
+        //             ruthConnection,
+        //             frankConnection,
+        //             grantUsage,
+        //             revokeUsage,
+        //             createStatement,
+        //             dropStatement,
+        //             badRevokeSQLState
+        //             );
 
         
     }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java?rev=908627&r1=908626&r2=908627&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java
Wed Feb 10 18:49:57 2010
@@ -297,5 +297,69 @@
         s.execute("SELECT NEXT VALUE FOR mySeq1 from sys.systables");
     }
 
+    /**
+     * Verify that sequences can't be used in many contexts.
+     */
+    public void test_11_forbiddenContexts() throws Exception
+    {
+        Connection conn = openUserConnection(ALPHA);
+
+        goodStatement( conn, "create sequence seq_11_a\n" );
+        goodStatement( conn, "create sequence seq_11_b\n" );
+
+        String illegalSequence = SQLState.LANG_NEXT_VALUE_FOR_ILLEGAL;
+        
+        // sequences not allowed in WHERE clause
+        expectCompilationError( conn, illegalSequence, "select * from sys.systables where
( next value for seq_11_a ) > 100\n" );
+
+        // sequences not allowed in HAVING clause
+        expectCompilationError
+            ( conn, illegalSequence,
+              "select max( conglomeratenumber ), tableid\n" +
+              "from sys.sysconglomerates\n" +
+              "group by tableid\n" +
+              "having max( conglomeratenumber ) > ( next value for seq_11_a )\n"
+              );
+        
+        // sequences not allowed in ON clause
+        expectCompilationError
+            ( conn, illegalSequence, "select * from sys.sysconglomerates left join sys.sysschemas
on conglomeratenumber = ( next value for seq_11_a )\n" );
+
+        // sequences not allowed in CHECK constraints
+        expectCompilationError
+            ( conn, illegalSequence, "create table t_11_1( a int check ( a > ( next value
for seq_11_a ) ) )\n" );
+
+        // sequences not allowed in generated columns
+        expectCompilationError
+            ( conn, illegalSequence, "create table t_11_1( a int, b generated always as (
a + ( next value for seq_11_a ) ) )\n" );
+
+        // sequences not allowed in aggregates
+        expectCompilationError
+            ( conn, illegalSequence, "select max( next value for seq_11_a ) from sys.systables\n"
);
+
+        // sequences not allowed in CASE expressions
+        expectCompilationError
+            ( conn, illegalSequence, "values case when ( next value for seq_11_a ) < 0
then 100 else 200 end\n" );
+
+        // sequences not allowed in DISTINCT clauses
+        expectCompilationError
+            ( conn, illegalSequence, "select distinct( next value for seq_11_a ) from sys.systables\n"
);
+
+        // sequences not allowed in ORDER BY clauses
+        expectCompilationError
+            ( conn, illegalSequence, "select tableid, ( next value for seq_11_a ) a from
sys.systables order by a\n" );
+
+        // sequences not allowed in GROUP BY expressions
+        expectCompilationError
+            ( conn, illegalSequence, "select max( tableid ), ( next value for seq_11_a )
from sys.systables group by ( next value for seq_11_a )\n" );
+
+        // given sequence only allowed once per statement. see DERBY-4513.
+        expectCompilationError
+            ( conn, SQLState.LANG_SEQUENCE_REFERENCED_TWICE, "select next value for seq_11_a,
next value for seq_11_a from sys.systables where 1=2\n" );
+
+        // however, two different sequences can appear in a statement
+        goodStatement( conn, "select next value for seq_11_a, next value for seq_11_b from
sys.systables where 1=2\n" );
+    }
+
 
 }



Mime
View raw message