db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r787505 - in /db/derby/code/branches/10.2/java: engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 23 Jun 2009 00:36:34 GMT
Author: kmarsden
Date: Tue Jun 23 00:36:33 2009
New Revision: 787505

URL: http://svn.apache.org/viewvc?rev=787505&view=rev
Log:
DERBY-3926 Incorrect ORDER BY caused by index

Contributed by Mamta Satoor (msatoor at gmail dot com)


Modified:
    db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java Tue Jun 23 00:36:33 2009
@@ -122,6 +122,23 @@
 	 * hash table on a next are qualifiers.
 	 */
 	public void markAllPredicatesQualifiers();
+	
+	/**
+	 * Check into the predicate list if the passed column has an equijoin 
+	 * predicate on it.
+	 * 
+	 * @param optTable
+	 * @param columnNumber
+	 * @param isNullOkay
+	 * @return the position of the predicate in the list which corresponds to 
+	 *   the equijoin. If no quijoin predicate found, then the return value 
+	 *   will be -1
+	 * @throws StandardException
+	 */
+	public int hasEqualityPredicateOnOrderedColumn(Optimizable optTable,
+			  int columnNumber,
+			  boolean isNullOkay)
+	throws StandardException;
 
 	/**
 	 * Is there an optimizable equality predicate on the specified column?

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java Tue Jun 23 00:36:33 2009
@@ -24,6 +24,7 @@
 import org.apache.derby.iapi.error.StandardException;
 
 import org.apache.derby.iapi.util.JBitSet;
+import org.apache.derby.impl.sql.compile.PredicateList;
 
 /**
  * This interface provides a representation of the required ordering of rows
@@ -43,6 +44,11 @@
 	 * given a RowOrdering.
 	 *
 	 * @param rowOrdering	The order of rows in question
+	 * @param optimizableList	The current join order being considered by 
+	 *    the optimizer. We need to look into this to determine if the outer
+	 *    optimizables are single row resultset if the order by column is
+	 *    on an inner optimizable and that inner optimizable is not a one
+	 *    row resultset. DERBY-3926
 	 *
 	 * @return	SORT_REQUIRED if sorting is required,
 	 *			ELIMINATE_DUPS if no sorting is required but duplicates
@@ -52,7 +58,8 @@
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	int sortRequired(RowOrdering rowOrdering) throws StandardException;
+	int sortRequired(RowOrdering rowOrdering, OptimizableList optimizableList) 
+	throws StandardException;
 
 	/**
 	 * Tell whether sorting is required for this RequiredRowOrdering,
@@ -63,6 +70,11 @@
 	 *
 	 * @param rowOrdering	The order of rows in the partial join order
 	 * @param tableMap		A bit map of the tables in the partial join order
+	 * @param optimizableList	The current join order being considered by 
+	 *    the optimizer. We need to look into this to determine if the outer
+	 *    optimizables are single row resultset if the order by column is
+	 *    on an inner optimizable and that inner optimizable is not a one
+	 *    row resultset. DERBY-3926
 	 *
 	 * @return	SORT_REQUIRED if sorting is required,
 	 *			ELIMINATE_DUPS if no sorting is required by duplicates
@@ -72,7 +84,8 @@
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	int sortRequired(RowOrdering rowOrdering, JBitSet tableMap)
+	int sortRequired(RowOrdering rowOrdering, JBitSet tableMap, 
+			OptimizableList optimizableList)
 			throws StandardException;
 
 	/**

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java Tue Jun 23 00:36:33 2009
@@ -130,13 +130,27 @@
 	/**
 	 * Tell this RowOrdering that it is always ordered on the given column
 	 * of the given optimizable.  This is useful when a column in the
-	 * optimizable has an equals comparison with a constant expression.
-	 * This is reset when the optimizable is removed from this RowOrdering.
+	 * optimizable has an equals comparison with a constant expression or
+	 * it is involved in a equijoin with an optimizable which is always 
+	 * ordered on the column on which the equijoin is happening. This is 
+	 * reset when the optimizable is removed from this RowOrdering.
 	 *
 	 * @param optimizable	The table in question
 	 * @param columnNumber	The number of the column in question.
 	 */
 	void columnAlwaysOrdered(Optimizable optimizable, int columnNumber);
+	
+	/**
+	 * Return true if the column is always ordered. That will be true if the
+	 * column has a constant comparison predicate on it or it is involved in
+	 * a equijoin with an optimizable which is always ordered on the column
+	 * on which the equijoin is happening.
+	 *
+	 * @param tableNumber	The table in question
+	 * @param columnNumber	The number of the column in question.
+	 * @return	true means this column is always ordered
+	*/
+	public boolean isColumnAlwaysOrdered(int tableNumber, int columnNumber);
 
 	/**
 	 * Ask whether the given table is always ordered.

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Tue Jun 23 00:36:33 2009
@@ -456,6 +456,13 @@
 
 				for (int i = 0; i < baseColumnPositions.length; i++)
 				{
+					//Check if the order by column has equijoin on another 
+					//column which is already identified as an ordered column
+					if (doesOrderByColumnHaveEquiJoin(
+							irg, predList, rowOrdering))
+						rowOrdering.columnAlwaysOrdered(this, 
+								baseColumnPositions[i]);
+
 					/*
 					** Don't add the column to the ordering if it's already
 					** an ordered column.  This can happen in the following
@@ -4263,6 +4270,86 @@
 		return true;
 	}
 
+	//Check if the columns in the index have an equijoin on them
+	//with other already ordered columns from the other optimizables. This
+	//is done by going through the columns in the index and checking the
+	//predicate list for equijoins on the index columns. 
+	private boolean doesOrderByColumnHaveEquiJoin(IndexRowGenerator irg,
+			OptimizablePredicateList predList, RowOrdering ro)
+	throws StandardException
+	{
+		if (predList == null)
+		{
+			return false;
+		}
+
+		// is this a unique index. 
+		if (! irg.isUnique())
+		{
+			return false;
+		}
+
+		PredicateList restrictionList = (PredicateList) predList;
+
+		int[] baseColumnPositions = irg.baseColumnPositions();
+
+		for (int index = 0; index < baseColumnPositions.length; index++)
+		{
+			// get the column number at this position
+			int curCol = baseColumnPositions[index];
+
+			//Check if this column from the index has an equi join predicate
+			//on them.
+			int j = restrictionList.hasEqualityPredicateOnOrderedColumn(
+					this, curCol, true);
+			if (j == -1)
+				return false;
+
+			//We have found a predicate which has an equi join involving the
+			//index column. Now ensure that the equi join is with a column
+			//which is already identified as always ordered.
+			Predicate		predicate;
+			predicate = (Predicate) restrictionList.elementAt(j);
+			ValueNode vn = predicate.getAndNode().getLeftOperand();
+			ColumnReference cr;
+  			if (vn instanceof BinaryRelationalOperatorNode)
+  			{
+  				BinaryRelationalOperatorNode bon = 
+  					(BinaryRelationalOperatorNode) vn;
+  				cr = null;
+				if (bon.columnOnOneSide(this) == 
+					BinaryRelationalOperatorNode.LEFT)
+				{
+	  				//If the index column is on left side, then look for the 
+					//operand on the other side to see if it is of type 
+					//ColumnReference. If it is, then check if that column 
+					//is identified as always ordered
+					if (bon.getRightOperand() instanceof ColumnReference)
+						cr = (ColumnReference)bon.getRightOperand();
+				} else
+				{
+	  				//If the index column is on right side, then look for the 
+					//operand on the other side to see if it is of type 
+					//ColumnReference. If it is, then check if that column 
+					//is identified as always ordered
+					if (bon.getLeftOperand() instanceof ColumnReference)
+						cr = (ColumnReference)bon.getLeftOperand();
+				}
+				if (cr!=null)
+				{
+					//We have found that the index column is involved in an
+					//equijoin with another column. Now check if that other
+					//column is always ordered
+					if (ro.orderedOnColumn(1, cr.getTableNumber(), 
+							cr.getColumnNumber()))
+						return true;
+				}
+  			}
+		}
+
+		return false;
+	}
+
 	/**
 	 * Is this a one-row result set with the given conglomerate descriptor?
 	 */

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java Tue Jun 23 00:36:33 2009
@@ -1665,8 +1665,9 @@
 				if (requiredRowOrdering != null &&
 					curOpt.considerSortAvoidancePath())
 				{
-					if (requiredRowOrdering.sortRequired(bestRowOrdering) ==
-									RequiredRowOrdering.NOTHING_REQUIRED)
+					if (requiredRowOrdering.sortRequired(
+							bestRowOrdering, optimizableList) == 
+								RequiredRowOrdering.NOTHING_REQUIRED)
 					{
 						if (optimizerTrace)
 						{
@@ -2122,8 +2123,9 @@
 				** path avoid a sort?
 				*/
 				if (requiredRowOrdering.sortRequired(currentRowOrdering,
-														assignedTableMap)
-										== RequiredRowOrdering.NOTHING_REQUIRED)
+														assignedTableMap,
+														optimizableList)
+										==RequiredRowOrdering.NOTHING_REQUIRED)
 				{
 					ap = optimizable.getBestSortAvoidancePath();
 					bestCostEstimate = ap.getCostEstimate();
@@ -2253,7 +2255,8 @@
 				** path avoid a sort?
 				*/
 				if (requiredRowOrdering.sortRequired(currentRowOrdering,
-														assignedTableMap)
+														assignedTableMap,
+														optimizableList)
 										== RequiredRowOrdering.NOTHING_REQUIRED)
 				{
 					ap = optimizable.getBestSortAvoidancePath();

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java Tue Jun 23 00:36:33 2009
@@ -22,7 +22,9 @@
 package	org.apache.derby.impl.sql.compile;
 
 import org.apache.derby.iapi.sql.compile.CompilerContext;
+import org.apache.derby.iapi.sql.compile.Optimizable;
 import org.apache.derby.iapi.sql.compile.CostEstimate;
+import org.apache.derby.iapi.sql.compile.OptimizableList;
 import org.apache.derby.iapi.sql.compile.RequiredRowOrdering;
 import org.apache.derby.iapi.sql.compile.RowOrdering;
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
@@ -413,9 +415,10 @@
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	public int sortRequired(RowOrdering rowOrdering) throws StandardException
+	public int sortRequired(RowOrdering rowOrdering,
+			OptimizableList optimizableList) throws StandardException
 	{
-		return sortRequired(rowOrdering, (JBitSet) null);
+		return sortRequired(rowOrdering, (JBitSet) null, optimizableList);
 	}
 
 	/**
@@ -423,7 +426,9 @@
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	public int sortRequired(RowOrdering rowOrdering, JBitSet tableMap)
+	public int sortRequired(RowOrdering rowOrdering, 
+			JBitSet tableMap,
+			OptimizableList optimizableList)
 				throws StandardException
 	{
 		/*
@@ -499,7 +504,92 @@
 					return RequiredRowOrdering.NOTHING_REQUIRED;
 				}
 			}
-
+			/*
+			 * Does this order by column belong to the outermost optimizable in
+			 * the current join order?
+			 * 
+			 * If yes, then we do not need worry about the ordering of the rows
+			 * feeding into it. Because the order by column is associated with 
+			 * the outermost optimizable, optimizer will not have to deal with 
+			 * the order of any rows coming in from the previous optimizables. 
+			 * 
+			 * But if the current order by column belongs to an inner 
+			 * optimizable in the join order, then go through the following
+			 * if condition logic.
+			 */
+
+			/* If the following boolean is true, then it means that the join 
+			 * order being considered has more than one table 
+			 */
+			boolean moreThanOneTableInJoinOrder = tableMap!=null?
+					(!tableMap.hasSingleBitSet()) : false;
+			if (moreThanOneTableInJoinOrder) 
+			{
+				/*
+				 * First check if the order by column has a constant comparison
+				 * predicate on it or it belongs to an optimizable which is 
+				 * always ordered(that means it is a single row table) or the 
+				 * column is involved in an equijoin with an optimizable which 
+				 * is always ordered on the column on which the equijoin is 
+				 * happening. If yes, then we know that the rows will always be 
+				 * sorted and hence we do not need to worry if (any) prior 
+				 * optimizables in join order are one-row resultsets or not. 
+				 */
+				if ((!rowOrdering.alwaysOrdered(cr.getTableNumber())) &&
+						(!rowOrdering.isColumnAlwaysOrdered(
+								cr.getTableNumber(), cr.getColumnNumber())))
+				{
+					/*
+					 * The current order by column is not always ordered which 
+					 * means that the rows from it will not necessarily be in 
+					 * the sorted order on that column. Because of this, we 
+					 * need to make sure that the outer optimizables (outer to 
+					 * the order by columns's optimizable) in the join order 
+					 * are all one row optimizables, meaning that they can at 
+					 * the most return only one row. If they return more than 
+					 * one row, then it will require multiple scans of the 
+					 * order by column's optimizable and the rows returned 
+					 * from those multiple scans may not be ordered correctly.
+					 */
+					for (int i=0; i < optimizableList.size(); i++)
+					{
+						//Get one outer optimizable at a time from the join
+						//order
+						Optimizable considerOptimizable = 
+							optimizableList.getOptimizable(i);
+						//If we have come across the optimizable for the order 
+						//by column in the join order, then we do not need to 
+						//look at the inner optimizables in the join order. As
+						//long as the outer optimizables are one row resultset,
+						//we are fine to consider sort avoidance.
+						if (considerOptimizable.getTableNumber() == 
+							cr.getTableNumber())
+							break;
+						/*
+						 * The following if condition is checking if the
+						 * outer optimizable to the order by column's 
+						 * optimizable is one row resultset or not. 
+						 * 
+						 * If the outer optimizable is one row resultset, 
+						 * then move on to the next optimizable in the join 
+						 * order and do the same check on that optimizable. 
+						 * Continue this  until we are done checking that all 
+						 * the outer optimizables in the join order are single 
+						 * row resultsets. If we run into an outer optimizable 
+						 * which is not one row resultset, then we can not 
+						 * consider sort avoidance for the query.
+						 */
+						if (rowOrdering.alwaysOrdered(
+								considerOptimizable.getTableNumber()))
+							continue;
+						else
+							//This outer optimizable can return more than 
+							//one row. Because of this, we can't avoid the
+							//sorting for this query.
+							return RequiredRowOrdering.SORT_REQUIRED;
+					}
+				}
+			}
 			if ( ! rowOrdering.alwaysOrdered(cr.getTableNumber()))
 			{
 				/*

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java Tue Jun 23 00:36:33 2009
@@ -329,6 +329,45 @@
 	}
 
 	/**
+	 * @see OptimizablePredicateList#hasEqualityPredicateOnOrderedColumn
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public int hasEqualityPredicateOnOrderedColumn(Optimizable optTable,
+													  int columnNumber,
+													  boolean isNullOkay)
+							throws StandardException
+	{
+		ValueNode opNode = null;
+		int size = size();
+		for (int index = 0; index < size; index++)
+		{
+			AndNode			andNode;
+			Predicate		predicate;
+			predicate = (Predicate) elementAt(index);
+			//We are not looking at constant comparison predicate.
+			if (predicate.getReferencedMap().hasSingleBitSet())
+			{
+				continue;
+			}
+
+			andNode = (AndNode) predicate.getAndNode();
+
+			// skip non-equality predicates
+			opNode = andNode.getLeftOperand();
+
+			if (opNode.optimizableEqualityNode(optTable,
+											   columnNumber,
+											   isNullOkay))
+			{
+				return index;
+			}
+		}
+
+		return -1;
+	}
+
+	/**
 	 * @see OptimizablePredicateList#hasOptimizableEqualityPredicate
 	 *
 	 * @exception StandardException		Thrown on error

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Tue Jun 23 00:36:33 2009
@@ -4064,4 +4064,21 @@
 	protected void markInitialSize() {
 		initialListSize = size();
 	}
+	
+	/**
+	 * Remove any columns that may have been added for an order by clause.
+	 * In a query like:
+	 * <pre>select a from t order by b</pre> b is added to the select list
+	 * However in the final projection, after the sort is complete, b will have
+	 * to be removed. 
+	 *
+	 */
+	public void removeOrderByColumns() 
+	{
+		int idx = size() - 1;
+		for (int i = 0; i < orderBySelect; i++, idx--) {
+			removeElementAt(idx);
+		}
+		orderBySelect = 0;
+	}
 }

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java Tue Jun 23 00:36:33 2009
@@ -39,7 +39,9 @@
 	** This ColumnOrdering represents the columns that can be considered
 	** ordered no matter what.  For example, columns that are compared to
 	** constants with = are always ordered.  Also, all columns in a one-row
-	** result set are ordered.
+	** result set are ordered. Another instance of always ordered is when
+	** the column is involved in an equijoin with an optimizable which is 
+	** always ordered on the column on which the equijoin is happening.
 	*/
 	ColumnOrdering	columnsAlwaysOrdered;
 
@@ -60,6 +62,11 @@
 		columnsAlwaysOrdered = new ColumnOrdering(RowOrdering.DONTCARE);
 		alwaysOrderedOptimizables = new Vector();
 	}
+	
+	/** @see RowOrdering#isColumnAlwaysOrdered */
+	public boolean isColumnAlwaysOrdered(int tableNumber, int columnNumber){
+		return (columnsAlwaysOrdered.contains(tableNumber, columnNumber)); 
+	}
 
 	/**
 	 * @see RowOrdering#orderedOnColumn

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out Tue Jun 23 00:36:33 2009
@@ -21828,97 +21828,112 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Hash Exists Join ResultSet:
+Project-Restrict ResultSet (6):
 <filtered number of opens>
-<filtered rows seen from the left>
-<filtered rows seen from the right>
+<filtered rows seen>
 Rows filtered = 0
-<filtered rows returned>
+restriction = false
+projection = true
 	constructor time (milliseconds) = 0
 	open time (milliseconds) = 0
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
-Left result set:
-	Index Row to Base Row ResultSet for TENKTUP1:
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Hash Exists Join ResultSet:
 	<filtered number of opens>
-	<filtered rows seen>
-	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+	<filtered rows seen from the left>
+	<filtered rows seen from the right>
+	Rows filtered = 0
+	<filtered rows returned>
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-		Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+	Left result set:
+		Index Row to Base Row ResultSet for TENKTUP2:
 		<filtered number of opens>
 		<filtered rows seen>
-		Rows filtered = 0
-		Fetch Size = 1
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
-		scan information: 
-			Bit set of columns fetched=All
-			Number of columns fetched=2
-			Number of deleted rows visited=0
-			<filtered number of pages visited>
-			<filtered number of rows qualified>
-			<filtered number of rows visited>
-			Scan type=btree
-			Tree height=2
-			start position: 
+			Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+			<filtered number of opens>
+			<filtered rows seen>
+			Rows filtered = 0
+			Fetch Size = 1
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
 	None
-			stop position: 
+				stop position: 
 	>= on first 1 column(s).
 	Ordered null semantics on the following columns: 
 0 
-			qualifiers:
+				qualifiers:
 None
-Right result set:
-	Index Row to Base Row ResultSet for TENKTUP2:
-	<filtered number of opens>
-	<filtered rows seen>
-	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
-		constructor time (milliseconds) = 0
-		open time (milliseconds) = 0
-		next time (milliseconds) = 0
-		close time (milliseconds) = 0
-		Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: 
+	Right result set:
+		Index Row to Base Row ResultSet for TENKTUP1:
 		<filtered number of opens>
-		Hash table size = 2500
-		Hash key is column number 0
 		<filtered rows seen>
-		Rows filtered = 0
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
-		scan information: 
-			Bit set of columns fetched=All
-			Number of columns fetched=2
-			Number of deleted rows visited=0
-			<filtered number of pages visited>
-			<filtered number of rows qualified>
-			<filtered number of rows visited>
-			Scan type=btree
-			Tree height=2
-			start position: 
+			Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: 
+			<filtered number of opens>
+			Hash table size = 2500
+			Hash key is column number 0
+			<filtered rows seen>
+			Rows filtered = 0
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
 	None
-			stop position: 
+				stop position: 
 	>= on first 1 column(s).
 	Ordered null semantics on the following columns: 
 0 
-			scan qualifiers:
+				scan qualifiers:
 None
-			next qualifiers:
+				next qualifiers:
 Column[0][0] Id: 0
 Operator: =
 Ordered nulls: false
 Unknown return value: false
 Negate comparison result: false
 ij> commit;
-ij> -- 25% of rows from joining table
+ij> -- force TENKTUP1 as the outermost join table to make sure 
+-- that no sorting is necessary. DERBY-3926
 get cursor c as
-	'select * from TENKTUP1, TENKTUP2
+	'select * from --DERBY-PROPERTIES joinOrder=FIXED
+	 TENKTUP1, TENKTUP2
 	 where TENKTUP1.unique1 = TENKTUP2.unique1
 	 and TENKTUP2.unique1 < 2500
 	 order by TENKTUP1.unique1';
@@ -21929,7 +21944,8 @@
 Statement Name: 
 	C
 Statement Text: 
-	select * from TENKTUP1, TENKTUP2
+	select * from --DERBY-PROPERTIES joinOrder=FIXED
+	 TENKTUP1, TENKTUP2
 	 where TENKTUP1.unique1 = TENKTUP2.unique1
 	 and TENKTUP2.unique1 < 2500
 	 order by TENKTUP1.unique1
@@ -21944,6 +21960,7 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
+User supplied optimizer overrides for join are { joinOrder=FIXED }
 Hash Exists Join ResultSet:
 <filtered number of opens>
 <filtered rows seen from the left>
@@ -22032,11 +22049,13 @@
 Unknown return value: false
 Negate comparison result: false
 ij> commit;
-ij> -- 10% of rows from joining table
+ij> -- This time, force TENKTUP2 as the outermost join table to make sure 
+-- that still no sorting is necessary. DERBY-3926
 get cursor c as
-	'select * from TENKTUP1, TENKTUP2
+	'select * from --DERBY-PROPERTIES joinOrder=FIXED
+	 TENKTUP2, TENKTUP1
 	 where TENKTUP1.unique1 = TENKTUP2.unique1
-	 and TENKTUP2.unique1 < 1000
+	 and TENKTUP2.unique1 < 2500
 	 order by TENKTUP1.unique1';
 ij> close c;
 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
@@ -22045,9 +22064,10 @@
 Statement Name: 
 	C
 Statement Text: 
-	select * from TENKTUP1, TENKTUP2
+	select * from --DERBY-PROPERTIES joinOrder=FIXED
+	 TENKTUP2, TENKTUP1
 	 where TENKTUP1.unique1 = TENKTUP2.unique1
-	 and TENKTUP2.unique1 < 1000
+	 and TENKTUP2.unique1 < 2500
 	 order by TENKTUP1.unique1
 Parse Time: 0
 Bind Time: 0
@@ -22060,6 +22080,7 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
+User supplied optimizer overrides for join are { joinOrder=FIXED }
 Hash Exists Join ResultSet:
 <filtered number of opens>
 <filtered rows seen from the left>
@@ -22071,7 +22092,7 @@
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
 Left result set:
-	Index Row to Base Row ResultSet for TENKTUP1:
+	Index Row to Base Row ResultSet for TENKTUP2:
 	<filtered number of opens>
 	<filtered rows seen>
 	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
@@ -22079,7 +22100,7 @@
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-		Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+		Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
 		<filtered number of opens>
 		<filtered rows seen>
 		Rows filtered = 0
@@ -22106,7 +22127,7 @@
 			qualifiers:
 None
 Right result set:
-	Index Row to Base Row ResultSet for TENKTUP2:
+	Index Row to Base Row ResultSet for TENKTUP1:
 	<filtered number of opens>
 	<filtered rows seen>
 	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
@@ -22114,9 +22135,9 @@
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-		Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: 
+		Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: 
 		<filtered number of opens>
-		Hash table size = 1000
+		Hash table size = 2500
 		Hash key is column number 0
 		<filtered rows seen>
 		Rows filtered = 0
@@ -22148,11 +22169,11 @@
 Unknown return value: false
 Negate comparison result: false
 ij> commit;
-ij> -- 5% of rows from joining table
+ij> -- 25% of rows from joining table
 get cursor c as
 	'select * from TENKTUP1, TENKTUP2
 	 where TENKTUP1.unique1 = TENKTUP2.unique1
-	 and TENKTUP2.unique1 < 500
+	 and TENKTUP2.unique1 < 2500
 	 order by TENKTUP1.unique1';
 ij> close c;
 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
@@ -22163,7 +22184,7 @@
 Statement Text: 
 	select * from TENKTUP1, TENKTUP2
 	 where TENKTUP1.unique1 = TENKTUP2.unique1
-	 and TENKTUP2.unique1 < 500
+	 and TENKTUP2.unique1 < 2500
 	 order by TENKTUP1.unique1
 Parse Time: 0
 Bind Time: 0
@@ -22176,99 +22197,112 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Hash Exists Join ResultSet:
+Project-Restrict ResultSet (6):
 <filtered number of opens>
-<filtered rows seen from the left>
-<filtered rows seen from the right>
+<filtered rows seen>
 Rows filtered = 0
-<filtered rows returned>
+restriction = false
+projection = true
 	constructor time (milliseconds) = 0
 	open time (milliseconds) = 0
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
-Left result set:
-	Index Row to Base Row ResultSet for TENKTUP1:
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Hash Exists Join ResultSet:
 	<filtered number of opens>
-	<filtered rows seen>
-	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+	<filtered rows seen from the left>
+	<filtered rows seen from the right>
+	Rows filtered = 0
+	<filtered rows returned>
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-		Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+	Left result set:
+		Index Row to Base Row ResultSet for TENKTUP2:
 		<filtered number of opens>
 		<filtered rows seen>
-		Rows filtered = 0
-		Fetch Size = 1
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
-		scan information: 
-			Bit set of columns fetched=All
-			Number of columns fetched=2
-			Number of deleted rows visited=0
-			<filtered number of pages visited>
-			<filtered number of rows qualified>
-			<filtered number of rows visited>
-			Scan type=btree
-			Tree height=2
-			start position: 
+			Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+			<filtered number of opens>
+			<filtered rows seen>
+			Rows filtered = 0
+			Fetch Size = 1
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
 	None
-			stop position: 
+				stop position: 
 	>= on first 1 column(s).
 	Ordered null semantics on the following columns: 
 0 
-			qualifiers:
+				qualifiers:
 None
-Right result set:
-	Index Row to Base Row ResultSet for TENKTUP2:
-	<filtered number of opens>
-	<filtered rows seen>
-	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
-		constructor time (milliseconds) = 0
-		open time (milliseconds) = 0
-		next time (milliseconds) = 0
-		close time (milliseconds) = 0
-		Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: 
+	Right result set:
+		Index Row to Base Row ResultSet for TENKTUP1:
 		<filtered number of opens>
-		Hash table size = 500
-		Hash key is column number 0
 		<filtered rows seen>
-		Rows filtered = 0
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
-		scan information: 
-			Bit set of columns fetched=All
-			Number of columns fetched=2
-			Number of deleted rows visited=0
-			<filtered number of pages visited>
-			<filtered number of rows qualified>
-			<filtered number of rows visited>
-			Scan type=btree
-			Tree height=2
-			start position: 
+			Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: 
+			<filtered number of opens>
+			Hash table size = 2500
+			Hash key is column number 0
+			<filtered rows seen>
+			Rows filtered = 0
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
 	None
-			stop position: 
+				stop position: 
 	>= on first 1 column(s).
 	Ordered null semantics on the following columns: 
 0 
-			scan qualifiers:
+				scan qualifiers:
 None
-			next qualifiers:
+				next qualifiers:
 Column[0][0] Id: 0
 Operator: =
 Ordered nulls: false
 Unknown return value: false
 Negate comparison result: false
 ij> commit;
-ij> -- 1% of rows from joining table
+ij> -- 10% of rows from joining table
 get cursor c as
 	'select * from TENKTUP1, TENKTUP2
 	 where TENKTUP1.unique1 = TENKTUP2.unique1
-	 and TENKTUP2.unique1 < 100
+	 and TENKTUP2.unique1 < 1000
 	 order by TENKTUP1.unique1';
 ij> close c;
 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
@@ -22279,7 +22313,7 @@
 Statement Text: 
 	select * from TENKTUP1, TENKTUP2
 	 where TENKTUP1.unique1 = TENKTUP2.unique1
-	 and TENKTUP2.unique1 < 100
+	 and TENKTUP2.unique1 < 1000
 	 order by TENKTUP1.unique1
 Parse Time: 0
 Bind Time: 0
@@ -22292,88 +22326,359 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Hash Exists Join ResultSet:
+Project-Restrict ResultSet (6):
 <filtered number of opens>
-<filtered rows seen from the left>
-<filtered rows seen from the right>
+<filtered rows seen>
 Rows filtered = 0
-<filtered rows returned>
+restriction = false
+projection = true
 	constructor time (milliseconds) = 0
 	open time (milliseconds) = 0
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
-Left result set:
-	Index Row to Base Row ResultSet for TENKTUP1:
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Hash Exists Join ResultSet:
 	<filtered number of opens>
-	<filtered rows seen>
-	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
-		constructor time (milliseconds) = 0
+	<filtered rows seen from the left>
+	<filtered rows seen from the right>
+	Rows filtered = 0
+	<filtered rows returned>
+		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-		Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+	Left result set:
+		Index Row to Base Row ResultSet for TENKTUP2:
 		<filtered number of opens>
 		<filtered rows seen>
-		Rows filtered = 0
-		Fetch Size = 1
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
-		scan information: 
-			Bit set of columns fetched=All
-			Number of columns fetched=2
-			Number of deleted rows visited=0
-			<filtered number of pages visited>
-			<filtered number of rows qualified>
-			<filtered number of rows visited>
-			Scan type=btree
-			Tree height=2
-			start position: 
+			Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+			<filtered number of opens>
+			<filtered rows seen>
+			Rows filtered = 0
+			Fetch Size = 1
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
 	None
-			stop position: 
+				stop position: 
 	>= on first 1 column(s).
 	Ordered null semantics on the following columns: 
 0 
-			qualifiers:
+				qualifiers:
 None
-Right result set:
-	Index Row to Base Row ResultSet for TENKTUP2:
+	Right result set:
+		Index Row to Base Row ResultSet for TENKTUP1:
+		<filtered number of opens>
+		<filtered rows seen>
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+			constructor time (milliseconds) = 0
+			open time (milliseconds) = 0
+			next time (milliseconds) = 0
+			close time (milliseconds) = 0
+			Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: 
+			<filtered number of opens>
+			Hash table size = 1000
+			Hash key is column number 0
+			<filtered rows seen>
+			Rows filtered = 0
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
+	None
+				stop position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+0 
+				scan qualifiers:
+None
+				next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+ij> commit;
+ij> -- 5% of rows from joining table
+get cursor c as
+	'select * from TENKTUP1, TENKTUP2
+	 where TENKTUP1.unique1 = TENKTUP2.unique1
+	 and TENKTUP2.unique1 < 500
+	 order by TENKTUP1.unique1';
+ij> close c;
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
+Statement Name: 
+	C
+Statement Text: 
+	select * from TENKTUP1, TENKTUP2
+	 where TENKTUP1.unique1 = TENKTUP2.unique1
+	 and TENKTUP2.unique1 < 500
+	 order by TENKTUP1.unique1
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text: 
+Project-Restrict ResultSet (6):
+<filtered number of opens>
+<filtered rows seen>
+Rows filtered = 0
+restriction = false
+projection = true
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Hash Exists Join ResultSet:
 	<filtered number of opens>
-	<filtered rows seen>
-	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+	<filtered rows seen from the left>
+	<filtered rows seen from the right>
+	Rows filtered = 0
+	<filtered rows returned>
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-		Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking: 
+	Left result set:
+		Index Row to Base Row ResultSet for TENKTUP2:
 		<filtered number of opens>
-		Hash table size = 100
-		Hash key is column number 0
 		<filtered rows seen>
-		Rows filtered = 0
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
-		scan information: 
-			Bit set of columns fetched=All
-			Number of columns fetched=2
-			Number of deleted rows visited=0
-			<filtered number of pages visited>
-			<filtered number of rows qualified>
-			<filtered number of rows visited>
-			Scan type=btree
-			Tree height=2
-			start position: 
+			Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+			<filtered number of opens>
+			<filtered rows seen>
+			Rows filtered = 0
+			Fetch Size = 1
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
 	None
-			stop position: 
+				stop position: 
 	>= on first 1 column(s).
 	Ordered null semantics on the following columns: 
 0 
-			scan qualifiers:
+				qualifiers:
 None
-			next qualifiers:
+	Right result set:
+		Index Row to Base Row ResultSet for TENKTUP1:
+		<filtered number of opens>
+		<filtered rows seen>
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+			constructor time (milliseconds) = 0
+			open time (milliseconds) = 0
+			next time (milliseconds) = 0
+			close time (milliseconds) = 0
+			Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: 
+			<filtered number of opens>
+			Hash table size = 500
+			Hash key is column number 0
+			<filtered rows seen>
+			Rows filtered = 0
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
+	None
+				stop position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+0 
+				scan qualifiers:
+None
+				next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+ij> commit;
+ij> -- 1% of rows from joining table
+get cursor c as
+	'select * from TENKTUP1, TENKTUP2
+	 where TENKTUP1.unique1 = TENKTUP2.unique1
+	 and TENKTUP2.unique1 < 100
+	 order by TENKTUP1.unique1';
+ij> close c;
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
+Statement Name: 
+	C
+Statement Text: 
+	select * from TENKTUP1, TENKTUP2
+	 where TENKTUP1.unique1 = TENKTUP2.unique1
+	 and TENKTUP2.unique1 < 100
+	 order by TENKTUP1.unique1
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text: 
+Project-Restrict ResultSet (6):
+<filtered number of opens>
+<filtered rows seen>
+Rows filtered = 0
+restriction = false
+projection = true
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Hash Exists Join ResultSet:
+	<filtered number of opens>
+	<filtered rows seen from the left>
+	<filtered rows seen from the right>
+	Rows filtered = 0
+	<filtered rows returned>
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+	Left result set:
+		Index Row to Base Row ResultSet for TENKTUP2:
+		<filtered number of opens>
+		<filtered rows seen>
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+			constructor time (milliseconds) = 0
+			open time (milliseconds) = 0
+			next time (milliseconds) = 0
+			close time (milliseconds) = 0
+			Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+			<filtered number of opens>
+			<filtered rows seen>
+			Rows filtered = 0
+			Fetch Size = 1
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
+	None
+				stop position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+0 
+				qualifiers:
+None
+	Right result set:
+		Index Row to Base Row ResultSet for TENKTUP1:
+		<filtered number of opens>
+		<filtered rows seen>
+		Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+			constructor time (milliseconds) = 0
+			open time (milliseconds) = 0
+			next time (milliseconds) = 0
+			close time (milliseconds) = 0
+			Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking: 
+			<filtered number of opens>
+			Hash table size = 100
+			Hash key is column number 0
+			<filtered rows seen>
+			Rows filtered = 0
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched=All
+				Number of columns fetched=2
+				Number of deleted rows visited=0
+				<filtered number of pages visited>
+				<filtered number of rows qualified>
+				<filtered number of rows visited>
+				Scan type=btree
+				Tree height=2
+				start position: 
+	None
+				stop position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+0 
+				scan qualifiers:
+None
+				next qualifiers:
 Column[0][0] Id: 0
 Operator: =
 Ordered nulls: false

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql?rev=787505&r1=787504&r2=787505&view=diff
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql Tue Jun 23 00:36:33 2009
@@ -2702,6 +2702,34 @@
 
 commit;
 
+-- force TENKTUP1 as the outermost join table to make sure 
+-- that no sorting is necessary. DERBY-3926
+get cursor c as
+	'select * from --DERBY-PROPERTIES joinOrder=FIXED
+	 TENKTUP1, TENKTUP2
+	 where TENKTUP1.unique1 = TENKTUP2.unique1
+	 and TENKTUP2.unique1 < 2500
+	 order by TENKTUP1.unique1';
+close c;
+
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+commit;
+
+-- This time, force TENKTUP2 as the outermost join table to make sure 
+-- that still no sorting is necessary. DERBY-3926
+get cursor c as
+	'select * from --DERBY-PROPERTIES joinOrder=FIXED
+	 TENKTUP2, TENKTUP1
+	 where TENKTUP1.unique1 = TENKTUP2.unique1
+	 and TENKTUP2.unique1 < 2500
+	 order by TENKTUP1.unique1';
+close c;
+
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+commit;
+
 -- 25% of rows from joining table
 get cursor c as
 	'select * from TENKTUP1, TENKTUP2



Mime
View raw message