db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r891952 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Thu, 17 Dec 2009 22:19:09 GMT
Author: dag
Date: Thu Dec 17 22:19:08 2009
New Revision: 891952

URL: http://svn.apache.org/viewvc?rev=891952&view=rev
Log:
DERBY-4397 Allow ORDER BY in subqueries

Patch derby-4397-2 which implements this functionality and also adds a
new test, OrderByInSubqueries. This patch also solves DERBY-4.


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java   (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.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/OrderByNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/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/testing/org/apache/derbyTesting/functionTests/master/orderby.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java Thu Dec 17 22:19:08 2009
@@ -286,8 +286,11 @@
 	 */
 	public void printSubNodes(int depth) {
 		if (SanityManager.DEBUG) {
-			printLabel(depth, "tableElementList: ");
-			tableElementList.treePrint(depth + 1);
+			if (tableElementList != null) {
+				printLabel(depth, "tableElementList: ");
+				tableElementList.treePrint(depth + 1);
+			}
+
 		}
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java Thu Dec 17 22:19:08 2009
@@ -68,7 +68,7 @@
 	int					checkOption;
 	ProviderInfo[]		providerInfos;
 	ColumnInfo[]		colInfos;
-
+	private OrderByList orderByList;
 
 	/**
 	 * Initializer for a CreateViewNode
@@ -80,6 +80,7 @@
 	 * @param checkOption		The type of WITH CHECK OPTION that was specified
 	 *							(NONE for now)
 	 * @param qeText			The text for the queryExpression
+	 * @param orderCols         ORDER BY list
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
@@ -88,7 +89,8 @@
 				   Object resultColumns,
 				   Object	 queryExpression,
 				   Object checkOption,
-				   Object qeText)
+				   Object qeText,
+				   Object orderCols)
 		throws StandardException
 	{
 		initAndCheck(newObjectName);
@@ -96,6 +98,7 @@
 		this.queryExpression = (ResultSetNode) queryExpression;
 		this.checkOption = ((Integer) checkOption).intValue();
 		this.qeText = ((String) qeText).trim();
+		this.orderByList = (OrderByList)orderCols;
 
 		implicitCreateSchema = true;
 	}
@@ -408,4 +411,7 @@
 		}
 	}
 
+	public OrderByList getOrderByList() {
+		return orderByList;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java Thu Dec 17 22:19:08 2009
@@ -677,7 +677,7 @@
 	 *
 	 * @exception StandardException		Thrown on failure
 	 */
-	void	bindRowScopedExpression
+	static void	bindRowScopedExpression
 	(
 		NodeFactory			nodeFactory,
         ContextManager    contextManager,
@@ -1915,6 +1915,30 @@
 	}
 
 	/**
+	 * Prints the sub-nodes of this object.  See QueryTreeNode.java for
+	 * how tree printing is supposed to work.
+	 *
+	 * @param depth		The depth of this node in the tree
+	 */
+
+	public void printSubNodes(int depth)
+	{
+		if (SanityManager.DEBUG)
+		{
+			super.printSubNodes(depth);
+
+			printLabel(depth, "targetTableName: ");
+			targetTableName.treePrint(depth + 1);
+
+			if (resultColumnList != null)
+			{
+				printLabel(depth, "resultColumnList: ");
+				resultColumnList.treePrint(depth + 1);
+			}
+		}
+	}
+
+	/**
 	 * Accept the visitor for all visitable children of this node.
 	 * 
 	 * @param v the visitor
@@ -1931,7 +1955,6 @@
 			targetTableName.accept(v);
 		}
 	}
-
 }
 
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Thu Dec 17 22:19:08 2009
@@ -2237,12 +2237,14 @@
 					SanityManager.ASSERT(vd != null,
 						"vd not expected to be null for " + tableName);
 				}
-	
+
 				cvn = (CreateViewNode)
 				          parseStatement(vd.getViewText(), false);
 
 				rsn = cvn.getParsedQueryExpression();
-	
+
+				OrderByList orderByList = cvn.getOrderByList();
+
 				/* If the view contains a '*' then we mark the views derived column list
 				 * so that the view will still work, and return the expected results,
 				 * if any of the tables referenced in the view have columns added to
@@ -2271,7 +2273,8 @@
 
 				fsq = (FromSubquery) getNodeFactory().getNode(
 					C_NodeTypes.FROM_SUBQUERY,
-					rsn, 
+					rsn,
+					orderByList,
 					(correlationName != null) ? 
                         correlationName : getOrigTableName().getTableName(), 
 					resultColumns,

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Thu Dec 17 22:19:08 2009
@@ -46,6 +46,7 @@
 public class FromSubquery extends FromTable
 {
 	ResultSetNode	subquery;
+	private OrderByList orderByList;
 
 	/**
 	 * DERBY-3270: If this subquery represents an expanded view, this holds the
@@ -57,18 +58,21 @@
 	 * Intializer for a table in a FROM list.
 	 *
 	 * @param subquery		The subquery
+	 * @param orderByList   ORDER BY list if any, or null
 	 * @param correlationName	The correlation name
 	 * @param derivedRCL		The derived column list
 	 * @param tableProperties	Properties list associated with the table
 	 */
 	public void init(
 					Object subquery,
+					Object orderByList,
 					Object correlationName,
 				 	Object derivedRCL,
 					Object tableProperties)
 	{
 		super.init(correlationName, tableProperties);
 		this.subquery = (ResultSetNode) subquery;
+		this.orderByList = (OrderByList)orderByList;
 		resultColumns = (ResultColumnList) derivedRCL;
 	}
 
@@ -89,6 +93,12 @@
 				printLabel(depth, "subquery: ");
 				subquery.treePrint(depth + 1);
 			}
+
+			if (orderByList != null)
+			{
+				printLabel(depth, "orderByList: ");
+				orderByList.treePrint(depth + 1);
+			}
 		}
 	}
 
@@ -200,7 +210,10 @@
 		/* From subqueries cannot be correlated, so we pass an empty FromList
 		 * to subquery.bindExpressions() and .bindResultColumns()
 		 */
-		
+		if (orderByList != null) {
+			orderByList.pullUpOrderByColumns(subquery);
+		}
+
 		nestedFromList = emptyFromList;
 
 		CompilerContext compilerContext = getCompilerContext();
@@ -219,6 +232,10 @@
 			}
 		}
 
+		if (orderByList != null) {
+			orderByList.bindOrderByColumns(subquery);
+		}
+
 		/* Now that we've bound the expressions in the subquery, we 
 		 * can propagate the subquery's RCL up to the FromSubquery.
 		 * Get the subquery's RCL, assign shallow copy back to
@@ -325,13 +342,27 @@
 									FromList fromList)
 								throws StandardException
 	{
+		// Push the order by list down to the ResultSet
+		if (orderByList != null)
+		{
+			// If we have more than 1 ORDERBY columns, we may be able to
+			// remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+			if (orderByList.size() > 1)
+			{
+				orderByList.removeDupColumns();
+			}
+
+			subquery.pushOrderByList(orderByList);
+			orderByList = null;
+		}
+
 		/* We want to chop out the FromSubquery from the tree and replace it 
 		 * with a ProjectRestrictNode.  One complication is that there may be 
 		 * ColumnReferences above us which point to the FromSubquery's RCL.
 		 * What we want to return is a tree with a PRN with the
 		 * FromSubquery's RCL on top.  (In addition, we don't want to be
 		 * introducing any redundant ProjectRestrictNodes.)
-		 * Another complication is that we want to be able to only only push
+		 * Another complication is that we want to be able to only push
 		 * projections and restrictions down to this ProjectRestrict, but
 		 * we want to be able to push them through as well.
 		 * So, we:
@@ -541,7 +572,11 @@
 		 * NOTE: This method will capture any column renaming due to 
 		 * a derived column list.
 		 */
-		int rclSize = resultColumns.size();
+
+		// Use visibleSize, because we don't want to propagate any order by
+		// columns not selected.
+		int rclSize = resultColumns.visibleSize();
+
 		for (int index = 0; index < rclSize; index++)
 		{
 			ResultColumn resultColumn = (ResultColumn) resultColumns.elementAt(index);

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java Thu Dec 17 22:19:08 2009
@@ -110,6 +110,7 @@
 	public		FKInfo				fkInfo;
 	protected	boolean				bulkInsert;
 	private 	boolean				bulkInsertReplace;
+	private     OrderByList         orderByList;
 	
 	protected   RowLocation[] 		autoincRowLocation;
 	/**
@@ -124,13 +125,16 @@
 	 * @param queryExpression	The query expression that will generate
 	 *				the rows to insert into the given table
 	 * @param targetProperties	The properties specified on the target table
+     * @param orderByList The order by list for the source result set, null if
+	 *			no order by list
 	 */
 
 	public void init(
 			Object targetName,
 			Object insertColumns,
 			Object queryExpression,
-			Object targetProperties)
+			Object targetProperties,
+            Object orderByList)
 	{
 		/* statementType gets set in super() before we've validated
 		 * any properties, so we've kludged the code to get the
@@ -144,6 +148,7 @@
 		setTarget((QueryTreeNode) targetName);
 		targetColumnList = (ResultColumnList) insertColumns;
 		this.targetProperties = (Properties) targetProperties;
+		this.orderByList = (OrderByList) orderByList;
 
 		/* Remember that the query expression is the source to an INSERT */
 		getResultSetNode().setInsertSource();
@@ -206,6 +211,11 @@
 				targetColumnList.treePrint(depth + 1);
 			}
 
+			if (orderByList != null) {
+				printLabel(depth, "orderByList: ");
+				orderByList.treePrint(depth + 1);
+			}
+
 			/* RESOLVE - need to print out targetTableDescriptor */
 		}
 	}
@@ -421,6 +431,18 @@
 			}
 		}
 
+		// Bind the ORDER BY columns
+		if (orderByList != null)
+		{
+			orderByList.pullUpOrderByColumns(resultSet);
+
+			// The select list may have new columns now, make sure to bind
+			// those.
+			super.bindExpressions();
+
+			orderByList.bindOrderByColumns(resultSet);
+		}
+
 		resultSet = enhanceAndCheckForAutoincrement(resultSet, inOrder, colMap);
 
 		resultColumnList.checkStorableExpressions(resultSet.getResultColumns());
@@ -796,6 +818,32 @@
 	}
 
 	/**
+     * {@inheritDoc}
+     * <p>
+     * Remove any duplicate ORDER BY columns and push an ORDER BY if present
+     * down to the source result set, before calling super.optimizeStatement.
+     * </p>
+	 */
+
+	public void optimizeStatement() throws StandardException
+	{
+		// Push the order by list down to the ResultSet
+		if (orderByList != null)
+		{
+			// If we have more than 1 ORDERBY columns, we may be able to
+			// remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+			if (orderByList.size() > 1)
+			{
+				orderByList.removeDupColumns();
+			}
+
+			resultSet.pushOrderByList(orderByList);
+			orderByList = null;
+		}
+		super.optimizeStatement();
+	}
+
+	/**
 	 * Code generation for insert
 	 * creates an expression for:
 	 *   ResultSetFactory.getInsertResultSet(resultSet.generate(ps), generationClausesResult, checkConstrainResult, this )

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java Thu Dec 17 22:19:08 2009
@@ -686,5 +686,16 @@
 								   dependentScan);
 	}
 
+	/**
+	 * Push the order by list down from InsertNode into its child result set so
+	 * that the optimizer has all of the information that it needs to consider
+	 * sort avoidance.
+	 *
+	 * @param orderByList	The order by list
+	 */
+	void pushOrderByList(OrderByList orderByList)
+	{
+		childResult.pushOrderByList(orderByList);
+	}
 
 }

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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -76,6 +76,7 @@
 	private ColumnOrdering[] columnOrdering;
 	private int estimatedRowSize;
 	private boolean sortNeeded = true;
+	private int resultSetNumber = -1;
 
 	/**
 		Add a column to the list
@@ -112,12 +113,11 @@
 	}
 
 	/**
-		Bind the update columns by their names to the target resultset
-		of the cursor specification.
-
-		@param target	The underlying result set
-	
-		@exception StandardException		Thrown on error
+	 *	Bind the update columns by their names to the target resultset of the
+	 * cursor specification. This variant is used by InsertNode.
+	 *
+	 * 	@param target	The underlying result set
+	 *	@exception StandardException		Thrown on error
 	 */
 	public void bindOrderByColumns(ResultSetNode target)
 					throws StandardException {
@@ -149,7 +149,7 @@
 			}
 		}
 	}
-	
+
 	/**
 	 * Adjust addedColumnOffset values due to removal of a duplicate column
 	 *
@@ -387,7 +387,7 @@
 
 		child.generate(acb, mb);
 
-		int resultSetNumber = cc.getNextResultSetNumber();
+		resultSetNumber = cc.getNextResultSetNumber();
 
 		// is a distinct query
 		mb.push(false);
@@ -842,4 +842,8 @@
 			buff.toString() + "\n" +
 			super.toString();
 	}
+
+	public int getResultSetNumber() {
+		return resultSetNumber;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByNode.java Thu Dec 17 22:19:08 2009
@@ -128,5 +128,19 @@
 		}
 
 	    orderByList.generate(acb, mb, childResult);
+
+		// We need to take note of result set number if ORDER BY is used in a
+		// subquery for the case where a PRN is inserted in top of the select's
+		// PRN to project away a sort column that is not part of the select
+		// list, e.g.
+		//
+		//     select * from (select i from t order by j desc) s
+		//
+		// If the resultSetNumber is not correctly set in our resultColumns,
+		// code generation for the PRN above us will fail when calling
+		// resultColumns.generateCore -> VCN.generateExpression, cf. the Sanity
+		// assert in VCN.generateExpression on sourceResultSetNumber >= 0.
+		resultSetNumber = orderByList.getResultSetNumber();
+		resultColumns.setResultSetNumber(resultSetNumber);
 	}
 }

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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -1865,4 +1865,16 @@
 		this.restriction = restriction;
 	}
 
+	/**
+	 * Push the order by list down from InsertNode into its child result set so
+	 * that the optimizer has all of the information that it needs to consider
+	 * sort avoidance.
+	 *
+	 * @param orderByList	The order by list
+	 */
+	void pushOrderByList(OrderByList orderByList)
+	{
+		childResult.pushOrderByList(orderByList);
+	}
+
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Thu Dec 17 22:19:08 2009
@@ -343,6 +343,25 @@
 	}
 
 	/**
+	 * Print call stack for debug purposes
+	 */
+
+	public void stackPrint()
+	{
+		if (SanityManager.DEBUG)
+		{
+			debugPrint("Stacktrace:\n");
+			Exception e = new Exception("dummy");
+            StackTraceElement[] st= e.getStackTrace();
+            for (int i=0; i<st.length; i++) {
+                debugPrint(st[i] + "\n");
+            }
+
+			debugFlush();
+		}
+	}
+
+	/**
 	 * Print this tree for debugging purposes.  This recurses through
 	 * all the sub-nodes and prints them indented by their depth in
 	 * the tree, starting with the given indentation.
@@ -371,6 +390,10 @@
 					debugPrint(thisStr);
 				}
 
+				if (thisStr.charAt(thisStr.length()-1) != '\n') {
+					debugPrint("\n");
+				}
+
 				printSubNodes(depth);
 			}
 

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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -646,15 +646,16 @@
 		 */
 		if (SanityManager.DEBUG)
 		{
-			 if ((! countMismatchAllowed) && size() != nameList.size())
+			 if ((! countMismatchAllowed) && visibleSize() != nameList.size())
 			 {
 				SanityManager.THROWASSERT(
-					"The size of the 2 lists is expected to be the same. size() = " +
-					size() + ", nameList.size() = " + nameList.size());
+					"The size of the 2 lists is expected to be the same. " +
+					"visibleSize() = " + visibleSize() +
+					", nameList.size() = " + nameList.size());
 			 }
 		 }
 
-		int size = (countMismatchAllowed) ? nameList.size() : size();
+		int size = (countMismatchAllowed) ? nameList.size() : visibleSize();
 		for (int index = 0; index < size; index++)
 		{
 			ResultColumn thisResultColumn = (ResultColumn) elementAt(index);
@@ -4227,6 +4228,7 @@
 	{
 		if (SanityManager.DEBUG) {
 			return "indexRow: " + indexRow + "\n" +
+				 "orderBySelect: " + orderBySelect + "\n" +
 				(indexRow ? "conglomerateId: " + conglomerateId + "\n"
 				 : "") +
 				super.toString();

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Thu Dec 17 22:19:08 2009
@@ -935,7 +935,8 @@
 			InsertNode target, boolean inOrder, int[] colMap)
 		throws StandardException
 	{
-		if (!inOrder || resultColumns.size() < target.resultColumnList.size()) {
+		if (!inOrder ||
+                resultColumns.visibleSize() < target.resultColumnList.size()) {
 			return generateProjectRestrictForInsert(target, colMap);
 		}
 		return this;
@@ -1001,7 +1002,7 @@
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	ResultColumn genNewRCForInsert(TableDescriptor targetTD,
+	private ResultColumn genNewRCForInsert(TableDescriptor targetTD,
                                    FromVTI targetVTI,
                                    int columnNumber,
 								   DataDictionary dataDictionary)
@@ -1901,4 +1902,15 @@
 		return null;
 	}
 	
+	/**
+	 * Override this for nodes that can take an ORDER BY
+	 */
+	public void setOrderBy(OrderByList orderCols) throws StandardException {
+		if (SanityManager.DEBUG)
+		{
+			SanityManager.THROWASSERT(
+				"ORDER BY attempted set for wrong node type:" +
+				getClass().getName());
+		}
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -1342,6 +1342,7 @@
 	 *		o  It does not contain a group by or having clause
 	 *		o  It does not contain aggregates.
 	 *		o  It is not a DISTINCT.
+	 *      o  It does not have an ORDER BY clause (pushed from FromSubquery).
 	 *
 	 * @param fromList	The outer from list
 	 *
@@ -1385,6 +1386,13 @@
 			return false;
 		}
 
+		/* Don't flatten if selectNode now has an order by */
+		if ((orderByList != null) &&
+			 (orderByList.size() > 0))
+		{
+			return false;
+		}
+
 		return true;
 	}
 
@@ -1573,14 +1581,22 @@
 			int orderBySelect = this.getResultColumns().getOrderBySelect();
 			if (orderBySelect > 0)
 			{
-				ResultColumnList selectRCs = prnRSN.getResultColumns().copyListAndObjects();
-				
-				selectRCs.removeOrderByColumns();
-				selectRCs.genVirtualColumnNodes(prnRSN, prnRSN.getResultColumns());				
+				// Keep the same RCL on top, since there may be references to
+				// its result columns above us, i.e. in this query:
+                //
+				// select sum(j),i from t group by i having i
+				//             in (select i from t order by j)
+				//
+				ResultColumnList topList = prnRSN.getResultColumns();
+				ResultColumnList newSelectList = topList.copyListAndObjects();
+				prnRSN.setResultColumns(newSelectList);
+
+				topList.removeOrderByColumns();
+				topList.genVirtualColumnNodes(prnRSN, newSelectList);
 				prnRSN = (ResultSetNode) getNodeFactory().getNode(
 								C_NodeTypes.PROJECT_RESTRICT_NODE,
 								prnRSN,
-								selectRCs,
+								topList,
 								null,
 								null,
 								null,

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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -166,6 +166,8 @@
 	private boolean foundVariant;
 	private boolean doneInvariantCheck;
 
+	private OrderByList orderByList;
+
 	/* Subquery types.
 	 * NOTE: FROM_SUBQUERY only exists for a brief second in the parser.  It
 	 * should never appear in a query tree.
@@ -201,15 +203,18 @@
 	 * @param resultSet		The ResultSetNode for the subquery
 	 * @param subqueryType	The type of the subquery
 	 * @param leftOperand	The left operand, if any, of the subquery
+	 * @param orderCols     ORDER BY list
 	 */
 
 	public void init(
 							Object resultSet,
 							Object subqueryType,
-							Object leftOperand)
+							Object leftOperand,
+					        Object orderCols)
 	{
 		this.resultSet = (ResultSetNode) resultSet;
 		this.subqueryType = ((Integer) subqueryType).intValue();
+		this.orderByList = (OrderByList)orderCols;
 
 		/* Subqueries are presumed not to be under a top level AndNode by
 		 * default.  This is because expression normalization only recurses
@@ -269,6 +274,12 @@
 				printLabel(depth, "leftOperand: ");
 				leftOperand.treePrint(depth + 1);
 			}
+
+			if (orderByList != null)
+			{
+				printLabel(depth, "orderByList: ");
+				orderByList.treePrint(depth + 1);
+			}
 		}
 	}
 
@@ -504,11 +515,19 @@
 									   aggregateVector);
 		}
 
+		if (orderByList != null) {
+			orderByList.pullUpOrderByColumns(resultSet);
+		}
+
 		/* bind the expressions in the underlying subquery */
 		resultSet.bindExpressions(fromList);
 
 		resultSet.bindResultColumns(fromList);
 
+		if (orderByList != null) {
+			orderByList.bindOrderByColumns(resultSet);
+		}
+
 		/* reject any untyped nulls in the subquery */
 		resultSet.bindUntypedNullsToResultColumns(null);
         
@@ -625,6 +644,7 @@
 		 */
 		flattenable = (resultSet instanceof RowResultSetNode) &&
 					  underTopAndNode && !havingSubquery &&
+			          orderByList == null &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
 					  parentComparisonOperator instanceof BinaryComparisonOperatorNode;
 
@@ -694,6 +714,7 @@
 
 		flattenable = (resultSet instanceof SelectNode) &&
  			          !((SelectNode)resultSet).hasWindows() &&
+			          orderByList == null &&
 					  underTopAndNode && !havingSubquery &&
 					  !isWhereExistsAnyInWithWhereSubquery() &&
 					  (isIN() || isANY() || isEXISTS() || flattenableNotExists ||
@@ -788,6 +809,20 @@
 			}
 		}
 
+		// Push the order by list down to the ResultSet
+		if (orderByList != null) {
+			// If we have more than 1 ORDERBY columns, we may be able to
+			// remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+			if (orderByList.size() > 1)
+			{
+				orderByList.removeDupColumns();
+			}
+
+			resultSet.pushOrderByList(orderByList);
+			orderByList = null;
+		}
+
+
 		/* We transform the leftOperand and the select list for quantified 
 		 * predicates that have a leftOperand into a new predicate and push it
 		 * down to the subquery after we preprocess the subquery's resultSet.
@@ -2463,4 +2498,14 @@
 			return false;
 		}
 	}
+
+	/**
+	 * Get ORDER BY list (used to construct FROM_SUBQUERY only), cf.
+	 * FromSubquery, for which this node is transient.
+	 *
+	 * @return order by list if specified, else null.
+	 */
+	public OrderByList getOrderByList() {
+		return orderByList;
+	}
 }

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=891952&r1=891951&r2=891952&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 Thu Dec 17 22:19:08 2009
@@ -1746,6 +1746,15 @@
 			autoIncrementInfo,
 			getContextManager());
 	}
+
+	private boolean
+		isTableValueConstructor(ResultSetNode expression)
+			throws StandardException {
+
+		return expression instanceof RowResultSetNode ||
+			(expression instanceof UnionNode &&
+			 ((UnionNode)expression).tableConstructor());
+	}
 }
 
 PARSER_END(SQLParser)
@@ -4903,6 +4912,7 @@
 nonJoinQueryPrimary() throws StandardException :
 {
 	ResultSetNode	primary;
+	OrderByList orderCols = null;
 }
 {
 	primary = simpleTable()
@@ -4910,8 +4920,14 @@
 		return primary;
 	}
 |
-	<LEFT_PAREN> primary = queryExpression(null, NO_SET_OP) <RIGHT_PAREN>
+	<LEFT_PAREN> primary = queryExpression(null, NO_SET_OP)
+                 [ orderCols = orderByClause() ]
+	<RIGHT_PAREN>
 	{
+		if (orderCols != null) {
+			primary.setOrderBy(orderCols);
+		}
+
 		return primary;
 	}
 }
@@ -8295,6 +8311,7 @@
 	Properties			targetProperties = null;
 	ResultSetNode	queryExpression;
 	ResultColumnList	columnList = null;
+	OrderByList orderCols = null;
 }
 {
 	[
@@ -8308,13 +8325,25 @@
 	]
 	[ targetProperties = propertyList(false) <CHECK_PROPERTIES>]
 	queryExpression = queryExpression(null, NO_SET_OP)
+	[ orderCols = orderByClause() ]
 	{
+		if (orderCols != null && isTableValueConstructor(queryExpression)) {
+			// Not allowed by the standard since this is a <contextually typed
+			// table value constructor> according SQL 2008, vol2, section 14.11
+			// "<insert statement>, SR 17. (I.e. it is not a <subquery> and
+			// can't have an ORDER BY).
+
+			throw StandardException.newException(
+				SQLState.LANG_SYNTAX_ERROR, "ORDER BY");
+		}
+
 		return (StatementNode) nodeFactory.getNode(
 							C_NodeTypes.INSERT_NODE,
 							targetTable,
 							columnList,
 							queryExpression,
 							targetProperties,
+							orderCols,
 							getContextManager());
 	}
 	/* RESOLVE: Ignoring default values for now
@@ -8552,15 +8581,18 @@
 {
 	ResultSetNode	queryExpression;
 	SubqueryNode	subqueryNode;
+	OrderByList orderCols = null;
 }
 {
 	queryExpression = queryExpression(null, NO_SET_OP)
+	[ orderCols = orderByClause() ]
 	{
 		subqueryNode = (SubqueryNode) nodeFactory.getNode(
 										C_NodeTypes.SUBQUERY_NODE,
 										queryExpression, 
 										ReuseFactory.getInteger(subqueryType),
 										leftOperand,
+										orderCols,
 										getContextManager());
 		return subqueryNode;
 	}
@@ -8964,7 +8996,7 @@
 	FromTable			tableReference;
 	Object[]			optionalTableClauses = new Object[OPTIONAL_TABLE_CLAUSES_SIZE];
 	Properties			tableProperties = null;
-	ResultSetNode		derivedTable;
+	SubqueryNode		derivedTable;
 }
 {
 	/* NOTE: this rule has to come first in order to avoid making NEW
@@ -9033,7 +9065,8 @@
 	{
 		fromTable = (FromTable) nodeFactory.getNode(
 											C_NodeTypes.FROM_SUBQUERY,
-											derivedTable, 
+											derivedTable.getResultSet(),
+											derivedTable.getOrderByList(),
 											correlationName,
 											derivedRCL,
 											((optionalTableClauses != null) ?
@@ -9141,7 +9174,7 @@
 /*
  * <A NAME="derivedTable">derivedTable</A>
  */
-ResultSetNode
+SubqueryNode
 derivedTable() throws StandardException :
 {
 	SubqueryNode tableSubquery;
@@ -9151,7 +9184,7 @@
 	tableSubquery = tableSubquery(SubqueryNode.FROM_SUBQUERY, null)
 	<RIGHT_PAREN>
 	{
-		return tableSubquery.getResultSet();
+		return tableSubquery;
 	}
 }
 
@@ -10618,11 +10651,13 @@
 	TableName			tableName;
 	Token				checkTok = null;
 	Token				endToken;
+	OrderByList         orderCols = null;
 }
 {
 	<VIEW> tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) 
 		[ <LEFT_PAREN> resultColumns = viewColumnList() <RIGHT_PAREN> ]
 		<AS> queryExpression = queryExpression(null, NO_SET_OP)
+		[ orderCols = orderByClause() ]
 	{
 		checkOptionType = ViewDescriptor.NO_CHECK_OPTION;
 		endToken = getToken(0);
@@ -10643,6 +10678,7 @@
 								StringUtil.slice(statementSQLText,
 												beginToken.beginOffset,
 												endToken.endOffset,false),
+								orderCols,
 								getContextManager());
 	}
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out Thu Dec 17 22:19:08 2009
@@ -742,22 +742,31 @@
 ----------------------------------------
 ij> -- rollback should release the prepared statements
 rollback ;
-ij> -- . order by only allowed on cursor spec, not subquerys (error) 
-select v from obt where i in (select i from obt2 order by i);
-ERROR 42X01: Syntax error: Encountered "order" at line 2, column 50.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select v from obt where i = (select i from obt2 order by i);
-ERROR 42X01: Syntax error: Encountered "order" at line 1, column 49.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select v from (select i,v from obt2 order by i);
-ERROR 42X01: Syntax error: Encountered "order" at line 1, column 37.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
+ij> select v from obt where i in (select i from obt2 order by i);
+V                                       
+----------------------------------------
+hello                                   
+planet                                  
+world                                   
+hello                                   
+ij> select v from obt where i in (select i2 from obt2 order by i2);
+V                                       
+----------------------------------------
+hello                                   
+world                                   
+hello                                   
+ij> -- more than one row in subquery: error
+select v from obt where i = (select i from obt2 order by i);
+V                                       
+----------------------------------------
+ERROR 21000: Scalar subquery is only allowed to return a single row.
+ij> select v from (select i2,v from obt2 order by i2)s;
+V                                       
+----------------------------------------
+shoe                                    
+planet                                  
+hello                                   
+planet                                  
 ij> -- rollback should release the prepared statements
 rollback ;
 ij> -- order by allowed on datatypes, 

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java?rev=891952&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java Thu Dec 17 22:19:08 2009
@@ -0,0 +1,502 @@
+/*
+ * Class org.apache.derbyTesting.functionTests.tests.lang.OrderByInSubqueries
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.ResultSet;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SQLUtilities;
+
+/**
+ * Tests for DERBY-4397: allow ORDER BY in subqueries.
+ */
+public class OrderByInSubqueries extends BaseJDBCTestCase {
+
+    public OrderByInSubqueries(String name) {
+        super(name);
+    }
+
+    /**
+     * Construct top level suite in this JUnit test
+     *
+     * @return A suite containing embedded and client suites.
+     */
+    public static Test suite()
+    {
+        TestSuite suite = new TestSuite("OrderByInSubqueries");
+
+        suite.addTest(makeSuite());
+        suite.addTest(
+             TestConfiguration.clientServerDecorator(makeSuite()));
+
+        return suite;
+    }
+
+    /**
+     * Construct suite of tests
+     *
+     * @return A suite containing the test cases.
+     */
+    private static Test makeSuite()
+    {
+        return new CleanDatabaseTestSetup(
+            new TestSuite(OrderByInSubqueries.class)) {
+                protected void decorateSQL(Statement s)
+                        throws SQLException {
+                    getConnection().setAutoCommit(false);
+
+                    s.execute("create table temp1(s varchar(10))");
+
+                    // GENERATED ALWAYS AS IDENTITY
+                    s.execute("create table temp2(" +
+                              "i integer not null " +
+                              "    generated always as identity," +
+                              "s varchar(10))");
+                    // DEFAULT value
+                    s.execute("create table temp3(" +
+                              "i integer not null " +
+                              "    generated always as identity," +
+                              "s varchar(10)," +
+                              "j integer not null " +
+                              "    default 66," +
+                              "t varchar(10))");
+
+                    // GENERATED ALWAYS AS (expression)
+                    s.execute("create table temp4(" +
+                              "i integer not null " +
+                              "    generated always as identity," +
+                              "s varchar(10)," +
+                              "j integer not null " +
+                              "    generated always as (2*i)," +
+                              "t varchar(10))");
+
+
+                    s.execute("create table t01(c1 int)");
+                    s.execute("create table t02(c2 int)");
+
+                    s.execute("create table t_source(c1 int, c2 varchar(10))");
+                    s.execute("create table t(i int not null, " +
+                              "               constraint c unique (i), " +
+                              "               j int, k int)");
+
+                    getConnection().commit();
+                }
+            };
+    }
+
+    /**
+     * Test INSERT INTO t SELECT .. FROM .. ORDER BY ..
+     */
+    public void testInsertSelectOrderBy() throws SQLException {
+        //
+        // Shows that DERBY-4 is now solved.
+        //
+        getConnection().setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs = null;
+
+        s.execute("insert into temp1 values 'x','a','c','b','a'");
+        s.execute("insert into temp2(s) select s from temp1 order by s");
+        s.execute("insert into temp2(s) select s as a1 from temp1 order by a1");
+        s.execute("insert into temp2(s) select * from temp1 order by s");
+
+        rs = s.executeQuery("select * from temp2");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "a"},
+                {"2", "a"},
+                {"3", "b"},
+                {"4", "c"},
+                {"5", "x"},
+                {"6", "a"},
+                {"7", "a"},
+                {"8", "b"},
+                {"9", "c"},
+                {"10", "x"},
+                {"11", "a"},
+                {"12", "a"},
+                {"13", "b"},
+                {"14", "c"},
+                {"15", "x"}});
+
+        rs = s.executeQuery("select * from temp2 order by i");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "a"},
+                {"2", "a"},
+                {"3", "b"},
+                {"4", "c"},
+                {"5", "x"},
+                {"6", "a"},
+                {"7", "a"},
+                {"8", "b"},
+                {"9", "c"},
+                {"10", "x"},
+                {"11", "a"},
+                {"12", "a"},
+                {"13", "b"},
+                {"14", "c"},
+                {"15", "x"}});
+
+        s.execute("insert into temp2(s) select s as a1 from temp1 order by s");
+
+        // This should be rejected as "no such column" errors:
+        assertStatementError(
+            "42X04", s,
+            "insert into temp2(s) select s as a1 from temp1 order by no_such");
+
+        // A similar example, but with integers rather than strings, and some
+        // intermediate select statements to show that the ordering is working.
+        //
+        s.execute("insert into t01 values (50), (10), (1000), (15), (51)");
+
+        rs = s.executeQuery("select * from t01");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"50"},
+                {"10"},
+                {"1000"},
+                {"15"},
+                {"51"}});
+
+
+        s.execute("insert into t02 select * from t01 order by c1");
+        s.execute("insert into t02 select * from t01");
+        s.execute("insert into t02 select * from t01 order by c1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"10"},
+                {"15"},
+                {"50"},
+                {"51"},
+                {"1000"},
+                {"50"},
+                {"10"},
+                {"1000"},
+                {"15"},
+                {"51"},
+                {"10"},
+                {"15"},
+                {"50"},
+                {"51"},
+                {"1000"}});
+
+        // Combining ORDER BY and VALUES is not legal SQL, cf.  SQL 2008,
+        // section 14.11, Syntactic Rule 17: "A <query expression> simply
+        // contained in a <from subquery> shall not be a <table value
+        // constructor>. See also discussion in JIRA on DERBY-4413
+        // (2009-OCT-23).
+        //
+        assertStatementError(
+             "42X01", s,
+             "insert into t02 values 66 order by 1");
+        assertStatementError(
+             "42X01", s,
+             "insert into t02 values (901), (920), (903) order by 1");
+
+        // But this should work:
+        s.executeUpdate("delete from t02");
+        s.executeUpdate("insert into t02 select 900 from sys.systables " +
+                        "                union values 66 order by 1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                 {"66"},
+                 {"900"}});
+
+        // other way around:
+        s.executeUpdate("delete from t02");
+        s.executeUpdate(
+            "insert into t02 values 66 " +
+            "       union select 900 from sys.systables order by 1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                 {"66"},
+                 {"900"}});
+
+        // and, somewhat perversely (since a plain "values 66 order by 1" is
+        // illegal), this:
+        s.executeUpdate("delete from t02");
+        s.executeUpdate("insert into t02 values 66 " +
+                        "       union values 66 order by 1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                 {"66"}});
+
+
+        // UNION
+        //
+        // ok:
+        s.execute("delete from t02");
+        s.execute("insert into t02 select * from t01 union all " +
+                  "                select * from t01 order by c1");
+        rs = s.executeQuery("select * from t02");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"10"},
+                {"10"},
+                {"15"},
+                {"15"},
+                {"50"},
+                {"50"},
+                {"51"},
+                {"51"},
+                {"1000"},
+                {"1000"}});
+
+        // Not ok (c1 is not a column in the union result set, since t02 has
+        // column c02.
+        assertStatementError(
+            "42X78", s,
+            "insert into t02 select * from t01 union all " +
+            "                select * from t02 order by c1");
+
+
+        // Complication: project away sort column
+        s.execute("delete from t02");
+        s.execute("insert into t_source " +
+                  "    values (1, 'one'), (2, 'two'), (8, 'three')");
+        s.execute("insert into t_source(c1) " +
+                  "    select c1 from t_source order by c2 desc");
+        rs = s.executeQuery("select * from t_source");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1", "one"},
+                {"2", "two"},
+                {"8", "three"},
+                {"2", null},
+                {"8", null},
+                {"1", null}});
+
+        rollback();
+    }
+
+
+    /**
+     * SELECT subqueries with ORDER BY
+     */
+    public void testSelectSubqueriesOrderBy() throws SQLException {
+        getConnection().setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.execute(
+            "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')");
+
+        /*
+         * Simple SELECT FromSubquery
+         */
+        rs = s.executeQuery(
+            "select * from (select c1 from t_source order by c1 desc) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8"}, {"2"}, {"1"}});
+
+        rs = s.executeQuery(
+            "select * from (select c1+1 from t_source order by c1+1 desc) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"9"}, {"3"}, {"2"}});
+
+        rs = s.executeQuery(
+            "select * from (select c1,c2 from t_source order by c1 desc,2) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8", "three"}, {"2", "two"}, {"1", "one"}});
+
+        // Complication: project away sort column
+        rs = s.executeQuery(
+            "select * from (select c2 from t_source order by c1 desc) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"three"}, {"two"}, {"one"}});
+
+        rs = s.executeQuery(
+            "select * from " +
+            "    (select c2 from t_source order by c1 desc) s order by 1");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"one"}, {"three"}, {"two"}});
+
+        /*
+         * Simple VALUES FromSubquery
+         */
+        rs = s.executeQuery(
+            "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" +
+            "               order by 1 desc) s");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8", "three"}, {"2", "two"}, {"1", "one"}});
+
+
+        /*
+         * ORDER BY in EXISTS subquery
+         */
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c1)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c1 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c2 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"}, {"2"}, {"8"}});
+
+        rs = s.executeQuery(
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c2 desc) order by 1 desc");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8"}, {"2"}, {"1"}});
+
+        /*
+         * NOT EXISTS
+         */
+        rs = s.executeQuery(
+            "select c1 from t_source where not exists " +
+            "    (select c1 from t_source order by c2 desc) order by 1 desc");
+        JDBC.assertEmpty(rs);
+
+        rs = s.executeQuery(
+            "select c1 from t_source ot where not exists " +
+            "   (select c1 from t_source where ot.c1=(c1/2) order by c2 desc)" +
+            "    order by 1 desc");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"8"}, {"2"}});
+
+        /*
+         * IN subquery
+         */
+        s.executeUpdate("insert into t values (1,10,1), (2,40,1)," +
+                        "         (3,45,1), (4,46,1), (5,90,1)");
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by 1 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by i/5 desc)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+        rs = s.executeQuery(
+            "select * from t where i in (select i/5 from t order by j)");
+        JDBC.assertFullResultSet(rs, new String[][]{{"1", "10", "1"}});
+
+
+        /*
+         * Scalar subquery inside ALL subquery with correlation
+         */
+        String[][] expected = new String[][]{
+            {"1", "10", "1"},
+            {"2", "40", "1"}};
+
+        // First without any ORDER BYs
+        rs = s.executeQuery(
+            "select * from t t_o where i <= all (" +
+            "    select i+1 from t where i = t_o.k + (" +
+            "        select count(*) from t) - 5)");
+        JDBC.assertFullResultSet(rs, expected);
+
+        // Then with ORDER BY at both subquery levels; should be the same result
+        rs = s.executeQuery(
+            "select * from t t_o where i <= all (" +
+            "    select i+1 from t where i = t_o.k + (" +
+            "        select count(*) from t order by 1) - 5 " +
+            "    order by 1 desc)");
+        JDBC.assertFullResultSet(rs, expected);
+
+        rollback();
+    }
+
+
+    /**
+     * Test ORDER BY in a view definition
+     */
+    public void testView() throws SQLException {
+
+        getConnection().setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.executeUpdate(
+            "create view v1 as select i from t order by j desc");
+        s.executeUpdate(
+            "create view v2 as select i from t order by i");
+        s.executeUpdate(
+            "insert into t values (1,10,1), (2,40,1)," +
+            "         (3,45,1), (4,46,1), (5,90,1)");
+        rs = s.executeQuery(
+            "select i from v1");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"5"},{"4"},{"3"},{"2"},{"1"}});
+
+        rs = s.executeQuery(
+            "select i from v2");
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"1"},{"2"},{"3"},{"4"},{"5"}});
+
+        rollback();
+    }
+
+    /**
+     * SELECT subqueries with ORDER BY - negative tests
+     */
+    public void testSelectSubqueriesOrderByNegative() throws SQLException {
+        getConnection().setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.execute(
+            "insert into t_source values (1, 'one'), (2, 'two'), (8, 'three')");
+
+        /*
+         * Simple SELECT FromSubquery
+         */
+        assertStatementError(
+            "42X04", s,
+            "select * from (select c1 from t_source order by c3 desc) s");
+
+        assertStatementError(
+            "42X77", s,
+            "select * from (select c1 from t_source order by 3 desc) s");
+
+
+        /*
+         * Simple VALUES FromSubquery
+         */
+        assertStatementError(
+            "42X77", s,
+            "select * from (values (1, 'one'), (2, 'two'), (8, 'three')" +
+            "               order by 3 desc) s");
+
+        /*
+         * ORDER BY in EXISTS subquery:
+         */
+        assertStatementError(
+            "42X04", s,
+            "select c1 from t_source where exists " +
+            "    (select c1 from t_source order by c4)");
+
+        rollback();
+    }
+}

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

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=891952&r1=891951&r2=891952&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql Thu Dec 17 22:19:08 2009
@@ -300,12 +300,13 @@
 -- rollback should release the prepared statements
 rollback ;
 
--- . order by only allowed on cursor spec, not subquerys (error) 
 select v from obt where i in (select i from obt2 order by i);
+select v from obt where i in (select i2 from obt2 order by i2);
 
+-- more than one row in subquery: error
 select v from obt where i = (select i from obt2 order by i);
 
-select v from (select i,v from obt2 order by i);
+select v from (select i2,v from obt2 order by i2)s;
 
 -- rollback should release the prepared statements
 rollback ;



Mime
View raw message