db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r829155 [1/2] - 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...
Date Fri, 23 Oct 2009 18:05:18 GMT
Author: dag
Date: Fri Oct 23 18:05:03 2009
New Revision: 829155

URL: http://svn.apache.org/viewvc?rev=829155&view=rev
Log:
DERBY-3634 Cannot use row_number() in ORDER BY clause

Patch derby-3634-newimpl-4. This reimplements the ROW_NUMBER
functionality after it was moved in svn 820483 and 820494. The patch
makes ROW_NUMBER usable in all places it should be usable according to
the standard (modulo the current Derby SQL subset).  So, DERBY-3634 is
also fixed with this patch. The ordering problem seen in DERBY-4069 is
also fixed. More test cases have been to OLAPTest for ROW_NUMBER
usage.  The patch also adds some amount of generalizations to pave way
for other window functionality, but this needs more work.


Added:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateWindowFunctionNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceWindowFuncCallsWithCRVisitor.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberFunctionNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowDefinitionNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowList.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/compile/WindowReferenceNode.java   (with props)
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowResultSetNode.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/AggregateNode.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/DeleteNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.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/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/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/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/SQLStateConstants.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=829155&r1=829154&r2=829155&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 Oct 23 18:05:03 2009
@@ -236,8 +236,15 @@
     static final int CREATE_SEQUENCE_NODE = 224;
     static final int DROP_SEQUENCE_NODE = 225;
 
+	// Windowing
+	static final int AGGREGATE_WINDOW_FUNCTION_NODE = 226;
+	static final int ROW_NUMBER_FUNCTION_NODE = 227;
+	static final int WINDOW_DEFINITION_NODE = 228;
+	static final int WINDOW_REFERENCE_NODE = 229;
+	static final int WINDOW_RESULTSET_NODE = 230;
+
     // Final value in set, keep up to date!
-    static final int FINAL_VALUE = DROP_SEQUENCE_NODE;
+    static final int FINAL_VALUE = WINDOW_RESULTSET_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=829155&r1=829154&r2=829155&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 Oct 23 18:05:03 2009
@@ -1103,6 +1103,41 @@
 								double optimizerEstimatedCost)
 			throws StandardException;
 
+
+
+	/**
+	   A OLAP window on top of a regular result set. It is used to realize
+	   window functions.
+	   <p>
+	   @param activation   Activation
+	   @param source       The result set input to this result set.
+	   @param rowAllocator A reference to a method in the activation
+			               that generates rows of the right size and
+						   shape for the source.
+	   @param resultSetNumber The resultSetNumber for the ResultSet
+	   @param erdNumber    Int for ResultDescription
+	                       (so it can be turned back into an object)
+	   @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
+	   @throws StandardException
+	 */
+	public NoPutResultSet getWindowResultSet(
+								Activation activation,
+								NoPutResultSet source,
+								GeneratedMethod rowAllocator,
+								int resultSetNumber,
+								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/AggregateNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java?rev=829155&r1=829154&r2=829155&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java Fri Oct 23 18:05:03 2009
@@ -296,6 +296,10 @@
 						aggregateName);
 			}
 
+			// Also forbid any window function inside an aggregate unless in
+			// subquery, cf. SQL 2003, section 10.9, SR 7 a).
+			SelectNode.checkNoWindowFunctions(operand, aggregateName);
+
 			/*
 			** Check the type of the operand.  Make sure that the user
 			** defined aggregate can handle the operand datatype.

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateWindowFunctionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateWindowFunctionNode.java?rev=829155&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateWindowFunctionNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateWindowFunctionNode.java Fri Oct 23 18:05:03 2009
@@ -0,0 +1,93 @@
+/*
+   Derby - Class org.apache.derby.impl.sql.compile.AggregateWindowFunctionNode
+
+   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 org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.reference.SQLState;
+
+import java.sql.Types;
+import java.util.Vector;
+
+/**
+ * Represents aggregate function calls on a window
+ */
+public final class AggregateWindowFunctionNode extends WindowFunctionNode
+{
+
+    private AggregateNode aggregateFunction;
+
+    /**
+     * Initializer. QueryTreeNode override.
+     *
+     * @param arg1 The window definition or reference
+     * @param arg2 aggregate function node
+     *
+     * @exception StandardException
+     */
+    public void init(Object arg1, Object arg2)
+        throws StandardException
+    {
+        super.init(null, "?", arg1);
+        aggregateFunction = (AggregateNode)arg2;
+
+        throw StandardException.newException(
+            SQLState.NOT_IMPLEMENTED,
+            "WINDOW/" + aggregateFunction.getAggregateName());
+    }
+
+
+    /**
+     * ValueNode override.
+     * @see ValueNode#bindExpression
+     */
+    public ValueNode bindExpression(
+                    FromList            fromList,
+                    SubqueryList        subqueryList,
+                    Vector              aggregateVector)
+            throws StandardException
+    {
+        aggregateFunction.bindExpression(
+            fromList, subqueryList, aggregateVector);
+        return this;
+    }
+
+
+
+    /**
+     * QueryTreeNode override. Prints the sub-nodes of this object.
+     * @see QueryTreeNode#printSubNodes
+     *
+     * @param depth     The depth of this node in the tree
+     */
+
+    public void printSubNodes(int depth)
+    {
+        if (SanityManager.DEBUG)
+        {
+            super.printSubNodes(depth);
+
+            printLabel(depth, "aggregate: ");
+            aggregateFunction.treePrint(depth + 1);
+        }
+    }
+}

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

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=829155&r1=829154&r2=829155&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 Oct 23 18:05:03 2009
@@ -43,7 +43,6 @@
     // THEM TO tools/jar/DBMSnodes.properties
 
 	static final String AGGREGATE_NODE_NAME = "org.apache.derby.impl.sql.compile.AggregateNode";
-
 	static final String ALL_RESULT_COLUMN_NAME = "org.apache.derby.impl.sql.compile.AllResultColumn";
 
 	static final String ALTER_TABLE_NODE_NAME = "org.apache.derby.impl.sql.compile.AlterTableNode";
@@ -301,6 +300,11 @@
 	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";
+	static final String AGGREGATE_WINDOW_FUNCTION_NAME = "org.apache.derby.impl.sql.compile.AggregateWindowFunctionNode";
+	static final String ROW_NUMBER_FUNCTION_NAME = "org.apache.derby.impl.sql.compile.RowNumberFunctionNode";
+	static final String WINDOW_DEFINITION_NAME = "org.apache.derby.impl.sql.compile.WindowDefinitionNode";
+	static final String WINDOW_REFERENCE_NAME = "org.apache.derby.impl.sql.compile.WindowReferenceNode";
+	static final String WINDOW_RESULTSET_NODE_NAME = "org.apache.derby.impl.sql.compile.WindowResultSetNode";
 
 	static final String ROW_COUNT_NODE_NAME = "org.apache.derby.impl.sql.compile.RowCountNode";
 

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=829155&r1=829154&r2=829155&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 Oct 23 18:05:03 2009
@@ -75,6 +75,7 @@
 	//Expression genResult;
 
 	private boolean		replacesAggregate;
+	private boolean		replacesWindowFunctionCall;
 
 	private int			nestingLevel = -1;
 	private int			sourceLevel = -1;
@@ -146,7 +147,11 @@
 				"tableNumber: " + tableNumber + "\n" +
 				"columnNumber: " + columnNumber + "\n" +
 				"replacesAggregate: " + replacesAggregate + "\n" +
-				"tableName: " + ( ( tableName != null) ? tableName.toString() : "null") + "\n" +
+				"replacesWindowFunctionCall: " +
+				    replacesWindowFunctionCall + "\n" +
+				"tableName: " + ( ( tableName != null) ?
+								  tableName.toString() :
+								  "null") + "\n" +
 				"nestingLevel: " + nestingLevel + "\n" +
 				"sourceLevel: " + sourceLevel + "\n" +
 				super.toString();
@@ -258,6 +263,15 @@
 		replacesAggregate = true;
 	}
 
+
+	/**
+	 * Mark this node as being generated to replace a window function call.
+	 */
+	public void markGeneratedToReplaceWindowFunctionCall()
+	{
+		replacesWindowFunctionCall = true;
+	}
+
 	/**
 	 * Determine whether or not this node was generated to
 	 * replace an aggregate in the user's SELECT.
@@ -270,6 +284,19 @@
 		return replacesAggregate;
 	}
 
+
+	/**
+	 * Determine whether or not this node was generated to
+	 * replace a window function call in the user's SELECT.
+	 *
+	 * @return boolean	Whether or not this node was generated to replace
+	 *					a window function call in the user's SELECT.
+	 */
+	public boolean getGeneratedToReplaceWindowFunctionCall()
+	{
+		return replacesWindowFunctionCall;
+	}
+
 	/**
 	 * Return a clone of this node.
 	 *
@@ -309,6 +336,8 @@
 		nestingLevel = oldCR.getNestingLevel();
 		sourceLevel = oldCR.getSourceLevel();
 		replacesAggregate = oldCR.getGeneratedToReplaceAggregate();
+		replacesWindowFunctionCall =
+			oldCR.getGeneratedToReplaceWindowFunctionCall();
 		scoped = oldCR.isScoped();
 	}
 
@@ -592,6 +621,8 @@
 	 * pushing the predicate into the SelectNode that evaluates the aggregate,
 	 * which doesn't make sense, since the having clause is supposed to be
 	 * applied to the result of the SelectNode.
+	 * This also goes for column references that replaces a window function.
+	 *
 	 *
 	 * RESOLVE - revisit this issue once we have views.
 	 *
@@ -611,6 +642,7 @@
 		referencedTabs.set(tableNumber);
 
 		return ( ! replacesAggregate ) &&
+			   ( ! replacesWindowFunctionCall ) &&
 			   ( (source.getExpression() instanceof ColumnReference) ||
 			     (source.getExpression() instanceof VirtualColumnNode) ||
 				 (source.getExpression() instanceof ConstantNode));

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java?rev=829155&r1=829154&r2=829155&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java Fri Oct 23 18:05:03 2009
@@ -787,7 +787,8 @@
                                                      whereClause, /* WHERE clause */
                                                      null, /* GROUP BY list */
                                                      null, /* having clause */
-                                                     getContextManager());
+													 null, /* windows */
+													 getContextManager());
 
         return (StatementNode) nodeFactory.getNode(
                                                     C_NodeTypes.DELETE_NODE,
@@ -836,7 +837,8 @@
                                                      fromList, /* FROM list */
                                                      whereClause, /* WHERE clause */
                                                      null, /* GROUP BY list */
-						     null, /* having clause */
+													 null, /* having clause */
+													 null, /* windows */
                                                      getContextManager());
 
         return (StatementNode) nodeFactory.getNode(

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java?rev=829155&r1=829154&r2=829155&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java Fri Oct 23 18:05:03 2009
@@ -73,6 +73,13 @@
 	 */
 	private boolean isTransparent;
 
+	/**
+	 * Window definitions used for resolving window functions not containing
+	 * in-line window specifications, but referring window definitions
+	 */
+	private WindowList windows;
+
+
 	/** Initializer for a FromList */
 
 	public void init(Object optimizeJoinOrder)
@@ -1615,4 +1622,21 @@
 
 		return this;
 	}
+
+
+	/**
+	 * Set windows field to the supplied value.
+	 * @param windows list of window definitions associated with a SELECT.
+	 */
+	public void setWindows(WindowList windows) {
+		this.windows = windows;
+	}
+
+
+	/**
+	 * @return list of window definitions associated with a SELECT.
+	 */
+	public WindowList getWindows() {
+		return windows;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java?rev=829155&r1=829154&r2=829155&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java Fri Oct 23 18:05:03 2009
@@ -24,6 +24,8 @@
 import org.apache.derby.iapi.error.StandardException;
 
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.sql.compile.Visitor;
+import org.apache.derby.iapi.sql.compile.Visitable;
 
 import org.apache.derby.iapi.types.TypeId;
 
@@ -136,4 +138,28 @@
 		this.columnExpression = cexpr;
 		
 	}
+
+	/**
+	 * Accept a visitor, and call v.visit()
+	 * on child nodes as necessary.
+	 *
+	 * @param v the visitor
+	 *
+	 * @exception StandardException on error
+	 */
+	public Visitable accept(Visitor v)
+		throws StandardException {
+
+		Visitable returnNode = v.visit(this);
+
+		if (v.skipChildren(this)) {
+			return returnNode;
+		}
+
+		if (columnExpression != null) {
+			columnExpression = (ValueNode)columnExpression.accept(v);
+		}
+
+		return returnNode;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java?rev=829155&r1=829154&r2=829155&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java Fri Oct 23 18:05:03 2009
@@ -580,13 +580,15 @@
 			CollectNodesVisitor collectNodesVisitor = 
 				new CollectNodesVisitor(ColumnReference.class, AggregateNode.class);
 			havingClause.accept(collectNodesVisitor);
+
 			for (Iterator it = collectNodesVisitor.getList().iterator();
 			     it.hasNext(); ) 
 			{
 				ColumnReference cr = (ColumnReference)it.next();
-				
-				if (!cr.getGeneratedToReplaceAggregate() && 
-						cr.getSourceLevel() == level) {
+
+				if ( ! (cr.getGeneratedToReplaceAggregate() ||
+						cr.getGeneratedToReplaceWindowFunctionCall()) &&
+					 cr.getSourceLevel() == level) {
 					throw StandardException.newException(
 							SQLState.LANG_INVALID_COL_HAVING_CLAUSE, 
 							cr.getSQLColumnName());						

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=829155&r1=829154&r2=829155&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java Fri Oct 23 18:05:03 2009
@@ -826,6 +826,10 @@
 				throw se;
 			}
 
+			// SQL 2003, section 7.7 SR 5
+			SelectNode.checkNoWindowFunctions(joinClause, "ON");
+
+
 			/* DB2 doesn't allow subquerries in the ON clause */
 			if (subqueryList.size() > 0)
 				throw StandardException.newException(SQLState.LANG_DB2_ON_CLAUSE_INVALID); 

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=829155&r1=829154&r2=829155&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 Oct 23 18:05:03 2009
@@ -614,6 +614,21 @@
           case C_NodeTypes.TABLE_PRIVILEGES_NODE:
             return C_NodeNames.TABLE_PRIVILEGES_NAME;
 
+		  case C_NodeTypes.AGGREGATE_WINDOW_FUNCTION_NODE:
+			return C_NodeNames.AGGREGATE_WINDOW_FUNCTION_NAME;
+
+		  case C_NodeTypes.ROW_NUMBER_FUNCTION_NODE:
+			return C_NodeNames.ROW_NUMBER_FUNCTION_NAME;
+
+		  case C_NodeTypes.WINDOW_DEFINITION_NODE:
+			return C_NodeNames.WINDOW_DEFINITION_NAME;
+
+		  case C_NodeTypes.WINDOW_REFERENCE_NODE:
+			return C_NodeNames.WINDOW_REFERENCE_NAME;
+
+		  case C_NodeTypes.WINDOW_RESULTSET_NODE:
+			return C_NodeNames.WINDOW_RESULTSET_NODE_NAME;
+
           case C_NodeTypes.GENERATION_CLAUSE_NODE:
             return C_NodeNames.GENERATION_CLAUSE_NODE_NAME;
 		  	

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java?rev=829155&r1=829154&r2=829155&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java Fri Oct 23 18:05:03 2009
@@ -32,6 +32,8 @@
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
 
 import org.apache.derby.iapi.util.ReuseFactory;
+import org.apache.derby.iapi.sql.compile.Visitable;
+import org.apache.derby.iapi.sql.compile.Visitor;
 
 /**
  * An OrderByColumn is a column in the ORDER BY clause.  An OrderByColumn
@@ -84,7 +86,6 @@
 		}
 	}
 
-
 	/**
 	 * Prints the sub-nodes of this object.  See QueryTreeNode.java for
 	 * how tree printing is supposed to work.
@@ -497,4 +498,31 @@
 		if (addedColumnOffset > gap)
 			addedColumnOffset--;
 	}
+
+
+	/**
+	 * Accept a visitor, and call v.visit()
+	 * on child nodes as necessary.
+	 *
+	 * @param v the visitor
+	 *
+	 * @exception StandardException on error
+	 */
+	public Visitable accept(Visitor v)
+		throws StandardException
+	{
+		Visitable returnNode = v.visit(this);
+
+		if (v.skipChildren(this))
+		{
+			return returnNode;
+		}
+
+		if (expression != null && !v.stopTraversal())
+		{
+			expression = (ValueNode)expression.accept(v);
+		}
+		return returnNode;
+	}
+
 }

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=829155&r1=829154&r2=829155&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 Oct 23 18:05:03 2009
@@ -1099,11 +1099,27 @@
 		 * 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))
 		{
-			pushPList.pushExpressionsIntoSelect((SelectNode) childResult, false);
+			SelectNode childSelect = (SelectNode)childResult;
+
+			if ( (childSelect.hasWindows()  &&
+				  childSelect.orderByList != null) ) {
+				// We can't push down if there is an ORDER BY and a window
+				// function because that would make ROW_NUMBER give wrong
+				// result:
+				// E.g.
+				//     SELECT * from (SELECT ROW_NUMBER() OVER (), j FROM T
+				//                    ORDER BY j) WHERE j=5
+				//
+			} else {
+				pushPList.pushExpressionsIntoSelect((SelectNode) childResult,
+													false);
+			}
 		}
 
+
 		/* DERBY-649: Push simple predicates into Unions. It would be up to UnionNode
 		 * to decide if these predicates can be pushed further into underlying SelectNodes
 		 * or UnionNodes.  Note, we also keep the predicateList at this

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceWindowFuncCallsWithCRVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceWindowFuncCallsWithCRVisitor.java?rev=829155&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceWindowFuncCallsWithCRVisitor.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceWindowFuncCallsWithCRVisitor.java Fri Oct 23 18:05:03 2009
@@ -0,0 +1,106 @@
+/*
+   Derby - Class org.apache.derby.impl.sql.compile.ReplaceWindowFuncCallsWithCRVisitor
+
+   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.sql.compile.Visitable;
+import org.apache.derby.iapi.sql.compile.Visitor;
+
+import org.apache.derby.iapi.error.StandardException;
+
+/**
+ * Replace all window function calls with result columns.
+ *
+ */
+public class ReplaceWindowFuncCallsWithCRVisitor implements Visitor
+{
+	private ResultColumnList rcl;
+	private Class skipOverClass;
+	private int tableNumber;
+
+	/**
+	 * Replace all window function calls with column references.  Add
+	 * the reference to the RCL.  Delegates most work to
+	 * WindowFunctionNode.replaceCallsWithColumnReferences(rcl, tableNumber).
+	 *
+	 * @param rcl the result column list
+	 * @param tableNumber	The tableNumber for the new CRs
+	 * @param skipOverClass Don't go past this
+	 */
+	public ReplaceWindowFuncCallsWithCRVisitor(ResultColumnList rcl,
+											int tableNumber,
+											Class skipOverClass)
+	{
+		this.rcl = rcl;
+		this.tableNumber = tableNumber;
+		this.skipOverClass = skipOverClass;
+	}
+
+	////////////////////////////////////////////////
+	//
+	// VISITOR INTERFACE
+	//
+	////////////////////////////////////////////////
+
+	/**
+	 * Don't do anything unless we have a window function node
+	 * node. Vistor override.
+	 * @see Visitor#visit
+	 *
+	 */
+	public Visitable visit(Visitable node)
+		throws StandardException
+	{
+		if (node instanceof WindowFunctionNode)
+		{
+			/*
+			** Let windowFunctionNode replace itself.
+			*/
+			node = ((WindowFunctionNode)node).
+				replaceCallsWithColumnReferences(rcl, tableNumber);
+		}
+
+		return node;
+	}
+
+	/**
+	 * Don't visit childen under the skipOverClass
+	 * node, if it isn't null. Vistor override.
+	 * @see Visitor#skipChildren
+	 */
+	public boolean skipChildren(Visitable node)
+	{
+		return (skipOverClass == null) ?
+				false:
+				skipOverClass.isInstance(node);
+	}
+
+
+	/**
+	 * Vistor override.
+	 * @return false
+	 * @see Visitor#skipChildren
+	 */
+	public boolean stopTraversal()
+	{
+		return false;
+	}
+}

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

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberFunctionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberFunctionNode.java?rev=829155&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberFunctionNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowNumberFunctionNode.java Fri Oct 23 18:05:03 2009
@@ -0,0 +1,68 @@
+/*
+   Derby - Class org.apache.derby.impl.sql.compile.RowNumberFunctionNode
+
+   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 org.apache.derby.iapi.services.sanity.SanityManager;
+
+import java.sql.Types;
+import java.util.Vector;
+
+/**
+ * Class that represents a call to the ROW_NUMBER() window function.
+ */
+public final class RowNumberFunctionNode extends WindowFunctionNode
+{
+
+    /**
+     * Initializer. QueryTreeNode override.
+     *
+     * @param arg1 null (Operand)
+     * @param arg2 The window definition or reference
+     *
+     * @exception StandardException
+     */
+    public void init(Object arg1, Object arg2)
+        throws StandardException
+    {
+        super.init(arg1, "ROW_NUMBER", arg2);
+        setType( TypeId.getBuiltInTypeId( Types.BIGINT ),
+                 TypeId.LONGINT_PRECISION,
+                 TypeId.LONGINT_SCALE,
+                 false,
+                 TypeId.LONGINT_MAXWIDTH);
+    }
+
+    /**
+     * ValueNode override.
+     * @see ValueNode#bindExpression
+     */
+    public ValueNode bindExpression(
+                    FromList            fromList,
+                    SubqueryList        subqueryList,
+                    Vector              aggregateVector)
+            throws StandardException
+    {
+        super.bindExpression(fromList, subqueryList, aggregateVector);
+        return this;
+    }
+}

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

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=829155&r1=829154&r2=829155&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 Fri Oct 23 18:05:03 2009
@@ -244,6 +244,8 @@
 		{
 			throw StandardException.newException(SQLState.LANG_NO_AGGREGATES_IN_WHERE_CLAUSE);
 		}
+
+		SelectNode.checkNoWindowFunctions(resultColumns, "VALUES");
 	}
 
 	/**

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=829155&r1=829154&r2=829155&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 Oct 23 18:05:03 2009
@@ -88,6 +88,16 @@
 	 */
 	GroupByList	groupByList;
 
+	/**
+	 * List of windows.
+	 */
+	WindowList windows;
+
+	/**
+	 * List of window function calls (e.g. ROW_NUMBER, AVG(i), DENSE_RANK).
+	 */
+	Vector windowFuncCalls;
+
 	/** User specified a group by without aggregates and we turned 
 	 * it into a select distinct 
 	 */
@@ -125,7 +135,8 @@
 			  Object fromList,
 			  Object whereClause,
 			  Object groupByList,
-			  Object havingClause)
+			  Object havingClause,
+			  Object windowDefinitionList)
 			throws StandardException
 	{
 		/* RESOLVE - remove aggregateList from constructor.
@@ -139,6 +150,15 @@
 		this.originalWhereClause = (ValueNode) whereClause;
 		this.groupByList = (GroupByList) groupByList;
 		this.havingClause = (ValueNode)havingClause;
+
+		// This initially represents an explicit <window definition list>, as
+		// opposed to <in-line window specifications>, see 2003, 6.10 and 6.11.
+		// <in-line window specifications> are added later, see right below for
+		// in-line window specifications used in window functions in the SELECT
+		// column list and in genProjectRestrict for such window specifications
+		// used in window functions in ORDER BY.
+		this.windows = (WindowList)windowDefinitionList;
+
 		bindTargetListOnly = false;
 		
 		this.originalWhereClauseHadSubqueries = false;
@@ -150,6 +170,65 @@
 				this.originalWhereClauseHadSubqueries = true;
 			}
 		}
+
+		if (resultColumns != null) {
+
+			// Collect window functions used in result columns, and check them
+			// for any <in-line window specification>s.
+
+			CollectNodesVisitor cnvw =
+				new CollectNodesVisitor(WindowFunctionNode.class);
+			resultColumns.accept(cnvw);
+			windowFuncCalls = cnvw.getList();
+
+			for (int i=0; i < windowFuncCalls.size(); i++) {
+				WindowFunctionNode wfn =
+					(WindowFunctionNode)windowFuncCalls.elementAt(i);
+
+				// Some window function, e.g. ROW_NUMBER() contains an inline
+				// window specification, so we add it to our list of window
+				// definitions.
+
+				if (wfn.getWindow() instanceof WindowDefinitionNode) {
+					// Window function call contains an inline definition, add
+					// it to our list of windows.
+					windows = addInlinedWindowDefinition(windows, wfn);
+				} else {
+					// a window reference, bind it later.
+
+					if (SanityManager.DEBUG) {
+						SanityManager.ASSERT(
+							wfn.getWindow() instanceof WindowReferenceNode);
+					}
+				}
+			}
+		}
+	}
+
+	private WindowList addInlinedWindowDefinition (WindowList wl,
+												   WindowFunctionNode wfn) {
+		WindowDefinitionNode wdn = (WindowDefinitionNode)wfn.getWindow();
+
+		if (wl == null) {
+			// This is the first window we see, so initialize list.
+			wl = new WindowList();
+			wl.setContextManager(getContextManager());
+		}
+
+		WindowDefinitionNode equiv = wdn.findEquivalentWindow(wl);
+
+		if (equiv != null) {
+			// If the window is equivalent an existing one, optimize
+			// it away.
+
+			wfn.setWindow(equiv);
+		} else {
+			// remember this window for posterity
+
+			wl.addWindow((WindowDefinitionNode)wfn.getWindow());
+		}
+
+		return wl;
 	}
 
 	/**
@@ -241,6 +320,11 @@
 				groupByList.treePrint(depth + 1);
 			}
 
+			if (havingClause != null) {
+				printLabel(depth, "havingClause:");
+				havingClause.treePrint(depth + 1);
+			}
+
 			if (orderByList != null) {
 				printLabel(depth, "orderByList:");
 				orderByList.treePrint(depth + 1);
@@ -252,6 +336,11 @@
 				preJoinFL.treePrint(depth + 1);
 			}
 
+			if (windows != null)
+			{
+				printLabel(depth, "windows: ");
+				windows.treePrint(depth + 1);
+			}
 		}
 	}
 
@@ -448,6 +537,18 @@
 			fromListParam.insertElementAt(fromList.elementAt(index), index);
 		}
 
+		// In preparation for resolving window references in expressions, we
+		// make the FromList carry the set of explicit window definitions.
+		//
+		// E.g. "select row_number () from r, .. from t window r as ()"
+		//
+		// Here the expression "row_number () from r" needs to be bound to r's
+		// definition. Window functions can also in-line window specifications,
+		// no resolution is necessary. See also
+		// WindowFunctionNode.bindExpression.
+
+		fromListParam.setWindows(windows);
+
 		resultColumns.bindExpressions(fromListParam, 
 									  selectSubquerys,
 									  selectAggregates);
@@ -496,6 +597,8 @@
 			
 			whereClause = whereClause.checkIsBoolean();
 			getCompilerContext().popCurrentPrivType();
+
+			checkNoWindowFunctions(whereClause, "WHERE");
 		}
 
 		if (havingClause != null) {
@@ -506,6 +609,7 @@
 			havingClause.bindExpression(
 					fromListParam, havingSubquerys, havingAggregates);
 			havingClause = havingClause.checkIsBoolean();
+			checkNoWindowFunctions(havingClause, "HAVING");
 		}
 		
 		/* Restore fromList */
@@ -541,6 +645,8 @@
 				SanityManager.ASSERT(gbAggregateVector.size() == 0,
 						"Unexpected Aggregate vector generated by Group By clause");
 			}
+
+			checkNoWindowFunctions(groupByList, "GROUP BY");
 		}
 		/* If ungrouped query with aggregates in SELECT list, verify
 		 * that all result columns are valid aggregate expressions -
@@ -637,7 +743,6 @@
 		 */
 		fromList.bindResultColumns(fromListParam);
 		super.bindResultColumns(fromListParam);
-
 		/* Only 1012 elements allowed in select list */
 		if (resultColumns.size() > Limits.DB2_MAX_ELEMENTS_IN_SELECT_LIST)
 		{
@@ -969,7 +1074,6 @@
 				// columns are ones that have equality comparisons with
 				// constant expressions (e.g. x = 3)
 				orderByList.removeConstantColumns(wherePredicates);
-
 				/*
 				** It's possible for the order by list to shrink to nothing
 				** as a result of removing constant columns.  If this happens,
@@ -1099,6 +1203,42 @@
 		{
 			newTop.setReferencedTableMap((JBitSet) referencedTableMap.clone());
 		}
+
+
+		if (orderByList != null) {
+
+			// Collect window function calls and in-lined window definitions
+			// contained in them from the orderByList.
+
+			CollectNodesVisitor cnvw =
+				new CollectNodesVisitor(WindowFunctionNode.class);
+			orderByList.accept(cnvw);
+			Vector wfcInOrderBy = cnvw.getList();
+
+			for (int i=0; i < wfcInOrderBy.size(); i++) {
+				WindowFunctionNode wfn =
+					(WindowFunctionNode)wfcInOrderBy.elementAt(i);
+				windowFuncCalls.add(wfn);
+
+
+				if (wfn.getWindow() instanceof WindowDefinitionNode) {
+					// Window function call contains an inline definition, add
+					// it to our list of windows.
+					windows = addInlinedWindowDefinition(windows, wfn);
+
+				} else {
+					// a window reference, should be bound already.
+
+					if (SanityManager.DEBUG) {
+						SanityManager.ASSERT(
+							false,
+							"a window reference, should be bound already");
+					}
+				}
+			}
+		}
+
+
 		return newTop;
 	}
 
@@ -1267,7 +1407,6 @@
 		ResultColumnList	prRCList;
 		ResultSetNode		prnRSN;
 
-
 		prnRSN = (ResultSetNode) getNodeFactory().getNode(
 								C_NodeTypes.PROJECT_RESTRICT_NODE,
 								fromList.elementAt(0),	/* Child ResultSet */
@@ -1315,6 +1454,33 @@
 			eliminateSort = eliminateSort || gbn.getIsInSortedOrder();
 		}
 
+
+		if (windows != null) {
+
+			// Now we add a window result set wrapped in a PRN on top of what
+			// we currently have.
+
+			if (windows.size() > 1) {
+				throw StandardException.newException(
+					SQLState.LANG_WINDOW_LIMIT_EXCEEDED);
+			}
+
+			WindowNode wn = (WindowNode)windows.elementAt(0);
+
+			WindowResultSetNode wrsn =
+				(WindowResultSetNode)getNodeFactory().getNode(
+					C_NodeTypes.WINDOW_RESULTSET_NODE,
+					prnRSN,
+					wn,
+					windowFuncCalls,
+					new Integer(nestingLevel),
+					getContextManager());
+
+			prnRSN = wrsn.getParent();
+			wrsn.assignCostEstimate(optimizer.getOptimizedCost());
+		}
+
+
 		// if it is distinct, that must also be taken care of.
 		if (isDistinct)
 		{
@@ -1386,6 +1552,7 @@
 		/* Generate the OrderByNode if a sort is still required for
 		 * the order by.
 		 */
+
 		if (orderByList != null)
 		{
 			if (orderByList.getSortNeeded())
@@ -1423,8 +1590,12 @@
 			}
 		}
 
-		
-		if (wasGroupBy && resultColumns.numGeneratedColumnsForGroupBy() > 0) {
+
+		if (wasGroupBy &&
+			resultColumns.numGeneratedColumnsForGroupBy() > 0 &&
+			windows == null) // windows handling already added a PRN which
+							 // obviates this.
+		{
 			// This case takes care of columns generated for group by's which 
 			// will need to be removed from the final projection. Note that the
 			// GroupByNode does remove generated columns but in certain cases
@@ -2224,4 +2395,35 @@
 	{
 		return !selectAggregates.isEmpty();
 	}
+
+	/**
+	 * Used by SubqueryNode to avoid flattening of a subquery if a window is
+	 * defined on it. Note that any inline window definitions should have been
+	 * collected from both the selectList and orderByList at the time this
+	 * method is called, so the windows list is complete. This is true after
+	 * preprocess is completed.
+	 *
+	 * @return true if this select node has any windows on it
+	 */
+	public boolean hasWindows()
+	{
+		return windows != null;
+	}
+
+
+	public static void checkNoWindowFunctions(QueryTreeNode clause,
+											   String clauseName)
+			throws StandardException {
+
+		// Clause cannot contain window functions except inside subqueries
+		HasNodeVisitor visitor = new HasNodeVisitor(WindowFunctionNode.class,
+													SubqueryNode.class);
+		clause.accept(visitor);
+
+		if (visitor.hasNode()) {
+			throw StandardException.newException(
+				SQLState.LANG_WINDOW_FUNCTION_CONTEXT_ERROR,
+				clauseName);
+		}
+	}
 }

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=829155&r1=829154&r2=829155&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 Fri Oct 23 18:05:03 2009
@@ -1027,6 +1027,7 @@
 				null,     // WHERE clause
 				null,     // GROUP BY list
 				null,     // having clause
+				null, /* window list */
 				getContextManager());
 
 		/* And finally, transform the "*" in the new SELECT node

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=829155&r1=829154&r2=829155&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 Oct 23 18:05:03 2009
@@ -627,6 +627,7 @@
 					  underTopAndNode && !havingSubquery &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
 					  parentComparisonOperator instanceof BinaryComparisonOperatorNode;
+
 		if (flattenable)
 		{
 			/* If we got this far and we are an expression subquery
@@ -680,6 +681,7 @@
 		 *	  that the flattening of the subquery will not
 		 *	  introduce duplicates into the result set.
          *  o The subquery is not part of a having clause (DERBY-3257)
+		 *  o There are no windows defined on it
 		 *
 		 *	OR,
 		 *  o The subquery is NOT EXISTS, NOT IN, ALL (beetle 5173).
@@ -691,6 +693,7 @@
 		boolean flattenableNotExists = (isNOT_EXISTS() || canAllBeFlattened());
 
 		flattenable = (resultSet instanceof SelectNode) &&
+ 			          !((SelectNode)resultSet).hasWindows() &&
 					  underTopAndNode && !havingSubquery &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
 					  (isIN() || isANY() || isEXISTS() || flattenableNotExists ||
@@ -728,6 +731,7 @@
 				}
 				/* Never flatten to normal join for NOT EXISTS.
 				 */
+
 				if ((! flattenableNotExists) && select.uniqueSubquery(additionalEQ))
 				{
 					// Flatten the subquery

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowDefinitionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowDefinitionNode.java?rev=829155&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowDefinitionNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowDefinitionNode.java Fri Oct 23 18:05:03 2009
@@ -0,0 +1,155 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.WindowDefinitionNode
+
+   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 org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.reference.SQLState;
+
+import java.sql.Types;
+
+/**
+ * This class represents an OLAP window definition.
+ */
+public final class WindowDefinitionNode extends WindowNode
+{
+    /**
+     * True of the window definition was inlined.
+     */
+    private boolean inlined;
+
+    /**
+     * The order by list if the window definition contains a <window order
+     * clause>, else null.
+     */
+    private OrderByList orderByList;
+
+    /**
+     * Initializer.
+     *
+     * @param arg1 The window name, null if in-lined definition
+     * @param arg2 ORDER BY list
+     * @exception StandardException
+     */
+    public void init(Object arg1,
+                     Object arg2)
+        throws StandardException
+    {
+        String name = (String)arg1;
+
+        orderByList = (OrderByList)arg2;
+
+        if (name != null) {
+            super.init(arg1);
+            inlined = false;
+        } else {
+            super.init("IN-LINE");
+            inlined = true;
+        }
+
+        if (orderByList != null) {
+            throw StandardException.newException(SQLState.NOT_IMPLEMENTED,
+                                                 "WINDOW/ORDER BY");
+        }
+    }
+
+
+    /**
+     * java.lang.Object override.
+     * @see QueryTreeNode#toString
+     */
+    public String toString() {
+        return ("name: " + getName() + "\n" +
+                "inlined: " + inlined + "\n" +
+                "()\n");
+    }
+
+
+
+    /**
+     * QueryTreeNode override. Prints the sub-nodes of this object.
+     * @see QueryTreeNode#printSubNodes
+     *
+     * @param depth     The depth of this node in the tree
+     */
+
+    public void printSubNodes(int depth)
+    {
+        if (SanityManager.DEBUG)
+        {
+            super.printSubNodes(depth);
+
+            if (orderByList != null) {
+                printLabel(depth, "orderByList: "  + depth);
+                orderByList.treePrint(depth + 1);
+            }
+        }
+    }
+
+
+    /**
+     * Used to merge equivalent window definitions.
+     *
+     * @param wl list of window definitions
+     * @return an existing window definition from wl, if 'this' is equivalent
+     * to a window in wl.
+     */
+    public WindowDefinitionNode findEquivalentWindow(WindowList wl) {
+        for (int i = 0; i < wl.size(); i++) {
+            WindowDefinitionNode old = (WindowDefinitionNode)wl.elementAt(i);
+
+            if (isEquivalent(old)) {
+                return old;
+            }
+        }
+        return null;
+    }
+
+
+
+    /**
+     * @return true if the window specifications are equal; no need to create
+     * more than one window then.
+     */
+    private boolean isEquivalent(WindowDefinitionNode other) {
+        if (orderByList == null && other.getOrderByList() == null) {
+            return true;
+        }
+
+        if (SanityManager.DEBUG) {
+            SanityManager.ASSERT(
+                false,
+                "FIXME: ordering in windows not implemented yet");
+        }
+        return false;
+    }
+
+
+
+    /**
+     * @return the order by list of this window definition if any, else null.
+     */
+    public OrderByList getOrderByList() {
+        return orderByList;
+    }
+}

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

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionNode.java?rev=829155&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowFunctionNode.java Fri Oct 23 18:05:03 2009
@@ -0,0 +1,275 @@
+/*
+   Derby - Class org.apache.derby.impl.sql.compile.WindowFunctionNode
+
+   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 org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.sql.compile.CompilerContext;
+import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+import org.apache.derby.iapi.reference.SQLState;
+
+import java.sql.Types;
+import java.util.Vector;
+
+
+/**
+ * Superclass of any window function call.
+ */
+public abstract class WindowFunctionNode extends UnaryOperatorNode
+{
+
+    private WindowNode window; // definition or reference
+
+    /*
+    ** We wind up pushing all window function calls into a different
+    ** resultColumnList.  When we do this (in replaceCallsWithColumnReference),
+    ** we return a column reference and create a new result column.  This is
+    ** used to store that result column.
+    */
+    private ResultColumn            generatedRC;
+    private ColumnReference         generatedRef;
+
+    /**
+     * Initializer for a WindowFunctionNode
+     * @param arg1 null (operand)
+     * @param arg2 function mame (operator)
+     * @param arg3 window node (definition or reference)
+     * @exception StandardException
+     */
+    public void init(Object arg1, Object arg2, Object arg3)
+    {
+        super.init(arg1, arg2, null);
+        this.window = (WindowNode)arg3;
+    }
+
+    /**
+     * ValueNode override.
+     * @see ValueNode#isConstantExpression
+     */
+    public boolean isConstantExpression()
+    {
+        return false;
+    }
+
+    /**
+     * ValueNode override.
+     * @see ValueNode#isConstantExpression
+     */
+    public boolean constantExpression(PredicateList whereClause)
+    {
+        // Without this, an ORDER by on ROW_NUMBER could get optimised away
+        // if there is a restriction, e.g.
+        //
+        // SELECT -ABS(i) a, ROW_NUMBER() OVER () c FROM t
+        //     WHERE i > 1 ORDER BY c DESC
+        return false;
+    }
+
+
+    /**
+     * @return window associated with this window function
+     */
+    public WindowNode getWindow() {
+        return window;
+    }
+
+
+    /**
+     * Set window associated with this window function call.
+     * @param wdn window definition
+     */
+    public void setWindow(WindowDefinitionNode wdn) {
+        this.window = wdn;
+    }
+
+
+    /**
+     * ValueNode override.
+     * @see ValueNode#bindExpression
+     */
+    public ValueNode bindExpression(
+            FromList fromList,
+            SubqueryList subqueryList,
+            Vector  aggregateVector)
+        throws StandardException
+    {
+        if (window instanceof WindowReferenceNode) {
+
+            WindowDefinitionNode found =
+                definedWindow(fromList.getWindows(), window.getName());
+
+            if (found != null) {
+                window = found;
+            } else {
+                throw StandardException.
+                    newException(SQLState.LANG_NO_SUCH_WINDOW,
+                                 window.getName());
+            }
+        }
+
+        return this;
+    }
+
+
+    /**
+     * @return if name matches a defined window (in windows), return the
+     * definition of that window, else null.
+     */
+    private WindowDefinitionNode definedWindow(WindowList windows,
+                                               String name) {
+        for (int i=0; i < windows.size(); i++) {
+            WindowDefinitionNode wdn =
+                (WindowDefinitionNode)windows.elementAt(i);
+
+            if (wdn.getName().equals(name)) {
+                return wdn;
+            }
+        }
+        return null;
+    }
+
+
+    /**
+     * QueryTreeNode override.
+     * @see QueryTreeNode#printSubNodes
+     */
+
+    public void printSubNodes(int depth)
+    {
+        if (SanityManager.DEBUG)
+        {
+            super.printSubNodes(depth);
+
+            printLabel(depth, "window: ");
+            window.treePrint(depth + 1);
+        }
+    }
+
+    /**
+     * Replace window function calls in the expression tree with a
+     * ColumnReference to that window function, append the aggregate to the
+     * supplied RCL (assumed to be from the child ResultSetNode) and return the
+     * ColumnReference.
+     *
+     * @param rcl   The RCL to append to.
+     * @param tableNumber   The tableNumber for the new ColumnReference
+     *
+     * @return ValueNode    The (potentially) modified tree.
+     *
+     * @exception StandardException         Thrown on error
+     */
+    public ValueNode replaceCallsWithColumnReferences(ResultColumnList rcl,
+                                                      int tableNumber)
+        throws StandardException
+    {
+        /*
+         * This call is idempotent.  Do the right thing if we have already
+         * replaced ourselves.
+         */
+        if (generatedRef == null)
+        {
+            String                  generatedColName;
+            CompilerContext         cc = getCompilerContext();
+            generatedColName ="SQLCol" + cc.getNextColumnNumber();
+            generatedRC = (ResultColumn) getNodeFactory().getNode(
+                                            C_NodeTypes.RESULT_COLUMN,
+                                            generatedColName,
+                                            this,
+                                            getContextManager());
+            generatedRC.markGenerated();
+
+            // Parse time.
+            //
+            generatedRef = (ColumnReference) getNodeFactory().getNode(
+                                                C_NodeTypes.COLUMN_REFERENCE,
+                                                generatedRC.getName(),
+                                                null,
+                                                getContextManager());
+
+            // RESOLVE - unknown nesting level, but not correlated, so nesting
+            // levels must be 0
+            generatedRef.setSource(generatedRC);
+            generatedRef.setNestingLevel(0);
+            generatedRef.setSourceLevel(0);
+
+            if (tableNumber != -1)
+            {
+                generatedRef.setTableNumber(tableNumber);
+            }
+
+            rcl.addResultColumn(generatedRC);
+
+
+            // Mark the ColumnReference as being generated to replace a call to
+            // a window function
+
+            generatedRef.markGeneratedToReplaceWindowFunctionCall();
+        }
+        else
+        {
+            rcl.addResultColumn(generatedRC);
+        }
+
+        return generatedRef;
+    }
+
+    /**
+     * Get the generated ColumnReference to this window function after the
+     * parent called replaceCallsWithColumnReferences().
+     * <p/>
+     * There are cases where this will not have been done because the tree has
+     * been re-written to eliminate the window function, e.g. for this query:
+     * <p/><pre>
+     *     {@code SELECT * FROM t WHERE EXISTS
+     *           (SELECT ROW_NUMBER() OVER () FROM t)}
+     * </pre><p/>
+     * in which case the top PRN of the subquery sitting over a
+     * WindowResultSetNode just contains a RC which is boolean constant {@code
+     * true}. This means that the replaceCallsWithColumnReferences will not
+     * have been called for {@code this}, so the returned {@code generatedRef}
+     * is null.
+     *
+     * @return the column reference
+     */
+    public ColumnReference getGeneratedRef()
+    {
+        return generatedRef;
+    }
+
+
+    /**
+     * Get the null result expression column.
+     *
+     * @return the value node
+     *
+     * @exception StandardException on error
+     */
+    public ValueNode    getNewNullResultExpression()
+        throws StandardException
+    {
+        //
+        // Create a result column with the aggregate operand
+        // it.
+        //
+        return getNullNode(getTypeServices());
+    }
+}

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

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowList.java?rev=829155&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowList.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowList.java Fri Oct 23 18:05:03 2009
@@ -0,0 +1,40 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.WindowList
+
+   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;
+
+/**
+ * A WindowList represents the list of windows (definitions) for a table
+ * expression, either defined explicitly in a WINDOW clause, or inline in the
+ * SELECT list or ORDER BY clause.
+ *
+ */
+
+public class WindowList extends OrderedColumnList
+{
+    /**
+     * @param window the window definition to add to the list
+     */
+    public void addWindow(WindowDefinitionNode window)
+    {
+        addElement(window);
+    }
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowList.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=829155&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 Oct 23 18:05:03 2009
@@ -0,0 +1,68 @@
+/*
+
+   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 org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.types.TypeId;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+import java.sql.Types;
+import java.util.Vector;
+
+/**
+ * Superclass of window definition and window reference.
+ */
+public abstract class WindowNode extends QueryTreeNode
+{
+    /**
+     * The provided name of the window if explicitly defined in a window
+     * clause. If the definition is inlined, currently the definition has
+     * windowName "IN_LINE".  The standard 2003 sec. 4.14.9 calls for a
+     * impl. defined one.
+     */
+    private String windowName;
+
+
+    /**
+     * Initializer
+     *
+     * @param arg1 The window name
+     *
+     * @exception StandardException
+     */
+    public void init(Object arg1)
+        throws StandardException
+    {
+        windowName = (String)arg1;
+    }
+
+
+    /**
+     * @return the name of this window
+     */
+    public String getName() {
+        return windowName;
+    }
+
+
+
+}

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

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowReferenceNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowReferenceNode.java?rev=829155&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowReferenceNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowReferenceNode.java Fri Oct 23 18:05:03 2009
@@ -0,0 +1,54 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.WindowReferenceNode
+
+   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 org.apache.derby.iapi.services.sanity.SanityManager;
+
+import java.sql.Types;
+import java.util.Vector;
+
+/**
+ * Represents a reference to an explicitly defined window
+ */
+public final class WindowReferenceNode extends WindowNode
+{
+    /**
+     * Initializer
+     *
+     * @param arg1 The window name referenced
+     *
+     * @exception StandardException
+     */
+    public void init(Object arg1)
+        throws StandardException
+    {
+        super.init(arg1);
+    }
+
+    // java.lang.Object override
+    public String toString() {
+        return "referenced window: " + getName() + "\n" +
+            super.toString();
+    }
+}

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

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowResultSetNode.java?rev=829155&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowResultSetNode.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/WindowResultSetNode.java Fri Oct 23 18:05:03 2009
@@ -0,0 +1,455 @@
+/*
+   Derby - Class org.apache.derby.impl.sql.compile.WindowResultSetNode
+
+   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.Iterator;
+import java.util.Vector;
+import java.util.ArrayList;
+import java.util.Comparator;
+import java.util.Collections;
+
+import org.apache.derby.catalog.IndexDescriptor;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.reference.ClassName;
+import org.apache.derby.iapi.reference.SQLState;
+import org.apache.derby.iapi.services.classfile.VMOpcode;
+import org.apache.derby.iapi.services.compiler.MethodBuilder;
+import org.apache.derby.iapi.services.io.FormatableArrayHolder;
+import org.apache.derby.iapi.services.io.FormatableBitSet;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.sql.LanguageFactory;
+import org.apache.derby.iapi.sql.ResultColumnDescriptor;
+import org.apache.derby.iapi.sql.compile.AccessPath;
+import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+import org.apache.derby.iapi.sql.compile.CostEstimate;
+import org.apache.derby.iapi.sql.compile.Optimizable;
+import org.apache.derby.iapi.sql.compile.OptimizablePredicate;
+import org.apache.derby.iapi.sql.compile.OptimizablePredicateList;
+import org.apache.derby.iapi.sql.compile.Optimizer;
+import org.apache.derby.iapi.sql.compile.RequiredRowOrdering;
+import org.apache.derby.iapi.sql.compile.RowOrdering;
+import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor;
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.store.access.ColumnOrdering;
+import org.apache.derby.impl.sql.execute.AggregatorInfo;
+import org.apache.derby.impl.sql.execute.AggregatorInfoList;
+
+
+/**
+ * A WindowResultSetNode represents a result set for a window partitioning on a
+ * select. Modelled on the code in GroupByNode.
+ */
+public class WindowResultSetNode extends SingleChildResultSetNode
+{
+    /**
+     * The parent to the WindowResultSetNode.  We generate a ProjectRestrict
+     * over the windowing node and parent is set to that node.
+     */
+    FromTable   parent;
+    Vector windowFuncCalls;
+    WindowDefinitionNode wdn;
+
+    /**
+     * Intializer for a WindowResultSetNode.
+     * @param bottomPR The project restrict result set we want to wrap
+     * @param windowDef The window definition
+     * @param windowFuncCalls All window function calls in SELECT's select list
+     * and order by list.
+     * @param nestingLevel Nesting level
+     *
+     * @exception StandardException     Thrown on error
+     */
+    public void init(
+        Object bottomPR,
+        Object windowDef,
+        Object windowFuncCalls,
+        Object nestingLevel) throws StandardException
+    {
+        super.init(bottomPR, null);
+        this.wdn = (WindowDefinitionNode)windowDef;
+        this.windowFuncCalls = (Vector)windowFuncCalls;
+        setLevel(((Integer)nestingLevel).intValue());
+
+        ResultColumnList newBottomRCL;
+
+        this.parent = this;
+
+        /*
+        ** The first thing we do is put ourselves on top of the SELECT.  The
+        ** select becomes the childResult.  So our RCL becomes its RCL (so
+        ** nodes above it now point to us).  Map our RCL to its columns.
+        */
+        newBottomRCL = childResult.getResultColumns().copyListAndObjects();
+        resultColumns = childResult.getResultColumns();
+        childResult.setResultColumns(newBottomRCL);
+
+        // Wrao purselved int a project/restrict as per convention.
+        addNewPRNode();
+
+        // Add the extra result columns required
+        addNewColumns();
+    }
+
+    /**
+     * Add a new PR node.  Put the new PR under any sort.
+     *
+     * @exception standard exception
+     */
+    private void addNewPRNode()
+        throws StandardException
+    {
+        /*
+        ** Get the new PR, put above the WindowResultSetNode.
+        */
+        ResultColumnList rclNew = (ResultColumnList)getNodeFactory().
+            getNode(C_NodeTypes.RESULT_COLUMN_LIST,
+                    getContextManager());
+
+        int sz = resultColumns.size();
+        for (int i = 0; i < sz; i++)
+        {
+            ResultColumn rc = (ResultColumn) resultColumns.elementAt(i);
+            if (!rc.isGenerated()) {
+                rclNew.addElement(rc);
+            }
+        }
+
+        // if any columns in the source RCL were generated for an order by
+        // remember it in the new RCL as well. After the sort is done it will
+        // have to be projected out upstream.
+        rclNew.copyOrderBySelect(resultColumns);
+
+        parent = (FromTable) getNodeFactory().getNode(
+                                        C_NodeTypes.PROJECT_RESTRICT_NODE,
+                                        this, // child
+                                        rclNew,
+                                        null, // havingClause,
+                                        null, // restriction list
+                                        null, // project subqueries
+                                        null, // havingSubquerys,
+                                        null, // tableProperties,
+                                        getContextManager());
+
+
+        /*
+         * Reset the bottom RCL to be empty.
+         */
+        childResult.setResultColumns((ResultColumnList)
+                                            getNodeFactory().getNode(
+                                                C_NodeTypes.RESULT_COLUMN_LIST,
+                                                getContextManager()));
+
+        /*
+         * Set the Windowing RCL to be empty
+         */
+        resultColumns = (ResultColumnList) getNodeFactory().getNode(
+                                            C_NodeTypes.RESULT_COLUMN_LIST,
+                                            getContextManager());
+
+
+        // Add all referenced columns in select list to windowing node's RCL
+        // and substitute references in original node to point to the Windowing
+        // result set. (modelled on GroupByNode's action for addUnAggColumns)
+        CollectNodesVisitor getCRVisitor =
+            new CollectNodesVisitor(ColumnReference.class);
+
+        ResultColumnList prcl = parent.getResultColumns();
+
+        parent.getResultColumns().accept(getCRVisitor);
+
+        Vector colRefs = getCRVisitor.getList();
+
+        // Find all unique columns referenced and add those to windowing result
+        // set.
+        Vector uniqueCols = new Vector();
+        for (int i= 0; i< colRefs.size(); i++) {
+            ColumnReference cr = (ColumnReference)colRefs.elementAt(i);
+            if (!colRefAlreadySeen(uniqueCols, cr)) {
+                uniqueCols.add(cr);
+            }
+        }
+
+        // Add all virtual column select list to windowing node's RCL and
+        // substitute references in original node to point to the Windowing
+        // result set. Happens for example when we have a window over a group
+        // by.
+        CollectNodesVisitor getVCVisitor =
+            new CollectNodesVisitor(VirtualColumnNode.class);
+
+        parent.getResultColumns().accept(getVCVisitor);
+        Vector vcs = getVCVisitor.getList();
+
+        // Add any virtual columns to windowing result.
+        for (int i= 0; i< vcs.size(); i++) {
+            uniqueCols.add(vcs.elementAt(i));
+        }
+
+        ResultColumnList bottomRCL  = childResult.getResultColumns();
+        ResultColumnList windowingRCL = resultColumns;
+
+        for (int i= 0; i< uniqueCols.size(); i++) {
+            ValueNode crOrVcn = (ValueNode)uniqueCols.elementAt(i);
+
+            ResultColumn newRC = (ResultColumn) getNodeFactory().getNode(
+                    C_NodeTypes.RESULT_COLUMN,
+                    "##UnWindowingColumn",
+                    crOrVcn,
+                    getContextManager());
+
+            // add this result column to the bottom rcl
+            bottomRCL.addElement(newRC);
+            newRC.markGenerated();
+            newRC.bindResultColumnToExpression();
+            newRC.setVirtualColumnId(bottomRCL.size());
+
+            // now add this column to the windowing result column list
+            ResultColumn wRC = (ResultColumn) getNodeFactory().getNode(
+                    C_NodeTypes.RESULT_COLUMN,
+                    "##UnWindowingColumn",
+                    crOrVcn,
+                    getContextManager());
+            windowingRCL.addElement(wRC);
+            wRC.markGenerated();
+            wRC.bindResultColumnToExpression();
+            wRC.setVirtualColumnId(windowingRCL.size());
+
+            /*
+             ** Reset the original node to point to the
+             ** Windowing result set.
+             */
+            VirtualColumnNode vc = (VirtualColumnNode) getNodeFactory().getNode(
+                    C_NodeTypes.VIRTUAL_COLUMN_NODE,
+                    this, // source result set.
+                    wRC,
+                    new Integer(windowingRCL.size()),
+                    getContextManager());
+
+            SubstituteExpressionVisitor seVis =
+                new SubstituteExpressionVisitor(crOrVcn, vc, null);
+            parent.getResultColumns().accept(seVis);
+        }
+    }
+
+
+    /**
+     * @return true if an equivalent column reference to cand is already
+     * present in uniqueColRefs
+     */
+    private boolean colRefAlreadySeen(Vector uniqueColRefs,
+                                      ColumnReference cand)
+            throws StandardException {
+
+        for (int i= 0; i< uniqueColRefs.size(); i++) {
+            ColumnReference cr = (ColumnReference)uniqueColRefs.elementAt(i);
+
+            if (cr.isEquivalent(cand)) {
+                return true;
+            }
+        }
+        return false;
+    }
+
+    /**
+     * Substitute new result columns for window function calls and add the
+     * result columns to childResult's list of columns.
+     */
+    private void addNewColumns() throws StandardException {
+        /*
+         * Now process all of the window function calls.  Replace every
+         * call with an RC.  We toss out the list of RCs, we need to get
+         * each RC as we process its corresponding window function.
+         */
+        LanguageFactory lf =
+            getLanguageConnectionContext().getLanguageFactory();
+
+        ResultColumnList bottomRCL  = childResult.getResultColumns();
+        ResultColumnList windowingRCL = resultColumns;
+
+        ReplaceWindowFuncCallsWithCRVisitor replaceCallsVisitor =
+            new ReplaceWindowFuncCallsWithCRVisitor(
+                (ResultColumnList) getNodeFactory().getNode(
+                    C_NodeTypes.RESULT_COLUMN_LIST,
+                    getContextManager()),
+                ((FromTable) childResult).getTableNumber(),
+                ResultSetNode.class);
+        parent.getResultColumns().accept(replaceCallsVisitor);
+
+        for (int i=0; i < windowFuncCalls.size(); i++) {
+            WindowFunctionNode winFunc =
+                (WindowFunctionNode)windowFuncCalls.elementAt(i);
+
+            if (SanityManager.DEBUG) {
+                SanityManager.ASSERT(
+                    !(winFunc.getWindow() instanceof WindowReferenceNode),
+                    "unresolved window-reference: " +
+                    winFunc.getWindow().getName());
+            }
+
+            WindowDefinitionNode funcWindow =
+                (WindowDefinitionNode)winFunc.getWindow();
+
+            if (funcWindow == wdn) {
+                ResultColumn newRC = (ResultColumn) getNodeFactory().getNode(
+                    C_NodeTypes.RESULT_COLUMN,
+                    "##winFuncResult",
+                    winFunc.getNewNullResultExpression(),
+                    getContextManager());
+
+                newRC.markGenerated();
+                newRC.bindResultColumnToExpression();
+                bottomRCL.addElement(newRC);
+                newRC.setVirtualColumnId(bottomRCL.size());
+                int winFuncResultVColId = newRC.getVirtualColumnId();
+
+                /*
+                ** Set the WindowResultSetNode result column to point to this.
+                ** The Windowing Node result was created when we called
+                ** ReplaceWindowFuncCallsWithCRVisitor.
+                */
+                ColumnReference newColumnRef =
+                    (ColumnReference) getNodeFactory().getNode(
+                        C_NodeTypes.COLUMN_REFERENCE,
+                        newRC.getName(),
+                        null,
+                        getContextManager());
+
+                newColumnRef.setSource(newRC);
+                newColumnRef.setNestingLevel(this.getLevel());
+                newColumnRef.setSourceLevel(this.getLevel());
+                newColumnRef.markGeneratedToReplaceWindowFunctionCall();
+
+                ResultColumn tmpRC = (ResultColumn) getNodeFactory().getNode(
+                    C_NodeTypes.RESULT_COLUMN,
+                    newRC.getColumnName(),
+                    newColumnRef,
+                    getContextManager());
+
+                tmpRC.markGenerated();
+                tmpRC.bindResultColumnToExpression();
+                windowingRCL.addElement(tmpRC);
+                tmpRC.setVirtualColumnId(windowingRCL.size());
+
+                /*
+                ** Set the column reference to point to
+                ** this.
+                */
+                newColumnRef = winFunc.getGeneratedRef();
+
+                if (newColumnRef != null) {
+                    newColumnRef.setSource(tmpRC);
+                } // Not generated, meaning it's no longer in use
+            }
+        }
+    }
+
+
+    /**
+     * override
+     * @see QueryTreeNode#generate
+     */
+    public void generate(ActivationClassBuilder acb,
+                         MethodBuilder mb)
+            throws StandardException
+    {
+        // Get the next ResultSet#, so we can number this ResultSetNode, its
+        // ResultColumnList and ResultSet.
+
+        assignResultSetNumber();
+
+        // Get the final cost estimate from the child.
+        costEstimate = childResult.getFinalCostEstimate();
+
+
+        acb.pushGetResultSetFactoryExpression(mb);
+
+        int rclSize = resultColumns.size();
+        FormatableBitSet referencedCols = new FormatableBitSet(rclSize);
+
+        /*
+         * Build a FormatableBitSet for columns to copy from source.
+         */
+
+        for (int index = rclSize-1; index >= 0; index--) {
+            ResultColumn rc = (ResultColumn) resultColumns.elementAt(index);
+            ValueNode expr = rc.getExpression();
+
+            if (rc.isGenerated() &&
+                    (expr instanceof ColumnReference) &&
+                    ((ColumnReference)expr).
+                        getGeneratedToReplaceWindowFunctionCall()) {
+
+                // meaningless to copy these, they arise in this rs.
+            } else {
+                referencedCols.set(index);
+            }
+        }
+
+        int 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
+
+        /* Pass in the erdNumber for the referenced column FormatableBitSet */
+        mb.push(erdNumber); // arg 5
+
+        /* There is no restriction at this level, we just want to pass null. */
+        mb.pushNull(ClassName.GeneratedMethod); // arg 6
+
+        mb.push(costEstimate.rowCount()); //arg 7
+        mb.push(costEstimate.getEstimatedCost()); // arg 8
+
+        mb.callMethod(VMOpcode.INVOKEINTERFACE, (String) null,
+            "getWindowResultSet", ClassName.NoPutResultSet, 8);
+
+    }
+
+
+
+    /**
+     * @return parent of this node, a PRN, used by SelectNode to retrieve new
+     * top result set node after window result set rewrite of result set tree.
+     */
+    public FromTable getParent() {
+        return parent;
+    }
+
+
+    /**
+     * QueryTreeNode override
+     * @see QueryTreeNode#printSubNodes
+     */
+    public void printSubNodes(int depth) {
+        if (SanityManager.DEBUG) {
+			super.printSubNodes(depth);
+
+            printLabel(depth, "wdn: ");
+            wdn.treePrint(depth + 1);
+        }
+    }
+
+}

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



Mime
View raw message