db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r897934 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Mon, 11 Jan 2010 16:46:04 GMT
Author: dag
Date: Mon Jan 11 16:46:03 2010
New Revision: 897934

URL: http://svn.apache.org/viewvc?rev=897934&view=rev
Log:
DERBY-4398 Allow OFFSET/FETCH in subqueries

Patch derby-4398-3, which implements this feature, and also adds test
cases for this to the now renamed test
OrderByAndOffsetFetchInSubqueries and OffsetFetchNextTest.


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java   (with props)
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.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/FromBaseTable.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.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/ResultSetNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.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/SetOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.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/RowCountResultSet.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java Mon Jan 11 16:46:03 2010
@@ -69,6 +69,8 @@
 	ProviderInfo[]		providerInfos;
 	ColumnInfo[]		colInfos;
 	private OrderByList orderByList;
+    private ValueNode   offset;
+    private ValueNode   fetchFirst;
 
 	/**
 	 * Initializer for a CreateViewNode
@@ -81,6 +83,8 @@
 	 *							(NONE for now)
 	 * @param qeText			The text for the queryExpression
 	 * @param orderCols         ORDER BY list
+     * @param offset            OFFSET if any, or null
+     * @param fetchFirst        FETCH FIRST if any, or null
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
@@ -90,7 +94,9 @@
 				   Object	 queryExpression,
 				   Object checkOption,
 				   Object qeText,
-				   Object orderCols)
+                   Object orderCols,
+                   Object offset,
+                   Object fetchFirst)
 		throws StandardException
 	{
 		initAndCheck(newObjectName);
@@ -99,6 +105,8 @@
 		this.checkOption = ((Integer) checkOption).intValue();
 		this.qeText = ((String) qeText).trim();
 		this.orderByList = (OrderByList)orderCols;
+        this.offset = (ValueNode)offset;
+        this.fetchFirst = (ValueNode)fetchFirst;
 
 		implicitCreateSchema = true;
 	}
@@ -411,7 +419,15 @@
 		}
 	}
 
-	public OrderByList getOrderByList() {
-		return orderByList;
-	}
+    public OrderByList getOrderByList() {
+        return orderByList;
+    }
+
+    public ValueNode getOffset() {
+        return offset;
+    }
+
+    public ValueNode getFetchFirst() {
+        return fetchFirst;
+    }
 }

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=897934&r1=897933&r2=897934&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 Mon Jan 11 16:46:03 2010
@@ -280,7 +280,7 @@
 		}
 
 
-		bindOffsetFetch();
+        bindOffsetFetch(offset, fetchFirst);
 
 		// bind the updatability
 
@@ -364,46 +364,6 @@
 	}
 
 
-	private void bindOffsetFetch() throws StandardException {
-
-		if (offset instanceof ConstantNode) {
-			DataValueDescriptor dvd = ((ConstantNode)offset).getValue();
-			long val = dvd.getLong();
-
-			if (val < 0) {
-				throw StandardException.newException(
-					SQLState.LANG_INVALID_ROW_COUNT_OFFSET,
-					Long.toString(val) );
-			}
-		} else if (offset instanceof ParameterNode) {
-			offset.
-				setType(new DataTypeDescriptor(
-							TypeId.getBuiltInTypeId(Types.BIGINT),
-							false /* ignored tho; ends up nullable,
-									 so we test for NULL at execute time */));
-		}
-
-
-		if (fetchFirst instanceof ConstantNode) {
-			DataValueDescriptor dvd = ((ConstantNode)fetchFirst).getValue();
-			long val = dvd.getLong();
-
-			if (val < 1) {
-				throw StandardException.newException(
-					SQLState.LANG_INVALID_ROW_COUNT_FIRST,
-					Long.toString(val) );
-			}
-		} else if (fetchFirst instanceof ParameterNode) {
-			fetchFirst.
-				setType(new DataTypeDescriptor(
-							TypeId.getBuiltInTypeId(Types.BIGINT),
-							false /* ignored tho; ends up nullable,
-									 so we test for NULL at execute time*/));
-		}
-	}
-
-
-
 	/**
 	 * Return true if the node references SESSION schema tables (temporary or permanent)
 	 *
@@ -570,7 +530,9 @@
 			orderByList = null;
 		}
 
-		super.optimizeStatement(offset, fetchFirst);
+        resultSet.pushOffsetFetchFirst(offset, fetchFirst);
+
+        super.optimizeStatement();
 
 	}
 

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=897934&r1=897933&r2=897934&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 Mon Jan 11 16:46:03 2010
@@ -299,22 +299,6 @@
 	 */
 	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);
@@ -332,25 +316,6 @@
 
 		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.
@@ -392,26 +357,6 @@
 	}
 
 
-	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/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Mon Jan 11 16:46:03 2010
@@ -2243,8 +2243,6 @@
 
 				rsn = cvn.getParsedQueryExpression();
 
-				OrderByList orderByList = cvn.getOrderByList();
-
 				/* If the view contains a '*' then we mark the views derived column list
 				 * so that the view will still work, and return the expected results,
 				 * if any of the tables referenced in the view have columns added to
@@ -2274,7 +2272,9 @@
 				fsq = (FromSubquery) getNodeFactory().getNode(
 					C_NodeTypes.FROM_SUBQUERY,
 					rsn,
-					orderByList,
+                    cvn.getOrderByList(),
+                    cvn.getOffset(),
+                    cvn.getFetchFirst(),
 					(correlationName != null) ? 
                         correlationName : getOrigTableName().getTableName(), 
 					resultColumns,

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Mon Jan 11 16:46:03 2010
@@ -47,6 +47,8 @@
 {
 	ResultSetNode	subquery;
 	private OrderByList orderByList;
+    private ValueNode offset;
+    private ValueNode fetchFirst;
 
 	/**
 	 * DERBY-3270: If this subquery represents an expanded view, this holds the
@@ -59,6 +61,8 @@
 	 *
 	 * @param subquery		The subquery
 	 * @param orderByList   ORDER BY list if any, or null
+     * @param offset        OFFSET if any, or null
+     * @param fetchFirst    FETCH FIRST if any, or null
 	 * @param correlationName	The correlation name
 	 * @param derivedRCL		The derived column list
 	 * @param tableProperties	Properties list associated with the table
@@ -66,6 +70,8 @@
 	public void init(
 					Object subquery,
 					Object orderByList,
+                    Object offset,
+                    Object fetchFirst,
 					Object correlationName,
 				 	Object derivedRCL,
 					Object tableProperties)
@@ -73,6 +79,8 @@
 		super.init(correlationName, tableProperties);
 		this.subquery = (ResultSetNode) subquery;
 		this.orderByList = (OrderByList)orderByList;
+        this.offset = (ValueNode)offset;
+        this.fetchFirst = (ValueNode)fetchFirst;
 		resultColumns = (ResultColumnList) derivedRCL;
 	}
 
@@ -94,12 +102,24 @@
 				subquery.treePrint(depth + 1);
 			}
 
-			if (orderByList != null)
-			{
-				printLabel(depth, "orderByList: ");
-				orderByList.treePrint(depth + 1);
-			}
-		}
+            if (orderByList != null)
+            {
+                printLabel(depth, "orderByList: ");
+                orderByList.treePrint(depth + 1);
+            }
+
+            if (offset != null)
+            {
+                printLabel(depth, "offset: ");
+                offset.treePrint(depth + 1);
+            }
+
+            if (fetchFirst != null)
+            {
+                printLabel(depth, "fetchFirst: ");
+                fetchFirst.treePrint(depth + 1);
+            }
+        }
 	}
 
 	/** 
@@ -236,6 +256,8 @@
 			orderByList.bindOrderByColumns(subquery);
 		}
 
+        bindOffsetFetch(offset, fetchFirst);
+
 		/* Now that we've bound the expressions in the subquery, we 
 		 * can propagate the subquery's RCL up to the FromSubquery.
 		 * Get the subquery's RCL, assign shallow copy back to
@@ -356,6 +378,8 @@
 			orderByList = null;
 		}
 
+        subquery.pushOffsetFetchFirst(offset, fetchFirst);
+
 		/* We want to chop out the FromSubquery from the tree and replace it 
 		 * with a ProjectRestrictNode.  One complication is that there may be 
 		 * ColumnReferences above us which point to the FromSubquery's RCL.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java Mon Jan 11 16:46:03 2010
@@ -111,7 +111,9 @@
 	protected	boolean				bulkInsert;
 	private 	boolean				bulkInsertReplace;
 	private     OrderByList         orderByList;
-	
+    private     ValueNode           offset;
+    private     ValueNode           fetchFirst;
+
 	protected   RowLocation[] 		autoincRowLocation;
 	/**
 	 * Initializer for an InsertNode.
@@ -134,7 +136,9 @@
 			Object insertColumns,
 			Object queryExpression,
 			Object targetProperties,
-            Object orderByList)
+            Object orderByList,
+            Object offset,
+            Object fetchFirst)
 	{
 		/* statementType gets set in super() before we've validated
 		 * any properties, so we've kludged the code to get the
@@ -149,6 +153,8 @@
 		targetColumnList = (ResultColumnList) insertColumns;
 		this.targetProperties = (Properties) targetProperties;
 		this.orderByList = (OrderByList) orderByList;
+        this.offset = (ValueNode)offset;
+        this.fetchFirst = (ValueNode)fetchFirst;
 
 		/* Remember that the query expression is the source to an INSERT */
 		getResultSetNode().setInsertSource();
@@ -443,6 +449,8 @@
 			orderByList.bindOrderByColumns(resultSet);
 		}
 
+        bindOffsetFetch(offset, fetchFirst);
+
 		resultSet = enhanceAndCheckForAutoincrement(resultSet, inOrder, colMap);
 
 		resultColumnList.checkStorableExpressions(resultSet.getResultColumns());
@@ -838,8 +846,12 @@
 			}
 
 			resultSet.pushOrderByList(orderByList);
+
 			orderByList = null;
 		}
+
+        resultSet.pushOffsetFetchFirst(offset, fetchFirst);
+
 		super.optimizeStatement();
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java Mon Jan 11 16:46:03 2010
@@ -312,14 +312,34 @@
 
 		addNewNodesCalled = true;
 
-        if( orderByList == null)
-            return this;
-        // Generate an order by node on top of the intersect/except
-        return (ResultSetNode) getNodeFactory().getNode( C_NodeTypes.ORDER_BY_NODE,
-                                                         this,
-                                                         orderByList,
-                                                         tableProperties,
-                                                         getContextManager());
+        ResultSetNode treeTop = this;
+
+        if( orderByList != null) {
+            // Generate an order by node on top of the intersect/except
+            treeTop = (ResultSetNode) getNodeFactory().getNode(
+                C_NodeTypes.ORDER_BY_NODE,
+                treeTop,
+                orderByList,
+                tableProperties,
+                getContextManager());
+        }
+
+        if (offset != null || fetchFirst != null) {
+            ResultColumnList newRcl =
+                treeTop.getResultColumns().copyListAndObjects();
+            newRcl.genVirtualColumnNodes(treeTop, treeTop.getResultColumns());
+
+            treeTop = (RowCountNode)getNodeFactory().getNode(
+                C_NodeTypes.ROW_COUNT_NODE,
+                treeTop,
+                newRcl,
+                offset,
+                fetchFirst,
+                getContextManager());
+        }
+
+        return treeTop;
+
     } // end of addNewNodes
 
     /**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java Mon Jan 11 16:46:03 2010
@@ -698,4 +698,15 @@
 		childResult.pushOrderByList(orderByList);
 	}
 
+    /**
+     * Push through the offset and fetch first parameters, if any, to the child
+     * result set.
+     *
+     * @param offset    the OFFSET, if any
+     * @param fetchFirst the OFFSET FIRST, if any
+     */
+    void pushOffsetFetchFirst(ValueNode offset, ValueNode fetchFirst)
+    {
+        childResult.pushOffsetFetchFirst(offset, fetchFirst);
+    }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java Mon Jan 11 16:46:03 2010
@@ -1877,4 +1877,15 @@
 		childResult.pushOrderByList(orderByList);
 	}
 
+    /**
+     * Push down the offset and fetch first parameters, if any, to the
+     * underlying child result set.
+     *
+     * @param offset    the OFFSET, if any
+     * @param fetchFirst the OFFSET FIRST, if any
+     */
+    void pushOffsetFetchFirst(ValueNode offset, ValueNode fetchFirst)
+    {
+        childResult.pushOffsetFetchFirst(offset, fetchFirst);
+    }
 }

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=897934&r1=897933&r2=897934&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 Mon Jan 11 16:46:03 2010
@@ -1727,6 +1727,55 @@
     }
 
 
+    /**
+     * Bind the parameters of OFFSET n ROWS and FETCH FIRST n ROWS ONLY, if
+     * any.
+     *
+     * @param offset the OFFSET parameter, if any
+     * @param fetchFirst the FETCH parameter, if any
+     *
+     * @exception StandardException         Thrown on error
+     */
+    public static void bindOffsetFetch(ValueNode offset,
+                                       ValueNode fetchFirst)
+            throws StandardException {
+
+        if (offset instanceof ConstantNode) {
+            DataValueDescriptor dvd = ((ConstantNode)offset).getValue();
+            long val = dvd.getLong();
+
+            if (val < 0) {
+                throw StandardException.newException(
+                    SQLState.LANG_INVALID_ROW_COUNT_OFFSET,
+                    Long.toString(val) );
+            }
+        } else if (offset instanceof ParameterNode) {
+            offset.
+                setType(new DataTypeDescriptor(
+                            TypeId.getBuiltInTypeId(Types.BIGINT),
+                            false /* ignored tho; ends up nullable,
+                                     so we test for NULL at execute time */));
+        }
+
+
+        if (fetchFirst instanceof ConstantNode) {
+            DataValueDescriptor dvd = ((ConstantNode)fetchFirst).getValue();
+            long val = dvd.getLong();
+
+            if (val < 1) {
+                throw StandardException.newException(
+                    SQLState.LANG_INVALID_ROW_COUNT_FIRST,
+                    Long.toString(val) );
+            }
+        } else if (fetchFirst instanceof ParameterNode) {
+            fetchFirst.
+                setType(new DataTypeDescriptor(
+                            TypeId.getBuiltInTypeId(Types.BIGINT),
+                            false /* ignored tho; ends up nullable,
+                                     so we test for NULL at execute time*/));
+        }
+    }
+
 }
 
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Mon Jan 11 16:46:03 2010
@@ -1686,6 +1686,24 @@
 		}
 	}
 
+
+    /**
+     * Push down the offset and fetch first parameters, if any. This method
+     * should be overridden by the result sets that need this.
+     *
+     * @param offset    the OFFSET, if any
+     * @param fetchFirst the OFFSET FIRST, if any
+     */
+    void pushOffsetFetchFirst(ValueNode offset, ValueNode fetchFirst)
+    {
+        if (SanityManager.DEBUG)
+        {
+            SanityManager.THROWASSERT(
+                "pushOffsetFetchFirst() not expected to be called for " +
+                getClass().getName());
+        }
+    }
+
 	/**
 	 * General logic shared by Core compilation and by the Replication Filter
 	 * compiler. A couple ResultSets (the ones used by PREPARE SELECT FILTER)
@@ -1901,16 +1919,4 @@
 
 		return null;
 	}
-	
-	/**
-	 * Override this for nodes that can take an ORDER BY
-	 */
-	public void setOrderBy(OrderByList orderCols) throws StandardException {
-		if (SanityManager.DEBUG)
-		{
-			SanityManager.THROWASSERT(
-				"ORDER BY attempted set for wrong node type:" +
-				getClass().getName());
-		}
-	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java Mon Jan 11 16:46:03 2010
@@ -61,6 +61,8 @@
 	SubqueryList subquerys;
 	Vector		 aggregateVector;
 	OrderByList	 orderByList;
+    ValueNode    offset; // OFFSET n ROWS
+    ValueNode    fetchFirst; // FETCH FIRST n ROWS ONLY
 
 	/**
 	 * Initializer for a RowResultSetNode.
@@ -370,7 +372,20 @@
 		this.orderByList = orderByList;
 	}
 
-	/** 
+    /**
+     * Push down the offset and fetch first parameters, if any, to this node.
+     *
+     * @param offset    the OFFSET, if any
+     * @param fetchFirst the OFFSET FIRST, if any
+     */
+    void pushOffsetFetchFirst(ValueNode offset, ValueNode fetchFirst)
+    {
+        this.offset = offset;
+        this.fetchFirst = fetchFirst;
+    }
+
+
+    /**
 	 * Put a ProjectRestrictNode on top of each FromTable in the FromList.
 	 * ColumnReferences must continue to point to the same ResultColumn, so
 	 * that ResultColumn must percolate up to the new PRN.  However,
@@ -628,6 +643,21 @@
 											tableProperties,
 											getContextManager());
 		}
+
+        if (offset != null || fetchFirst != null) {
+            ResultColumnList newRcl =
+                treeTop.getResultColumns().copyListAndObjects();
+            newRcl.genVirtualColumnNodes(treeTop, treeTop.getResultColumns());
+
+            treeTop = (RowCountNode)getNodeFactory().getNode(
+                C_NodeTypes.ROW_COUNT_NODE,
+                treeTop,
+                newRcl,
+                offset,
+                fetchFirst,
+                getContextManager());
+        }
+
 		return treeTop;
 	}
 

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=897934&r1=897933&r2=897934&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 Mon Jan 11 16:46:03 2010
@@ -107,6 +107,10 @@
 	OrderByList orderByList;
 	boolean		orderByQuery ;
 
+    ValueNode   offset;  // OFFSET n ROWS, if given
+    ValueNode   fetchFirst; // FETCH FIRST n ROWS ONLY, if given
+
+
 	/* PredicateLists for where clause */
 	PredicateList wherePredicates;
 
@@ -916,7 +920,20 @@
 		orderByQuery = true;
 	}
 
-	/** 
+    /**
+     * Push down the offset and fetch first parameters to this node.
+     *
+     * @param offset    the OFFSET, if any
+     * @param fetchFirst the OFFSET FIRST, if any
+     */
+    void pushOffsetFetchFirst(ValueNode offset, ValueNode fetchFirst)
+    {
+        this.offset = offset;
+        this.fetchFirst = fetchFirst;
+    }
+
+
+    /**
 	 * Put a ProjectRestrictNode on top of each FromTable in the FromList.
 	 * ColumnReferences must continue to point to the same ResultColumn, so
 	 * that ResultColumn must percolate up to the new PRN.  However,
@@ -1393,6 +1410,13 @@
 			return false;
 		}
 
+        /* Don't flatten if selectNode has OFFSET or FETCH */
+        if ((offset     != null) ||
+            (fetchFirst != null))
+        {
+            return false;
+        }
+
 		return true;
 	}
 
@@ -1573,7 +1597,7 @@
 												getContextManager());
 				prnRSN.costEstimate = costEstimate.cloneMe();
 			}
-			
+
 			// There may be columns added to the select projection list
 			// a query like:
 			// select a, b from t group by a,b order by a+b
@@ -1606,6 +1630,22 @@
 			}
 		}
 
+        if (offset != null || fetchFirst != null) {
+            // Keep the same RCL on top, since there may be references to
+            // its result columns above us.
+            ResultColumnList topList = prnRSN.getResultColumns();
+            ResultColumnList newSelectList = topList.copyListAndObjects();
+            prnRSN.setResultColumns(newSelectList);
+            topList.genVirtualColumnNodes(prnRSN, newSelectList);
+            prnRSN = (RowCountNode)getNodeFactory().getNode(
+                C_NodeTypes.ROW_COUNT_NODE,
+                prnRSN,
+                topList,
+                offset,
+                fetchFirst,
+                getContextManager());
+        }
+
 
 		if (wasGroupBy &&
 			resultColumns.numGeneratedColumnsForGroupBy() > 0 &&

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java Mon Jan 11 16:46:03 2010
@@ -61,7 +61,8 @@
 	boolean			all;
 
 	OrderByList orderByList;
-
+    ValueNode   offset; // OFFSET n ROWS
+    ValueNode   fetchFirst; // FETCH FIRST n ROWS ONLY
 	// List of scoped predicates for pushing during optimization.
 	private PredicateList leftOptPredicates;
 	private PredicateList rightOptPredicates;
@@ -797,7 +798,20 @@
 		this.orderByList = orderByList;
 	}
 
-	/** 
+    /**
+     * Push down the offset and fetch first parameters, if any, to this node.
+     *
+     * @param offset    the OFFSET, if any
+     * @param fetchFirst the OFFSET FIRST, if any
+     */
+    void pushOffsetFetchFirst(ValueNode offset, ValueNode fetchFirst)
+    {
+        this.offset = offset;
+        this.fetchFirst = fetchFirst;
+    }
+
+
+    /**
 	 * Put a ProjectRestrictNode on top of each FromTable in the FromList.
 	 * ColumnReferences must continue to point to the same ResultColumn, so
 	 * that ResultColumn must percolate up to the new PRN.  However,

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Mon Jan 11 16:46:03 2010
@@ -167,6 +167,8 @@
 	private boolean doneInvariantCheck;
 
 	private OrderByList orderByList;
+    private ValueNode   offset;
+    private ValueNode   fetchFirst;
 
 	/* Subquery types.
 	 * NOTE: FROM_SUBQUERY only exists for a brief second in the parser.  It
@@ -204,17 +206,23 @@
 	 * @param subqueryType	The type of the subquery
 	 * @param leftOperand	The left operand, if any, of the subquery
 	 * @param orderCols     ORDER BY list
+     * @param offset        OFFSET n ROWS
+     * @param fetchFirst    FETCH FIRST n ROWS ONLY
 	 */
 
 	public void init(
 							Object resultSet,
 							Object subqueryType,
 							Object leftOperand,
-					        Object orderCols)
+                            Object orderCols,
+                            Object offset,
+                            Object fetchFirst)
 	{
 		this.resultSet = (ResultSetNode) resultSet;
 		this.subqueryType = ((Integer) subqueryType).intValue();
 		this.orderByList = (OrderByList)orderCols;
+        this.offset = (ValueNode)offset;
+        this.fetchFirst = (ValueNode)fetchFirst;
 
 		/* Subqueries are presumed not to be under a top level AndNode by
 		 * default.  This is because expression normalization only recurses
@@ -280,6 +288,18 @@
 				printLabel(depth, "orderByList: ");
 				orderByList.treePrint(depth + 1);
 			}
+
+            if (offset != null)
+            {
+                printLabel(depth, "offset: ");
+                offset.treePrint(depth + 1);
+            }
+
+            if (fetchFirst != null)
+            {
+                printLabel(depth, "fetchFirst: ");
+                fetchFirst.treePrint(depth + 1);
+            }
 		}
 	}
 
@@ -528,6 +548,8 @@
 			orderByList.bindOrderByColumns(resultSet);
 		}
 
+        bindOffsetFetch(offset, fetchFirst);
+
 		/* reject any untyped nulls in the subquery */
 		resultSet.bindUntypedNullsToResultColumns(null);
         
@@ -644,7 +666,9 @@
 		 */
 		flattenable = (resultSet instanceof RowResultSetNode) &&
 					  underTopAndNode && !havingSubquery &&
-			          orderByList == null &&
+                      orderByList == null &&
+                      offset == null &&
+                      fetchFirst == null &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
 					  parentComparisonOperator instanceof BinaryComparisonOperatorNode;
 
@@ -714,7 +738,9 @@
 
 		flattenable = (resultSet instanceof SelectNode) &&
  			          !((SelectNode)resultSet).hasWindows() &&
-			          orderByList == null &&
+                      orderByList == null &&
+                      offset == null &&
+                      fetchFirst == null &&
 					  underTopAndNode && !havingSubquery &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
 					  (isIN() || isANY() || isEXISTS() || flattenableNotExists ||
@@ -823,6 +849,8 @@
 		}
 
 
+        resultSet.pushOffsetFetchFirst(offset, fetchFirst);
+
 		/* We transform the leftOperand and the select list for quantified 
 		 * predicates that have a leftOperand into a new predicate and push it
 		 * down to the subquery after we preprocess the subquery's resultSet.
@@ -2499,13 +2527,33 @@
 		}
 	}
 
-	/**
-	 * Get ORDER BY list (used to construct FROM_SUBQUERY only), cf.
-	 * FromSubquery, for which this node is transient.
-	 *
-	 * @return order by list if specified, else null.
-	 */
-	public OrderByList getOrderByList() {
-		return orderByList;
-	}
+    /**
+     * Get ORDER BY list (used to construct FROM_SUBQUERY only), cf.
+     * FromSubquery, for which this node is transient.
+     *
+     * @return order by list if specified, else null.
+     */
+    public OrderByList getOrderByList() {
+        return orderByList;
+    }
+
+    /**
+     * Get OFFSET  (used to construct FROM_SUBQUERY only), cf.
+     * FromSubquery, for which this node is transient.
+     *
+     * @return offset if specified, else null.
+     */
+    public ValueNode getOffset() {
+        return offset;
+    }
+
+    /**
+     * Get FETCH FIRST (used to construct FROM_SUBQUERY only), cf.
+     * FromSubquery, for which this node is transient.
+     *
+     * @return fetch first if specified, else null.
+     */
+    public ValueNode getFetchFirst() {
+        return fetchFirst;
+    }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java Mon Jan 11 16:46:03 2010
@@ -480,6 +480,22 @@
 											tableProperties,
 											getContextManager());
 		}
+
+
+        if (offset != null || fetchFirst != null) {
+            ResultColumnList newRcl =
+                treeTop.getResultColumns().copyListAndObjects();
+            newRcl.genVirtualColumnNodes(treeTop, treeTop.getResultColumns());
+
+            treeTop = (RowCountNode)getNodeFactory().getNode(
+                C_NodeTypes.ROW_COUNT_NODE,
+                treeTop,
+                newRcl,
+                offset,
+                fetchFirst,
+                getContextManager());
+        }
+
 		return treeTop;
 	}
 

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=897934&r1=897933&r2=897934&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 Mon Jan 11 16:46:03 2010
@@ -4981,7 +4981,6 @@
 nonJoinQueryPrimary() throws StandardException :
 {
 	ResultSetNode	primary;
-	OrderByList orderCols = null;
 }
 {
 	primary = simpleTable()
@@ -4990,13 +4989,8 @@
 	}
 |
 	<LEFT_PAREN> primary = queryExpression(null, NO_SET_OP)
-                 [ orderCols = orderByClause() ]
 	<RIGHT_PAREN>
 	{
-		if (orderCols != null) {
-			primary.setOrderBy(orderCols);
-		}
-
 		return primary;
 	}
 }
@@ -8386,6 +8380,8 @@
 	ResultSetNode	queryExpression;
 	ResultColumnList	columnList = null;
 	OrderByList orderCols = null;
+    ValueNode offset = null;
+    ValueNode fetchFirst = null;
 }
 {
 	[
@@ -8400,6 +8396,8 @@
 	[ targetProperties = propertyList(false) <CHECK_PROPERTIES>]
 	queryExpression = queryExpression(null, NO_SET_OP)
 	[ orderCols = orderByClause() ]
+    [ offset = offsetClause() ]
+    [ fetchFirst = fetchFirstClause() ]
 	{
 		if (orderCols != null && isTableValueConstructor(queryExpression)) {
 			// Not allowed by the standard since this is a <contextually typed
@@ -8411,6 +8409,13 @@
 				SQLState.LANG_SYNTAX_ERROR, "ORDER BY");
 		}
 
+        if ((offset != null || fetchFirst != null) &&
+                isTableValueConstructor(queryExpression)) {
+            throw StandardException.newException(
+                SQLState.LANG_SYNTAX_ERROR,
+                (offset != null) ? "OFFSET" : "FETCH");
+        }
+
 		return (StatementNode) nodeFactory.getNode(
 							C_NodeTypes.INSERT_NODE,
 							targetTable,
@@ -8418,6 +8423,8 @@
 							queryExpression,
 							targetProperties,
 							orderCols,
+                            offset,
+                            fetchFirst,
 							getContextManager());
 	}
 	/* RESOLVE: Ignoring default values for now
@@ -8656,10 +8663,14 @@
 	ResultSetNode	queryExpression;
 	SubqueryNode	subqueryNode;
 	OrderByList orderCols = null;
+    ValueNode offset = null;
+    ValueNode fetchFirst = null;
 }
 {
 	queryExpression = queryExpression(null, NO_SET_OP)
 	[ orderCols = orderByClause() ]
+    [ offset = offsetClause() ]
+    [ fetchFirst = fetchFirstClause() ]
 	{
 		subqueryNode = (SubqueryNode) nodeFactory.getNode(
 										C_NodeTypes.SUBQUERY_NODE,
@@ -8667,6 +8678,8 @@
 										ReuseFactory.getInteger(subqueryType),
 										leftOperand,
 										orderCols,
+                                        offset,
+                                        fetchFirst,
 										getContextManager());
 		return subqueryNode;
 	}
@@ -9141,6 +9154,8 @@
 											C_NodeTypes.FROM_SUBQUERY,
 											derivedTable.getResultSet(),
 											derivedTable.getOrderByList(),
+                                            derivedTable.getOffset(),
+                                            derivedTable.getFetchFirst(),
 											correlationName,
 											derivedRCL,
 											((optionalTableClauses != null) ?
@@ -10718,12 +10733,16 @@
 	Token				checkTok = null;
 	Token				endToken;
 	OrderByList         orderCols = null;
+    ValueNode           offset = null;
+    ValueNode           fetchFirst = null;
 }
 {
 	<VIEW> tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) 
 		[ <LEFT_PAREN> resultColumns = viewColumnList() <RIGHT_PAREN> ]
 		<AS> queryExpression = queryExpression(null, NO_SET_OP)
 		[ orderCols = orderByClause() ]
+        [ offset = offsetClause() ]
+        [ fetchFirst = fetchFirstClause() ]
 	{
 		checkOptionType = ViewDescriptor.NO_CHECK_OPTION;
 		endToken = getToken(0);
@@ -10745,6 +10764,8 @@
 												beginToken.beginOffset,
 												endToken.endOffset,false),
 								orderCols,
+                                offset,
+                                fetchFirst,
 								getContextManager());
 	}
 }

Modified: 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=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java Mon Jan 11 16:46:03 2010
@@ -158,6 +158,8 @@
 
         source.reopenCore();
 
+        virginal = true;
+        rowsFetched = 0;
         isOpen = true;
 
         numOpens++;

Modified: 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=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java Mon Jan 11 16:46:03 2010
@@ -558,6 +558,26 @@
         stm.close();
     }
 
+
+    public void testValues() throws SQLException {
+        Statement stm = createStatement();
+
+        queryAndCheck(
+            stm,
+            "values 4" +
+            "    fetch first 2 row only",
+            new String [][] {{"4"}});
+
+        queryAndCheck(
+            stm,
+            "values 4" +
+            "    offset 1 row",
+            new String [][] {});
+
+
+        stm.close();
+    }
+
     /**
      * Positive tests, result set metadata
      */

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java?rev=897934&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java Mon Jan 11 16:46:03 2010
@@ -0,0 +1,989 @@
+/*
+ * Class org.apache.derbyTesting.functionTests.tests.lang.OrderByAndOffsetFetchInSubqueries
+ *
+ * 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.SQLException;
+import java.sql.Statement;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SQLUtilities;
+
+/**
+ * Tests for DERBY-4397 Allow {@code ORDER BY} in subqueries
+ * and       DERBY-4398 Allow {@code OFFSET/FETCH} in subqueries.
+ */
+public class OrderByAndOffsetFetchInSubqueries extends BaseJDBCTestCase {
+
+    final static String SYNTAX_ERROR = "42X01";
+    final static String COLUMN_NOT_FOUND = "42X04";
+    final static String COLUMN_OUT_OF_RANGE = "42X77";
+    final static String ORDER_BY_COLUMN_NOT_FOUND = "42X78";
+
+    public OrderByAndOffsetFetchInSubqueries(String name) {
+        super(name);
+    }
+
+    /**
+     * Construct top level suite in this JUnit test
+     *
+     * @return A suite containing embedded and client suites.
+     */
+    public static Test suite()
+    {
+        TestSuite suite = new TestSuite("OrderByAndOffsetFetchInSubqueries");
+
+        suite.addTest(makeSuite());
+        suite.addTest(
+             TestConfiguration.clientServerDecorator(makeSuite()));
+
+        return suite;
+    }
+
+    /**
+     * Construct suite of tests
+     *
+     * @return A suite containing the test cases.
+     */
+    private static Test makeSuite()
+    {
+        return new CleanDatabaseTestSetup(
+            new TestSuite(OrderByAndOffsetFetchInSubqueries.class)) {
+                protected void decorateSQL(Statement s)
+                        throws SQLException {
+                    getConnection().setAutoCommit(false);
+
+                    s.execute("create table temp1(s varchar(10))");
+
+                    // GENERATED ALWAYS AS IDENTITY
+                    s.execute("create table temp2(" +
+                              "i integer not null " +
+                              "    generated always as identity," +
+                              "s varchar(10))");
+                    s.execute("create table temp2b(" +
+                              "i integer not null " +
+                              "    generated always as identity," +
+                              "s varchar(10))");
+                    // DEFAULT value
+                    s.execute("create table temp3(" +
+                              "i integer not null " +
+                              "    generated always as identity," +
+                              "s varchar(10)," +
+                              "j integer not null " +
+                              "    default 66," +
+                              "t varchar(10))");
+
+                    // GENERATED ALWAYS AS (expression)
+                    s.execute("create table temp4(" +
+                              "i integer not null " +
+                              "    generated always as identity," +
+                              "s varchar(10)," +
+                              "j integer not null " +
+                              "    generated always as (2*i)," +
+                              "t varchar(10))");
+
+
+                    s.execute("create table t01(c1 int)");
+                    s.execute("create table t02(c2 int)");
+
+                    s.execute("create table t_source(c1 int, c2 varchar(10))");
+                    s.execute("create table t(i int not null, " +
+                              "               constraint c unique (i), " +
+                              "               j int, k int)");
+
+                    getConnection().commit();
+                }
+            };
+    }
+
+    /**
+     * Test {@code INSERT INTO t SELECT .. FROM .. ORDER BY}.
+     */
+    public void testInsertSelectOrderBy() throws SQLException {
+        //
+        // Shows that DERBY-4 is now solved.
+        //
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs = null;
+
+        s.execute("insert into temp1 values 'x','a','c','b','a'");
+        s.execute("insert into temp2(s) select s from temp1 order by s");
+        s.execute("insert into temp2(s) select s as a1 from temp1 order by a1");
+        s.execute("insert into temp2(s) select * from temp1 order by s");
+
+        rs = s.executeQuery("select * from temp2");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "a"},
+                {"2", "a"},
+                {"3", "b"},
+                {"4", "c"},
+                {"5", "x"},
+                {"6", "a"},
+                {"7", "a"},
+                {"8", "b"},
+                {"9", "c"},
+                {"10", "x"},
+                {"11", "a"},
+                {"12", "a"},
+                {"13", "b"},
+                {"14", "c"},
+                {"15", "x"}});
+
+        rs = s.executeQuery("select * from temp2 order by i");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "a"},
+                {"2", "a"},
+                {"3", "b"},
+                {"4", "c"},
+                {"5", "x"},
+                {"6", "a"},
+                {"7", "a"},
+                {"8", "b"},
+                {"9", "c"},
+                {"10", "x"},
+                {"11", "a"},
+                {"12", "a"},
+                {"13", "b"},
+                {"14", "c"},
+                {"15", "x"}});
+
+        s.execute("insert into temp2(s) select s as a1 from temp1 order by s");
+
+        // This should be rejected as "no such column" errors:
+        assertStatementError(
+            COLUMN_NOT_FOUND, s,
+            "insert into temp2(s) select s as a1 from temp1 order by no_such");
+
+        // A similar example, but with integers rather than strings, and some
+        // intermediate select statements to show that the ordering is working.
+        //
+        s.execute("insert into t01 values (50), (10), (1000), (15), (51)");
+
+        rs = s.executeQuery("select * from t01");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"50"},
+                {"10"},
+                {"1000"},
+                {"15"},
+                {"51"}});
+
+
+        s.execute("insert into t02 select * from t01 order by c1");
+        s.execute("insert into t02 select * from t01");
+        s.execute("insert into t02 select * from t01 order by c1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"10"},
+                {"15"},
+                {"50"},
+                {"51"},
+                {"1000"},
+                {"50"},
+                {"10"},
+                {"1000"},
+                {"15"},
+                {"51"},
+                {"10"},
+                {"15"},
+                {"50"},
+                {"51"},
+                {"1000"}});
+
+        // Combining ORDER BY and VALUES is not legal SQL, cf.  SQL 2008,
+        // section 14.11, Syntactic Rule 17: "A <query expression> simply
+        // contained in a <from subquery> shall not be a <table value
+        // constructor>. See also discussion in JIRA on DERBY-4413
+        // (2009-OCT-23).
+        //
+        assertStatementError(
+             SYNTAX_ERROR, s,
+             "insert into t02 values 66 order by 1");
+        assertStatementError(
+             SYNTAX_ERROR, s,
+             "insert into t02 values (901), (920), (903) order by 1");
+
+        // But this should work:
+        s.executeUpdate("delete from t02");
+        s.executeUpdate("insert into t02 select 900 from sys.systables " +
+                        "                union values 66 order by 1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                 {"66"},
+                 {"900"}});
+
+        // other way around:
+        s.executeUpdate("delete from t02");
+        s.executeUpdate(
+            "insert into t02 values 66 " +
+            "       union select 900 from sys.systables order by 1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                 {"66"},
+                 {"900"}});
+
+        // and, somewhat perversely (since a plain "values 66 order by 1" is
+        // illegal), this:
+        s.executeUpdate("delete from t02");
+        s.executeUpdate("insert into t02 values 66 " +
+                        "       union values 66 order by 1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                 {"66"}});
+
+
+        // UNION
+        //
+        // ok:
+        s.execute("delete from t02");
+        s.execute("insert into t02 select * from t01 union all " +
+                  "                select * from t01 order by c1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"10"},
+                {"10"},
+                {"15"},
+                {"15"},
+                {"50"},
+                {"50"},
+                {"51"},
+                {"51"},
+                {"1000"},
+                {"1000"}});
+
+        // Not ok (c1 is not a column in the union result set, since t02 has
+        // column c02.
+        assertStatementError(
+            ORDER_BY_COLUMN_NOT_FOUND, s,
+            "insert into t02 select * from t01 union all " +
+            "                select * from t02 order by c1");
+
+
+        // Complication: project away sort column
+        s.execute("delete from t02");
+        s.execute("insert into t_source " +
+                  "    values (1, 'one'), (2, 'two'), (8, 'three')");
+        s.execute("insert into t_source(c1) " +
+                  "    select c1 from t_source order by c2 desc");
+        rs = s.executeQuery("select * from t_source");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "one"},
+                {"2", "two"},
+                {"8", "three"},
+                {"2", null},
+                {"8", null},
+                {"1", null}});
+
+        // DERBY-4496
+        s.executeUpdate("create table t4496(x varchar(100))");
+        s.execute("insert into t4496(x) select ibmreqd from " +
+                  "    (select * from sysibm.sysdummy1" +
+                  "         order by length(ibmreqd)) t1");
+
+        JDBC.assertFullResultSet(
+            s.executeQuery("select * from t4496"),
+            new String[][]{{"Y"}});
+
+        rollback();
+    }
+
+    /**
+     * Same test as {@code testInsertSelectOrderBy} but with use of
+     * {@code OFFSET/FETCH FIRST}.
+     * <p/>
+     * Test {@code INSERT INTO t SELECT .. FROM .. ORDER BY} + {@code OFFSET
+     * FETCH}
+     * <p/>
+     * This test is a variant made my modifying {@code testInsertSelectOrderBy}
+     * with suitable {@code OFFSET/FETCH FIRST} clauses.
+     */
+    public void testInsertSelectOrderByOffsetFetch() throws SQLException {
+        //
+        // Shows that DERBY-4 is now solved.
+        //
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs = null;
+
+        s.execute("insert into temp1 values 'x','a','c','b','a'");
+        s.execute("insert into temp2b(s) select s from temp1 order by s " +
+                  "    offset 1 rows fetch next 4 rows only");
+
+        JDBC.assertFullResultSet(
+            s.executeQuery("select * from temp2b"),
+            new String[][]{
+                {"1", "a"},
+                {"2", "b"},
+                {"3", "c"},
+                {"4", "x"}});
+
+        s.execute(
+            "insert into temp2b(s) select s as a1 from temp1 order by a1" +
+            "    offset 1 rows fetch next 4 rows only");
+
+        s.execute(
+            "insert into temp2b(s) select * from temp1 order by s " +
+            "    offset 1 rows fetch next 4 rows only");
+
+        rs = s.executeQuery("select * from temp2b");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "a"},
+                {"2", "b"},
+                {"3", "c"},
+                {"4", "x"},
+                {"5", "a"},
+                {"6", "b"},
+                {"7", "c"},
+                {"8", "x"},
+                {"9", "a"},
+                {"10", "b"},
+                {"11", "c"},
+                {"12", "x"}});
+
+        rs = s.executeQuery("select * from temp2b order by i");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "a"},
+                {"2", "b"},
+                {"3", "c"},
+                {"4", "x"},
+                {"5", "a"},
+                {"6", "b"},
+                {"7", "c"},
+                {"8", "x"},
+                {"9", "a"},
+                {"10", "b"},
+                {"11", "c"},
+                {"12", "x"}});
+
+
+        // A similar example, but with integers rather than strings, and some
+        // intermediate select statements to show that the ordering is working.
+        //
+        s.execute("insert into t01 values (50), (10), (1000), (15), (51)");
+
+        rs = s.executeQuery("select * from t01");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"50"},
+                {"10"},
+                {"1000"},
+                {"15"},
+                {"51"}});
+
+
+        s.execute(
+            "insert into t02 select * from t01 order by c1 " +
+            "    fetch first 2 rows only");
+        s.execute(
+            "insert into t02 select * from t01");
+        s.execute(
+            "insert into t02 select * from t01 order by c1 offset 0 rows");
+
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"10"},
+                {"15"},
+                {"50"},
+                {"10"},
+                {"1000"},
+                {"15"},
+                {"51"},
+                {"10"},
+                {"15"},
+                {"50"},
+                {"51"},
+                {"1000"}});
+
+        // Illegal context
+        assertStatementError(
+             SYNTAX_ERROR, s,
+             "insert into t02 values 66 offset 1 row");
+
+        // But this should work:
+        s.executeUpdate("delete from t02");
+        s.executeUpdate(
+            "insert into t02 select 900 from sys.systables " +
+            "                union values 66 order by 1 offset 1 row");
+
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                 {"900"}});
+
+        // other way around:
+        s.executeUpdate("delete from t02");
+        s.executeUpdate(
+            "insert into t02 values 66 " +
+            "       union select 900 from sys.systables fetch next 1 row only");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                 {"66"}});
+
+        s.executeUpdate("delete from t02");
+        s.executeUpdate("insert into t02 select * from (values 3,4,5 )v " +
+                        "    order by 1 offset 1 row fetch next 2 rows only");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"4"},
+                {"5"}});
+
+
+        // UNION
+        //
+        // ok:
+        s.execute("delete from t02");
+        s.execute("insert into t02 select * from t01 union all " +
+                  "                select * from t01 order by c1 " +
+                  "                fetch next 4 rows only");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"10"},
+                {"10"},
+                {"15"},
+                {"15"}});
+
+        // EXCEPT
+        s.execute("delete from t01");
+        s.execute("insert into t02 values 6,7");
+        s.execute("insert into t01 select * from t02 except " +
+                  "                values 10 order by 1 offset 1 row");
+        rs = s.executeQuery("select * from t01");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"7"},
+                {"15"}});
+
+        // Complication: project away sort column
+        s.execute("delete from t02");
+        s.execute("insert into t_source " +
+                  "    values (1, 'one'), (2, 'two'), (8, 'three')");
+        s.execute("insert into t_source(c1) " +
+                  "    select c1 from t_source order by c2 desc " +
+                  "    fetch next 2 rows only");
+        rs = s.executeQuery("select * from t_source");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "one"},
+                {"2", "two"},
+                {"8", "three"},
+                {"2", null},
+                {"8", null}});
+
+        rollback();
+    }
+
+
+    /**
+     * {@code SELECT} subqueries with {@code ORDER BY}
+     */
+    public void testSelectSubqueriesOrderBy() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.execute(
+            "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')");
+
+        /*
+         * Simple SELECT FromSubquery
+         */
+        rs = s.executeQuery(
+            "select * from (select c1 from t_source order by c1 desc) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8"}, {"2"}, {"1"}});
+
+        rs = s.executeQuery(
+            "select * from (select c1+1 from t_source order by c1+1 desc) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"9"}, {"3"}, {"2"}});
+
+        rs = s.executeQuery(
+            "select * from (select c1,c2 from t_source order by c1 desc,2) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8", "three"}, {"2", "two"}, {"1", "one"}});
+
+        // Complication: project away sort column
+        rs = s.executeQuery(
+            "select * from (select c2 from t_source order by c1 desc) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"three"}, {"two"}, {"one"}});
+
+        rs = s.executeQuery(
+            "select * from " +
+            "    (select c2 from t_source order by c1 desc) s order by 1");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"one"}, {"three"}, {"two"}});
+
+        /*
+         * Simple VALUES FromSubquery
+         */
+        rs = s.executeQuery(
+            "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" +
+            "               order by 1 desc) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8", "three"}, {"2", "two"}, {"1", "one"}});
+
+
+        /*
+         * ORDER BY in EXISTS subquery
+         */
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c1)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c1 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c2 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c2 desc) order by 1 desc");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8"}, {"2"}, {"1"}});
+
+        /*
+         * NOT EXISTS
+         */
+        rs = s.executeQuery(
+            "select c1 from t_source where not exists " +
+            "    (select c1 from t_source order by c2 desc) order by 1 desc");
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+            "select c1 from t_source ot where not exists " +
+            "   (select c1 from t_source where ot.c1=(c1/2) order by c2 desc)" +
+            "    order by 1 desc");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8"}, {"2"}});
+
+        /*
+         * IN subquery
+         */
+        s.executeUpdate("insert into t values (1,10,1), (2,40,1)," +
+                        "         (3,45,1), (4,46,1), (5,90,1)");
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by 1 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by i/5 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by j)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+
+        /*
+         * Scalar subquery inside ALL subquery with correlation
+         */
+        String[][] expected = new String[][]{
+            {"1", "10", "1"},
+            {"2", "40", "1"}};
+
+        // First without any ORDER BYs
+        rs = s.executeQuery(
+            "select * from t t_o where i <= all (" +
+            "    select i+1 from t where i = t_o.k + (" +
+            "        select count(*) from t) - 5)");
+        JDBC.assertFullResultSet(rs, expected);
+
+        // Then with ORDER BY at both subquery levels; should be the same result
+        rs = s.executeQuery(
+            "select * from t t_o where i <= all (" +
+            "    select i+1 from t where i = t_o.k + (" +
+            "        select count(*) from t order by 1) - 5 " +
+            "    order by 1 desc)");
+        JDBC.assertFullResultSet(rs, expected);
+
+        rollback();
+    }
+
+
+    /**
+     * {@code SELECT} subqueries with {@code ORDER BY} and {@code OFFSET/FETCH}.
+     * <p/>
+     * This test is a variant made my modifying {@code
+     * testSelectSubqueriesOrderBy} with suitable {@codeOFFSET/FETCH FIRST}
+     * clauses.
+     */
+    public void testSelectSubqueriesOrderByAndOffsetFetch()
+            throws SQLException {
+
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.execute(
+            "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')");
+
+        /*
+         * Simple SELECT FromSubquery
+         */
+        rs = s.executeQuery(
+            "select * from (select c1 from t_source order by c1 desc " +
+            "               offset 1 row) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"2"}, {"1"}});
+
+        rs = s.executeQuery(
+            "select * from (select c1+1 from t_source order by c1+1 desc " +
+            "               fetch first 2 rows only) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"9"}, {"3"}});
+
+        rs = s.executeQuery(
+            "select * from (select c1,c2 from t_source order by c1 desc,2 " +
+            "               offset 2 rows fetch next 1 row only) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "one"}});
+
+        // Complication: project away sort column
+        rs = s.executeQuery(
+            "select * from (select c2 from t_source order by c1 desc " +
+            "               offset 2 rows) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"one"}});
+
+        rs = s.executeQuery(
+            "select * from " +
+            "    (select c2 from t_source order by c1 desc " +
+            "     fetch first 2 row only) s order by 1");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"three"}, {"two"}});
+
+        /*
+         * Simple VALUES FromSubquery
+         */
+        rs = s.executeQuery(
+            "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" +
+            "               order by 1 desc offset 1 row) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"2", "two"}, {"1", "one"}});
+
+
+        /*
+         * ORDER BY in EXISTS subquery
+         */
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c1 offset 1 row)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        // OFFSET so we get an empty result set:
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c1 offset 3 rows)");
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c1 desc " +
+            "     fetch first 1 row only)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        // drop order by for once:
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source offset 1 row)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c2 desc " +
+            "     offset 1 row fetch first 1 row only) " +
+            "  order by 1 desc offset 1 row fetch first 1 row only");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"2"}});
+
+        /*
+         * NOT EXISTS
+         */
+
+        // We offset outside inner subquery, so NOT EXISTS should hold for all
+        rs = s.executeQuery(
+            "select c1 from t_source where not exists " +
+            "    (select c1 from t_source order by c2 desc " +
+            "         offset 3 rows) " +
+            "    order by 1 desc");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8"}, {"2"}, {"1"}});
+
+        // should remove the hit for 1 below since we offset past it:
+        rs = s.executeQuery(
+            "select c1 from t_source ot where not exists " +
+            "   (select c1 from t_source where ot.c1=(c1/2) order by c2 desc " +
+            "        offset 1 row)" +
+            "   order by 1 desc");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8"}, {"2"}, {"1"}});
+
+        /*
+         * IN subquery
+         */
+        s.executeUpdate("insert into t values (1,10,1), (2,40,1)," +
+                        "         (3,45,1), (4,46,1), (5,90,1)");
+
+        // offset away the interesting value in the subquery:
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by 1 desc " +
+            "                            offset 1 row)");
+        JDBC.assertEmpty(rs);
+
+        // turn rs around, and we should get a hit:
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by 1 asc " +
+            "                            offset 1 row)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by i/5 desc " +
+            "                            offset 1 row)");
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by i/5 asc " +
+            "                            offset 1 row)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by j " +
+            "                            offset 1 row)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by j desc " +
+            "                            offset 1 row)");
+        JDBC.assertEmpty(rs);
+
+        /*
+         * Scalar subquery inside ALL subquery with correlation
+         */
+        String[][] expected = new String[][]{
+            {"1", "10", "1"},
+            {"2", "40", "1"}};
+
+        // First without any ORDER BYs
+        rs = s.executeQuery(
+            "select * from t t_o where i <= all (" +
+            "    select i+1 from t where i = t_o.k + (" +
+            "        select count(*) from t) - 5)");
+        JDBC.assertFullResultSet(rs, expected);
+
+        // Should give null from subquery
+        rs = s.executeQuery(
+            "select * from t where i = (select count(*) from t order by 1 " +
+            "                           offset 1 row)");
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+            "select * from t t_o where i <= all (" +
+            "    select i+1 from t where i = t_o.k + cast(null as int) +" +
+            "         - 5 " +
+            "    order by 1 desc)");
+        // Notice the cast(null as int) I use above to check that the
+        // subquery in the next query using an offset which makes the scalar
+        // subquery return null gives the same result as this one.
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1","10","1"},
+                {"2","40","1"},
+                {"3","45","1"},
+                {"4","46","1"},
+                {"5","90","1"}});
+
+        rs = s.executeQuery(
+            "select * from t t_o where i <= all (" +
+            "    select i+1 from t where i = t_o.k + (" +
+            "        select count(*) from t order by 1 offset 1 row) - 5 " +
+            "    order by 1 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1","10","1"},
+                {"2","40","1"},
+                {"3","45","1"},
+                {"4","46","1"},
+                {"5","90","1"}});
+
+        rollback();
+    }
+
+
+    /**
+     * Test JOIN with delimited subqueries
+     */
+    public void testJoinsWithOffsetFetch() throws SQLException {
+
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.execute("insert into temp1 values 'x','a','c','b','a'");
+
+        PreparedStatement ps = prepareStatement(
+            "select * from " +
+            "   (select s from temp1 order by s " +
+            "                        fetch first ? rows only) t1 join " +
+            "   (select s from temp1 order by s offset ? row " +
+            "                        fetch first ? row only) t2 " +
+            "   on t1.s=t2.s");
+
+        ps.setInt(1,2);
+        ps.setInt(2,1);
+        ps.setInt(3,1);
+
+        rs = ps.executeQuery();
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"a", "a"},
+                {"a", "a"}});
+
+        ps.setInt(1,1);
+        rs = ps.executeQuery();
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"a", "a"}});
+
+        rollback();
+    }
+
+
+    /**
+     * Test {@code ORDER BY} in a view definition
+     */
+    public void testView() throws SQLException {
+
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.executeUpdate(
+            "create view v1 as select i from t order by j desc");
+        s.executeUpdate(
+            "create view v2 as select i from t order by i");
+        s.executeUpdate(
+            "insert into t values (1,10,1), (2,40,1)," +
+            "         (3,45,1), (4,46,1), (5,90,1)");
+        rs = s.executeQuery(
+            "select i from v1");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"5"},{"4"},{"3"},{"2"},{"1"}});
+
+        rs = s.executeQuery(
+            "select i from v2");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"},{"2"},{"3"},{"4"},{"5"}});
+
+        rollback();
+    }
+
+    /**
+     * Test {@code ORDER BY} + {@code FETCH/OFFSET} in a view definition
+     * <p/>
+     * This test is a variant made my modifying {@code testView} with suitable
+     * {@code OFFSET/FETCH FIRST} clauses.
+     */
+    public void testViewFetchOffset() throws SQLException {
+
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.executeUpdate(
+            "create view v1 as select i from t order by j desc " +
+            "                  offset 2 rows fetch first 1 row only");
+        s.executeUpdate(
+            "create view v2 as select i from t order by i " +
+            "                  fetch next 2 rows only");
+
+        s.executeUpdate(
+            "insert into t values (1,10,1), (2,40,1)," +
+            "         (3,45,1), (4,46,1), (5,90,1)");
+
+        rs = s.executeQuery(
+            "select i from v1");
+        JDBC.assertFullResultSet(rs, new String[][]{{"3"}});
+
+        rs = s.executeQuery(
+            "select i from v2");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"},{"2"}});
+
+        rollback();
+    }
+
+
+    /**
+     * {@code SELECT} subqueries with {@code ORDER BY} - negative tests
+     */
+    public void testSelectSubqueriesOrderByNegative() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.execute(
+            "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')");
+
+        /*
+         * Simple SELECT FromSubquery
+         */
+        assertStatementError(
+            COLUMN_NOT_FOUND, s,
+            "select * from (select c1 from t_source order by c3 desc) s");
+
+        assertStatementError(
+            COLUMN_OUT_OF_RANGE, s,
+            "select * from (select c1 from t_source order by 3 desc) s");
+
+
+        /*
+         * Simple VALUES FromSubquery
+         */
+        assertStatementError(
+            COLUMN_OUT_OF_RANGE, s,
+            "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" +
+            "               order by 3 desc) s");
+
+        /*
+         * ORDER BY in EXISTS subquery:
+         */
+        assertStatementError(
+            COLUMN_NOT_FOUND, s,
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c4)");
+
+        rollback();
+    }
+}

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

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=897934&r1=897933&r2=897934&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Mon Jan 11 16:46:03 2010
@@ -208,8 +208,8 @@
         suite.addTest(OffsetFetchNextTest.suite());
         suite.addTest(SequenceTest.suite());
         suite.addTest(SequencePermsTest.suite());
-        suite.addTest(OrderByInSubqueries.suite());
         suite.addTest(OLAPTest.suite());
+        suite.addTest(OrderByAndOffsetFetchInSubqueries.suite());
 
         return suite;
 	}



Mime
View raw message