db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r754558 - in /db/derby/code/trunk: java/engine/org/apache/derby/iapi/sql/compile/ java/engine/org/apache/derby/iapi/sql/execute/ java/engine/org/apache/derby/impl/sql/compile/ java/engine/org/apache/derby/impl/sql/execute/ java/engine/org/a...
Date Sat, 14 Mar 2009 23:45:06 GMT
Author: dag
Date: Sat Mar 14 23:45:04 2009
New Revision: 754558

URL: http://svn.apache.org/viewvc?rev=754558&view=rev
Log:
DERBY-4079 Add support for SQL:2008 <result offset clause> and <fetch first clause> to limit result set cardinality

Added this new feature, corresponding to patch derby-4079-3. Documentation is committed separately.

Added:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealRowCountStatistics.java   (with props)
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java   (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLStatementNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RealResultSetStatisticsFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.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/tools/jar/DBMSnodes.properties

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java Sat Mar 14 23:45:04 2009
@@ -233,8 +233,11 @@
     // generated columns
     static final int GENERATION_CLAUSE_NODE = 222;
 
+	// OFFSET, FETCH FIRST node
+	static final int ROW_COUNT_NODE = 223;
+
     // Final value in set, keep up to date!
-    static final int FINAL_VALUE = GENERATION_CLAUSE_NODE;
+    static final int FINAL_VALUE = ROW_COUNT_NODE;
 
     /**
      * Extensions to this interface can use nodetypes > MAX_NODE_TYPE with out fear of collision

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java Sat Mar 14 23:45:04 2009
@@ -1608,4 +1608,34 @@
 									int fkColArrayItem,
 									int rltItem)
 		throws StandardException;
+
+
+	/**
+	 * This result sets implements the filtering needed by <result offset
+	 * clause> and <fetch first clause>. It is only ever generated if at least
+	 * one of the two clauses is present.
+	 *
+	 * @param source          The source result set being filtered
+	 * @param activation      The activation for this result set,
+	 *		                  which provides the context for the row
+	 *                        allocation operation
+	 * @param resultSetNumber The resultSetNumber for the ResultSet
+	 * @param offset          The offset value (0 by default)
+	 * @param fetchFirst      The fetch first value (-1 if not in use)
+	 * @param optimizerEstimatedRowCount
+	 *                        Estimated total # of rows by optimizer
+	 * @param optimizerEstimatedCost
+	 *                        Estimated total cost by optimizer
+	 * @exception StandardException Standard error policy
+	 */
+
+	public NoPutResultSet getRowCountResultSet(
+		NoPutResultSet source,
+		Activation activation,
+		int resultSetNumber,
+		long offset,
+		long fetchFirst,
+		double optimizerEstimatedRowCount,
+		double optimizerEstimatedCost) throws StandardException;
+
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java Sat Mar 14 23:45:04 2009
@@ -302,6 +302,8 @@
 
 	static final String XML_CONSTANT_NODE_NAME = "org.apache.derby.impl.sql.compile.XMLConstantNode";
 
+	static final String ROW_COUNT_NODE_NAME = "org.apache.derby.impl.sql.compile.RowCountNode";
+
 	// The names are in alphabetic order.
 	//
     // WARNING: WHEN ADDING NODE TYPES HERE, YOU MUST ALSO ADD

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java Sat Mar 14 23:45:04 2009
@@ -34,6 +34,7 @@
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 import org.apache.derby.impl.sql.CursorInfo;
 import org.apache.derby.impl.sql.CursorTableReference;
+import org.apache.derby.iapi.types.DataValueDescriptor;
 
 /**
  * A CursorNode represents a result set that can be returned to a client.
@@ -52,6 +53,8 @@
 
 	private String		name;
 	private OrderByList	orderByList;
+	private NumericConstantNode   offset;     // <result offset clause> value
+	private NumericConstantNode   fetchFirst; // <fetch first clause> value
 	private String		statementType;
 	private int		updateMode;
 	private boolean		needTarget;
@@ -78,6 +81,8 @@
 	 * @param name		The name of the cursor, null if no name
 	 * @param orderByList	The order by list for the cursor, null if no
 	 *			order by list
+	 * @param offset The value of a <result offset clause> if present
+	 * @param fetchFirst The value of a <fetch first clause> if present
 	 * @param updateMode	The user-specified update mode for the cursor,
 	 *			for example, CursorNode.READ_ONLY
 	 * @param updatableColumns The list of updatable columns specified by
@@ -92,6 +97,8 @@
 		Object resultSet,
 		Object name,
 		Object orderByList,
+		Object offset,
+		Object fetchFirst,
 		Object updateMode,
 		Object updatableColumns)
 	{
@@ -99,6 +106,8 @@
 		this.name = (String) name;
 		this.statementType = (String) statementType;
 		this.orderByList = (OrderByList) orderByList;
+		this.offset = (NumericConstantNode)offset;
+		this.fetchFirst = (NumericConstantNode)fetchFirst;
 
 		this.updateMode = ((Integer) updateMode).intValue();
 		this.updatableColumns = (Vector) updatableColumns;
@@ -266,6 +275,9 @@
 			orderByList.bindOrderByColumns(resultSet);
 		}
 
+
+		bindOffsetFetch();
+
 		// bind the updatability
 
 		// if it says it is updatable, verify it.
@@ -347,6 +359,34 @@
 
 	}
 
+
+	private void bindOffsetFetch() throws StandardException {
+
+		if (offset != null) {
+			DataValueDescriptor dvd = ((ConstantNode)offset).getValue();
+			long val = dvd.getLong();
+
+			if (val < 0) {
+				throw StandardException.newException(
+					SQLState.LANG_INVALID_ROW_COUNT_OFFSET,
+					Long.toString(val) );
+			}
+		}
+
+		if (fetchFirst != null) {
+			DataValueDescriptor dvd = ((ConstantNode)fetchFirst).getValue();
+			long val = dvd.getLong();
+
+			if (val < 1) {
+				throw StandardException.newException(
+					SQLState.LANG_INVALID_ROW_COUNT_FIRST,
+					Long.toString(val) );
+			}
+		}
+	}
+
+
+
 	/**
 	 * Return true if the node references SESSION schema tables (temporary or permanent)
 	 *
@@ -512,7 +552,9 @@
 			resultSet.pushOrderByList(orderByList);
 			orderByList = null;
 		}
-		super.optimizeStatement();
+
+		super.optimizeStatement(offset, fetchFirst);
+
 	}
 
 	/**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLStatementNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLStatementNode.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLStatementNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLStatementNode.java Sat Mar 14 23:45:04 2009
@@ -299,6 +299,22 @@
 	 */
 	public void optimizeStatement() throws StandardException
 	{
+		optimizeStatement(null, null);
+	}
+
+	/**
+	 * This overload variant of optimizeStatement is used by subclass
+	 * CursorNode (as well as a minion for the no-arg variant).
+	 *
+	 * @param offset     Any OFFSET row count, or null
+	 * @param fetchFirst Any FETCH FIRST row count or null
+	 *
+	 * @exception StandardException		Thrown on error
+	 * @see DMLStatementNode#optimizeStatement()
+	 */
+	protected void optimizeStatement(ValueNode offset, ValueNode fetchFirst)
+			throws StandardException
+	{
 		resultSet = resultSet.preprocess(getCompilerContext().getNumTables(),
 										 null,
 										 (FromList) null);
@@ -306,6 +322,25 @@
 
 		resultSet = resultSet.modifyAccessPaths();
 
+		// Any OFFSET/FETCH FIRST narrowing must be done *after* any rewrite of
+		// the query tree (if not, underlying GROUP BY fails), but *before* the
+		// final scroll insensitive result node set is added - that one needs
+		// to sit on top - so now is the time.
+		// 
+		// This example statement fails if we wrap *before* the optimization
+		// above:
+		//     select max(a) from t1 group by b fetch first row only
+		//
+		// A java.sql.ResultSet#previous on a scrollable result set will fail
+		// if we don't wrap *after* the ScrollInsensitiveResultSetNode below.
+		//
+		// We need only wrap the RowCountNode set if at least one of the
+		// clauses is present.
+		
+		if (offset != null || fetchFirst != null) {
+			resultSet = wrapRowCountNode(resultSet, offset, fetchFirst);
+		}
+
 		/* If this is a cursor, then we
 		 * need to generate a new ResultSetNode to enable the scrolling
 		 * on top of the tree before modifying the access paths.
@@ -343,8 +378,30 @@
 				resultSet.setReferencedTableMap((JBitSet) siChild.getReferencedTableMap().clone());
 			}
 		}
+
 	}
 
+
+	private ResultSetNode wrapRowCountNode(
+		ResultSetNode resultSet,
+		ValueNode offset,
+		ValueNode fetchFirst) throws StandardException {
+
+		ResultSetNode topRS = resultSet;
+		ResultColumnList selectRCs =
+			topRS.getResultColumns().copyListAndObjects();
+		selectRCs.genVirtualColumnNodes(topRS, topRS.getResultColumns());
+
+		return (RowCountNode)getNodeFactory().getNode(
+			C_NodeTypes.ROW_COUNT_NODE,
+			topRS,
+			selectRCs,
+			offset,
+			fetchFirst,
+			getContextManager());
+	}
+
+
 	/**
 	 * Make a ResultDescription for use in a PreparedStatement.
 	 *

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java Sat Mar 14 23:45:04 2009
@@ -622,7 +622,10 @@
 		  	
           case C_NodeTypes.ROW_NUMBER_COLUMN_NODE:
             return C_NodeNames.ROW_NUMBER_COLUMN_NODE_NAME;
-		  	
+
+          case C_NodeTypes.ROW_COUNT_NODE:
+            return C_NodeNames.ROW_COUNT_NODE_NAME;
+
 		  // WARNING: WHEN ADDING NODE TYPES HERE, YOU MUST ALSO ADD
 		  // THEM TO tools/jar/DBMSnodes.properties
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Sat Mar 14 23:45:04 2009
@@ -816,6 +816,23 @@
 		}
 	}
 
+
+	/**
+	 * Set the column source's table name
+	 * @param t The source table name
+	 */
+	public void setSourceTableName(String t) {
+		sourceTableName = t;
+	}
+
+	/**
+	 * Set the column source's schema name
+	 * @param s The source schema name
+	 */
+	public void setSourceSchemaName(String s) {
+		sourceSchemaName = s;
+	}
+
 	/**
 	 * Preprocess an expression tree.  We do a number of transformations
 	 * here (including subqueries, IN lists, LIKE and BETWEEN) plus
@@ -1410,6 +1427,12 @@
 		newResultColumn.setType(getTypeServices());
 		newResultColumn.setNameGenerated(isNameGenerated());
 
+		// For OFFSET/FETCH we need the also clone table name to avoid failing
+		// check #2 in EmbedResultSet#checksBeforeUpdateXXX. Clone schema for
+		// good measure...
+		newResultColumn.setSourceTableName(getSourceTableName());
+		newResultColumn.setSourceSchemaName(getSourceSchemaName());
+
 		/* Set the "is generated for unmatched column in insert" status in the new node
 		This if for bug 4194*/
 		if (isGeneratedForUnmatchedColumnInInsert())

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java?rev=754558&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java Sat Mar 14 23:45:04 2009
@@ -0,0 +1,136 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.RowCountNode
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+*/
+
+package org.apache.derby.impl.sql.compile;
+
+import java.util.Vector;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.services.compiler.MethodBuilder;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.reference.ClassName;
+import org.apache.derby.iapi.services.classfile.VMOpcode;
+
+/**
+ * The result set generated by this node (RowCountResultSet) implements the
+ * filtering of rows needed for the <result offset clause> and the <fetch first
+ * clause>.  It sits on top of the normal SELECT's top result set, but under any
+ * ScrollInsensitiveResultSet. The latter's positioning is needed for the correct
+ * functioning of <result offset clause> and <fetch first clause> in the
+ * presence of scrollable and/or updatable result sets and CURRENT OF cursors.
+ */
+public final class RowCountNode extends SingleChildResultSetNode
+{
+    /**
+     * If not null, this represents the value of a <result offset clause>.
+     */
+    private ValueNode offset;
+    /**
+     * If not null, this represents the value of a <fetch first clause>.
+     */
+    private ValueNode fetchFirst;
+
+
+    /**
+     * Initializer for a RowCountNode
+     *
+     * @exception StandardException
+     */
+    public void init(Object childResult,
+                     Object rcl,
+                     Object offset,
+                     Object fetchFirst)
+        throws StandardException {
+
+        init(childResult, null);
+        resultColumns = (ResultColumnList) rcl;
+
+        this.offset = (ValueNode)offset;
+        this.fetchFirst = (ValueNode)fetchFirst;
+    }
+
+
+    /**
+     * Generate code.
+     *
+     * @param acb activation class builder
+     * @param mb  method builder
+     *
+     * @exception StandardException         Thrown on error
+     */
+    public void generate(ActivationClassBuilder acb,
+                         MethodBuilder mb)
+            throws StandardException {
+
+        /* Get the next ResultSet #, so that we can number this ResultSetNode,
+         * its ResultColumnList and ResultSet.
+         */
+        assignResultSetNumber();
+
+        costEstimate = childResult.getFinalCostEstimate();
+
+        acb.pushGetResultSetFactoryExpression(mb);
+
+        childResult.generate(acb, mb); // arg1
+
+        acb.pushThisAsActivation(mb);  // arg2
+        mb.push(resultSetNumber);      // arg3
+
+
+        // If OFFSET is not given, we pass in the default, i.e 0.
+        long offsetVal =
+            (offset != null) ?
+            ((ConstantNode)offset).getValue().getLong() : 0;
+
+        // If FETCH FIRST is not given, we pass in -1 to RowCountResultSet.
+        long fetchFirstVal =
+            (fetchFirst != null) ?
+            ((ConstantNode)fetchFirst).getValue().getLong() : -1;
+
+        mb.push(offsetVal);            // arg4
+        mb.push(fetchFirstVal);        // arg5
+        mb.push(costEstimate.rowCount()); // arg6
+        mb.push(costEstimate.getEstimatedCost()); // arg7
+
+        mb.callMethod(VMOpcode.INVOKEINTERFACE,
+                      (String) null,
+                      "getRowCountResultSet",
+                      ClassName.NoPutResultSet,
+                      7);
+    }
+
+
+    /**
+     * Convert this object to a String.  See comments in QueryTreeNode.java
+     * for how this should be done for tree printing.
+     *
+     * @return  This object as a String
+     */
+
+    public String toString() {
+        if (SanityManager.DEBUG) {
+            return "offset: " + offset + "\n" +
+                "fetchFirst:" + fetchFirst + "\n" +
+                super.toString();
+        } else {
+            return "";
+        }
+    }
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java
------------------------------------------------------------------------------
    svn:eol-style = native

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=754558&r1=754557&r2=754558&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 Sat Mar 14 23:45:04 2009
@@ -578,16 +578,20 @@
 	 * Translate a String containing a number into the appropriate type
 	 * of Numeric node.
 	 *
+	 * @param num      the string containing the number
+	 * @param intsOnly accept only integers (not decimal)
+	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	ValueNode getNumericNode(String num) throws StandardException
+	NumericConstantNode getNumericNode(String num, boolean intsOnly)
+		throws StandardException
 	{
 		ContextManager cm = getContextManager();
 
 		// first, see if it might be an integer
 		try
 		{
-			return (ValueNode) nodeFactory.getNode(
+			return (NumericConstantNode) nodeFactory.getNode(
 										C_NodeTypes.INT_CONSTANT_NODE,
 										new Integer(num),
 										cm);
@@ -600,17 +604,20 @@
 		// next, see if it might be a long
 		try
 		{
-			return (ValueNode) nodeFactory.getNode(
+			return (NumericConstantNode) nodeFactory.getNode(
 										C_NodeTypes.LONGINT_CONSTANT_NODE,
 										new Long(num),
 										cm);
 		}
 		catch (NumberFormatException nfe)
 		{
-			// we catch because we want to continue on below
+			if (intsOnly) {
+				throw nfe;
+			}
+			// else we want to continue on below
 		}
 
-		return (ValueNode) nodeFactory.getNode(
+		return (NumericConstantNode) nodeFactory.getNode(
 									C_NodeTypes.DECIMAL_CONSTANT_NODE,
 									num,
 									cm);
@@ -2205,6 +2212,7 @@
 |	<NULLS: "nulls">
 |	<NUMBER: "number">
 |	<OBJECT: "object">
+|	<OFFSET: "offset">
 |	<PASCAL: "pascal">
 |	<PLI: "pli">
 |	<PRECISION: "precision">
@@ -3107,10 +3115,14 @@
 	int				  isolationLevel = ExecutionContext.UNSPECIFIED_ISOLATION_LEVEL;
 	CursorNode		  retval;
 	OrderByList orderCols = null;
+	NumericConstantNode offset = null;
+	NumericConstantNode fetchFirst = null;
 }
 {
 	queryExpression = queryExpression(null, NO_SET_OP) 
 		[ orderCols = orderByClause() ]
+		[ offset = offsetClause() ]
+		[ fetchFirst = fetchFirstClause() ]
 		[ <FOR> forUpdateState = forUpdateClause(updateColumns) ]
 		[ isolationLevel = atIsolationLevel() ]
 	{
@@ -3124,6 +3136,8 @@
 				queryExpression,
 				null,
 				orderCols,
+				offset,
+				fetchFirst,
 				ReuseFactory.getInteger(forUpdateState),
 				(forUpdateState == CursorNode.READ_ONLY ? null : updateColumns ),
 				getContextManager());
@@ -3392,6 +3406,8 @@
 						resultSetNode,
 						null,
 						null,
+						null,
+						null,
 						ReuseFactory.getInteger(CursorNode.READ_ONLY),
 						null,
 						getContextManager());
@@ -8049,6 +8065,45 @@
         }
 }
 
+
+/*
+ * <A NAME="offsetClause">offsetClause</A>
+ */
+NumericConstantNode
+offsetClause() throws StandardException :
+{
+	NumericConstantNode result;
+}
+{
+	// Since OFFSET is not yet a reserved keyword, cf. disambiguation
+	// look-ahead for it w.r.t. offsetClause in method nonReservedKeyword.
+	// This solves the shift/reduce conflict, and allows us to use OFFSET as an
+	// identifier in all contexts.
+	<OFFSET> result = intLiteral() ( <ROW> | <ROWS> )
+    {
+		return result;
+	}
+}
+
+
+/*
+ * <A NAME="fetchFirstClause">fetchFirstClause</A>
+ */
+NumericConstantNode
+fetchFirstClause() throws StandardException :
+{
+	// The default number of rows to fetch if the literal is omitted is 1:
+	NumericConstantNode result = getNumericNode("1", true);
+}
+{
+	<FETCH> ( <FIRST> | <NEXT> )
+		[ result = intLiteral() ] ( <ROW> | <ROWS> ) <ONLY>
+    {
+		return result;
+	}
+}
+
+
 /*
  * <A NAME="forUpdateClause">forUpdateClause</A>
  */
@@ -10974,6 +11029,42 @@
 */
 }
 
+
+/*
+ * <A NAME="int">intLiteral</A>
+ */
+NumericConstantNode
+intLiteral() throws StandardException :
+{
+	Token	tok;
+	String sign = null;
+	NumericConstantNode result;
+}
+{
+	[ sign = sign() ] tok = <EXACT_NUMERIC>
+	{
+		/*
+		** The various java parse utilities can't handle leading +,
+		** so only concatenate leading -.
+		*/
+
+		String num = tok.image;
+
+		if (sign != null && sign.equals("-"))
+			num = sign.concat(num);
+
+		try {
+			result = getNumericNode(num, true);
+		} catch (NumberFormatException e) {
+			throw StandardException.newException(
+				SQLState.LANG_INTEGER_LITERAL_EXPECTED);
+		}
+
+		return result;
+	}
+}
+
+
 /*
  * <A NAME="numericLiteral">numericLiteral</A>
  */
@@ -10995,7 +11086,7 @@
 		if (sign.equals("-"))
 			num = sign.concat(num);
 
-		return getNumericNode(num);
+		return getNumericNode(num, false);
 	}
 |
 	tok = <APPROXIMATE_NUMERIC>
@@ -13798,6 +13889,13 @@
 	|	tok = <NUMBER>
 	|	tok = <OBJECT>
 	|	tok = <OFF>
+	|	LOOKAHEAD({
+			getToken(1).kind == OFFSET &&
+			!(getToken(2).kind == PLUS_SIGN ||
+			  getToken(2).kind == MINUS_SIGN ||
+			  getToken(2).kind == EXACT_NUMERIC)
+		})
+		tok = <OFFSET>
 	|	tok = <OLD>
 	|	tok = <OLD_TABLE>
 	|	tok = <OJ>

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java Sat Mar 14 23:45:04 2009
@@ -1266,6 +1266,29 @@
 								rltItem);
 	}
 	
+	/**
+	 * @see ResultSetFactory#getRowCountResultSet
+	 */
+	public NoPutResultSet getRowCountResultSet(
+		NoPutResultSet source,
+		Activation activation,
+		int resultSetNumber,
+		long offset,
+		long fetchFirst,
+		double optimizerEstimatedRowCount,
+		double optimizerEstimatedCost)
+		throws StandardException
+	{
+		return new RowCountResultSet(source,
+									 activation,
+									 resultSetNumber,
+									 offset,
+									 fetchFirst,
+									 optimizerEstimatedRowCount,
+									 optimizerEstimatedCost);
+	}
+
+
 	static private Authorizer getAuthorizer(Activation activation)
 	{
 		LanguageConnectionContext lcc = activation.getLanguageConnectionContext();

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java Sat Mar 14 23:45:04 2009
@@ -317,7 +317,7 @@
 	 * Clear the current row
 	 *
 	 */
-	public final void clearCurrentRow()
+	public void clearCurrentRow()
 	{
 		currentRow = null;
 		activation.clearCurrentRow(resultSetNumber);

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RealResultSetStatisticsFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RealResultSetStatisticsFactory.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RealResultSetStatisticsFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RealResultSetStatisticsFactory.java Sat Mar 14 23:45:04 2009
@@ -106,6 +106,7 @@
 import org.apache.derby.impl.sql.execute.rts.RealUpdateResultSetStatistics;
 import org.apache.derby.impl.sql.execute.rts.RealVTIStatistics;
 import org.apache.derby.impl.sql.execute.rts.RealWindowResultSetStatistics;
+import org.apache.derby.impl.sql.execute.rts.RealRowCountStatistics;
 import org.apache.derby.impl.sql.execute.rts.ResultSetStatistics;
 import org.apache.derby.impl.sql.execute.rts.RunTimeStatisticsImpl;
 
@@ -407,6 +408,23 @@
 											getResultSetStatistics(prrs.source)
 											);
 		}
+		else if (rs instanceof RowCountResultSet)
+		{
+			RowCountResultSet rcrs = (RowCountResultSet) rs;
+
+			return new RealRowCountStatistics(
+				rcrs.numOpens,
+				rcrs.rowsSeen,
+				rcrs.rowsFiltered,
+				rcrs.constructorTime,
+				rcrs.openTime,
+				rcrs.nextTime,
+				rcrs.closeTime,
+				rcrs.resultSetNumber,
+				rcrs.optimizerEstimatedRowCount,
+				rcrs.optimizerEstimatedCost,
+				getResultSetStatistics(rcrs.source) );
+		}
 		else if (rs instanceof SortResultSet)
 		{
 			SortResultSet srs = (SortResultSet) rs;

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java?rev=754558&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java Sat Mar 14 23:45:04 2009
@@ -0,0 +1,332 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.RowCountResultSet
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+*/
+
+package org.apache.derby.impl.sql.execute;
+
+import org.apache.derby.iapi.sql.conn.StatementContext;
+import org.apache.derby.iapi.sql.execute.CursorResultSet;
+import org.apache.derby.iapi.sql.execute.ExecRow;
+import org.apache.derby.iapi.sql.execute.NoPutResultSet;
+import org.apache.derby.iapi.sql.Activation;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.types.RowLocation;
+
+
+
+/**
+ * This result set implements the filtering of rows needed for the <result
+ * offset clause> and the <fetch first clause>.  It sits on top of the normal
+ * SELECT's top result set, but under any ScrollInsensitiveResultSet needed for
+ * cursors. The latter positioning is needed for the correct functioning of
+ * <result offset clause> and <fetch first clause> in the presence of
+ * scrollable and/or updatable result sets and CURRENT OF cursors.
+ *
+ * It is only ever generated if at least one of the two clauses is present.
+ */
+class RowCountResultSet extends NoPutResultSetImpl
+    implements CursorResultSet
+{
+    // set in constructor and not altered during
+    // life of object.
+    final NoPutResultSet source;
+    final private boolean runTimeStatsOn;
+    private long offset;
+    private long fetchFirst;
+
+    /**
+     * RowCountResultSet constructor
+     *
+     * @param s               The source result set being filtered
+     * @param a               The activation for this result set,
+     *                        which provides the context for the row
+     *                        allocation operation
+     * @param resultSetNumber The resultSetNumber for the ResultSet
+     * @param offset          The offset value (0 by default)
+     * @param fetchFirst      The fetch first value (-1 if not in use)
+     * @param optimizerEstimatedRowCount
+     *                        Estimated total # of rows by optimizer
+     * @param optimizerEstimatedCost
+     *                        Estimated total cost by optimizer
+     * @exception StandardException Standard error policy
+     */
+    RowCountResultSet
+        (NoPutResultSet s,
+         Activation a,
+         int resultSetNumber,
+         long offset,
+         long fetchFirst,
+         double optimizerEstimatedRowCount,
+         double optimizerEstimatedCost)
+        throws StandardException
+    {
+        super(a,
+              resultSetNumber,
+              optimizerEstimatedRowCount,
+              optimizerEstimatedCost);
+
+        source = s;
+
+        this.offset = offset;
+        this.fetchFirst = fetchFirst;
+
+        /* Remember whether or not RunTimeStatistics is on */
+        runTimeStatsOn =
+            getLanguageConnectionContext().getRunTimeStatisticsMode();
+        recordConstructorTime();
+    }
+
+    //
+    // NoPutResultSet interface
+    //
+
+    /**
+     * Open a scan on the table. scan parameters are evaluated
+     * at each open, so there is probably some way of altering
+     * their values...
+     *
+     * @exception StandardException thrown if cursor finished.
+     */
+    public void openCore() throws StandardException {
+
+        boolean constantEval = true;
+
+        beginTime = getCurrentTimeMillis();
+
+        source.openCore();
+        isOpen = true;
+
+        numOpens++;
+
+        openTime += getElapsedMillis(beginTime);
+    }
+
+    /**
+     * Reopen a scan on the table. scan parameters are evaluated
+     * at each open, so there is probably some way of altering
+     * their values...
+     *
+     * @exception StandardException thrown if cursor finished.
+     */
+    public void reopenCore() throws StandardException {
+
+        boolean constantEval = true;
+
+        beginTime = getCurrentTimeMillis();
+
+        if (SanityManager.DEBUG)
+            SanityManager.ASSERT(isOpen,
+                                 "RowCountResultSet not open, cannot reopen");
+
+        source.reopenCore();
+
+        isOpen = true;
+
+        numOpens++;
+
+        openTime += getElapsedMillis(beginTime);
+    }
+
+    /**
+     * Return the requested values computed from the next row (if any)
+     * <p>
+     * @exception StandardException thrown on failure.
+     * @exception StandardException ResultSetNotOpen thrown if not yet open.
+     *
+     * @return the next row in the result
+     */
+    public ExecRow  getNextRowCore() throws StandardException {
+
+        ExecRow result = null;
+
+        beginTime = getCurrentTimeMillis();
+
+        if (offset > 0) {
+            do {
+                result = source.getNextRowCore();
+                offset--;
+
+                if (result != null && offset >= 0) {
+                    rowsFiltered++;
+                } else {
+                    break;
+                }
+
+            } while (true);
+
+            // only skip row first time
+            offset = 0;
+        } else {
+
+            if (fetchFirst != -1 && rowsSeen >= fetchFirst) {
+                result = null;
+            } else {
+                result = source.getNextRowCore();
+            }
+        }
+
+
+        if (result != null) {
+            rowsSeen++;
+        }
+
+        setCurrentRow(result);
+
+        if (runTimeStatsOn) {
+            if (! isTopResultSet) {
+                 // This is simply for RunTimeStats.  We first need to get the
+                 // subquery tracking array via the StatementContext
+                StatementContext sc = activation.getLanguageConnectionContext().
+                    getStatementContext();
+                subqueryTrackingArray = sc.getSubqueryTrackingArray();
+            }
+
+            nextTime += getElapsedMillis(beginTime);
+        }
+        return result;
+    }
+
+    /**
+     * Return the total amount of time spent in this ResultSet
+     *
+     * @param type
+     *    CURRENT_RESULTSET_ONLY - time spent only in this ResultSet
+     *    ENTIRE_RESULTSET_TREE  - time spent in this ResultSet and below.
+     *
+     * @return long     The total amount of time spent (in milliseconds).
+     */
+    public long getTimeSpent(int type) {
+        long totTime = constructorTime + openTime + nextTime + closeTime;
+
+        if (type == CURRENT_RESULTSET_ONLY) {
+            return  totTime - source.getTimeSpent(ENTIRE_RESULTSET_TREE);
+        } else {
+            return totTime;
+        }
+    }
+
+    // ResultSet interface
+
+    /**
+     * @see org.apache.derby.iapi.sql.ResultSet#close
+     */
+    public void close() throws StandardException {
+
+        beginTime = getCurrentTimeMillis();
+        if ( isOpen ) {
+
+            // we don't want to keep around a pointer to the
+            // row ... so it can be thrown away.
+            // REVISIT: does this need to be in a finally
+            // block, to ensure that it is executed?
+            clearCurrentRow();
+            source.close();
+
+            super.close();
+        } else {
+            if (SanityManager.DEBUG) {
+                SanityManager.DEBUG("CloseRepeatInfo",
+                                    "Close of RowCountResultSet repeated");
+            }
+        }
+
+        closeTime += getElapsedMillis(beginTime);
+    }
+
+
+    /**
+     * @see org.apache.derby.iapi.sql.ResultSet#finish
+     */
+    public void finish() throws StandardException {
+        source.finish();
+        finishAndRTS();
+    }
+
+
+    /**
+     * @see org.apache.derby.iapi.sql.ResultSet#clearCurrentRow
+     */
+    public final void clearCurrentRow()
+    {
+        currentRow = null;
+        activation.clearCurrentRow(resultSetNumber);
+
+        // Added this since we need it to keep in synch for updatable result
+        // sets/cursors; this result set needs to be "transparent" in such
+        // cases, cf. getCurrentRow which gets the current row from the source
+        // as well.
+        source.clearCurrentRow();
+    }
+
+
+
+    //
+    // CursorResultSet interface
+    //
+
+    /**
+     * Gets information from its source.
+     *
+     * @see org.apache.derby.iapi.sql.execute.CursorResultSet#getRowLocation
+     */
+    public RowLocation getRowLocation() throws StandardException {
+
+        return ( (CursorResultSet)source ).getRowLocation();
+    }
+
+
+    /**
+     * Gets information from source
+     *
+     * @see org.apache.derby.iapi.sql.execute.CursorResultSet#getCurrentRow
+     * @return the last row returned.
+     */
+
+    /* RESOLVE - this should return activation.getCurrentRow(resultSetNumber),
+     * once there is such a method.  (currentRow is redundant)
+     */
+    public ExecRow getCurrentRow() throws StandardException
+    {
+        return ( (CursorResultSet)source ).getCurrentRow();
+        // return currentRow;
+    }
+
+    /**
+     * Override of NoPutResultSetImpl method. Ask the source.
+     */
+    public boolean isForUpdate() {
+        return source.isForUpdate();
+    }
+
+
+    /**
+     * Return underlying result set (the source og this result set) if it is a
+     * ProjectRestrictResultSet, else null.
+     */
+    public ProjectRestrictResultSet getUnderlyingProjectRestrictRS() {
+        if (source instanceof ProjectRestrictResultSet) {
+            return (ProjectRestrictResultSet)source;
+        } else {
+            return null;
+        }
+    }
+
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java Sat Mar 14 23:45:04 2009
@@ -1101,12 +1101,22 @@
 	public void updateRow(ExecRow row) throws StandardException {
 		ExecRow newRow = row;
 		boolean undoProjection = false;
-		
+
+		ProjectRestrictResultSet prRS = null;
+
 		if (source instanceof ProjectRestrictResultSet) {
-			newRow = ((ProjectRestrictResultSet)source).
-				doBaseRowProjection(row);
+			prRS = (ProjectRestrictResultSet)source;
+		} else if (source instanceof RowCountResultSet) {
+			// To do any projection in the presence of an intervening
+			// RowCountResultSet, we get its child.
+			prRS = ((RowCountResultSet)source).getUnderlyingProjectRestrictRS();
+		}
+
+		if (prRS != null) {
+			newRow = prRS.doBaseRowProjection(row);
 			undoProjection = true;
 		}
+
 		positionInHashTable.setValue(currentPosition);
 		DataValueDescriptor[] hashRowArray = (DataValueDescriptor[]) 
 				ht.get(positionInHashTable);
@@ -1124,8 +1134,7 @@
 			final DataValueDescriptor[] newRowData = newRow.getRowArray();
 			
 			// Array of original position in row
-			final int[] origPos =((ProjectRestrictResultSet)source).
-				getBaseProjectMapping(); 
+			final int[] origPos = prRS.getBaseProjectMapping();
 			
 			// We want the row to contain data backed in BackingStoreHashtable
 			final DataValueDescriptor[] backedData = 

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealRowCountStatistics.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealRowCountStatistics.java?rev=754558&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealRowCountStatistics.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealRowCountStatistics.java Sat Mar 14 23:45:04 2009
@@ -0,0 +1,143 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.rts.RealRowCountStatistics
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+*/
+
+package org.apache.derby.impl.sql.execute.rts;
+
+
+import org.apache.derby.iapi.services.i18n.MessageService;
+import org.apache.derby.iapi.reference.SQLState;
+
+import java.util.Vector;
+
+public class RealRowCountStatistics
+    extends RealNoPutResultSetStatistics
+{
+
+    /* Leave these fields public for object inspectors */
+    public ResultSetStatistics childResultSetStatistics;
+
+    // CONSTRUCTORS
+
+    /**
+     * Statistics for the RowCountResultSet
+     */
+    public  RealRowCountStatistics(
+        int numOpens,
+        int rowsSeen,
+        int rowsFiltered,
+        long constructorTime,
+        long openTime,
+        long nextTime,
+        long closeTime,
+        int resultSetNumber,
+        double optimizerEstimatedRowCount,
+        double optimizerEstimatedCost,
+        ResultSetStatistics childResultSetStatistics)
+    {
+        super(
+            numOpens,
+            rowsSeen,
+            rowsFiltered,
+            constructorTime,
+            openTime,
+            nextTime,
+            closeTime,
+            resultSetNumber,
+            optimizerEstimatedRowCount,
+            optimizerEstimatedCost
+            );
+        this.childResultSetStatistics = childResultSetStatistics;
+    }
+
+    // ResultSetStatistics methods
+
+    /**
+     * Return the statement execution plan as a String.
+     *
+     * @param depth     Indentation level.
+     *
+     * @return String   The statement execution plan as a String.
+     */
+    public String getStatementExecutionPlanText(int depth) {
+
+        String subqueryInfo = "";
+
+        initFormatInfo(depth);
+
+        return
+            subqueryInfo +
+            indent + MessageService.getTextMessage(SQLState.RTS_RC_RS) +
+                " (" +  resultSetNumber + "):" + "\n" +
+            indent + MessageService.getTextMessage(SQLState.RTS_NUM_OPENS) +
+                " = " + numOpens + "\n" +
+            indent + MessageService.getTextMessage(SQLState.RTS_ROWS_SEEN) +
+                " = " + rowsSeen + "\n" +
+            indent + MessageService.getTextMessage(
+                                                SQLState.RTS_ROWS_FILTERED) +
+                " = " + rowsFiltered + "\n" +
+            dumpTimeStats(indent, subIndent) + "\n" +
+            dumpEstimatedCosts(subIndent) + "\n" +
+            indent + MessageService.getTextMessage(SQLState.RTS_SOURCE_RS) +
+                ":" + "\n" +
+            childResultSetStatistics.getStatementExecutionPlanText(sourceDepth);
+    }
+
+    /**
+     * Return information on the scan nodes from the statement execution
+     * plan as a String.
+     *
+     * @param depth     Indentation level.
+     * @param tableName if not NULL then print information for this table only
+     *
+     * @return String   The information on the scan nodes from the
+     *                  statement execution plan as a String.
+     */
+    public String getScanStatisticsText(String tableName, int depth) {
+        return childResultSetStatistics.getScanStatisticsText(tableName, depth);
+    }
+
+
+
+    // Class implementation
+
+    public String toString() {
+        return getStatementExecutionPlanText(0);
+    }
+
+
+    /**
+     * @see RealBasicNoPutResultSetStatistics#getChildren
+     */
+    public Vector getChildren() {
+        Vector children = new Vector();
+        children.addElement(childResultSetStatistics);
+
+        return children;
+    }
+
+
+    /**
+     * @see RealBasicNoPutResultSetStatistics#getNodeName
+     */
+    public String getNodeName() {
+        return MessageService.getTextMessage(SQLState.RTS_RC);
+    }
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealRowCountStatistics.java
------------------------------------------------------------------------------
    svn:eol-style = native

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=754558&r1=754557&r2=754558&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 Sat Mar 14 23:45:04 2009
@@ -746,6 +746,16 @@
                 <text>An ESCAPE clause of NULL returns undefined results and is not allowed.</text>
             </msg>
 
+            <msg>
+                <name>2201X</name>
+                <text>Invalid row count for OFFSET, must be >= 0.</text>
+            </msg>
+
+            <msg>
+                <name>2201W</name>
+                <text>Invalid row count for FIRST/NEXT, must be >= 1.</text>
+            </msg>
+
         </family>
 
 
@@ -1512,6 +1522,11 @@
             </msg>
 
             <msg>
+                <name>42X20</name>
+                <text>Syntax error; integer literal expected.</text>
+            </msg>
+
+            <msg>
                 <name>42X23</name>
                 <text>Cursor {0} is not updatable.</text>
                 <arg>cursorName</arg>
@@ -6674,6 +6689,16 @@
             </msg>
 
             <msg>
+                <name>43X9A.U</name>
+                <text>Row Count ResultSet</text>
+            </msg>
+
+            <msg>
+                <name>43X9B.U</name>
+                <text>Row Count</text>
+            </msg>
+
+            <msg>
                 <name>43Y00.U</name>
                 <text>Scalar Aggregate ResultSet</text>
             </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=754558&r1=754557&r2=754558&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 Sat Mar 14 23:45:04 2009
@@ -711,7 +711,8 @@
 	String LANG_INVALID_TRIM_SET                                       = "22027";
     String LANG_STRING_TOO_LONG                                        = "22028";
 	String LANG_ESCAPE_IS_NULL                                  	   = "22501";
-
+	String LANG_INVALID_ROW_COUNT_OFFSET                               = "2201X";
+	String LANG_INVALID_ROW_COUNT_FIRST                                = "2201W";
 
 	/*
 	** Integrity violations.
@@ -816,6 +817,7 @@
 	String LANG_INVALID_JOIN_ORDER_SPEC                                = "42X17";
 	String LANG_NOT_COMPARABLE                                         = "42818";
 	String LANG_NON_BOOLEAN_WHERE_CLAUSE                               = "42X19";
+	String LANG_INTEGER_LITERAL_EXPECTED                               = "42X20";
 	String LANG_CURSOR_NOT_UPDATABLE                                   = "42X23";
 	String LANG_INVALID_COL_HAVING_CLAUSE                              = "42X24";
 	String LANG_UNARY_FUNCTION_BAD_TYPE                                = "42X25";
@@ -1203,6 +1205,8 @@
 	String RTS_PROJECTION_TIME										   = "43X97.U";
 	String RTS_PR													   = "43X98.U";
 	String RTS_ROW_RS												   = "43X99.U";
+	String RTS_RC                                                      = "43X9A.U";
+	String RTS_RC_RS                                                   = "43X9B.U";
 
 	String RTS_SCALAR_AGG_RS										   = "43Y00.U";
 	String RTS_INDEX_KEY_OPT										   = "43Y01.U";

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java?rev=754558&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java Sat Mar 14 23:45:04 2009
@@ -0,0 +1,663 @@
+/*
+
+  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.OffsetFetchNextTest
+
+  Licensed to the Apache Software Foundation (ASF) under one or more
+  contributor license agreements.  See the NOTICE file distributed with
+  this work for additional information regarding copyright ownership.
+  The ASF licenses this file to You under the Apache License, Version 2.0
+  (the "License"); you may not use this file except in compliance with
+  the License.  You may obtain a copy of the License at
+
+  http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+
+*/
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import java.sql.PreparedStatement;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test <result offset clause> and <fetch first clause>.
+ */
+public class OffsetFetchNextTest extends BaseJDBCTestCase {
+
+    public OffsetFetchNextTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        TestSuite suite = new TestSuite("OffsetFetchNextTest");
+
+        suite.addTest(
+            baseSuite("OffsetFetchNextTest:embedded"));
+        suite.addTest(
+            TestConfiguration.clientServerDecorator(
+                baseSuite("OffsetFetchNextTest:client")));
+
+        return suite;
+    }
+
+    public static Test baseSuite(String suiteName) {
+        return new CleanDatabaseTestSetup(
+            new TestSuite(OffsetFetchNextTest.class,
+                          suiteName)) {
+            protected void decorateSQL(Statement s)
+                    throws SQLException {
+                createSchemaObjects(s);
+            }
+        };
+    }
+
+
+    /**
+     * Creates tables used by the tests (never modified, we use rollback after
+     * changes).
+     */
+    private static void createSchemaObjects(Statement st)
+            throws SQLException
+    {
+        // T1 (no indexes)
+        st.executeUpdate("create table t1 (a int, b bigint)");
+        st.executeUpdate("insert into t1 (a, b) " +
+                         "values (1,1), (1,2), (1,3), (1,4), (1,5)");
+
+        // T2 (primary key)
+        st.executeUpdate("create table t2 (a int primary key, b bigint)");
+        st.executeUpdate("insert into t2 (a, b) " +
+                         "values (1,1), (2,1), (3,1), (4,1), (5,1)");
+
+        // T3 (primary key + secondary key)
+        st.executeUpdate("create table t3 (a int primary key, " +
+                         "                 b bigint unique)");
+        st.executeUpdate("insert into t3 (a, b) " +
+                         "values (1,1), (2,2), (3,3), (4,4), (5,5)");
+    }
+
+    /**
+     * Negative tests. Test various invalid OFFSET and FETCH NEXT clauses.
+     */
+    public void testErrors()
+            throws Exception
+    {
+        Statement st = createStatement();
+
+        // Wrong range in row count argument
+
+        assertStatementError("2201X", st,
+                             "select * from t1 offset -1 rows");
+
+        assertStatementError("2201W", st,
+                             "select * from t1 fetch first 0 rows only");
+
+        assertStatementError("2201W", st,
+                             "select * from t1 fetch first -1 rows only");
+
+        // Wrong type in row count argument
+        assertStatementError("42X20", st,
+                             "select * from t1 fetch first 3.14 rows only");
+
+        // Wrong order of clauses
+        assertStatementError("42X01", st,
+                             "select * from t1 " +
+                             "fetch first 0 rows only offset 0 rows");
+    }
+
+
+    /**
+     * Positive tests. Check that the new keyword OFFSET introduced is not
+     * reserved so we don't risk breaking existing apps.
+     */
+    public void testNewKeywordNonReserved()
+            throws Exception
+    {
+        getConnection().prepareStatement(
+            "select a,b as OFFSET from t1 OFFSET 0 rows");
+
+        // Column and table correlation name usage
+        getConnection().prepareStatement(
+            "select a,b from t1 AS OFFSET");
+
+        getConnection().prepareStatement(
+            "select a,b OFFSET from t1 OFFSET");
+    }
+
+
+    /**
+     * Positive tests.
+     */
+    public void testOffsetFetchFirstReadOnlyForwardOnlyRS()
+            throws Exception
+    {
+        Statement stm = createStatement();
+
+        /*
+         * offset 0 rows (a no-op)
+         */
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1 offset 0 rows",
+            new String [][] {
+                {"1","1"}, {"1","2"},{"1","3"}, {"1","4"},{"1","5"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 offset 0 rows",
+            new String [][] {
+                {"1","1"}, {"2","1"},{"3","1"}, {"4","1"},{"5","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 offset 0 rows",
+            new String [][] {
+                {"1","1"}, {"2","2"},{"3","3"}, {"4","4"},{"5","5"}});
+
+        /*
+         * offset 1 rows
+         */
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1 offset 1 rows",
+            new String [][] {
+                {"1","2"},{"1","3"}, {"1","4"},{"1","5"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 offset 1 rows",
+            new String [][] {
+                {"2","1"},{"3","1"}, {"4","1"},{"5","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 offset 1 rows",
+            new String [][] {
+                {"2","2"},{"3","3"}, {"4","4"},{"5","5"}});
+
+        /*
+         * offset 4 rows
+         */
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1 offset 4 rows",
+            new String [][] {
+                {"1","5"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 offset 4 rows",
+            new String [][] {
+                {"5","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 offset 4 rows",
+            new String [][] {
+                {"5","5"}});
+
+        /*
+         * offset 1 rows fetch 1 row. Use "next"/"rows" syntax
+         */
+        queryAndCheck(
+            stm,
+            "select a,b from t1 offset 1 row fetch next 1 rows only",
+            new String [][] {
+                {"1","2"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 offset 1 row fetch next 1 rows only",
+            new String [][] {
+                {"2","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 offset 1 row  fetch next 1 rows only",
+            new String [][] {
+                {"2","2"}});
+
+        /*
+         * offset 1 rows fetch so many rows we drain rs row. Use "first"/"row"
+         * syntax
+         */
+        queryAndCheck(
+            stm,
+            "select a,b from t1 offset 1 rows fetch first 10 row only",
+            new String [][] {
+                {"1","2"},{"1","3"}, {"1","4"},{"1","5"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 offset 1 rows fetch first 10 row only",
+            new String [][] {
+                {"2","1"},{"3","1"}, {"4","1"},{"5","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 offset 1 rows  fetch first 10 row only",
+            new String [][] {
+                {"2","2"},{"3","3"}, {"4","4"},{"5","5"}});
+
+        /*
+         * offset so many rows that we see empty rs
+         */
+        queryAndCheck(
+            stm,
+            "select a,b from t1 offset 10 rows",
+            new String [][] {});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 offset 10 rows",
+            new String [][] {});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 offset 10 rows",
+            new String [][] {});
+
+        /*
+         * fetch first/next row (no row count given)
+         */
+        queryAndCheck(
+            stm,
+            "select a,b from t1 fetch first row only",
+            new String [][] {{"1","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 fetch next row only",
+            new String [][] {{"1","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 fetch next row only",
+            new String [][] {{"1","1"}});
+
+        /*
+         * Combine with order by asc
+         */
+        queryAndCheck(
+            stm,
+            "select a,b from t1 order by b asc fetch first row only",
+            new String [][] {{"1","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 order by a asc fetch next row only",
+            new String [][] {{"1","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 order by a asc fetch next row only",
+            new String [][] {{"1","1"}});
+
+
+        /*
+         * Combine with order by desc.
+         */
+        queryAndCheck(
+            stm,
+            // Note: use column b here since for t1 all column a values are the
+            // same and order can change after sorting, want unique row first
+            // in rs so we can test it.
+            "select a,b from t1 order by b desc fetch first row only",
+            new String [][] {{"1","5"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t2 order by a desc fetch next row only",
+            new String [][] {{"5","1"}});
+        queryAndCheck(
+            stm,
+            "select a,b from t3 order by a desc fetch next row only",
+            new String [][] {{"5","5"}});
+
+        /*
+         * Combine with group by, order by.
+         */
+        queryAndCheck(
+            stm,
+            "select max(a) from t1 group by b fetch first row only",
+            new String [][] {{"1"}});
+        queryAndCheck(
+            stm,
+            "select max(a) from t2 group by b offset 0 rows",
+            new String [][] {{"5"}});
+        queryAndCheck(
+            stm,
+            "select max(a) from t3 group by b " +
+            "    order by max(a) fetch next 2 rows only",
+            new String [][] {{"1"},{"2"}});
+
+        /*
+         * Combine with union
+         */
+
+        queryAndCheck(
+            stm,
+            "select * from t1 union all select * from t1 " +
+            "    fetch first 2 row only",
+            new String [][] {{"1","1"}, {"1","2"}});
+
+        /*
+         * Combine with join
+         */
+        queryAndCheck(
+            stm,
+            "select t2.b, t3.b from t2,t3 where t2.a=t3.a " +
+            "    fetch first 2 row only",
+            new String [][] {{"1","1"}, {"1","2"}});
+
+        stm.close();
+    }
+
+
+    /**
+     * Positive tests.
+     */
+    public void testOffsetFetchFirstUpdatableForwardOnlyRS()
+            throws Exception
+    {
+        Statement stm = createStatement(ResultSet.TYPE_FORWARD_ONLY,
+                                        ResultSet.CONCUR_UPDATABLE);
+
+        getConnection().setAutoCommit(false);
+
+        /*
+         * offset 0 rows (a no-op), update a row and verify result
+         */
+        ResultSet rs = stm.executeQuery("select * from t1  offset 0 rows");
+        rs.next();
+        rs.next(); // at row 2
+        rs.updateInt(1, -rs.getInt(1));
+        rs.updateRow();
+        rs.close();
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1",
+            new String [][] {
+                {"1","1"}, {"-1","2"},{"1","3"}, {"1","4"},{"1","5"}});
+
+        rollback();
+
+        /*
+         * offset 1 rows, update a row and verify result
+         */
+        rs = stm.executeQuery("select * from t1 offset 1 rows");
+        rs.next(); // at row 1, but row 2 of underlying rs
+
+        rs.updateInt(1, -rs.getInt(1));
+        rs.updateRow();
+        rs.close();
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1",
+            new String [][] {
+                {"1","1"}, {"-1","2"},{"1","3"}, {"1","4"},{"1","5"}});
+
+        rollback();
+        stm.close();
+    }
+
+
+    /**
+     * Positive tests with scrollable read-only.
+     */
+    public void testOffsetFetchFirstReadOnlyScrollableRS()
+            throws Exception
+    {
+        Statement stm = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
+                                        ResultSet.CONCUR_READ_ONLY);
+
+        /*
+         * offset 0 rows (a no-op), update a row and verify result
+         */
+        ResultSet rs = stm.executeQuery("select * from t1  offset 0 rows");
+        rs.next();
+        rs.next(); // at row 2
+        assertTrue(rs.getInt(2) == 2);
+        rs.close();
+
+        /*
+         * offset 1 rows, fetch 3 row, check that we have the right ones
+         */
+        rs = stm.executeQuery(
+            "select * from t1 " + "offset 1 rows fetch next 3 rows only");
+        rs.next();
+        rs.next(); // at row 2, but row 3 of underlying rs
+
+        assertTrue(rs.getInt(2) == 3);
+
+        // Go backbards and update
+        rs.previous();
+        assertTrue(rs.getInt(2) == 2);
+
+        // Try some navigation and border conditions
+        rs.previous();
+        assertTrue(rs.isBeforeFirst());
+        rs.next();
+        rs.next();
+        rs.next();
+        rs.next();
+        assertTrue(rs.isAfterLast());
+
+        stm.close();
+    }
+
+
+    /**
+     * Positive tests with SUR (Scrollable updatable result set).
+     */
+    public void testOffsetFetchFirstUpdatableScrollableRS()
+            throws Exception
+    {
+        Statement stm = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
+                                        ResultSet.CONCUR_UPDATABLE);
+
+        getConnection().setAutoCommit(false);
+
+        /*
+         * offset 0 rows (a no-op), update a row and verify result
+         * also try the "for update" syntax so we see that it still works
+         */
+        ResultSet rs = stm.executeQuery(
+            "select * from t1  offset 0 rows for update");
+        rs.next();
+        rs.next(); // at row 2
+        rs.updateInt(1, -rs.getInt(1));
+        rs.updateRow();
+        rs.close();
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1",
+            new String [][] {
+                {"1","1"}, {"-1","2"},{"1","3"}, {"1","4"},{"1","5"}});
+
+        rollback();
+
+        /*
+         * offset 1 rows, fetch 3 row, update some rows and verify result
+         */
+        rs = stm.executeQuery(
+            "select * from t1 offset 1 rows fetch next 3 rows only");
+        rs.next();
+        rs.next(); // at row 2, but row 3 of underlying rs
+
+        rs.updateInt(1, -rs.getInt(1));
+        rs.updateRow();
+
+        // Go backbards and update
+        rs.previous();
+        rs.updateInt(1, -rs.getInt(1));
+        rs.updateRow();
+
+        // Try some navigation and border conditions
+        rs.previous();
+        assertTrue(rs.isBeforeFirst());
+        rs.next();
+        rs.next();
+        rs.next();
+        rs.next();
+        assertTrue(rs.isAfterLast());
+
+        // Insert a row
+        rs.moveToInsertRow();
+        rs.updateInt(1,42);
+        rs.updateInt(2,42);
+        rs.insertRow();
+
+        // Delete a row
+        rs.previous();
+        rs.deleteRow();
+
+        // .. and see that a hole is left in its place
+        rs.previous();
+        rs.next();
+        assertTrue(rs.rowDeleted());
+
+        rs.close();
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1",
+            new String [][] {
+                {"1","1"}, {"-1","2"},{"-1","3"},{"1","5"},{"42","42"}});
+        rollback();
+
+        // Test with projection
+        rs = stm.executeQuery(
+            "select * from t1 where a + 1 < b offset 1 rows");
+        // should yield 2 rows
+        rs.absolute(2);
+        assertTrue(rs.getInt(2) == 5);
+        rs.updateInt(2, -5);
+        rs.updateRow();
+        rs.close();
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1",
+            new String [][] {
+                {"1","1"}, {"1","2"},{"1","3"},{"1","4"},{"1","-5"}});
+        rollback();
+
+        stm.close();
+    }
+
+    /**
+     * Positive tests, result set metadata
+     */
+    public void testMetadata() throws SQLException {
+        Statement stm = createStatement();
+
+        ResultSet rs = stm.executeQuery("select * from t1 offset 1 rows");
+        ResultSetMetaData rsmd= rs.getMetaData();
+        int cnt = rsmd.getColumnCount();
+
+        String[] cols = new String[]{"A","B"};
+        int[] types = {Types.INTEGER, Types.BIGINT};
+
+        for (int i=1; i <= cnt; i++) {
+            String name = rsmd.getColumnName(i);
+            int type = rsmd.getColumnType(i);
+
+            assertTrue(name.equals(cols[i-1]));
+            assertTrue(type == types[i-1]);
+        }
+
+        rs.close();
+        stm.close();
+    }
+
+
+    /**
+     * Test that we see correct traces of the filtering in the statistics
+     */
+    public void testRunTimeStatistics() throws SQLException {
+        Statement stm = createStatement();
+
+        stm.executeUpdate("call syscs_util.syscs_set_runtimestatistics(1)");
+
+        queryAndCheck(
+            stm,
+            "select a,b from t1 offset 2 rows",
+            new String [][] {
+                {"1","3"}, {"1","4"},{"1","5"}});
+
+        stm.executeUpdate("call syscs_util.syscs_set_runtimestatistics(0)");
+
+        ResultSet rs = stm.executeQuery(
+            "values syscs_util.syscs_get_runtimestatistics()");
+        rs.next();
+        String plan = rs.getString(1);
+
+        String nl = System.getProperty("line.separator");
+
+        // Verify that the plan shows the filtering (2 rows of 3 seen):
+        assertTrue(plan.indexOf("Row Count (1):" + nl +
+                                "Number of opens = 1" + nl +
+                                "Rows seen = 3" + nl +
+                                "Rows filtered = 2") != -1);
+
+        rs.close();
+        stm.close();
+    }
+
+
+    /**
+     * Test against a bigger table
+     */
+    public void testBigTable() throws SQLException {
+        Statement stm = createStatement();
+
+        getConnection().setAutoCommit(false);
+
+        stm.executeUpdate("declare global temporary table session.t (i int) " +
+                          "on commit preserve rows not logged");
+
+        PreparedStatement ps =
+            getConnection().prepareStatement("insert into session.t values ?");
+
+        for (int i=1; i <= 100000; i++) {
+            ps.setInt(1, i);
+            ps.executeUpdate();
+
+            if (i % 10000 == 0) {
+                commit();
+            }
+        }
+
+        queryAndCheck(
+            stm,
+            "select count(*) from session.t",
+            new String [][] {
+                {"100000"}});
+
+        queryAndCheck(
+            stm,
+            "select i from session.t offset 99999 rows",
+            new String [][] {
+                {"100000"}});
+
+        stm.executeUpdate("drop table session.t");
+        stm.close();
+    }
+
+
+    private void queryAndCheck(
+        Statement stm,
+        String queryText,
+        String [][] expectedRows) throws SQLException {
+
+        ResultSet rs = stm.executeQuery(queryText);
+        JDBC.assertFullResultSet(rs, expectedRows);
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/tools/jar/DBMSnodes.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/tools/jar/DBMSnodes.properties?rev=754558&r1=754557&r2=754558&view=diff
==============================================================================
--- db/derby/code/trunk/tools/jar/DBMSnodes.properties (original)
+++ db/derby/code/trunk/tools/jar/DBMSnodes.properties Sat Mar 14 23:45:04 2009
@@ -142,6 +142,7 @@
 derby.module.cloudscapenodes.gs=org.apache.derby.impl.sql.compile.RevokeRoleNode
 derby.module.cloudscapenodes.gt=org.apache.derby.impl.sql.compile.RowNumberColumnNode
 derby.module.cloudscapenodes.gu=org.apache.derby.impl.sql.compile.WindowNode
+derby.module.cloudscapenodes.gv=org.apache.derby.impl.sql.compile.RowCountNode
 
 # Warning: make sure this file is properly terminated with a newline,
 # else the build can fail silently. Symptom: derby.jar lacks many



Mime
View raw message