db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1422164 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Sat, 15 Dec 2012 00:34:49 GMT
Author: dag
Date: Sat Dec 15 00:34:47 2012
New Revision: 1422164

URL: http://svn.apache.org/viewvc?rev=1422164&view=rev
Log:
DERBY-6008 Allow ORDER BY and FETCH/OFFSET in set operands

Patch derby-6008-e; adds this feature as well as accompanying new tests.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.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/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/UnionNode.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/tests/lang/OrderByAndOffsetFetchInSubqueries.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=1422164&r1=1422163&r2=1422164&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java Sat Dec 15 00:34:47 2012
@@ -204,10 +204,20 @@ public class CursorNode extends DMLState
 		{
 			super.printSubNodes(depth);
 
-			if (orderByList != null) {
-				printLabel(depth, "orderByList: "  + depth);
-				orderByList.treePrint(depth + 1);
-			}
+            if (orderByList != null) {
+                printLabel(depth, "orderByList: "  + depth);
+                orderByList.treePrint(depth + 1);
+            }
+
+            if (offset != null) {
+                printLabel(depth, "offset:");
+                offset.treePrint(depth + 1);
+            }
+
+            if (fetchFirst != null) {
+                printLabel(depth, "fetch first/next:");
+                fetchFirst.treePrint(depth + 1);
+            }
 		}
 	}
 

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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -215,6 +215,16 @@ public final class InsertNode extends DM
 				orderByList.treePrint(depth + 1);
 			}
 
+            if (offset != null) {
+                printLabel(depth, "offset:");
+                offset.treePrint(depth + 1);
+            }
+
+            if (fetchFirst != null) {
+                printLabel(depth, "fetch first/next:");
+                fetchFirst.treePrint(depth + 1);
+            }
+
 			/* RESOLVE - need to print out targetTableDescriptor */
 		}
 	}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java?rev=1422164&r1=1422163&r2=1422164&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java Sat Dec 15 00:34:47 2012
@@ -151,16 +151,17 @@ public class IntersectOrExceptNode exten
          * cases it would be better to sort the inputs on a different sequence of columns, but it is hard to analyze
          * the input query expressions to see if a sort can be avoided.
          */
-        if( orderByList != null)
+        if( orderByLists[0] != null)
         {
             BitSet colsOrdered = new BitSet( intermediateOrderByColumns.length);
-            int orderByListSize = orderByList.size();
+            int orderByListSize = orderByLists[0].size();
             int intermediateOrderByIdx = 0;
             for( int i = 0; i < orderByListSize; i++)
             {
                 if( colsOrdered.get(i))
                     continue;
-                OrderByColumn orderByColumn = orderByList.getOrderByColumn(i);
+                OrderByColumn orderByColumn =
+                    orderByLists[0].getOrderByColumn(i);
                 intermediateOrderByDirection[intermediateOrderByIdx] = orderByColumn.isAscending() ? 1 : -1;
                 intermediateOrderByNullsLow[intermediateOrderByIdx] = orderByColumn.isNullsOrderedLow();
                 int columnIdx = orderByColumn.getResultColumn().getColumnPosition() - 1;
@@ -178,7 +179,7 @@ public class IntersectOrExceptNode exten
                     intermediateOrderByIdx++;
                 }
             }
-            orderByList = null; // It will be pushed down.
+            orderByLists[0] = null; // It will be pushed down.
         }
         else // The output of the intersect/except does not have to be ordered
         {
@@ -314,12 +315,12 @@ public class IntersectOrExceptNode exten
 
         ResultSetNode treeTop = this;
 
-        if( orderByList != null) {
+        if( orderByLists[0] != null) {
             // Generate an order by node on top of the intersect/except
             treeTop = (ResultSetNode) getNodeFactory().getNode(
                 C_NodeTypes.ORDER_BY_NODE,
                 treeTop,
-                orderByList,
+                orderByLists[0],
                 tableProperties,
                 getContextManager());
         }
@@ -338,7 +339,6 @@ public class IntersectOrExceptNode exten
                 Boolean.valueOf( hasJDBClimitClause ),
                 getContextManager());
         }
-
         return treeTop;
 
     } // end of addNewNodes

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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -586,4 +586,7 @@ public class OrderByColumn extends Order
 		}
 	}
 
+    ValueNode getExpression() {
+        return expression;
+    }
 }

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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -2488,9 +2488,9 @@ public class ResultColumnList extends Qu
 							")");
 			}
 
-            // Generated grouping columns should have been removed for the RCL
-            // of a SetOperatorNode, so that size and visible size are equal
-            // (DERBY-3764).
+            // Generated grouping columns and unselected ORDER BY columns
+            // should have been removed for the RCL of a SetOperatorNode, so
+            // that size and visible size are equal (DERBY-3764).
             SanityManager.ASSERT(size() == visibleSize(),
                                  "size() and visibleSize() should be equal");
 		}
@@ -2645,9 +2645,9 @@ public class ResultColumnList extends Qu
             // The visible size of the two RCLs must be equal.
             SanityManager.ASSERT(visibleSize() == otherRCL.visibleSize(),
                                  "visibleSize() should match");
-            // The generated grouping columns should have been removed from the
-            // RCL of the SetOperatorNode, so size and visible size should be
-            // equal (DERBY-3764).
+            // The generated grouping columns and unselected ORDER BY columns
+            // should have been removed from the RCL of the SetOperatorNode,
+            // so size and visible size should be equal (DERBY-3764).
             SanityManager.ASSERT(size() == visibleSize(),
                                  "size() and visibleSize() should match");
         }

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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -123,6 +123,22 @@ public class RowResultSetNode extends Fr
 				printLabel(depth, "subquerys: ");
 				subquerys.treePrint(depth + 1);
 			}
+
+            if (orderByList != null) {
+                printLabel(depth, "orderByList:");
+                orderByList.treePrint(depth + 1);
+            }
+
+            if (offset != null) {
+                printLabel(depth, "offset:");
+                offset.treePrint(depth + 1);
+            }
+
+            if (fetchFirst != null) {
+                printLabel(depth, "fetch first/next:");
+                fetchFirst.treePrint(depth + 1);
+            }
+
 		}
 	}
 
@@ -253,7 +269,14 @@ public class RowResultSetNode extends Fr
 		}
 
 		SelectNode.checkNoWindowFunctions(resultColumns, "VALUES");
-	}
+
+        if (orderByList != null) {
+            orderByList.pullUpOrderByColumns(this);
+            orderByList.bindOrderByColumns(this);
+        }
+
+        bindOffsetFetch(offset, fetchFirst);
+    }
 
 	/**
 	 * Bind the expressions in this ResultSetNode if it has tables.  This means binding the
@@ -438,7 +461,17 @@ public class RowResultSetNode extends Fr
 		/* Allocate a dummy referenced table map */ 
 		referencedTableMap = new JBitSet(numTables);
 		referencedTableMap.set(tableNumber);
-		return this;
+
+        // If we have more than 1 ORDERBY columns, we may be able to
+        // remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+        // Well, not very likely here, since we're here:
+        //     VALUES x followed by ORDER BY 1,1,2
+        // but for completeness...
+        if (orderByList != null && orderByList.size() > 1) {
+            orderByList.removeDupColumns();
+        }
+
+        return this;
 	}
 	
 	/**

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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -22,6 +22,7 @@
 
 package	org.apache.derby.impl.sql.compile;
 
+import java.util.Arrays;
 import org.apache.derby.iapi.sql.compile.CostEstimate;
 import org.apache.derby.iapi.sql.compile.Optimizer;
 import org.apache.derby.iapi.sql.compile.Visitor;
@@ -101,8 +102,14 @@ public class SelectNode extends ResultSe
 	 */
 	private boolean wasGroupBy;
 	
-	/* List of columns in ORDER BY list */
-	OrderByList orderByList;
+    /**
+     * List of columns in ORDER BY list. Usually size 1, if size 2, we
+     * are a VALUES top node UNION node and element 2 has been passed
+     * from InterceptOrExceptNode to prepare for merge implementation
+     * of intersect or except.
+     */
+    OrderByList[] orderByLists = new OrderByList[1];
+
 	boolean		orderByQuery ;
 
     ValueNode   offset;  // OFFSET n ROWS, if given
@@ -330,11 +337,23 @@ public class SelectNode extends ResultSe
 				havingClause.treePrint(depth + 1);
 			}
 
-			if (orderByList != null) {
-				printLabel(depth, "orderByList:");
-				orderByList.treePrint(depth + 1);
+            if (orderByLists[0] != null) {
+                for (int i = 0; i < orderByLists.length; i++) {
+                    printLabel(depth, "orderByLists[" + i + "]:");
+                    orderByLists[i].treePrint(depth + 1);
+                }
 			}
 
+            if (offset != null) {
+                printLabel(depth, "offset:");
+                offset.treePrint(depth + 1);
+            }
+
+            if (fetchFirst != null) {
+                printLabel(depth, "fetch first/next:");
+                fetchFirst.treePrint(depth + 1);
+            }
+
 			if (preJoinFL != null)
 			{
 				printLabel(depth, "preJoinFL: ");
@@ -519,6 +538,10 @@ public class SelectNode extends ResultSe
 		SanityManager.ASSERT(fromList != null && resultColumns != null,
 			"Both fromList and resultColumns are expected to be non-null");
 
+        if (orderByLists[0] != null) {
+            orderByLists[0].pullUpOrderByColumns(this);
+        }
+
 		/* NOTE - a lot of this code would be common to bindTargetExpression(),
 		 * so we use a private boolean to share the code instead of duplicating
 		 * it.  bindTargetExpression() is responsible for toggling the boolean.
@@ -690,7 +713,13 @@ public class SelectNode extends ResultSe
 		{
 			throw StandardException.newException(SQLState.LANG_USER_AGGREGATE_MULTIPLE_DISTINCTS);
 		}
-	}
+
+        if (orderByLists[0] != null) {
+            orderByLists[0].bindOrderByColumns(this);
+        }
+
+        bindOffsetFetch(offset, fetchFirst);
+    }
 
 	/**
 	 * Bind the expressions in this ResultSetNode if it has tables.  This means binding the
@@ -933,7 +962,34 @@ public class SelectNode extends ResultSe
 	 */
 	void pushOrderByList(OrderByList orderByList)
 	{
-		this.orderByList = orderByList;
+        if (orderByLists[0] != null) {
+            // A push down of an internal ordering from IntersectOrExceptNode
+            // on a SELECT that already has an ORDER BY. The following sanity
+            // check helps verify that this is indeed the case.
+            if (SanityManager.DEBUG) {
+                SanityManager.ASSERT(
+                    orderByList.size() == resultColumns.visibleSize());
+                OrderByColumn obc = (OrderByColumn)orderByList.elementAt(0);
+                SanityManager.ASSERT(
+                    obc.getExpression() instanceof NumericConstantNode);
+                try {
+                    SanityManager.ASSERT(
+                            ((NumericConstantNode)obc.getExpression()).
+                            value.getInt() == 1);
+                } catch (Exception e) {
+                    SanityManager.THROWASSERT(e);
+                }
+            }
+
+            // Possible optimization: check to see if this extra ordering can
+            // be eliminated, i.e. the two orderings are the same.
+            OrderByList[] newOrderByLists = {
+                orderByLists[0],
+                orderByList};
+            orderByLists = newOrderByLists;
+        } else {
+            orderByLists[0] = orderByList;
+        }
 		// remember that there was an order by list
 		orderByQuery = true;
 	}
@@ -1107,23 +1163,25 @@ public class SelectNode extends ResultSe
 				performTransitiveClosure(numTables);
 			}
 
-			if (orderByList != null)
-			{
-				// Remove constant columns from order by list.  Constant
-				// 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,
-				** get rid of the list entirely.
-				*/
-				if (orderByList.size() == 0)
-				{
-					orderByList = null;
-                    resultColumns.removeOrderByColumns();
-				}
-			}
+            for (int i = 0; i < orderByLists.length; i++) {
+                if (orderByLists[i] != null)
+                {
+                    // Remove constant columns from order by list.  Constant
+                    // columns are ones that have equality comparisons with
+                    // constant expressions (e.g. x = 3)
+                    orderByLists[i].removeConstantColumns(wherePredicates);
+                    /*
+                     ** It's possible for the order by list to shrink to
+                     ** nothing as a result of removing constant columns.  If
+                     ** this happens, get rid of the list entirely.
+                     */
+                    if (orderByLists[i].size() == 0)
+                    {
+                        orderByLists[i] = null;
+                        resultColumns.removeOrderByColumns();
+                    }
+                }
+            }
 		}
 
 		/* A valid group by without any aggregates or a having clause
@@ -1175,45 +1233,50 @@ public class SelectNode extends ResultSe
 				}
 			}
 
-			/* If we were unable to eliminate the distinct and we have
-			 * an order by then we can consider eliminating the sort for the
-			 * order by.  All of the columns in the order by list must
-			 * be ascending in order to do this.  There are 2 cases:
-			 *	o	The order by list is an in order prefix of the columns
-			 *		in the select list.  In this case the output of the
-			 *		sort from the distinct will be in the right order
-			 *		so we simply eliminate the order by list.
-			 *	o	The order by list is a subset of the columns in the
-			 *		the select list.  In this case we need to reorder the
-			 *		columns in the select list so that the ordering columns
-			 *		are an in order prefix of the select list and put a PRN
-			 *		above the select so that the shape of the result set
-			 *		is as expected.
-			 */
-			if (isDistinct && orderByList != null && orderByList.allAscending())
-			{
-				/* Order by list currently restricted to columns in select
-				 * list, so we will always eliminate the order by here.
-				 */
-				if (orderByList.isInOrderPrefix(resultColumns))
-				{
-					orderByList = null;
-				}
-				else 
-				{
-					/* Order by list is not an in order prefix of the select list
-					 * so we must reorder the columns in the the select list to
-					 * match the order by list and generate the PRN above us to
-					 * preserve the expected order.
-					 */
-					newTop = genProjectRestrictForReordering();
- 					orderByList.resetToSourceRCs();
-					resultColumns = orderByList.reorderRCL(resultColumns);
-					newTop.getResultColumns().removeOrderByColumns();
-					orderByList = null;
-				}
-				orderByAndDistinctMerged = true;
-			}
+            for (int i = 0; i < orderByLists.length; i++) {
+                /* If we were unable to eliminate the distinct and we have
+                 * an order by then we can consider eliminating the sort for
+                 * the order by.  All of the columns in the order by list must
+                 * be ascending in order to do this.  There are 2 cases:
+                 *  o   The order by list is an in order prefix of the columns
+                 *      in the select list.  In this case the output of the
+                 *      sort from the distinct will be in the right order
+                 *      so we simply eliminate the order by list.
+                 *  o   The order by list is a subset of the columns in the
+                 *      the select list.  In this case we need to reorder the
+                 *      columns in the select list so that the ordering columns
+                 *      are an in order prefix of the select list and put a PRN
+                 *      above the select so that the shape of the result set
+                 *      is as expected.
+                 */
+                if (isDistinct && orderByLists[i] != null &&
+                    orderByLists[i].allAscending())
+                {
+                    /* Order by list currently restricted to columns in select
+                     * list, so we will always eliminate the order by here.
+                     */
+                    if (orderByLists[i].isInOrderPrefix(resultColumns))
+                    {
+                        orderByLists[i] = null;
+                    }
+                    else
+                    {
+                        /* Order by list is not an in order prefix of the
+                         * select list so we must reorder the columns in the
+                         * the select list to match the order by list and
+                         * generate the PRN above us to preserve the expected
+                         * order.
+                         */
+                        newTop = genProjectRestrictForReordering();
+                        orderByLists[i].resetToSourceRCs();
+                        resultColumns =
+                            orderByLists[i].reorderRCL(resultColumns);
+                        newTop.getResultColumns().removeOrderByColumns();
+                        orderByLists[i] = null;
+                    }
+                    orderByAndDistinctMerged = true;
+                }
+            }
 		}
 
 		/*
@@ -1244,14 +1307,14 @@ public class SelectNode extends ResultSe
 		}
 
 
-		if (orderByList != null) {
+        if (orderByLists[0] != null) { // only relevant for first one
 
 			// Collect window function calls and in-lined window definitions
 			// contained in them from the orderByList.
 
 			CollectNodesVisitor cnvw =
 				new CollectNodesVisitor(WindowFunctionNode.class);
-			orderByList.accept(cnvw);
+            orderByLists[0].accept(cnvw);
 			Vector wfcInOrderBy = cnvw.getList();
 
 			for (int i=0; i < wfcInOrderBy.size(); i++) {
@@ -1426,8 +1489,8 @@ public class SelectNode extends ResultSe
 		}
 
 		/* Don't flatten if selectNode now has an order by */
-		if ((orderByList != null) &&
-			 (orderByList.size() > 0))
+        if ((orderByLists[0] != null) &&
+             (orderByLists[0].size() > 0))
 		{
 			return false;
 		}
@@ -1456,9 +1519,8 @@ public class SelectNode extends ResultSe
 	public ResultSetNode genProjectRestrict(int origFromListSize)
 				throws StandardException
 	{
-		boolean				eliminateSort = false;
-		PredicateList		restrictionList;
-		ResultColumnList	prRCList;
+        boolean[] eliminateSort = new boolean[orderByLists.length];
+
 		ResultSetNode		prnRSN;
 
 		prnRSN = (ResultSetNode) getNodeFactory().getNode(
@@ -1505,7 +1567,9 @@ public class SelectNode extends ResultSe
 			prnRSN  = gbn.getParent();
 
 			// Remember whether or not we can eliminate the sort.
-			eliminateSort = eliminateSort || gbn.getIsInSortedOrder();
+            for (int i=0; i < eliminateSort.length; i++ ) {
+                eliminateSort[i] = eliminateSort[i] || gbn.getIsInSortedOrder();
+            }
 		}
 
 
@@ -1598,8 +1662,10 @@ public class SelectNode extends ResultSe
 											getContextManager());
 				prnRSN.costEstimate = costEstimate.cloneMe();
 
-				// Remember whether or not we can eliminate the sort.
-				eliminateSort = eliminateSort || inSortedOrder;
+                // Remember whether or not we can eliminate the sort.
+                for (int i=0; i < eliminateSort.length; i++) {
+                    eliminateSort[i] = eliminateSort[i] || inSortedOrder;
+                }
 			}
 		}
 
@@ -1607,66 +1673,71 @@ public class SelectNode extends ResultSe
 		 * the order by.
 		 */
 
-		if (orderByList != null)
-		{
-			if (orderByList.getSortNeeded())
-			{
-				prnRSN = (ResultSetNode) getNodeFactory().getNode(
-												C_NodeTypes.ORDER_BY_NODE,
-												prnRSN,
-												orderByList,
-												null,
-												getContextManager());
-				prnRSN.costEstimate = costEstimate.cloneMe();
-			}
-
-			// There may be columns added to the select projection list
-			// a query like:
-			// select a, b from t group by a,b order by a+b
-			// the expr a+b is added to the select list.  
-			int orderBySelect = this.getResultColumns().getOrderBySelect();
-			if (orderBySelect > 0)
-			{
-				// 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);
+        for (int i=0; i < orderByLists.length; i++) {
+            if (orderByLists[i] != null)
+            {
+                if (orderByLists[i].getSortNeeded())
+                {
+                    prnRSN = (ResultSetNode) getNodeFactory().getNode(
+                            C_NodeTypes.ORDER_BY_NODE,
+                            prnRSN,
+                            orderByLists[i],
+                            null,
+                            getContextManager());
+                    prnRSN.costEstimate = costEstimate.cloneMe();
+                }
 
-				topList.removeOrderByColumns();
-				topList.genVirtualColumnNodes(prnRSN, newSelectList);
-				prnRSN = (ResultSetNode) getNodeFactory().getNode(
-								C_NodeTypes.PROJECT_RESTRICT_NODE,
-								prnRSN,
-								topList,
-								null,
-								null,
-								null,
-								null,
-								null,
-								getContextManager());
-			}
-		}
+                // There may be columns added to the select projection list
+                // a query like:
+                // select a, b from t group by a,b order by a+b
+                // the expr a+b is added to the select list.
+                int orderBySelect = this.getResultColumns().getOrderBySelect();
+                if (orderBySelect > 0)
+                {
+                    // 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,
+                            topList,
+                            null,
+                            null,
+                            null,
+                            null,
+                            null,
+                            getContextManager());
+                }
+            }
 
-        if (offset != null || fetchFirst != null) {
-            // Keep the same RCL on top, since there may be references to
-            // its result columns above us.
-            ResultColumnList topList = prnRSN.getResultColumns();
-            ResultColumnList newSelectList = topList.copyListAndObjects();
-            prnRSN.setResultColumns(newSelectList);
-            topList.genVirtualColumnNodes(prnRSN, newSelectList);
-            prnRSN = (ResultSetNode)getNodeFactory().getNode(
-                C_NodeTypes.ROW_COUNT_NODE,
-                prnRSN,
-                topList,
-                offset,
-                fetchFirst,
-                Boolean.valueOf( hasJDBClimitClause ),
-                getContextManager());
+            // Do this only after the main ORDER BY; any extra added by
+            // IntersectOrExceptNode should sit on top of us.
+            if (i == 0 && (offset != null || fetchFirst != null)) {
+                // Keep the same RCL on top, since there may be references to
+                // its result columns above us.
+                ResultColumnList topList = prnRSN.getResultColumns();
+                ResultColumnList newSelectList = topList.copyListAndObjects();
+                prnRSN.setResultColumns(newSelectList);
+                topList.genVirtualColumnNodes(prnRSN, newSelectList);
+                prnRSN = (ResultSetNode)getNodeFactory().getNode(
+                        C_NodeTypes.ROW_COUNT_NODE,
+                        prnRSN,
+                        topList,
+                        offset,
+                        fetchFirst,
+                        Boolean.valueOf( hasJDBClimitClause ),
+                        getContextManager());
+            }
         }
 
 
@@ -1709,24 +1780,27 @@ public class SelectNode extends ResultSe
 						getContextManager());
 		}
 
-		if (!(orderByList != null && orderByList.getSortNeeded()) && orderByQuery)
-		{
-			// Remember whether or not we can eliminate the sort.
-			eliminateSort = true;
-		}
+        for (int i=0; i < orderByLists.length; i++) {
+            if (!(orderByLists[i] != null && orderByLists[i].getSortNeeded()) &&
+                orderByQuery)
+            {
+                // Remember whether or not we can eliminate the sort.
+                eliminateSort[i] = true;
+            }
 
-		/* If we were able to eliminate the sort during optimization then
-		 * we must tell the underlying tree.  At minimum, this means no
-		 * group fetch on an index under an IndexRowToBaseRow since that
-		 * that could lead to incorrect results.  (Bug 2347.)
-		 */
-		if (eliminateSort)
-		{
-			prnRSN.adjustForSortElimination(orderByList);
-		}
+            /* If we were able to eliminate the sort during optimization then
+             * we must tell the underlying tree.  At minimum, this means no
+             * group fetch on an index under an IndexRowToBaseRow since that
+             * that could lead to incorrect results.  (Bug 2347.)
+             */
+            if (eliminateSort[i])
+            {
+                prnRSN.adjustForSortElimination(orderByLists[i]);
+            }
 
-		/* Set the cost of this node in the generated node */
-		prnRSN.costEstimate = costEstimate.cloneMe();
+            /* Set the cost of this node in the generated node */
+            prnRSN.costEstimate = costEstimate.cloneMe();
+        }
 
 		return prnRSN;
 	}
@@ -1834,7 +1908,15 @@ public class SelectNode extends ResultSe
 		SanityManager.ASSERT(selectSubquerys != null,
 			"selectSubquerys is expected to be non-null");
 
-		/* If this select node is the child of an outer node that is
+        // If we have more than 1 ORDERBY columns, we may be able to
+        // remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+        for (int i=0; i < orderByLists.length; i++) {
+            if (orderByLists[i] != null && orderByLists[i].size() > 1) {
+                orderByLists[i].removeDupColumns();
+            }
+        }
+
+        /* If this select node is the child of an outer node that is
 		 * being optimized, we can get here multiple times (once for
 		 * every permutation that is done for the outer node).  With
 		 * DERBY-805, we can add optimizable predicates to the WHERE
@@ -1914,7 +1996,7 @@ public class SelectNode extends ResultSe
 		optimizer = getOptimizer(fromList,
 								wherePredicates,
 								dataDictionary,
-								orderByList);
+                                orderByLists[0]); // use first one
 		optimizer.setOuterRows(outerRows);
 
 		/* Optimize this SelectNode */

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=1422164&r1=1422163&r2=1422164&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 Sat Dec 15 00:34:47 2012
@@ -60,7 +60,14 @@ abstract class SetOperatorNode extends T
 	*/
 	boolean			all;
 
-	OrderByList orderByList;
+    /**
+     * List of columns in ORDER BY list. Usually size 1, if size 2, we
+     * are a VALUES top node UNION node and element 2 has been passed
+     * from InterceptOrExceptNode to prepare for merge implementation
+     * of intersect or except.
+     */
+    OrderByList[] orderByLists = new OrderByList[1];
+
     ValueNode   offset; // OFFSET n ROWS
     ValueNode   fetchFirst; // FETCH FIRST n ROWS ONLY
     boolean   hasJDBClimitClause; // were OFFSET/FETCH FIRST specified by a JDBC LIMIT clause?
@@ -552,11 +559,22 @@ abstract class SetOperatorNode extends T
 		{
 			super.printSubNodes(depth);
 
-			if (orderByList != null) {
-				printLabel(depth, "orderByList:");
-				orderByList.treePrint(depth + 1);
+            if (orderByLists[0] != null) {
+                for (int i = 0; i < orderByLists.length; i++) {
+                    printLabel(depth, "orderByLists[" + i + "]:");
+                    orderByLists[i].treePrint(depth + 1);
+                }
 			}
 
+            if (offset != null) {
+                printLabel(depth, "offset:");
+                offset.treePrint(depth + 1);
+            }
+
+            if (fetchFirst != null) {
+                printLabel(depth, "fetch first/next:");
+                fetchFirst.treePrint(depth + 1);
+            }
 		}
 	}
 	/**
@@ -649,6 +667,8 @@ abstract class SetOperatorNode extends T
         // part of the result from the set operation (DERBY-3764).
         resultColumns.removeGeneratedGroupingColumns();
 
+        resultColumns.removeOrderByColumns();
+
 		/* Create new expressions with the dominant types after verifying
 		 * union compatibility between left and right sides.
 		 */
@@ -769,6 +789,18 @@ abstract class SetOperatorNode extends T
 		}
 	}
 
+    public void bindExpressions(FromList fromList) throws StandardException {
+        // Actions for UnionNode qua top node of a multi-valued table value
+        // constructor
+        if (orderByLists[0] != null) {
+            orderByLists[0].bindOrderByColumns(this);
+            orderByLists[0].pullUpOrderByColumns(this);
+        }
+
+        bindOffsetFetch(offset, fetchFirst);
+        super.bindExpressions(fromList);
+    }
+
 	/**
 	 * Bind the expressions in the target list.  This means binding the
 	 * sub-expressions, as well as figuring out what the return type is
@@ -796,7 +828,32 @@ abstract class SetOperatorNode extends T
 	 */
 	void pushOrderByList(OrderByList orderByList)
 	{
-		this.orderByList = orderByList;
+        if (this.orderByLists[0] != null) {
+            // Presumably a push down order by from IntersectOrExceptNode
+            // on a VALUES clause that already has an ORDER BY.
+            if (SanityManager.DEBUG) {
+                SanityManager.ASSERT(
+                    orderByList.size() == resultColumns.visibleSize());
+                OrderByColumn obc = (OrderByColumn)orderByList.elementAt(0);
+                SanityManager.ASSERT(
+                    obc.getExpression() instanceof NumericConstantNode);
+                try {
+                    SanityManager.ASSERT(
+                            ((NumericConstantNode)obc.getExpression())
+                            .value.getInt() == 1);
+                } catch (Exception e) {
+                    SanityManager.THROWASSERT(e);
+                }
+            }
+
+            // FIXME: Check to see if this extra ordering can be eliminated
+            OrderByList[] newOrderByLists = new OrderByList[2];
+            newOrderByLists[0] = orderByLists[0];
+            newOrderByLists[1] = orderByList;
+            this.orderByLists = newOrderByLists;
+        } else {
+            this.orderByLists[0] = orderByList;
+        }
 	}
 
     /**
@@ -869,28 +926,38 @@ abstract class SetOperatorNode extends T
 		 *		above the select so that the shape of the result set
 		 *		is as expected.
 		 */
-		if ((! all) && orderByList != null && orderByList.allAscending())
-		{
-			/* Order by list currently restricted to columns in select
-			 * list, so we will always eliminate the order by here.
-			 */
-			if (orderByList.isInOrderPrefix(resultColumns))
-			{
-				orderByList = null;
-			}
-			/* RESOLVE - We currently only eliminate the order by if it is
-			 * a prefix of the select list.  We do not currently do the 
-			 * elimination if the order by is not a prefix because the code
-			 * doesn't work.  The problem has something to do with the
-			 * fact that we generate additional nodes between the union
-			 * and the PRN (for reordering that we would generate here)
-			 * when modifying the access paths.  VCNs under the PRN can be
-			 * seen as correlated since their source resultset is the Union
-			 * which is no longer the result set directly under them.  This
-			 * causes the wrong code to get generated. (jerry - 11/3/98)
-			 * (bug 59)
-			 */
-		}
+        for (int i = 0; i < orderByLists.length; i++) {
+            if ((! all) && orderByLists[i] != null &&
+                orderByLists[i].allAscending())
+            {
+                /* Order by list currently restricted to columns in select
+                 * list, so we will always eliminate the order by here.
+                 */
+                if (orderByLists[i].isInOrderPrefix(resultColumns))
+                {
+                    orderByLists[i] = null;
+                }
+                /* RESOLVE - We currently only eliminate the order by if it is
+                 * a prefix of the select list.  We do not currently do the
+                 * elimination if the order by is not a prefix because the code
+                 * doesn't work.  The problem has something to do with the
+                 * fact that we generate additional nodes between the union
+                 * and the PRN (for reordering that we would generate here)
+                 * when modifying the access paths.  VCNs under the PRN can be
+                 * seen as correlated since their source resultset is the Union
+                 * which is no longer the result set directly under them.  This
+                 * causes the wrong code to get generated. (jerry - 11/3/98)
+                 * (bug 59)
+                 */
+            }
+
+            // UnionNode qua top of table value constructor with ordering
+            // If we have more than 1 ORDERBY columns, we may be able to
+            // remove duplicate columns, e.g., "ORDER BY 1, 1, 2".
+            if (orderByLists[i] != null && orderByLists[i].size() > 1) {
+                orderByLists[i].removeDupColumns();
+            }
+        }
 
 		return newTop;
 	}
@@ -1150,5 +1217,4 @@ abstract class SetOperatorNode extends T
 
 		return rightOptPredicates;
 	}
-
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java?rev=1422164&r1=1422163&r2=1422164&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java Sat Dec 15 00:34:47 2012
@@ -471,30 +471,34 @@ public class UnionNode extends SetOperat
 		/* Generate the OrderByNode if a sort is still required for
 		 * the order by.
 		 */
-		if (orderByList != null)
-		{
-			treeTop = (ResultSetNode) getNodeFactory().getNode(
-											C_NodeTypes.ORDER_BY_NODE,
-											treeTop,
-											orderByList,
-											tableProperties,
-											getContextManager());
-		}
-
-
-        if (offset != null || fetchFirst != null) {
-            ResultColumnList newRcl =
-                treeTop.getResultColumns().copyListAndObjects();
-            newRcl.genVirtualColumnNodes(treeTop, treeTop.getResultColumns());
-
-            treeTop = (ResultSetNode)getNodeFactory().getNode(
-                C_NodeTypes.ROW_COUNT_NODE,
-                treeTop,
-                newRcl,
-                offset,
-                fetchFirst,
-                Boolean.valueOf( hasJDBClimitClause ),
-                getContextManager());
+        for (int i=0; i < orderByLists.length; i++) {
+            if (orderByLists[i] != null)
+            {
+                treeTop = (ResultSetNode) getNodeFactory().getNode(
+                        C_NodeTypes.ORDER_BY_NODE,
+                        treeTop,
+                        orderByLists[i],
+                        tableProperties,
+                        getContextManager());
+            }
+
+            // Do this only after the main ORDER BY; any extra added by
+            // IntersectOrExceptNode should sit on top of us.
+            if (i == 0 && (offset != null || fetchFirst != null)) {
+                ResultColumnList newRcl =
+                        treeTop.getResultColumns().copyListAndObjects();
+                newRcl.genVirtualColumnNodes(treeTop,
+                                             treeTop.getResultColumns());
+
+                treeTop = (ResultSetNode)getNodeFactory().getNode(
+                        C_NodeTypes.ROW_COUNT_NODE,
+                        treeTop,
+                        newRcl,
+                        offset,
+                        fetchFirst,
+                        Boolean.valueOf( hasJDBClimitClause ),
+                        getContextManager());
+            }
         }
 
 		return treeTop;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1422164&r1=1422163&r2=1422164&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Sat Dec 15 00:34:47 2012
@@ -1978,6 +1978,14 @@ public class SQLParser
         }
     }
 
+    private static boolean hasSelectSuffixClause(
+        OrderByList orderBy, ValueNode[] offsetClauses)
+    {
+        return
+            orderBy != null ||
+            offsetClauses[OFFSET_CLAUSE] != null ||
+            offsetClauses[FETCH_FIRST_CLAUSE] != null;
+    }
 }
 
 PARSER_END(SQLParser)
@@ -5150,6 +5158,9 @@ ResultSetNode
 nonJoinQueryPrimary() throws StandardException :
 {
 	ResultSetNode	primary;
+    boolean     hasJDBClimitClause = false;
+    OrderByList orderCols = null;
+    ValueNode[] offsetClauses = new ValueNode[OFFSET_CLAUSE_COUNT];
 }
 {
 	primary = simpleTable()
@@ -5157,10 +5168,42 @@ nonJoinQueryPrimary() throws StandardExc
 		return primary;
 	}
 |
-	<LEFT_PAREN> primary = queryExpression(null, NO_SET_OP)
+    <LEFT_PAREN>
+    primary = queryExpression(null, NO_SET_OP)
+    [ orderCols = orderByClause() ]
+    hasJDBClimitClause = offsetFetchFirstClause( offsetClauses )
 	<RIGHT_PAREN>
 	{
-		return primary;
+        if (hasSelectSuffixClause(orderCols, offsetClauses)) {
+            if (primary instanceof SelectNode ||
+                primary instanceof UnionNode || // table value constructor
+                primary instanceof RowResultSetNode) {
+
+                primary.pushOrderByList(orderCols);
+                primary.pushOffsetFetchFirst(
+                    offsetClauses[OFFSET_CLAUSE],
+                    offsetClauses[FETCH_FIRST_CLAUSE],
+                    hasJDBClimitClause);
+            } else {
+                // shouldn't happen
+                String tokenName;
+
+                if (orderCols != null) {
+                    tokenName = "ORDER BY";
+                } else if (hasJDBClimitClause)  {
+                    tokenName = "LIMIT";
+                } else if (offsetClauses[OFFSET_CLAUSE] != null) {
+                    tokenName = "OFFSET";
+                } else {
+                    tokenName = "FETCH";
+                }
+
+                throw StandardException.newException(
+                    SQLState.LANG_SYNTAX_ERROR, tokenName);
+            }
+        }
+
+        return primary;
 	}
 }
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java?rev=1422164&r1=1422163&r2=1422164&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java Sat Dec 15 00:34:47 2012
@@ -1080,4 +1080,154 @@ public class OrderByAndOffsetFetchInSubq
 
         rollback();
     }
+
+
+    /**
+     * Test nesting inside set operands, cf. this production in SQL
+     * 2011, section 7.12:
+     * <pre>
+     * <query primary> ::=
+     *      <simple table>
+     *   |  <left paren> <query expression body>
+     *      [ <order by clause> ] [ <result offset clause> ]
+     *      [ <fetch first clause> ] <right paren>
+     * </pre>
+     * The corresponding production in {@code sqlgrammar.jj} is
+     * {@code nonJoinQueryPrimary}.
+     * Cf. DERBY-6008.
+     */
+    public void testNestingInsideSetOperation() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+
+        s.executeUpdate("create table t1(i int, j int )");
+        s.executeUpdate("create table t2(i int, j int)");
+
+        s.executeUpdate("insert into t1 values (1,1),(4,8),(2,4)");
+        s.executeUpdate("insert into t2 values (10,10),(40,80),(20,40)");
+
+        ResultSet rs = s.executeQuery(
+            "(select i from t1 order by j desc offset 1 row) union " +
+            "(select i from t2 order by j desc offset 1 rows " +
+                                             "fetch next 1 row only)");
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"1"}, {"2"}, {"20"}});
+
+        // Without parentheses, expect syntax error
+        assertCompileError("42X01",
+                "select i from t1 order by j desc offset 1 row union " +
+                "(select i from t2 order by j desc offset 2 rows)");
+
+        // With VALUES (single) instead of SELECT:
+        // Single values exercise changes in RowResultSetNode
+        rs = s.executeQuery(
+            "(values 1 order by 1 fetch first 1 row only) union " +
+            "(select i from t2 order by j desc offset 2 rows)");
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"1"}, {"10"}});
+
+        // With VALUES (single) instead of SELECT and duplicate ordering key
+        rs = s.executeQuery(
+            "(values 1 order by 1,1 fetch first 1 row only) union " +
+            "(select i from t2 order by j desc offset 2 rows)");
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"1"}, {"10"}});
+
+        // With VALUES (multiple) instead of SELECT
+        // Multiples values exercise changes in SetOperatorNode when used in
+        // table value constructor context (UNION).
+        rs = s.executeQuery(
+            "(values 1,2 order by 1 desc offset 1 row " +
+            "                       fetch first 1 row only)" +
+            " union (select i from t2 order by j desc offset 2 rows)");
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"1"}, {"10"}});
+
+        // With VALUES (multiple) instead of SELECT plus duplicate ordering
+        // key
+        rs = s.executeQuery(
+            "(values 1,2 order by 1,1 offset 1 row fetch first 1 row only)" +
+            " union (select i from t2 order by j desc offset 2 rows)");
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"2"}, {"10"}});
+
+        // Intersect and except
+        s.executeUpdate(
+                "create table countries(name varchar(20), " +
+                "                       population int, " +
+                "                       area int)");
+
+        s.executeUpdate("insert into countries values" +
+                "('Norway', 5033675, 385252)," +
+                "('Sweden', 9540065, 449964)," +
+                "('Denmark', 5580413, 42894)," +
+                "('Iceland', 320060, 103001)," +
+                "('Liechtenstein', 36281, 160)");
+
+        rs = s.executeQuery(
+           "(select name from countries " +
+           "    order by population desc fetch first 2 rows only)" +
+           " intersect " +
+           "(select name from countries " +
+           "    order by area desc fetch first 2 rows only)");
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"Sweden"}});
+
+        rs = s.executeQuery(
+           "(values ('Norway', 5033675, 385252), " +
+           "        ('Sweden', 9540065, 449964)," +
+           "        ('Denmark', 5580413, 42894)," +
+           "        ('Iceland', 320060, 103001)," +
+           "        ('Liechtenstein', 36281, 160)" +
+           "    order by 2 desc fetch first 3 rows only)" +
+           " intersect " +
+           "(select * from countries " +
+           "    order by area desc fetch first 3 rows only)");
+        // Note: we use 3 rows here to check that both sorts work the way they
+        // should: at the lowest level, the "order by 2 desc", then the
+        // "fetch first" of only three of those rows, then on top the ascending
+        // sort on all columns to get the data ready for the intersect.
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"Norway", "5033675", "385252"},
+            {"Sweden", "9540065", "449964"}});
+
+        rs = s.executeQuery(
+           "(values ('Norway', 5033675, 385252)" +
+           "    order by 2 desc fetch first 3 rows only)" +
+           " intersect " +
+           "(values ('Norway', 5033675, 385252))");
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"Norway", "5033675", "385252"}});
+
+        rs = s.executeQuery(
+           "(select name from countries " +
+           "    order by population desc fetch first 2 rows only)" +
+           " except " +
+           "(select name from countries " +
+           "    order by area desc fetch first 2 rows only)");
+
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"Denmark"}});
+
+        rs = s.executeQuery(
+           "(values ('Norway', 5033675, 385252), " +
+           "        ('Sweden', 9540065, 449964)," +
+           "        ('Denmark', 5580413, 42894)," +
+           "        ('Iceland', 320060, 103001)," +
+           "        ('Liechtenstein', 36281, 160)" +
+           "    order by 2 desc fetch first 3 rows only)" +
+           " except " +
+           "(select * from countries " +
+           "    order by area desc fetch first 3 rows only)");
+        JDBC.assertFullResultSet(rs, new String[][]{
+            {"Denmark", "5580413", "42894"}});
+
+        rollback();
+    }
 }



Mime
View raw message