db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r632494 - 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 Fri, 29 Feb 2008 23:24:58 GMT
Author: abrown
Date: Fri Feb 29 15:24:54 2008
New Revision: 632494

URL: http://svn.apache.org/viewvc?rev=632494&view=rev
Log:
DERBY-2998 (partial): Initial drop of code to support the OLAP
"ROW_NUMBER () OVER ()" syntax and functionality within the result
column list of a SELECT query.  This commit adds the functionality,
enables it, and provides an initial set of test cases.

Follow-up work addressing remaining issues will be committed as
it is ready.

Contributed by: Thomas Nielsen (thomas dot nielsen at sun dot com)

Added:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberColumnNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionColumnNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/WindowResultSet.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealWindowResultSetStatistics.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/ColumnReference.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/OrderByList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.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/ResultColumn.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.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/SubqueryNode.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/RealResultSetStatisticsFactory.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -226,8 +226,12 @@
     static final int GRANT_ROLE_NODE = 215;
     static final int REVOKE_ROLE_NODE = 216;
 
+    // OLAP Window functions
+    static final int WINDOW_NODE = 220;
+    static final int ROW_NUMBER_COLUMN_NODE = 221;
+
     // Final value in set, keep up to date!
-    static final int FINAL_VALUE = REVOKE_ROLE_NODE;
+    static final int FINAL_VALUE = ROW_NUMBER_COLUMN_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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -23,7 +23,6 @@
 
 import org.apache.derby.iapi.error.StandardException;
 
-import org.apache.derby.iapi.sql.ResultDescription;
 import org.apache.derby.iapi.sql.ResultSet;
 import org.apache.derby.iapi.sql.Activation;
 
@@ -1090,6 +1089,35 @@
 								double optimizerEstimatedCost)
 			throws StandardException;
 
+        /**
+		A WindowResultSet is used for gathering rows from OLAP window functions.
+
+		<p>
+
+		@param source	the source result set, which is expected to provide
+						rows for the WindowResultSet
+		@param resultSetNumber	The resultSetNumber for the ResultSet
+		@param restriction		The restriction, if any, to be applied to the base row
+		@param optimizerEstimatedRowCount	Estimated total # of rows by
+											optimizer
+		@param optimizerEstimatedCost		Estimated total cost by optimizer
+
+		@return the window function as a result set.
+		@exception StandardException thrown when unable to create the
+			result set
+	 */
+	public NoPutResultSet getWindowResultSet(
+								Activation activation,
+								NoPutResultSet source,
+								GeneratedMethod rowAllocator,
+								int resultSetNumber,
+								int level,
+								int erdNumber,
+								GeneratedMethod restriction,
+								double optimizerEstimatedRowCount,
+								double optimizerEstimatedCost)
+	   	throws StandardException;
+        
 	/**
 		A nested loop left outer join result set forms a result set on top of
 		2 other result sets.

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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -228,6 +228,8 @@
 	static final String REVOKE_ROLE_NODE_NAME =
 		"org.apache.derby.impl.sql.compile.RevokeRoleNode";
 
+	static final String ROW_NUMBER_COLUMN_NODE_NAME = "org.apache.derby.impl.sql.compile.RowNumberColumnNode";    
+
 	static final String ROW_RESULT_SET_NODE_NAME = "org.apache.derby.impl.sql.compile.RowResultSetNode";
 
 	static final String SQL_BOOLEAN_CONSTANT_NODE_NAME = "org.apache.derby.impl.sql.compile.SQLBooleanConstantNode";
@@ -292,6 +294,8 @@
 
 	static final String VIRTUAL_COLUMN_NODE_NAME = "org.apache.derby.impl.sql.compile.VirtualColumnNode";
 
+	static final String WINDOW_NODE_NAME = "org.apache.derby.impl.sql.compile.WindowNode";
+        
 	static final String SAVEPOINT_NODE_NAME = "org.apache.derby.impl.sql.compile.SavepointNode";
 
 	static final String XML_CONSTANT_NODE_NAME = "org.apache.derby.impl.sql.compile.XMLConstantNode";

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java?rev=632494&r1=632493&r2=632494&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java Fri Feb 29 15:24:54 2008
@@ -611,6 +611,7 @@
 		referencedTabs.set(tableNumber);
 
 		return ( ! replacesAggregate ) &&
+			   ( ! source.isWindowFunction() ) && 
 			   ( (source.getExpression() instanceof ColumnReference) ||
 			     (source.getExpression() instanceof VirtualColumnNode) ||
 				 (source.getExpression() instanceof ConstantNode));
@@ -1003,6 +1004,22 @@
 		return (source.getExpression() instanceof ColumnReference);
 	}
 
+	/**
+	 * Return whether or not the source of this ColumnReference is a window 
+	 * function.
+	 * 
+	 * Note that we only care about the immediate source here.
+	 * This enables a push into a subquery that again has a windowfunction 
+	 * column in its subquery RCL.
+	 *
+	 * @return Whether or not the source of this ColumnReference 
+	 *         is a window function.
+	 */
+	boolean pointsToWindowFunction()
+	{ 
+		return (source.isWindowFunction());
+	}
+	
 	/**
 	 * The type of a ColumnReference is the type of its
      * source unless the source is null then it is

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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -614,6 +614,12 @@
           case C_NodeTypes.TABLE_PRIVILEGES_NODE:
             return C_NodeNames.TABLE_PRIVILEGES_NAME;
 
+          case C_NodeTypes.WINDOW_NODE:
+            return C_NodeNames.WINDOW_NODE_NAME;
+
+          case C_NodeTypes.ROW_NUMBER_COLUMN_NODE:
+            return C_NodeNames.ROW_NUMBER_COLUMN_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/OrderByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java?rev=632494&r1=632493&r2=632494&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java Fri Feb 29 15:24:54 2008
@@ -748,4 +748,34 @@
 		 */
 		return false;
 	}
+
+	/**
+	 * Adjust the OrderByList after removal of window function columns.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void adjustForWindowFunctionColumns() 	
+		throws StandardException
+	{	
+		/* 
+		 * Recreate the template due to removal of window function columns 
+		 * in the resultToSort RCL.
+		 */
+		resultRow =				
+				resultToSort.getResultColumns().buildEmptyRow().getRowArray();				
+		/*
+		 * Adjust the VirtualColumnIds OrderByColumns to the updated RCL. 
+		 */
+		for (int index = 0; index < size(); index++)
+		{
+			OrderByColumn obc = (OrderByColumn) elementAt(index);
+			obc.bindOrderByColumn(resultToSort, this);
+		}
+		/* 
+		 * Update the columOrdering
+		 *
+		 * TODO - is this really necessary?
+		 */
+		columnOrdering = getColumnOrdering();		
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java?rev=632494&r1=632493&r2=632494&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java Fri Feb 29 15:24:54 2008
@@ -1465,7 +1465,8 @@
 				for (Enumeration e = colRefs.elements(); e.hasMoreElements(); )
 				{
 					ColumnReference ref = (ColumnReference)e.nextElement();
-					if (!ref.pointsToColumnReference())
+					if (!ref.pointsToColumnReference() &&
+						!ref.pointsToWindowFunction())
 					{
 						state = false;
 						break;

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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -629,6 +629,31 @@
 			 */
 			childResult = childResult.modifyAccessPaths(restrictionList);
 
+			/*
+			 * If we have a subquery select with window function columns, we 
+			 * have the following simplified querytre before the above call:
+			 *    SELECT -> PRN -> SELECT
+			 * where middle PRN is what was originally a FromSubquery node.
+			 * With window functions we pull any WindowNodes into the tree, 
+			 * modify the lower selects RCL, and put a (noop) PRN on top in the 
+			 * above call. This results in:
+			 *    SELECT -> PRN -> PRN(noop) -> WN -> ...			 
+			 * Note that the RCL for the initial PRN and its child SELECT used 
+			 * to be the same object. After the above call, the initial PRNs RCL 
+			 * is incorrect, and we need to regenerate the VCNs. 
+			 */
+			if (childResult instanceof ProjectRestrictNode){
+				ProjectRestrictNode prn = (ProjectRestrictNode) childResult;
+				if (prn.childResult instanceof WindowNode){
+					/* 
+					 * We have a window function column in the RCL of our child 
+					 * PRN, and need to regenerate the VCNs.
+					 */					
+					resultColumns.genVirtualColumnNodes( prn.childResult, 
+														 prn.childResult.getResultColumns() );
+				}
+			}
+			
 			/* Mark this node as having the truly ... for
 			 * the underlying tree.
 			 */
@@ -1093,8 +1118,10 @@
 		 * view or derived table which couldn't be flattened, then see
 		 * if we can push any of the predicates which just got pushed
 		 * down to our level into the SelectNode.
-		 */
-		if (pushPList != null && (childResult instanceof SelectNode))
+		 */			
+		if (pushPList != null && 
+			childResult instanceof SelectNode &&
+			!resultColumns.containsWindowFunctionResultColumn() )
 		{
 			pushPList.pushExpressionsIntoSelect((SelectNode) childResult, false);
 		}
@@ -1382,9 +1409,7 @@
    		// }
    		// static Method exprN = method pointer to exprN;
 
-
-
-
+	
 		// Map the result columns to the source columns
 		int[] mapArray = resultColumns.mapSourceColumns();
 		int mapArrayItem = acb.addItem(new ReferencedColumnsDescriptorImpl(mapArray));
@@ -1432,7 +1457,7 @@
 		 *  arg11: estimated cost
 		 *  arg12: close method
 		 */
-
+		
 		acb.pushGetResultSetFactoryExpression(mb);
 		if (genChildResultSet)
 			childResult.generateResultSet(acb, mb);

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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -1710,4 +1710,35 @@
         return false;
 	}
 
+	/**
+	 * Check whether this ResultColumn immediate expression is a window function 
+	 * column or not.
+	 *
+	 * @return true if RCs expression is a window function column, false if not.
+	 */
+	public boolean expressionIsWindowFunction() 
+	{			
+		if (getExpression() instanceof WindowFunctionColumnNode){
+			return true;
+		}
+		return false;
+	}
+	
+	/**
+	 * Check whether this ResultColumn is a window function column or not, but 
+	 * do not traverse the complete chain of references.
+	 *
+	 * @return true if RC is a window function column, false if not.
+	 */
+	public boolean isWindowFunction() 
+	{	
+		ValueNode expr = getExpression();
+		
+		if (expr instanceof WindowFunctionColumnNode || 
+			(expr instanceof VirtualColumnNode && 
+			 expr.getSourceResultColumn().getExpression() instanceof WindowFunctionColumnNode)){
+			return true;
+		}
+		return false;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=632494&r1=632493&r2=632494&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Fri Feb 29 15:24:54 2008
@@ -1092,6 +1092,10 @@
 				{
 					continue;
 				}
+				/* Window function columns are added by the WindowResultSet for this levels RCL */
+				if (rc.isWindowFunction()) {
+					continue;
+				}
 			}
 
 
@@ -4115,4 +4119,40 @@
 	{
 		return size() - orderBySelect - numGeneratedColumns();
 	}
+	
+	/**
+	 * Return whether or not this RCL contains any window ResultColumns
+	 *
+	 * @return Whether or not this RCL contains any window ResultColumns
+	 */
+	public boolean containsWindowFunctionResultColumn()
+	{		
+		int size = size();
+		for (int index = 0; index < size; index++) {
+			ResultColumn rc = (ResultColumn) elementAt(index);			
+			if (rc.isWindowFunction()){
+				return true;
+			}
+		}
+		return false;
+	}
+
+	/*
+	 * Remove all window functions columns from this list
+	 */
+	void removeWindowFunctionColumns() {
+		/* Walk the list backwards as we are removing elements */
+		int sz = size();
+		for (int index = sz - 1; index >= 0; index--) {
+			ResultColumn rc = (ResultColumn) elementAt(index);			
+			if (rc.isWindowFunction()){
+				removeElementAt(index);
+			}
+		}
+		/* 
+		 * Remap VirtualColumnIds now that window function columns are gone. 
+		 * Needed for ordering to work.
+		 */
+		resetVirtualColumnIds();
+	}	
 }

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberColumnNode.java?rev=632494&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberColumnNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberColumnNode.java Fri Feb 29 15:24:54 2008
@@ -0,0 +1,81 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.RowNumberColumnNode
+
+   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 org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.types.TypeId;
+
+import java.sql.Types;
+
+public final class RowNumberColumnNode extends WindowFunctionColumnNode
+{
+	
+	/**
+	 * Initializer for a RowNumberColumnNode
+	 *
+	 * @exception StandardException
+	 */
+	public void init()
+		throws StandardException
+	{
+		super.init();
+		setType( TypeId.getBuiltInTypeId( Types.BIGINT ),
+				 TypeId.LONGINT_PRECISION,
+				 TypeId.LONGINT_SCALE, 
+				 false,
+				 TypeId.LONGINT_MAXWIDTH);			
+	}
+        
+	/**
+	 * Initializer for a RowNumberColumn node
+	 *
+	 * @paran arg1 The window definition
+	 *
+	 * @exception StandardException
+	 */
+	public void init(Object arg1)
+		throws StandardException
+	{
+		this.init();		
+		setWindowNode((WindowNode) arg1);
+	}
+            
+	public boolean isEquivalent(ValueNode o) throws StandardException
+	{
+        /* Two RowNumberColumnNodes should never be equivalent */
+        return false;
+	}
+
+	/**
+	 * Indicate whether this column is ascending or not.
+	 * By default assume that all ordered columns are
+	 * necessarily ascending.  If this class is inherited
+	 * by someone that can be desceneded, they are expected
+	 * to override this method.
+	 *
+	 * @return true
+	 */
+	public boolean isAscending()
+	{
+		return true;
+	}
+}		

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

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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -33,14 +33,11 @@
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 
-import org.apache.derby.iapi.types.TypeId;
-import org.apache.derby.iapi.types.DataTypeDescriptor;
 
 import org.apache.derby.iapi.reference.Limits;
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.error.StandardException;
 
-import org.apache.derby.iapi.store.access.TransactionController;
 
 import org.apache.derby.iapi.services.sanity.SanityManager;
 
@@ -1242,11 +1239,29 @@
 				throws StandardException
 	{
 		boolean				eliminateSort = false;
-		PredicateList		restrictionList;
-		ResultColumnList	prRCList;
 		ResultSetNode		prnRSN;
+		boolean				hasWindowFunction = false;
+		ResultColumnList	originalRCL = this.resultColumns.copyListAndObjects();		
 
-
+		/*
+		 * Even if we have a window function, we must do all projection and
+		 * restriction at this stage. Restricting on a window function, i.e
+		 *    SELECT ... <WINDOWFUNCTION> AS W ... WHERE W ...
+		 * is not allowed. Restrictions need to be evaluated in an outer SELECT
+		 * to achieve this.
+		 */
+		hasWindowFunction = resultColumns.containsWindowFunctionResultColumn();		
+		if (hasWindowFunction) {				
+			/*
+			 * Remove any window function columns now, and reinsert them from
+			 * the copy made above once grouping and ordering has been performed.
+			 */						
+			resultColumns.removeWindowFunctionColumns();			
+			if (orderByList != null) {
+				orderByList.adjustForWindowFunctionColumns();				
+			}
+		}
+		
 		prnRSN = (ResultSetNode) getNodeFactory().getNode(
 								C_NodeTypes.PROJECT_RESTRICT_NODE,
 								fromList.elementAt(0),	/* Child ResultSet */
@@ -1399,6 +1414,10 @@
 								null,
 								null,
 								getContextManager());
+				/*
+				 * Remove added ordering columns from the saved original RCL
+				*/								
+				originalRCL.removeOrderByColumns();				
 			}
 		}
 
@@ -1447,6 +1466,67 @@
 		/* Set the cost of this node in the generated node */
 		prnRSN.costEstimate = costEstimate.cloneMe();
 
+		/*
+		 * Now that grouping and ordering has been performed, we can reinsert 
+		 * the window function column(s), and pull the WindowNode(s) up from 
+		 * under the window function ResultColumn(s).
+		 */
+		if (hasWindowFunction) {		
+			/*
+			 * Pull all WindowNodes up from under their window function column,
+			 * and place the WindowNode on top.
+			 */			
+			int size = originalRCL.size();						
+			int windowFunctionLevel = 0;		
+			ResultColumnList windowFunctionRCL = originalRCL.copyListAndObjects();										
+			for (int index = 0; index < size; index++) {
+				ResultColumn rc = (ResultColumn) originalRCL.elementAt(index);				
+				/*
+				 * NOTE: We only care about window function columns that appear 
+				 * here, and not about references into subquerys or similar.
+				 */
+				if (rc.expressionIsWindowFunction()) {					
+			
+					WindowFunctionColumnNode wfcn = (WindowFunctionColumnNode) rc.getExpression();
+					WindowNode windowNode = wfcn.getWindowNode();
+
+					windowFunctionLevel++;
+					
+					windowNode.setResultColumns(windowFunctionRCL);
+					windowNode.setChildResult((ResultSetNode) prnRSN);
+					windowNode.setWindowFunctionLevel(windowFunctionLevel);
+					
+					/* Set the cost of this node in the generated node */
+					windowNode.costEstimate = costEstimate.cloneMe();
+					/* Set the new top */
+					prnRSN = windowNode;
+				}
+			}
+
+			/*
+			 * Top off with a PRN as this is the intent of this method. Even 
+			 * though this PRN is a noop and will never be generated, we should 
+			 * leave it here as other parts of the code expects to find 
+			 * PRN -> WN.
+			 */
+			ResultColumnList newRCL = windowFunctionRCL.copyListAndObjects(); 
+			newRCL.genVirtualColumnNodes(prnRSN, prnRSN.getResultColumns());
+						
+			prnRSN = (ResultSetNode) getNodeFactory().getNode(
+				C_NodeTypes.PROJECT_RESTRICT_NODE,
+				prnRSN, /* Child ResultSet */
+				newRCL, /* Projection */ 
+				null, /* Restriction */
+				null, /* Restriction as PredicateList */
+				null, /* Subquerys in Projection */
+				null, /* Subquerys in Restriction */
+				null,
+				getContextManager());
+		
+			/* Set the cost of this node in the generated node */
+			prnRSN.costEstimate = costEstimate.cloneMe();						
+		}
+	
 		return prnRSN;
 	}
 

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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -622,10 +622,12 @@
 		 *           b) it appears within a WHERE clause but does not itself 
 		 *              contain a WHERE clause with other subqueries in it. 
 		 *          (DERBY-3301)
+		 *  o It does not contain a window function column.
 		 */
 		flattenable = (resultSet instanceof RowResultSetNode) &&
 					  underTopAndNode && !havingSubquery &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
+					  !hasWindowFunctionColumn() &&
 					  parentComparisonOperator instanceof BinaryComparisonOperatorNode;
 		if (flattenable)
 		{
@@ -690,12 +692,14 @@
 		 *           b) it appears within a WHERE clause but does not itself 
 		 *              contain a WHERE clause with other subqueries in it. 
 		 *          (DERBY-3301)
+		 *  o The subquery has a window function column.
 		 */
 		boolean flattenableNotExists = (isNOT_EXISTS() || canAllBeFlattened());
 
 		flattenable = (resultSet instanceof SelectNode) &&
 					  underTopAndNode && !havingSubquery &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
+					  !hasWindowFunctionColumn() &&
 					  (isIN() || isANY() || isEXISTS() || flattenableNotExists ||
                        parentComparisonOperator != null);
 
@@ -2386,5 +2390,36 @@
 			 */
 			return false;
 		}
+	}
+	
+	/**
+	 * Check whether this subquery has a window function column. 
+	 * Used in flattening decision making.
+	 * 
+	 * @return true if this subquery has a windowfunction column.	 
+	 */
+	public boolean hasWindowFunctionColumn() 
+			throws StandardException
+	{
+		if (resultSet instanceof SelectNode){
+			/*
+			 * Check the subquery select for a window function column in the RCL 
+			 */
+			SelectNode sn = (SelectNode) resultSet;
+			ResultColumnList rcl = sn.getResultColumns();
+			/*
+			CollectNodesVisitor cnv = 
+					new CollectNodesVisitor(WindowFunctionColumnNode.class,
+											WindowFunctionColumnNode.class);
+			rcl.accept(cnv);
+			return !cnv.getList().isEmpty();
+			 */
+			/*
+			 * Check this SelectNodes immediate RCL, 
+			 * and not traverse the full tree 
+			 */
+			rcl.containsWindowFunctionResultColumn();
+		}
+		return false;
 	}
 }

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionColumnNode.java?rev=632494&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionColumnNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionColumnNode.java Fri Feb 29 15:24:54 2008
@@ -0,0 +1,171 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.WindowFunctionColumnNode
+
+   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;
+
+/**
+ * Abstract WindowFunctionColumnNode for window function columns in a 
+ * ResultColumnList. 
+ * <p>
+ * This abstract class contains the common code for window function columns, 
+ * and simplifies checks in the code for compilation and execution.
+ * <p>
+ */
+abstract class WindowFunctionColumnNode extends ResultColumn
+{
+	/* 
+	 * WindowNode containing the window definition 
+	 * for this window function column
+	 */
+	private WindowNode windowNode;
+	
+	
+	/**
+	 * Initializer for a WindowFunctionColumnNode
+	 *
+	 * @exception StandardException
+	 */
+	public void init()
+		throws StandardException
+	{		
+		this.windowNode = null;
+		this.isGenerated = true;		
+	}	
+	
+	/**
+	 * getWindowNode
+	 *
+	 * @return the WindowNode for this window function column 
+	 */
+	public WindowNode getWindowNode()
+	{
+		return this.windowNode;
+	}
+	
+	/**
+	 * setWindowNode
+	 *
+	 * @param	the WindowNode with the window definition for this 
+	 *			window function column 
+	 */	
+	public void setWindowNode(WindowNode wn)
+	{
+		this.windowNode = wn;
+	}
+	
+	/**
+	 * Preprocess an expression tree.  We do a number of transformations
+	 * here (including subqueries, IN lists, LIKE and BETWEEN) plus
+	 * subquery flattening.
+	 * NOTE: This is done before the outer ResultSetNode is preprocessed.
+	 *
+	 * @param	numTables			Number of tables in the DML Statement
+	 * @param	outerFromList		FromList from outer query block
+	 * @param	outerSubqueryList	SubqueryList from outer query block
+	 * @param	outerPredicateList	PredicateList from outer query block
+	 *
+	 * @return		The modified expression
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public ValueNode preprocess(int numTables,
+								FromList outerFromList,
+								SubqueryList outerSubqueryList,
+								PredicateList outerPredicateList) 
+					throws StandardException
+	{
+		/* 
+		 * Preprocess our WindowNode
+		 */
+		windowNode = (WindowNode)windowNode.preprocess(numTables, 
+			outerFromList,
+			outerSubqueryList,
+			outerPredicateList);
+		return this;
+	}
+	
+	
+	/**
+	 * Bind this expression.  This means binding the sub-expressions,
+	 * as well as figuring out what the return type is for this expression.
+	 * In this case, there are no sub-expressions, and the return type
+	 * is already known, so this is just a stub.
+	 *
+	 * @param fromList		The FROM list for the query this
+	 *						expression is in, for binding columns.
+	 * @param subqueryList		The subquery list being built as we find SubqueryNodes
+	 * @param aggregateVector	The aggregate vector being built as we find AggregateNodes
+	 *
+	 * @return	The new top of the expression tree.
+	 *
+	 * @exception StandardException		Thrown on error. Although this class
+	 * doesn't throw this exception, it's subclasses do and hence this method
+	 * signature here needs to have throws StandardException 
+	 */
+	public ValueNode bindExpression(
+			FromList fromList, 
+			SubqueryList subqueryList,
+			Vector	aggregateVector)
+		throws StandardException
+	{
+		/*
+		 * Call into the windows bind method
+		 */		
+		windowNode.bind(fromList, subqueryList, aggregateVector);		
+		return this;
+	}
+        
+	/**
+	 * Do code generation for this window function
+	 *
+	 * @param acb	The ExpressionClassBuilder for the class we're generating
+	 * @param mb	The method the code to place the code
+	 *
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void generateExpression(ExpressionClassBuilder acb,
+                                       MethodBuilder mb)                                       
+		throws StandardException
+	{
+		/* 
+		 * Window function columns are added by the WindowResultSet, so we 
+		 * should never call into here.
+		 */
+		if (SanityManager.DEBUG)
+			SanityManager.ASSERT(false, "Code generation for WindowFunctionColumnNode is unimplemented");
+	}        
+
+	/**
+	 * Window functions do not have a (base)tablename, so we return null.
+	 *
+	 * @override
+	 */
+	public String getTableName()
+	{
+		return null;
+	}
+}
\ No newline at end of file

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

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowNode.java?rev=632494&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowNode.java Fri Feb 29 15:24:54 2008
@@ -0,0 +1,278 @@
+/*
+	Derby - Class org.apache.derby.impl.sql.compile.WindowNode
+ 
+   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.Properties;
+
+import java.util.Vector;
+import org.apache.derby.iapi.error.StandardException;
+
+import org.apache.derby.iapi.reference.ClassName;
+import org.apache.derby.iapi.services.classfile.VMOpcode;
+import org.apache.derby.iapi.services.compiler.MethodBuilder;
+
+import org.apache.derby.iapi.services.io.FormatableBitSet;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.util.JBitSet;
+
+/**
+ * This node type handles window functions. It takes a
+ * FromTable as its source ResultSetNode, and generates an
+ * WindowResultSet.
+ * 
+ * The implementation is based on IndexToBaseRowNode.
+ */
+public class WindowNode extends SingleChildResultSetNode {	
+	
+	/* The following members define the window properties 
+	 * 
+	 * NOTE: Named windows, and window partitions except the full ResultSet 
+	 *       are not yet supported.
+	 */
+	private String windowName;
+	private ResultColumnList partitionDefinition;
+	private OrderByList orderByList;
+	private Object frameDefinition; // TODO
+		
+	/* 
+	 * When there are multiple window function columns in a RCL, 
+	 * 'windowFunctionLevel' is used to identify which level this WindowNode 
+	 * is at in the chain.
+	 */
+	private int windowFunctionLevel;
+	
+	private Properties tableProperties;
+	private int numTables;
+	
+	public void init(Object windowName,
+		Object partitionDefinition,
+		Object orderByList,
+		Object frameDefinition)
+		throws StandardException {
+		this.windowName = (String) windowName;
+		this.partitionDefinition = (ResultColumnList) partitionDefinition;
+		this.orderByList = (OrderByList) orderByList;
+		this.frameDefinition = (Object) frameDefinition; // TODO		
+		this.windowFunctionLevel = -1;
+	}
+
+	/*
+	 *  ResultSet implementation
+	 */
+	
+	/**
+	 * Preprocess a WindowNode by calling into its source preprocess.
+	 *
+	 * RESOLVE: We should probably push predicates down as well?
+	 *
+	 * @param numTables			The number of tables in the DML Statement	 
+	 * @param fromList			The from list, if any
+	 * @param subqueryList		The subquery list, if any
+	 * @param predicateList		The predicate list, if any
+	 *
+	 * @return ResultSetNode at top of preprocessed tree.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public ResultSetNode preprocess(int numTables,
+		FromList fromList,
+		SubqueryList subqueryList,
+		PredicateList predicateList)
+		throws StandardException {
+
+		/* Set up the referenced table map */
+		this.numTables = numTables;
+		referencedTableMap = new JBitSet(numTables);
+		int flSize = fromList.size();
+		for (int index = 0; index < flSize; index++)
+		{
+			referencedTableMap.or(((FromTable) fromList.elementAt(index)).
+													getReferencedTableMap());
+		}			
+		
+		return this;
+	}
+
+	/**
+	 * Bind this node. 
+	 *
+	 * @param fromList		The FROM list for the query this
+	 *						expression is in, for binding columns.
+	 * @param subqueryList		The subquery list being built as we find SubqueryNodes
+	 * @param aggregateVector	The aggregate vector being built as we find AggregateNodes
+	 *
+	 * @return	The new top of the expression tree.
+	 *
+	 * @exception StandardException		Thrown on error. Although this class
+	 * doesn't throw this exception, it's subclasses do and hence this method
+	 * signature here needs to have throws StandardException 
+	 */
+	public WindowNode bind(
+			FromList fromList, 
+			SubqueryList subqueryList,
+			Vector	aggregateVector)
+		throws StandardException
+	{
+		/*		 
+		 * This is simply a stub returning the new top of the querytree, since 
+		 * there is nothing to as long as we only support ROW_NUMBER(). It does 
+		 * not need any binding to source result columns.		 
+		 */
+		return this;
+	}
+	
+	/**
+	 * Generation of an WindowNode creates an WindowResultSet
+	 *
+	 * @param acb	The ActivationClassBuilder for the class being built
+	 * @param mb	the method  for the method to be built
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void generate(ActivationClassBuilder acb,	
+		MethodBuilder mb)
+		throws StandardException {
+		int rclSize = resultColumns.size();
+		FormatableBitSet referencedCols = new FormatableBitSet(rclSize);
+		int erdNumber = -1;
+		int numSet = 0;
+
+		/*
+		 ** Get the next ResultSet #, so that we can number this ResultSetNode,
+		 ** its ResultColumnList and ResultSet.
+		 */
+		assignResultSetNumber();
+
+		// Get the CostEstimate info for the underlying scan
+		costEstimate = getFinalCostEstimate();
+
+		acb.pushGetResultSetFactoryExpression(mb);
+
+		/* 
+		 * Build a FormatableBitSet for columns to copy from source. If there are 
+		 * multiple window function coulmns, they will be added right to left.		 
+		 */
+		int skip = 0;
+		for (int index = rclSize-1; index >= 0; index--) {		
+			ResultColumn rc = (ResultColumn) resultColumns.elementAt(index);
+			if ( rc.isWindowFunction() && skip < this.windowFunctionLevel) {
+				// Skip this
+				skip++;
+				continue;
+			}
+			// if not
+			referencedCols.set(index);
+			numSet++;
+		}
+
+		erdNumber = acb.addItem(referencedCols);
+
+		acb.pushThisAsActivation(mb); // arg 1
+
+		childResult.generate(acb, mb);	  // arg 2
+		mb.upCast(ClassName.NoPutResultSet);
+
+		/* row allocator */
+		resultColumns.generateHolder(acb, mb); // arg 3		
+
+		mb.push(resultSetNumber); //arg 4
+		mb.push(windowFunctionLevel); //arg 5
+
+		/* Pass in the erdNumber for the referenced column FormatableBitSet */
+		mb.push(erdNumber); // arg 6		
+
+		/* There is no restriction at this level, we just want to pass null. */
+		mb.pushNull(ClassName.GeneratedMethod); // arg 7
+		
+		mb.push(costEstimate.rowCount()); //arg 8
+		mb.push(costEstimate.getEstimatedCost()); // arg 9
+
+		mb.callMethod(VMOpcode.INVOKEINTERFACE, (String) null,			
+			"getWindowResultSet", ClassName.NoPutResultSet, 9);
+
+		/*
+		 ** Remember if this result set is the cursor target table, so we
+		 ** can know which table to use when doing positioned update and delete.
+		 */
+		if (cursorTargetTable) {
+			acb.rememberCursorTarget(mb);
+		}
+	}
+
+	/**
+	 * Consider materialization for this ResultSet tree if it is valid and cost 
+	 * effective. It is not valid if incorrect results would be returned.
+	 *
+	 * @return Top of the new/same ResultSet tree.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public ResultSetNode considerMaterialization(JBitSet outerTables)
+		throws StandardException {
+		/* 
+		 * For queries involving window functions like ROW_NUMBER() we should
+		 * most likely materialize the ResultSet.
+		 * 
+		 * Return a reference to ourselves.
+		 */
+		return this;
+	}
+
+	/**
+	 * Return whether or not to materialize this ResultSet tree.
+	 *
+	 * @return Whether or not to materialize this ResultSet tree.
+	 *			would return valid results.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public boolean performMaterialization(JBitSet outerTables)
+		throws StandardException {
+		/* 
+		 * Queries involving window functions will most likely benefit from 
+		 * materializing the ResultSet. It does not make sense for ROW_NUMBER 
+		 * though, so it should probably depend on what function is evaluated.
+		 */
+		return false;		
+	}
+	
+	/**
+	 * Get the windowFunctionLevel of this WindowNode in case there are 
+	 * multiple window functions in a RCL.
+	 *
+	 * @return the windowFunctionLevel for this window function column 
+	 */
+	public int getWindowFunctionLevel()
+	{
+		return this.windowFunctionLevel;
+	}
+	
+	/**
+	 * Set the windowFunctionLevel of this WindowNode in case there are 
+	 * multiple window functions in a RCL.
+	 *
+	 * @param	the window function level of this window function column 
+	 */	
+	public void setWindowFunctionLevel(int level)
+	{
+		this.windowFunctionLevel = level;
+	}
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowNode.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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -2269,7 +2269,9 @@
 |	<LONG: "long">
 |	<LTRIM: "ltrim">
 |	<NONE: "none">
+|	<OVER: "over">
 |	<ROLE: "role">
+|	<ROW_NUMBER: "row_number">
 |	<RTRIM: "rtrim">
 |	<SUBSTR:	"substr">
 |	<XML:	"xml">
@@ -4945,6 +4947,8 @@
 	ResultColumn	resultColumn;
 	ResultColumn	allResultColumn;
 	TableName	tableName;
+    String          columnName = null;
+
 }
 {
 	// This LOOKAHEAD is required because both a qualifiedName() and a
@@ -4972,6 +4976,54 @@
 		resultColumns.addResultColumn(allResultColumn);
 	}
 |
+		/*
+		 *  Lookahead to find window functions in unnamed windows
+		 *  TODO: Use a method rather than the explicit function names?
+		 *        Probably belongs in derivedColum()
+		 */
+		LOOKAHEAD(
+		{
+			getToken(1).kind == ROW_NUMBER &&
+			getToken(2).kind == LEFT_PAREN &&
+			getToken(3).kind == RIGHT_PAREN
+		}
+		)
+		<ROW_NUMBER> <LEFT_PAREN> <RIGHT_PAREN>        
+
+		/* 
+		 * TODO: <windowspecification> is ignored for now, but OVER is required
+		 */
+		<OVER> <LEFT_PAREN> <RIGHT_PAREN>
+
+		[ columnName = asClause() ]
+		{
+			/*
+			 * If there is no AS clause, use the windowfunction name as the result column name.
+			 */
+			if (columnName == null)
+			{
+				columnName = "row_number()";
+			}
+			WindowNode windowNode = (WindowNode) nodeFactory.getNode(
+											C_NodeTypes.WINDOW_NODE,
+											null,  // name
+											null,  // partition definition list
+											null,  // order by list
+											null,  // frame definition
+											getContextManager());
+			ValueNode expression = (ValueNode) nodeFactory.getNode(
+											C_NodeTypes.ROW_NUMBER_COLUMN_NODE,
+											windowNode,
+											getContextManager());
+			resultColumn = (ResultColumn) nodeFactory.getNode(
+											C_NodeTypes.RESULT_COLUMN,
+											columnName,
+											expression,
+											getContextManager());
+			/* Add the new ResultColumn to the end of the list */
+			resultColumns.addResultColumn(resultColumn);
+		}
+|
 	resultColumn = derivedColumn(resultColumns)
 	{
 		/* Add the new ResultColumn to the end of the list */
@@ -13545,6 +13597,7 @@
 	|	tok = <OLD>
 	|	tok = <OLD_TABLE>
 	|	tok = <OJ>
+	|	tok = <OVER>
 	|	tok = <PASCAL>
 	|	tok = <PASSING>
 	|	tok = <PLI>
@@ -13566,6 +13619,7 @@
 	|	tok = <ROLE>
 	|	tok = <ROW>
 //	|	tok = <ROW_COUNT>
+	|	tok = <ROW_NUMBER>
 	|   tok = <RR>
 	|   tok = <RS>
 	|	tok = <SCALE>

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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -823,6 +823,34 @@
 	}
 
 	/**
+		@see ResultSetFactory#getWindowResultSet
+		@exception StandardException	Thrown on error
+	 */
+	public NoPutResultSet getWindowResultSet(
+								Activation activation,
+								NoPutResultSet source,
+								GeneratedMethod rowAllocator,								
+								int resultSetNumber,
+								int level,
+								int erdNumber,								
+								GeneratedMethod restriction,
+								double optimizerEstimatedRowCount,
+								double optimizerEstimatedCost)																
+		throws StandardException
+	{
+		return new WindowResultSet(
+								activation,								
+								source,
+								rowAllocator,								
+								resultSetNumber,
+								level,
+								erdNumber,
+								restriction,
+								optimizerEstimatedRowCount,
+								optimizerEstimatedCost);
+	}
+
+	/**
 		@see ResultSetFactory#getNestedLoopJoinResultSet
 		@exception StandardException thrown on error
 	 */

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=632494&r1=632493&r2=632494&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 Fri Feb 29 15:24:54 2008
@@ -69,6 +69,7 @@
 import org.apache.derby.impl.sql.execute.UnionResultSet;
 import org.apache.derby.impl.sql.execute.UpdateResultSet;
 import org.apache.derby.impl.sql.execute.VTIResultSet;
+import org.apache.derby.impl.sql.execute.WindowResultSet;
 import org.apache.derby.impl.sql.execute.DependentResultSet;
 
 import org.apache.derby.iapi.sql.execute.RunTimeStatistics;
@@ -104,6 +105,7 @@
 import org.apache.derby.impl.sql.execute.rts.RealUnionResultSetStatistics;
 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.ResultSetStatistics;
 import org.apache.derby.impl.sql.execute.rts.RunTimeStatisticsImpl;
 
@@ -799,6 +801,24 @@
 											irtbrrs.optimizerEstimatedCost,
 											getResultSetStatistics(
 																irtbrrs.source)
+											);
+		}
+		else if (rs instanceof WindowResultSet)
+		{
+			WindowResultSet wrs = (WindowResultSet) rs;
+
+			return new RealWindowResultSetStatistics(											
+											wrs.numOpens,
+											wrs.rowsSeen,
+											wrs.rowsFiltered,
+											wrs.constructorTime,
+											wrs.openTime,
+											wrs.nextTime,
+											wrs.closeTime,
+											wrs.resultSetNumber,											
+											wrs.optimizerEstimatedRowCount,
+											wrs.optimizerEstimatedCost,
+											getResultSetStatistics(wrs.source)		
 											);
 		}
 		else if (rs instanceof RowResultSet)

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/WindowResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/WindowResultSet.java?rev=632494&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/WindowResultSet.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/WindowResultSet.java Fri Feb 29 15:24:54 2008
@@ -0,0 +1,307 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.WindowResultSet
+
+   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.services.sanity.SanityManager;
+import org.apache.derby.iapi.services.loader.GeneratedMethod;
+
+import org.apache.derby.iapi.sql.Activation;
+import org.apache.derby.iapi.sql.execute.ExecRow;
+import org.apache.derby.iapi.sql.execute.NoPutResultSet;
+
+import org.apache.derby.iapi.types.DataValueDescriptor;
+
+import org.apache.derby.iapi.reference.SQLState;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.services.io.FormatableBitSet;
+
+/**
+ * WindowResultSet
+ * 
+ * This ResultSet handles a window function ResultSet. 
+ * The implementation is based on IndexRowToBaseRowResultSet.
+ * 
+ * The ResultSet is opened using openCore().  Each row is fetched and any 
+ * restrictions evaluated for each row in a do-while loop in getNextRowCore().
+ * The ResultSet is closed using closeCore().
+ * 
+ * The WindowResultSet may have any type ResultSet as its source 
+ * ResultSet to accomodate future window function implementation. 
+ * Ranking window functions can use an already grouped or ordered 
+ * resultset as its source.
+ */
+class WindowResultSet extends NoPutResultSetImpl 
+{
+	private GeneratedMethod restriction = null;
+	private GeneratedMethod row;
+	public NoPutResultSet source = null;
+	public long restrictionTime;
+
+	private FormatableBitSet referencedColumns;
+	private ExecRow allocatedRow;
+	private int rownumber;
+	private int level;
+	
+	/**
+	 *  Constructor
+	 *
+	 *	@param	activation			The activation
+	 *	@param	resultSetNumber		The resultSetNumber
+	 *  @param	optimizerEstimatedRowCount	The optimizer's estimated number
+	 *										of rows.
+	 *  @param	optimizerEstimatedCost		The optimizer's estimated cost
+	 *  @param  restriction
+	 */
+	WindowResultSet(Activation activation,
+		NoPutResultSet source,
+		GeneratedMethod rowAllocator,
+		int resultSetNumber,
+		int level,
+		int erdNumber,		
+		GeneratedMethod restriction,
+		double optimizerEstimatedRowCount,
+		double optimizerEstimatedCost) {
+		super(activation,
+			resultSetNumber,
+			optimizerEstimatedRowCount,
+			optimizerEstimatedCost);
+
+		if (SanityManager.DEBUG) {
+			SanityManager.ASSERT(activation != null, "activation expected to be non-null");
+			SanityManager.ASSERT(resultSetNumber >= 0, "resultSetNumber expected to be >= 0");
+			SanityManager.ASSERT(level > 0, "level expected to be > 0");			
+		}
+		this.restriction = restriction;
+		this.source = source;
+		this.row = rowAllocator;
+		this.allocatedRow = null;
+		this.rownumber = 0;
+		this.level = level;
+		 
+		if (erdNumber != -1)
+		{
+			this.referencedColumns = (FormatableBitSet)(activation.getPreparedStatement().
+								getSavedObject(erdNumber));
+		}
+		
+		recordConstructorTime();
+	}
+
+	/**
+	 * open this ResultSet.
+	 *
+	 * @exception StandardException thrown if cursor finished.
+	 */
+	public void openCore() throws StandardException {
+		if (SanityManager.DEBUG) {
+			SanityManager.ASSERT(!isOpen,
+				"WindowResultSet already open");
+		}
+
+		beginTime = getCurrentTimeMillis();
+
+		/* Call into the source openCore() */
+		source.openCore();
+
+		isOpen = true;
+		rownumber = 0;
+		numOpens++;
+		openTime += getElapsedMillis(beginTime);
+	}
+
+	/**
+	 * reopen this ResultSet.
+	 *
+	 * @exception StandardException thrown if cursor finished.
+	 */
+	public void reopenCore() throws StandardException {
+		if (SanityManager.DEBUG) {
+			SanityManager.ASSERT(isOpen, "WindowResultSet already open");
+		}
+
+		beginTime = getCurrentTimeMillis();
+
+		/* Reopen the source */
+		source.reopenCore();
+
+		rownumber = 0;
+		numOpens++;
+		openTime += getElapsedMillis(beginTime);
+	}
+
+	/**
+	 * Return the requested values computed from the next row (if any) for which
+	 * the restriction evaluates to true.
+	 * <p>
+	 * restriction and projection parameters are evaluated for each row.
+	 *
+	 * @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 sourceRow = null;
+		ExecRow retval = null;
+		boolean restrict = false;
+		DataValueDescriptor restrictBoolean;
+		long beginRT = 0;
+
+		beginTime = getCurrentTimeMillis();
+		if (!isOpen) {
+			throw StandardException.newException(SQLState.LANG_RESULT_SET_NOT_OPEN, "next");
+		}
+
+		/*
+		 * Loop until we get a row from the source that qualifies, or there are 
+		 * no more rows to qualify. For each iteration fetch a row from the 
+		 * source, and evaluate against the restriction if any.
+		 */
+		ExecRow tmpRow = null;
+		
+		do {			
+			sourceRow = source.getNextRowCore();			
+			if (sourceRow != null) {
+				this.rownumber++;
+				tmpRow = getAllocatedRow();
+				populateFromSourceRow(sourceRow, tmpRow);						
+				setCurrentRow(tmpRow);
+
+				/* Evaluate any restrictions */
+				restrictBoolean = (DataValueDescriptor) ((restriction == null) ? 
+									null : restriction.invoke(activation));
+
+				restrictionTime += getElapsedMillis(beginRT);
+
+				// if the result is null, we make it false --
+				// so the row won't be returned.
+				restrict = (restrictBoolean == null) ||
+					((!restrictBoolean.isNull()) &&
+					restrictBoolean.getBoolean());
+
+				if (!restrict) {
+					rowsFiltered++;
+					clearCurrentRow();
+				}
+
+				/* Update the run time statistics */
+				rowsSeen++;
+				retval = currentRow;
+			} else {
+				clearCurrentRow();
+				retval = null;
+			}
+		} while ((sourceRow != null) && (!restrict));
+
+		nextTime += getElapsedMillis(beginTime);
+		return retval;
+	}
+
+	/**
+	 * If the result set has been opened, close the open scan.
+	 *
+	 * @exception StandardException thrown on error
+	 */
+	public void close() throws StandardException {
+		beginTime = getCurrentTimeMillis();
+		if (isOpen) {
+			clearCurrentRow();
+			
+			/* 
+			 * Make sure to close the source 
+			 */
+			source.close();
+			super.close();
+		} else if (SanityManager.DEBUG) {
+			SanityManager.DEBUG("CloseRepeatInfo", "Close of WindowResultSet repeated");
+		}
+
+		closeTime += getElapsedMillis(beginTime);
+	}
+
+	/**
+	 * @exception StandardException thrown on failure to open
+	 */
+	public void populateFromSourceRow(ExecRow srcrow, ExecRow destrow)
+		throws StandardException 
+	{		
+		int srcindex = 1;
+		int levelCnt = 1;
+		try {			
+			DataValueDescriptor[] columns = destrow.getRowArray();
+			for (int index = 0; index < columns.length; index++) {				
+				if (referencedColumns != null && (!referencedColumns.get(index))) {
+					/* 
+					 * TODO: For now any unreferenced columm is a RowNumberColumn 
+					 */
+					if (levelCnt > this.level){
+						/* Value will be added at higher level WindowNode */					
+						continue; 
+					}
+					columns[index].setValue((long)this.rownumber);
+					levelCnt++;
+				} else {								
+					destrow.setColumn(index+1, srcrow.getColumn(srcindex));
+					srcindex++;
+				}				
+			}
+		} catch (StandardException se) {
+			throw se;
+		} catch (Throwable t) {
+			throw StandardException.unexpectedUserException(t);
+		}
+	}
+
+	/**
+	 * 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 == NoPutResultSet.CURRENT_RESULTSET_ONLY) {
+			return totTime - source.getTimeSpent(ENTIRE_RESULTSET_TREE);
+		} else {
+			return totTime;
+		}
+	}
+
+	/**
+	 * Cache the ExecRow for this result set.
+	 *
+	 * @return The cached ExecRow for this ResultSet
+	 *
+	 * @exception StandardException thrown on failure.
+	 */
+	private ExecRow getAllocatedRow()
+		throws StandardException {
+		if (allocatedRow == null) {
+			allocatedRow = (ExecRow) row.invoke(activation);
+		}
+
+		return allocatedRow;
+	}
+}
+        
\ No newline at end of file

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

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealWindowResultSetStatistics.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealWindowResultSetStatistics.java?rev=632494&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealWindowResultSetStatistics.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/rts/RealWindowResultSetStatistics.java Fri Feb 29 15:24:54 2008
@@ -0,0 +1,156 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.rts.RealWindowResultSetStatistics
+
+   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;
+
+/**
+ * ResultSetStatistics implemenation for WindowResultSet.
+ */
+public class RealWindowResultSetStatistics 
+	extends RealNoPutResultSetStatistics
+{	
+	
+	/* Leave these fields public for object inspectors */	
+	public ResultSetStatistics childResultSetStatistics;
+
+	// CONSTRUCTORS
+
+	/**
+	 * 
+	 *
+	 */
+    public	RealWindowResultSetStatistics(								
+								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 interface
+
+	/**
+	 * 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)
+	{
+		initFormatInfo(depth);
+
+		return
+			indent + MessageService.getTextMessage(
+							SQLState.RTS_NUM_OPENS) +
+							" = " + numOpens + "\n" +
+			indent + MessageService.getTextMessage(
+							SQLState.RTS_ROWS_SEEN) +
+							" = " + rowsSeen + "\n" +
+			dumpTimeStats(indent, subIndent) + "\n" +
+			dumpEstimatedCosts(subIndent) + "\n" +
+			childResultSetStatistics.getStatementExecutionPlanText(sourceDepth) + "\n";
+	}
+
+	/**
+	 * 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 getStatementExecutionPlanText(depth);		
+	}
+
+
+	// Class implementation
+	
+	public String toString()
+	{
+		return getStatementExecutionPlanText(0);
+	}
+
+	public java.util.Vector getChildren()
+	{
+		java.util.Vector children = new java.util.Vector();
+		children.addElement(childResultSetStatistics);
+		return children;
+	}
+
+	/**
+	 * If this node is on a database item (like a table or an index), then provide a
+	 * string that describes the on item.
+	 *
+	 */
+	public String getNodeOn(){
+		return MessageService.getTextMessage(
+									SQLState.RTS_FOR_TAB_NAME,
+									"<WINDOW FUNCTION>");
+	}
+	
+	/**
+	 * Format for display, a name for this node.
+	 *
+	 */
+	public String getNodeName(){
+		return MessageService.getTextMessage(SQLState.RTS_IRTBR);
+	}
+
+	/**
+	 * Return the ResultSetStatistics for the child of this node.
+	 *
+	 * @return The ResultSetStatistics for the child of this node.
+	 */
+	ResultSetStatistics getChildResultSetStatistics()
+	{
+		return childResultSetStatistics;
+	}
+}

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

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java?rev=632494&r1=632493&r2=632494&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java Fri Feb 29 15:24:54 2008
@@ -47,68 +47,207 @@
 	 * 
 	 * @throws SQLException
 	 */
-	public void testBasicOperations() 
-		throws SQLException 
-	{   
+	public void testBasicOperations()
+		throws SQLException {
 		Statement s = createStatement();
 
 		s.executeUpdate("create table t1 (a int, b int)");
 		s.executeUpdate("create table t2 (x int)");
-		s.executeUpdate("create table t3 (y int)");      
+		s.executeUpdate("create table t3 (y int)");
+		s.executeUpdate("create table t4 (a int, b int)");
 
-		s.executeUpdate("insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5)");
+		s.executeUpdate("insert into t1 values (10,100),(20,200),(30,300),(40,400),(50,500)");
 		s.executeUpdate("insert into t2 values (1),(2),(3),(4),(5)");
-		s.executeUpdate("insert into t3 values (4),(5),(6),(7),(8)");
+		s.executeUpdate("insert into t3 values (4),(5),(6),(7),(8)");		
+		s.executeUpdate("insert into t4 values (10,100),(20,200)");
+
 		/*
 		 * Positive testing of Statements
-		 */
-		ResultSet rs = s.executeQuery("select row_number(), t1.* from t1");
-		String [][] expectedRows = {{"1","1","1"},{"2","2","2"},{"3","3","3"},{"4","4","4"},{"5","5","5"}};
-		JDBC.assertFullResultSet(rs,expectedRows);
-
-		rs = s.executeQuery("select row_number(), t1.* from t1 where a > 3");
-		expectedRows = new String [][] {{"1","4","4"},{"2","5","5"}};
-		JDBC.assertFullResultSet(rs,expectedRows);
-
-		rs = s.executeQuery("select row_number(), a from t1 where b > 3");
-		expectedRows = new String [] [] {{"1","4"},{"2","5"}};
-		JDBC.assertFullResultSet(rs,expectedRows);
-
-		rs = s.executeQuery("select row_number() as r, a from t1 where b > 3");
-		expectedRows = new String [] [] {{"1","4"},{"2","5"}};
-		JDBC.assertFullResultSet(rs,expectedRows);
-
-		rs = s.executeQuery("select * from (select row_number() as r, t1.* from t1) as tr where r < 3");
-		expectedRows = new String [] [] {{"1","1","1"},{"2","2","2"}};
-		JDBC.assertFullResultSet(rs,expectedRows);        
-
-		rs = s.executeQuery("select * from (select row_number() as r, t1.* from t1) as tr where r > 3");
-		expectedRows = new String [] [] {{"4","4","4"},{"5","5","5"}};
-		JDBC.assertFullResultSet(rs,expectedRows);        
-
+		 *
+		 * Simple queries
+		 */		
+		ResultSet rs = s.executeQuery("select row_number() over (), t1.* from t1");
+		String[][] expectedRows = {{"1", "10", "100"}, {"2", "20", "200"}, {"3", "30", "300"}, {"4", "40", "400"}, {"5", "50", "500"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select row_number() over (), t1.* from t1 where a > 30");
+		expectedRows = new String[][]{{"1", "40", "400"}, {"2", "50", "500"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select row_number() over (), a from t1 where b > 300");
+		expectedRows = new String[][]{{"1", "40"}, {"2", "50"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select row_number() over () as r, a from t1 where b > 300");
+		expectedRows = new String[][]{{"1", "40"}, {"2", "50"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Two instances of row_number columns in the same RCL */
+		rs = s.executeQuery("select row_number() over (), row_number() over (), b from t1 where b <= 300");
+		expectedRows = new String[][]{{"1", "1", "100"}, {"2", "2", "200"}, {"3", "3", "300"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Two instances of row_number columns in the same RCL, reorder columns */
+		rs = s.executeQuery("select row_number() over (), b, row_number() over (), a from t1 where b < 300 ");
+		expectedRows = new String[][]{{"1", "100", "1", "10"}, {"2", "200", "2", "20"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Pushing predicates (... where r ... ) too far cause this join to fail */
+		rs = s.executeQuery("select row_number() over(),x from t2,t3 where x=y");
+		expectedRows = new String[][]{{"1", "4"}, {"2", "5"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Ordering */
+		rs = s.executeQuery("select row_number() over () as r, t1.* from t1 order by b desc");
+		expectedRows = new String[][]{{"1", "50", "500"}, {"2", "40", "400"}, {"3", "30", "300"}, {"4", "20", "200"}, {"5", "10", "100"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Ordering on a column dropped in projection */
+		rs = s.executeQuery("select row_number() over () as r, t1.a from t1 order by b desc");
+		expectedRows = new String[][]{{"1", "50"}, {"2", "40"}, {"3", "30"}, {"4", "20"}, {"5", "10"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Only expressions in RCL */
+		rs = s.executeQuery("select row_number() over (), row_number() over (), 2*t1.a from t1");
+		expectedRows = new String[][]{{"1", "1", "20"}, {"2", "2","40"}, {"3", "3","60"}, {"4", "4", "80"}, {"5", "5", "100"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		/*
+		 * Subquerys 
+		 */ 
+			
+		/* This query returned no rows at one time */
+		rs = s.executeQuery("select * from (select row_number() over () as r,x from t2,t3 where x=y) s(r,x) where r < 3");
+		expectedRows = new String[][]{{"1", "4"}, {"2", "5"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
 		rs = s.executeQuery("select * from (select row_number() over () as r, t1.* from t1) as tr where r < 3");
-		expectedRows = new String [] [] {{"1","1","1"},{"2","2","2"}};
-		JDBC.assertFullResultSet(rs,expectedRows);        
-
-		// Pushing predicates (... where r ... ) too far cause this join to fail 
-		rs = s.executeQuery("select row_number(),x from t2,t3 where x=y");
-		expectedRows = new String [] [] {{"1","4"},{"2","5"}};
-		JDBC.assertFullResultSet(rs,expectedRows);
+		expectedRows = new String[][]{{"1", "10", "100"}, {"2", "20", "200"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
 
+		rs = s.executeQuery("select * from (select row_number() over () as r, t1.* from t1) as tr where r > 3");
+		expectedRows = new String[][]{{"4", "40", "400"}, {"5", "50", "500"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Two instances of row_number columns */
+		rs = s.executeQuery("select row_number() over(), tr.* from (select row_number() over () as r, t1.* from t1) as tr where r > 2 and r < 5");
+		expectedRows = new String[][]{{"1", "3", "30", "300"}, {"2", "4", "40", "400"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Two instances of row_number columns, with projection */
+		rs = s.executeQuery("select row_number() over(), tr.b from (select row_number() over () as r, t1.* from t1) as tr where r > 2 and r < 5");
+		expectedRows = new String[][]{{"1", "300"}, {"2", "400"}};
+		JDBC.assertFullResultSet(rs, expectedRows);		
+
+		/* Column ordering */
+		rs = s.executeQuery("select * from (select t1.b, row_number() over () as r from t1) as tr where r > 3");
+		expectedRows = new String[][]{{"400", "4"}, {"500", "5"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		/* Column ordering with projection*/
+		rs = s.executeQuery("select b from (select t1.b, row_number() over () as r from t1) as tr where r > 3");
+		expectedRows = new String[][]{{"400"}, {"500"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		/*
+		 * Aggregates over window functions once failed
+		 */
+		rs = s.executeQuery("select count(*) from (select row_number() over() from t1) x");
+		expectedRows = new String[][]{{"5"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select count(*) from (select row_number() over () as r from t1) as t(r) where r <=3");
+		expectedRows = new String[][]{{"3"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		/*
+		 * Some other joins with window functions.
+		 * Run off a smaller table t4 to reduce expected row count.
+		 */
+		rs = s.executeQuery("select row_number() over () from t1 union all select row_number() over () from t1");
+		expectedRows = new String[][]{{"1"},{"2"},{"3"},{"4"},{"5"},{"1"},{"2"},{"3"},{"4"},{"5"}};
+		JDBC.assertFullResultSet(rs, expectedRows);	
+		
+		rs = s.executeQuery("select 2 * r from (select row_number() over () from t1) x(r)");
+		expectedRows = new String[][]{{"2"},{"4"},{"6"},{"8"},{"10"},};
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		rs = s.executeQuery("select c3, c1, c2 from " + 
+							"(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), " +
+							"(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4)");
+		expectedRows = new String[][]{{"100", "10", "100"},
+										{"200", "10", "100"},																				
+										{"100", "20", "200"},
+										{"200", "20", "200"}};										
+		JDBC.assertFullResultSet(rs, expectedRows);
+					
+		rs = s.executeQuery("select c3, c1, c2 from " + 
+							"(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), " +
+							"(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4), " +
+							"t4");
+		expectedRows = new String[][]{{"100", "10", "100"},
+										{"100", "10", "100"},																				
+										{"200", "10", "100"},
+										{"200", "10", "100"},
+										{"100", "20", "200"},
+										{"100", "20", "200"},
+										{"200", "20", "200"},										
+										{"200", "20", "200"}};										
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select c3, c1, c2 from "+
+							"(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), "+
+							"(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4), "+
+							"t4 "+
+							"where x1.r1 = 2 * x2.r2");
+		expectedRows = new String[][]{{"100", "20", "200"}, {"100", "20", "200"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+
+		rs = s.executeQuery("select c3, c1, c2 from "+
+							"(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), "+
+							"(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4), "+
+							"t4 "+
+							"where x1.r1 = 2 * x2.r2");
+		expectedRows = new String[][]{{"100", "20", "200"}, {"100", "20", "200"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+				
+		/* Two problematic joins reported during development */
+		rs = s.executeQuery("select c3, c1, c2 from "+
+							"(select a, b, row_number() over() as r from t4) x1 (c1, c2, r1), "+
+							"(select row_number() over() as r, b, a from t4) x2 (r2, c3, c4), "+
+							"t4 "+
+							"where x2.c4 = t4.a");
+		expectedRows = new String[][]{{"100", "10", "100"}, 
+										{"100", "20", "200"},
+										{"200", "10", "100"},
+										{"200", "20", "200"}};			
+		JDBC.assertFullResultSet(rs, expectedRows);
+		
+		rs = s.executeQuery("select c3, c1, c2 from "+
+							"(select a, b, row_number() over() as r from t1) x1 (c1, c2, r1), "+
+							"(select row_number() over() as r, b, a from t1) x2 (r2, c3, c4), "+
+							"t1 "+
+							"where x1.r1 = 2 * x2.r2 and x2.c4 = t1.a");
+		expectedRows = new String[][]{{"100", "20", "200"}, {"200", "40", "400"}};
+		JDBC.assertFullResultSet(rs, expectedRows);
+			
 		/*
 		 * Negative testing of Statements
 		 */
 
+		// Missing required OVER () 
+		assertStatementError("42X01", s, "select row_number() as r, * from t1 where t1.a > 2");
+
 		// Illegal where clause, r not a named column of t1.        
-		assertStatementError("42X04",s,"select row_number() as r, a from t1 where r < 3");
+		assertStatementError("42X04", s, "select row_number() over () as r, a from t1 where r < 3");
 
 		// Illegal use of asterix with another column identifier.        
-		assertStatementError("42X01",s,"select row_number() as r, * from t1 where t1.a > 2");
+		assertStatementError("42X01", s, "select row_number() over () as r, * from t1 where t1.a > 2");
 
 		/*
 		 * Clean up the tables used.
 		 */
-		s.executeUpdate("drop table t1");        
+		s.executeUpdate("drop table t1");
 		s.executeUpdate("drop table t2");
 		s.executeUpdate("drop table t3");
 

Modified: db/derby/code/trunk/tools/jar/DBMSnodes.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/tools/jar/DBMSnodes.properties?rev=632494&r1=632493&r2=632494&view=diff
==============================================================================
--- db/derby/code/trunk/tools/jar/DBMSnodes.properties (original)
+++ db/derby/code/trunk/tools/jar/DBMSnodes.properties Fri Feb 29 15:24:54 2008
@@ -140,6 +140,8 @@
 derby.module.cloudscapenodes.gq=org.apache.derby.impl.sql.compile.SetRoleNode
 derby.module.cloudscapenodes.gr=org.apache.derby.impl.sql.compile.GrantRoleNode
 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
 
 # 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