Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 39455 invoked from network); 10 Feb 2010 18:53:53 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 10 Feb 2010 18:53:53 -0000 Received: (qmail 15313 invoked by uid 500); 10 Feb 2010 18:53:53 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 15267 invoked by uid 500); 10 Feb 2010 18:53:53 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 15258 invoked by uid 99); 10 Feb 2010 18:53:53 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 10 Feb 2010 18:53:53 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 10 Feb 2010 18:53:50 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 0A72423889DD; Wed, 10 Feb 2010 18:53:28 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20100210185328.0A72423889DD@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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 @@ [ tok = | ] expression = nonJoinQueryTerm(term, (tok != null) ? INTERSECT_ALL_OP : INTERSECT_OP) { + if ( (tok != null) && (tok.kind == DISTINCT) ) { forbidNextValueFor(); } + return expression; } } @@ -5061,6 +5076,8 @@ ) { + forbidNextValueFor(); + return true; } | @@ -8086,6 +8103,8 @@ { 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 @@ + 42XAH + 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. + + + + 42XAI + The statement references the following sequence more than once: '{0}'. + sequenceName + + + 42Y00 Class '{0}' does not implement org.apache.derby.iapi.db.AggregateDefinition and thus cannot be used as an aggregate expression. className 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" ); + } + }