db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r997924 - in /db/derby/code/branches/10.6: ./ java/engine/org/apache/derby/impl/sql/compile/ java/testing/org/apache/derbyTesting/functionTests/master/ java/testing/org/apache/derbyTesting/functionTests/suites/ java/testing/org/apache/derby...
Date Thu, 16 Sep 2010 20:57:48 GMT
Author: dag
Date: Thu Sep 16 20:57:47 2010
New Revision: 997924

URL: http://svn.apache.org/viewvc?rev=997924&view=rev
Log:
DERBY-4471 Left outer join reassociation rewrite gives wrong result

Merged back to 10.6 branch as
svn merge -c 990292 https://svn.apache.org/repos/asf/db/derby/code/trunk

Patch derby-4471-1d. This patch changes logic for determining if right
deep nested (left) outer join can reassiciated to left deep, i.e.

   OJ1                        OJ1
  /  \                        / \
 /    \                      /   t3
t1    OJ2          =>       /
      /  \                 OJ2
     /    \               /   \
    t2    t3           t1     t2 

The old behavior was both too liberal (this issue showed a query being
reordered erroneously), and too restrictive. The patch removes the
"too liberal" hole, and also opens up for some new valid cases.

For further details, refer to the JIRA issue and the code comments
especially the javadoc for the methods HalfOuterJoinNode#LOJ_reorderable and
HalfOuterJoinNode#isNullRejecting.

As a part of the patch, the old harness based test lojreorder.sql has been converted
into the JUnit based LojReorderTest equivalent.

For further details, refer to the JIRA issue and the code comments.


Added:
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LojReorderTest.java
      - copied unchanged from r990292, db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LojReorderTest.java
Removed:
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/master/lojreorder.out
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lojreorder.sql
Modified:
    db/derby/code/branches/10.6/   (props changed)
    db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/junit/JDBC.java

Propchange: db/derby/code/branches/10.6/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Thu Sep 16 20:57:47 2010
@@ -1,2 +1,2 @@
-/db/derby/code/trunk:938547,938796,938959,939231,940462,940469,941627,942031,942286,942476,942480,942587,944152,946794,948045,948069,951346,951366,952138,952237,952581,954344,954421,954544,954748,955001,955540,955634,956075,956234,956445,956569,956659,957260,958163,958522,958555,958618,958939,959550,962716,963206,963705,964115,965647,967304,980684,986689,986834,987539,989099
+/db/derby/code/trunk:938547,938796,938959,939231,940462,940469,941627,942031,942286,942476,942480,942587,944152,946794,948045,948069,951346,951366,952138,952237,952581,954344,954421,954544,954748,955001,955540,955634,956075,956234,956445,956569,956659,957260,958163,958522,958555,958618,958939,959550,962716,963206,963705,964115,965647,967304,980684,986689,986834,987539,989099,990292
 /db/derby/docs/trunk:954344

Modified: db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java?rev=997924&r1=997923&r2=997924&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java (original)
+++ db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java Thu Sep 16 20:57:47 2010
@@ -257,12 +257,48 @@ public class HalfOuterJoinNode extends J
 		rightFromTable.pushExpressions(noPredicates);
 	}
 
-	/**
-	 * This method determines if (1) the query is a LOJ, and (2) if the LOJ is a candidate for
-	 * reordering (i.e., linearization).  The condition for LOJ linearization is:
-	 * 1. only LOJ in the fromList, i.e., no INNER, no FULL JOINs, no ROJs
-	 * 2. ON clause must be equality join between left and right operands and in CNF (i.e., AND is allowed)
-	 */
+    /**
+     * This method recursively:
+     * <ul>
+     *    <li>determines if this part of the query tree is a compound OJ of
+     *            the shape required for reordering and if so,</li>
+     *    <li>does a reordering.</li>
+     * </ul>
+     * <pre>
+     *
+     *    OJ1  pT1T2                      OJ1  pT2T3
+     *   /  \                             / \
+     *  /    \                 can       /   t3
+     * t1    OJ2 pT2T3       reorder    /
+     *       /  \              to      OJ2  pT1T2
+     *      /    \                    /   \
+     *     t2    t3                  /     \
+     *                             t1     t2
+     *
+     * where pR1R2 is a null-rejecting predicate which references the schema
+     * of joinee R1 and R2, cf. terminology explanation in #isNullRejecting.
+     * <p/>
+     * OJ1 represents <em>this</em> before and after the reordering.
+     * </pre>
+     * <p/>
+     * The join predicates are assumed to be in CNF form.
+     * <p/>
+     * <em>Note:</em> Present implementation limitations
+     * <ul>
+     *   <li>Only left outer joins are considered, i.e. both OJs in diagram
+     *       above must be LOJ.</li>
+     *   <li>Top left side must be a base table (t1 above). The bottow right
+     *       side
+*       (t3 above) may be another OJ, so reordering can happen
+     *       recursively.</li>
+     * </ul>
+     *
+     * @param numTables number of tables involved (needed to right size the
+     *                  bitmaps)
+     * @return boolean true if any reordering took place at this level or deeper
+     *                 so caller can know whether rebinding may be necessary
+     * @throws StandardException standard error policy
+     */
 	public boolean LOJ_reorderable(int numTables)
 		throws StandardException
 	{
@@ -287,12 +323,12 @@ public class HalfOuterJoinNode extends J
 		// Redundantly normalize the ON predicate (it will also be called in preprocess()).
 		super.normExpressions();
 
-		// This is a very simple LOJ of base tables. Do nothing.
+        // This is a very simple OJ of base tables. Do nothing.
 		if (logicalLeftResultSet instanceof FromBaseTable &&
 			logicalRightResultSet instanceof FromBaseTable)
 			return anyChange;
 
-		// Recursively check if we can reordering LOJ, and build the table
+        // Recursively check if we can reordering OJ, and build the table
 		// references. Note that joins may have been reordered and therefore the
 		// table references need to be recomputed.
 		if (logicalLeftResultSet instanceof HalfOuterJoinNode)
@@ -300,7 +336,7 @@ public class HalfOuterJoinNode extends J
 			anyChange =	((HalfOuterJoinNode)logicalLeftResultSet).LOJ_reorderable(numTables) || anyChange;
 		}
 		else if (!(logicalLeftResultSet instanceof FromBaseTable))
-		{// left operand must be either a base table or another LOJ
+        {// left operand must be either a base table or another OJ
 			// In principle, we don't care about the left operand.  However, we
 			// need to re-bind the resultColumns.  If the left operand is a
 			// view, we may have to re-bind the where clause etc...
@@ -315,11 +351,11 @@ public class HalfOuterJoinNode extends J
 			anyChange = ((HalfOuterJoinNode)logicalRightResultSet).LOJ_reorderable(numTables) || anyChange;
 		}
 		else if (!(logicalRightResultSet instanceof FromBaseTable))
-		{// right operand must be either a base table or another LOJ
+        {// right operand must be either a base table or another OJ
 			return anyChange;
 		}
 
-		// It is much easier to do LOJ reordering if there is no ROJ.
+        // It is much easier to do OJ reordering if there is no ROJ.
 		// However, we ran into some problem downstream when we transform an ROJ
 		// into LOJ -- transformOuterJoin() didn't expect ROJ to be transformed
 		// into LOJ alread.  So, we skip optimizing ROJ at the moment.
@@ -329,11 +365,12 @@ public class HalfOuterJoinNode extends J
 			return LOJ_bindResultColumns(anyChange);
 		}
 
-		// Build the data structure for testing/doing LOJ reordering.
-		// Fill in the table references on row-preserving and null-producing sides.
-		// It may be possible that either operand is a complex view.
-		JBitSet				NPReferencedTableMap; // Null-producing
-		JBitSet				RPReferencedTableMap; // Row-preserving
+        // Build the data structure for testing/doing OJ reordering.  Fill in
+        // the table references on row-preserving and null-producing sides.  It
+        // may be possible that either operand is a complex view.
+
+        JBitSet RPReferencedTableMap; // Row-preserving
+        JBitSet NPReferencedTableMap; // Null-producing
 
 		RPReferencedTableMap = logicalLeftResultSet.LOJgetReferencedTables(numTables);
 		NPReferencedTableMap = logicalRightResultSet.LOJgetReferencedTables(numTables);
@@ -343,183 +380,256 @@ public class HalfOuterJoinNode extends J
 		{
 			return LOJ_bindResultColumns(anyChange);
 		}
-			
-		// Check if the predicate is equality predicate in CNF (i.e., AND only)
-		// and left/right column references must come from either operand.
-		// That is, we don't allow:
-		// 1. A=A
-		// 2. 1=1
-		// 3. B=C where both B and C are either from left or right operand.
-
-		// we probably need to make the joinClause "left-deep" so that we can
-		// walk it easier.
-		BinaryRelationalOperatorNode equals;
-		ValueNode leftCol;
-		ValueNode rightCol;
-		AndNode   and;
-		ValueNode left;
-		ValueNode vn = joinClause;
-		while (vn instanceof AndNode)
-		{
-			and = (AndNode) vn;
-			left = and.getLeftOperand();
-
-			// Make sure that this is an equijoin of the form "C = D" where C
-			// and D references tables from both left and right operands.
-			if (left instanceof RelationalOperator &&
-				((ValueNode)left).isBinaryEqualsOperatorNode())
-			{
-				equals = (BinaryRelationalOperatorNode) left;
-				leftCol = equals.getLeftOperand();
-				rightCol = equals.getRightOperand();
-
-				if (!( leftCol instanceof ColumnReference && rightCol instanceof ColumnReference))
-					return LOJ_bindResultColumns(anyChange);
-
-				boolean refCheck = false;
-				boolean leftOperandCheck = false;
-
-				if (RPReferencedTableMap.get(((ColumnReference)leftCol).getTableNumber()))
-				{
-					refCheck = true;
-					leftOperandCheck = true;
-				}
-				else if (NPReferencedTableMap.get(((ColumnReference)leftCol).getTableNumber()))
-				{
-					refCheck = true;
-				}
-
-				if (refCheck == false)
-					return LOJ_bindResultColumns(anyChange);
-
-				refCheck = false;
-				if (leftOperandCheck == false && RPReferencedTableMap.get(((ColumnReference)rightCol).getTableNumber()))
-				{
-					refCheck = true;
-				}
-				else if (leftOperandCheck == true && NPReferencedTableMap.get(((ColumnReference)rightCol).getTableNumber()))
-				{
-					refCheck = true;
-				}
-
-				if (refCheck == false)
-					return LOJ_bindResultColumns(anyChange);
-			}
-			else return LOJ_bindResultColumns(anyChange); //  get out of here
-
-			vn = and.getRightOperand();
-		}
 
-		// Check if the logical right resultset is a composite inner and as such
-		// that this current LOJ can be pushed through it.
-		boolean       push = false;
-		// logical right operand is another LOJ... so we may be able to push the
-		// join
-		if (logicalRightResultSet instanceof HalfOuterJoinNode)
-		{
-			// get the Null-producing operand of the child
-			JBitSet  logicalNPRefTableMap = ((HalfOuterJoinNode)logicalRightResultSet).LOJgetNPReferencedTables(numTables);
 
-			// does the current LOJ join predicate reference
-			// logicalNPRefTableMap?  If not, we can push the current
-			// join.
-			vn = joinClause;
-			push = true;
-			while (vn instanceof AndNode)
-			{
-				and = (AndNode) vn;
-				left = and.getLeftOperand();
-				equals = (BinaryRelationalOperatorNode) left;
-				leftCol = equals.getLeftOperand();
-				rightCol = equals.getRightOperand();
+        // Check if logical right operand is another OJ... so we may be able
+        // to push the join.
+        if (logicalRightResultSet instanceof HalfOuterJoinNode)
+		{
+            // Get the row-preserving map of the  child OJ
+            JBitSet  nestedChildOJRPRefTableMap =
+                ((HalfOuterJoinNode)logicalRightResultSet).
+                LOJgetRPReferencedTables(numTables);
+
+            // Checks that top has p(t1,t2)
+            if ( ! isNullRejecting(
+                         joinClause,
+                         RPReferencedTableMap,
+                         nestedChildOJRPRefTableMap)) {
+                // No, give up.
+                return LOJ_bindResultColumns(anyChange);
+            }
+
+            // Get the null-producing map of the child OJ
+            JBitSet  nestedChildOJNPRefTableMap =
+                ((HalfOuterJoinNode)logicalRightResultSet).
+                LOJgetNPReferencedTables(numTables);
+
+            // Checks that right child has p(t2,t3)
+            if ( isNullRejecting(
+                         ((HalfOuterJoinNode)logicalRightResultSet).joinClause,
+                         nestedChildOJRPRefTableMap,
+                         nestedChildOJNPRefTableMap)) {
+                // Push the current OJ into the next level For safety, check
+                // the JoinNode data members: they should null or empty list
+                // before we proceed.
+                if (super.subqueryList.size() != 0 ||
+                    ((JoinNode)logicalRightResultSet).
+                        subqueryList.size() != 0 ||
+                    super.joinPredicates.size() != 0 ||
+                    ((JoinNode)logicalRightResultSet).
+                        joinPredicates.size() != 0 ||
+                    super.usingClause != null ||
+                    ((JoinNode)logicalRightResultSet).
+                        usingClause != null) {
+
+                    return LOJ_bindResultColumns(anyChange); //  get out of here
+                }
+                anyChange = true; // we are reordering the OJs.
+
+                ResultSetNode tmp = logicalLeftResultSet;
+                ResultSetNode LChild, RChild;
+
+                //            this OJ
+                //            /      \
+                //  logicalLeftRS   LogicalRightRS
+                //                   /     \
+                //                LChild  RChild
+                // becomes
+                //
+                //               this OJ
+                //               /      \
+                //     LogicalRightRS   RChild
+                //           /     \
+                // logicalLeftRS LChild <<< we need to be careful about this
+                //                          order as the "LogicalRightRS
+                //                          may be a ROJ
+                //
+
+                // handle the lower level OJ node
+                LChild = ((HalfOuterJoinNode)logicalRightResultSet).
+                    leftResultSet;
+                RChild = ((HalfOuterJoinNode)logicalRightResultSet).
+                    rightResultSet;
+
+                ((HalfOuterJoinNode)logicalRightResultSet).
+                    rightResultSet = LChild;
+                ((HalfOuterJoinNode)logicalRightResultSet).
+                    leftResultSet  = tmp;
+
+                // switch the ON clause
+                {
+                    ValueNode vn = joinClause;
+                    joinClause =
+                        ((HalfOuterJoinNode)logicalRightResultSet).joinClause;
+                    ((HalfOuterJoinNode)logicalRightResultSet).joinClause = vn;
+                }
+
+                // No need to switch HalfOuterJoinNode data members for now
+                // because we are handling only OJ.
+                // boolean local_rightOuterJoin = rightOuterJoin;
+                // boolean local_transformed    = transformed;
+                // rightOuterJoin = ((HalfOuterJoinNode)logicalRightResultSet).
+                //     rightOuterJoin;
+                // transformed = ((HalfOuterJoinNode)logicalRightResultSet).
+                //     transformed;
+                // ((HalfOuterJoinNode)logicalRightResultSet).rightOuterJoin =
+                //     local_rightOuterJoin;
+                // ((HalfOuterJoinNode)logicalRightResultSet).transformed =
+                //     local_transformed;
+
+                FromList localFromList = (FromList) getNodeFactory().getNode(
+                    C_NodeTypes.FROM_LIST,
+                    getNodeFactory().doJoinOrderOptimization(),
+                    getContextManager());
+
+                // switch OJ nodes: by handling the current OJ node
+                leftResultSet  = logicalRightResultSet;
+                rightResultSet = RChild;
+
+                // rebuild the result columns and re-bind column references
+                ((HalfOuterJoinNode)leftResultSet).resultColumns = null;
+                 // localFromList is empty:
+                ((JoinNode)leftResultSet).bindResultColumns(localFromList);
+
+                // left operand must be another OJ, so recurse.
+                boolean localChange = ((HalfOuterJoinNode)leftResultSet).
+                    LOJ_reorderable(numTables);
+            }
+        }
 
-				if (logicalNPRefTableMap.get(((ColumnReference)leftCol).getTableNumber()) ||
-					logicalNPRefTableMap.get(((ColumnReference)rightCol).getTableNumber()))
-				{
-					push = false;
-					break;
-				}
+        return LOJ_bindResultColumns(anyChange);
+    }
 
-				vn = and.getRightOperand();
-			}
-		}
 
-		// Push the current LOJ into the next level
-		if (push)
-		{
-			// For safety, check the JoinNode data members: they should null or
-			// empty list before we proceed.
-			if (super.subqueryList.size() != 0 ||
-				((JoinNode)logicalRightResultSet).subqueryList.size() != 0 ||
-				super.joinPredicates.size() != 0 ||
-				((JoinNode)logicalRightResultSet).joinPredicates.size() != 0 ||
-				super.usingClause != null ||
-				((JoinNode)logicalRightResultSet).usingClause != null)
-				return LOJ_bindResultColumns(anyChange); //  get out of here
-
-			anyChange = true; // we are reordering the LOJs.
-
-			ResultSetNode tmp = logicalLeftResultSet;
-			ResultSetNode LChild, RChild;
-
-			//            this LOJ
-			//            /      \
-			//  logicalLeftRS   LogicalRightRS
-			//                   /     \
-			//                LChild  RChild
-			// becomes
-			//
-			//               this LOJ
-			//               /      \
-			//     LogicalRightRS   RChild
-			//           /     \
-			// logicalLeftRS   LChild  <<<  we need to be careful about this order
-			//                              as the "LogicalRightRS may be a ROJ
-			//
-
-			// handle the lower level LOJ node
-			LChild = ((HalfOuterJoinNode)logicalRightResultSet).leftResultSet;
-			RChild = ((HalfOuterJoinNode)logicalRightResultSet).rightResultSet;
-
-			((HalfOuterJoinNode)logicalRightResultSet).rightResultSet = LChild;
-			((HalfOuterJoinNode)logicalRightResultSet).leftResultSet  = tmp;
-
-			// switch the ON clause
-			vn = joinClause;
-			joinClause   = ((HalfOuterJoinNode)logicalRightResultSet).joinClause;
-			((HalfOuterJoinNode)logicalRightResultSet).joinClause = vn;
-
-			// No need to switch HalfOuterJoinNode data members for now because
-			// we are handling only LOJ.
-			// boolean local_rightOuterJoin = rightOuterJoin;
-			// boolean local_transformed    = transformed;
-			// rightOuterJoin = ((HalfOuterJoinNode)logicalRightResultSet).rightOuterJoin;
-			// transformed    = ((HalfOuterJoinNode)logicalRightResultSet).transformed;
-			// ((HalfOuterJoinNode)logicalRightResultSet).rightOuterJoin = local_rightOuterJoin;
-			// ((HalfOuterJoinNode)logicalRightResultSet).transformed    = local_transformed;
 
-			FromList localFromList = (FromList) getNodeFactory().getNode(
-																		 C_NodeTypes.FROM_LIST,
-																		 getNodeFactory().doJoinOrderOptimization(),
-																		 getContextManager());
+    /**
+     * Tests pRiRj in the sense of Galindo-Legaria et al: <em>Outerjoin
+     * Simplification and Reordering for Query Optimization</em>, ACM
+     * Transactions on Database Systems, Vol. 22, No. 1, March 1997, Pages
+     * 43-74:
+     * <quote>
+     *  "The set of attributes referenced by a predicate p is called the schema
+     *  of p, and denoted sch(p). As a notational convention, we annotate
+     *  predicates to reflect their schema. If sch(p) includes attributes of
+     *  both Ri, Rj and only those relations, we can write the predicate as
+     *  pRiRj.
+     * </quote>
+     *
+     * If a null-valued column is compared in a predicate that
+     * contains no OR connectives, the predicate evaluates to undefined, and
+     * the tuple is rejected. The relops satisfy this criterion.
+     * <p/>
+     * To simplify analysis, we only accept predicates of the form:
+     * <pre>
+     * X relop Y [and .. and X-n relop Y-n]
+     * </pre>
+     *
+     * At least one of the relops should reference both {@code leftTableMap}
+     * and {@code rightTableMap}, so that we know that sch(p) includes
+     * attributes of both Ri, Rj. I.e.
+     *
+     * <p/>
+     * {@code X} should be a table in {@code leftTableMap}, and
+     * {@code Y} should be a table in {@code rightTableMap}.
+     * <p/>
+     * <b>or</b>
+     * {@code X} should be a table in {@code rightTableMap}, and
+     * {@code Y} should be a table in {@code leftTableMap}.
+     *
+     * @param joinClause The join clause (i.e. predicate) we want to check
+     * @param leftTableMap a bit map representing the tables expected for the
+     *                     predicate (logical left)
+     * @param rightTableMap a bit map representing the tables expected for the
+     *                      predicate (logical right)
+     * @return true if the {@code joinClause} has at least one relop that
+     *              references both {@code leftTableMap} and {@code
+     *              rightTableMap}
+     * @throws StandardException standard exception policy
+     */
 
-			// switch LOJ nodes: by handling the current LOJ node
-			leftResultSet  = logicalRightResultSet;
-			rightResultSet = RChild;
-
-			// rebuild the result columns and re-bind column references
-			((HalfOuterJoinNode)leftResultSet).resultColumns = null;
-			((JoinNode)leftResultSet).bindResultColumns(localFromList); // localFromList is empty
+private boolean isNullRejecting (
+        ValueNode joinClause,
+        JBitSet leftTableMap,
+        JBitSet rightTableMap)
+        throws StandardException {
+
+        ValueNode vn = joinClause;
+        boolean foundPred = false;
+
+        while (vn != null) {
+            AndNode andNode = null;
+
+            if (vn instanceof AndNode) {
+                andNode = (AndNode)vn;
+                vn = andNode.getLeftOperand();
+            }
+
+            if (vn instanceof BinaryRelationalOperatorNode) {
+
+                BinaryRelationalOperatorNode relop =
+                    (BinaryRelationalOperatorNode)vn;
+                ValueNode leftCol = relop.getLeftOperand();
+                ValueNode rightCol = relop.getRightOperand();
+
+                boolean leftFound = false;
+                boolean rightFound = false;
+
+                if (leftCol instanceof ColumnReference) {
+                    if (leftTableMap.get(
+                                ((ColumnReference)leftCol).getTableNumber())) {
+
+                        leftFound = true;
+
+                    } else if (
+                        rightTableMap.get(
+                            ((ColumnReference)leftCol).getTableNumber())) {
+
+                        rightFound = true;
+                    } else {
+                        // references unexpected table
+                        return false;
+                    }
+
+                }
+
+                if (rightCol instanceof ColumnReference) {
+                    if (leftTableMap.get(
+                                ((ColumnReference)rightCol).getTableNumber())) {
+                        leftFound = true;
+
+                    } else if (rightTableMap.get(
+                                       ((ColumnReference)rightCol).
+                                       getTableNumber())) {
+                        rightFound = true;
+                    } else {
+                        // references unexpected table, sch(p) is wrong
+                        return false;
+                    }
+                }
+
+
+                if (leftFound && rightFound) {
+                    foundPred = true; // sch(p) covers both R1 and R2
+                }
+            } else if ((vn instanceof BooleanConstantNode) && foundPred) {
+                // OK, simple predicate which covers both R1 and R2 found
+            } else {
+                // reject other operators, e.g. OR
+                return false;
+            }
+
+            if (andNode != null) {
+                vn = andNode.getRightOperand();
+            } else {
+                vn = null;
+            }
+        }
 
-			// left operand must be another LOJ, try again until a fixpoint
-			boolean localChange = ((HalfOuterJoinNode)leftResultSet).LOJ_reorderable(numTables);
+        return foundPred;
+    }
 
-			// rebuild the result columns and re-bind column references for 'this'
-			return LOJ_bindResultColumns(anyChange);
-		}
 
-		return LOJ_bindResultColumns(anyChange);
-	}
 
 	// This method re-binds the result columns which may be referenced in the ON
 	// clause in this node.
@@ -792,4 +902,14 @@ public class HalfOuterJoinNode extends J
 		else
 			return (JBitSet) rightResultSet.LOJgetReferencedTables(numTables);
 	}
+
+    // return the row-preserving table references
+    public JBitSet LOJgetRPReferencedTables(int numTables)
+                throws StandardException
+    {
+        if (rightOuterJoin && !transformed)
+            return (JBitSet) rightResultSet.LOJgetReferencedTables(numTables);
+        else
+            return (JBitSet) leftResultSet.LOJgetReferencedTables(numTables);
+    }
 }

Modified: db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=997924&r1=997923&r2=997924&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Thu Sep 16 20:57:47 2010
@@ -16,7 +16,6 @@ lang/isolationLevels.sql
 lang/joinDeadlock.sql
 lang/langUnitTests.sql
 lang/lockTable.sql
-lang/lojreorder.sql
 lang/maxMemPerTab.java
 lang/nestedCommit.sql
 lang/openScans.sql

Modified: db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java?rev=997924&r1=997923&r2=997924&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java (original)
+++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OuterJoinTest.java Thu Sep 16 20:57:47 2010
@@ -1,4 +1,3 @@
-
 /*
 Derby - Class org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest
 
@@ -36,6 +35,7 @@ import org.apache.derbyTesting.junit.Bas
 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
 import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
 import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derby.iapi.services.sanity.SanityManager;
 
 public final class OuterJoinTest extends BaseJDBCTestCase
 {
@@ -52,7 +52,6 @@ public final class OuterJoinTest extends
     {
         //Add the test case into the test suite
         TestSuite suite = new TestSuite("OuterJoinTest Test");
-        suite.addTest(TestConfiguration.defaultSuite(OuterJoinTest.class));
         return TestConfiguration.defaultSuite(OuterJoinTest.class);
     }
 
@@ -2174,7 +2173,7 @@ public final class OuterJoinTest extends
         rs.close();
     }
 
-    public void TestdDerby5659() throws SQLException
+    public void testdDerby5659() throws SQLException
     {
         // Test fix for bug 5659
 
@@ -2392,6 +2391,532 @@ public final class OuterJoinTest extends
     }
 
 
+    /**
+     * Test that left outer join reordering works as expected.  This fixture is
+     * the first repro mentioned for this issue, DERBY-4471. It checks for
+     * correct results, and should fail prior to applying issue patch due to
+     * erroneous reordering.
+     * <p/>
+     * The results asserted in these tests for DERBY-4471 are the same as Derby
+     * gives without the reordering enabled and the same as Postgres yields.
+     * <p/>
+     * Note that the patch also opens up for correct reorderings of some
+     * queries in the seen in the original lojreorder.sql test (converted to
+     * JUnit as LojReorderTest in this patch) that did <em>not</em> happen
+     * earlier, in addition to denying the wrong ones documented in DERBY-4471
+     * and tested below. These new reorderings are tested, cf the comments in
+     * LojReorderTest. Look for the string 4471.
+
+     */
+    public void testDerby_4471a() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+
+        s.executeUpdate("create table r(c1 char(1))");
+        s.executeUpdate("create table s(c1 char(1), c2 char(1))");
+        s.executeUpdate("create table t(c1 char(1))");
+
+        s.executeUpdate("insert into r values 'a'");
+        s.executeUpdate("insert into s values ('b', default)");
+        s.executeUpdate("insert into t values ('c')");
+
+        ResultSet rs = s.executeQuery(
+            "select * from r left outer join (s left outer join t " +
+            "                                 on s.c2=t.c1 or s.c2 is null)" +
+            "                on r.c1=s.c1");
+        JDBC.assertFullResultSet(rs, new String[][]{{"a", null, null, null}});
+    }
+
+    /**
+     * Test that left outer join reordering works as expected.  The schema here
+     * is taken from the another issue which also saw this error;
+     * DERBY-4712/DERBY-4736. This fixture checks for correct results. The
+     * first query should fail prior to applying issue patch due to erroneous
+     * reordering, the second is OK to reorder (and would be reordered also
+     * prior to the patch).
+     */
+    public void testDerby_4471b() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+
+        s.executeUpdate("create table t0(x int)");
+        s.executeUpdate("create table t1(x int)");
+        s.executeUpdate("create table t2(x int)");
+        s.executeUpdate("create table t3(x int)");
+        s.executeUpdate("create table t4(x int)");
+        s.executeUpdate("insert into t4 values(0)");
+        s.executeUpdate("insert into t4 values(1)");
+        s.executeUpdate("insert into t4 values(2)");
+        s.executeUpdate("insert into t4 values(3)");
+        s.executeUpdate("create table t5(x int)");
+        s.executeUpdate("insert into t5 values(0)");
+        s.executeUpdate("insert into t5 values(1)");
+        s.executeUpdate("insert into t5 values(2)");
+        s.executeUpdate("insert into t5 values(3)");
+        s.executeUpdate("insert into t5 values(4)");
+        s.executeUpdate("create table t6(x int)");
+        s.executeUpdate("insert into t6 values(0)");
+        s.executeUpdate("insert into t6 values(1)");
+        s.executeUpdate("insert into t6 values(2)");
+        s.executeUpdate("insert into t6 values(3)");
+        s.executeUpdate("insert into t6 values(4)");
+        s.executeUpdate("insert into t6 values(5)");
+        s.executeUpdate("create table t7(x int)");
+        s.executeUpdate("insert into t7 values(0)");
+        s.executeUpdate("insert into t7 values(1)");
+        s.executeUpdate("insert into t7 values(2)");
+        s.executeUpdate("insert into t7 values(3)");
+        s.executeUpdate("insert into t7 values(4)");
+        s.executeUpdate("insert into t7 values(5)");
+        s.executeUpdate("insert into t7 values(6)");
+        s.executeUpdate("create table t8(x int)");
+        s.executeUpdate("insert into t8 values(0)");
+        s.executeUpdate("insert into t8 values(1)");
+        s.executeUpdate("insert into t8 values(2)");
+        s.executeUpdate("insert into t8 values(3)");
+        s.executeUpdate("insert into t8 values(4)");
+        s.executeUpdate("insert into t8 values(5)");
+        s.executeUpdate("insert into t8 values(6)");
+        s.executeUpdate("insert into t8 values(7)");
+        s.executeUpdate("create table t9(x int)");
+        s.executeUpdate("insert into t9 values(0)");
+        s.executeUpdate("insert into t9 values(1)");
+        s.executeUpdate("insert into t9 values(2)");
+        s.executeUpdate("insert into t9 values(3)");
+        s.executeUpdate("insert into t9 values(4)");
+        s.executeUpdate("insert into t9 values(5)");
+        s.executeUpdate("insert into t9 values(6)");
+        s.executeUpdate("insert into t9 values(7)");
+        s.executeUpdate("insert into t9 values(8)");
+        s.executeUpdate("insert into t0 values(1)");
+        s.executeUpdate("insert into t1 values(2)");
+        s.executeUpdate("insert into t0 values(3)");
+        s.executeUpdate("insert into t1 values(3)");
+        s.executeUpdate("insert into t2 values(4)");
+        s.executeUpdate("insert into t0 values(5)");
+        s.executeUpdate("insert into t2 values(5)");
+        s.executeUpdate("insert into t1 values(6)");
+        s.executeUpdate("insert into t2 values(6)");
+        s.executeUpdate("insert into t0 values(7)");
+        s.executeUpdate("insert into t1 values(7)");
+        s.executeUpdate("insert into t2 values(7)");
+        s.executeUpdate("insert into t3 values(8)");
+        s.executeUpdate("insert into t0 values(9)");
+        s.executeUpdate("insert into t3 values(9)");
+        s.executeUpdate("insert into t1 values(10)");
+        s.executeUpdate("insert into t3 values(10)");
+        s.executeUpdate("insert into t0 values(11)");
+        s.executeUpdate("insert into t1 values(11)");
+        s.executeUpdate("insert into t3 values(11)");
+        s.executeUpdate("insert into t2 values(12)");
+        s.executeUpdate("insert into t3 values(12)");
+        s.executeUpdate("insert into t0 values(13)");
+        s.executeUpdate("insert into t2 values(13)");
+        s.executeUpdate("insert into t3 values(13)");
+        s.executeUpdate("insert into t1 values(14)");
+        s.executeUpdate("insert into t2 values(14)");
+        s.executeUpdate("insert into t3 values(14)");
+        s.executeUpdate("insert into t0 values(15)");
+        s.executeUpdate("insert into t1 values(15)");
+        s.executeUpdate("insert into t2 values(15)");
+        s.executeUpdate("insert into t3 values(15)");
+
+        // The theory exposed in Galindo-Legaria, C. & Rosenthal, A.:
+        // "Outerjoin simplification and reordering for query optimization",
+        // ACM Transactions on Database Systems, Vol 22, No 1, March 1997 uses
+        // two assumption for its general case which involves full outer joins
+        // as well: no duplicate rows and no rows consisting of only nulls. We
+        // cannot make that assumption, this being SQL, but for our restricted
+        // OJ rewrites, this should work ok, so we throw in both into the test
+        // mix:
+
+        // Make duplicates
+        s.executeUpdate("insert into t2 select * from t2");
+        s.executeUpdate("insert into t3 select * from t3");
+        s.executeUpdate("insert into t4 select * from t4");
+
+        // Insert full NULL tuples
+        s.executeUpdate("insert into t2 values cast(null as int)");
+        s.executeUpdate("insert into t3 values cast(null as int)");
+        s.executeUpdate("insert into t4 values cast(null as int)");
+
+        // This query was wrong prior to DERBY-4471: 1=1 is not allowed, since
+        // the inner join predicate does not reference T3 and T4 as required.
+        ResultSet rs = s.executeQuery(
+            "SELECT * FROM (T2 LEFT JOIN (T3 left outer JOIN T4 " +
+            "                                    ON 1=1) " +
+            "                      ON T2.X = T3.X)");
+
+        JDBC.assertUnorderedResultSet(
+            rs,
+            new String[][] {
+                {"4", null, null},
+                {"5", null, null},
+                {"6", null, null},
+                {"7", null, null},
+                {"12", "12", "0"},
+                {"12", "12", "1"},
+                {"12", "12", "2"},
+                {"12", "12", "3"},
+                {"12", "12", "0"},
+                {"12", "12", "1"},
+                {"12", "12", "2"},
+                {"12", "12", "3"},
+                {"12", "12", null},
+                {"12", "12", "0"},
+                {"12", "12", "1"},
+                {"12", "12", "2"},
+                {"12", "12", "3"},
+                {"12", "12", "0"},
+                {"12", "12", "1"},
+                {"12", "12", "2"},
+                {"12", "12", "3"},
+                {"12", "12", null},
+                {"13", "13", "0"},
+                {"13", "13", "1"},
+                {"13", "13", "2"},
+                {"13", "13", "3"},
+                {"13", "13", "0"},
+                {"13", "13", "1"},
+                {"13", "13", "2"},
+                {"13", "13", "3"},
+                {"13", "13", null},
+                {"13", "13", "0"},
+                {"13", "13", "1"},
+                {"13", "13", "2"},
+                {"13", "13", "3"},
+                {"13", "13", "0"},
+                {"13", "13", "1"},
+                {"13", "13", "2"},
+                {"13", "13", "3"},
+                {"13", "13", null},
+                {"14", "14", "0"},
+                {"14", "14", "1"},
+                {"14", "14", "2"},
+                {"14", "14", "3"},
+                {"14", "14", "0"},
+                {"14", "14", "1"},
+                {"14", "14", "2"},
+                {"14", "14", "3"},
+                {"14", "14", null},
+                {"14", "14", "0"},
+                {"14", "14", "1"},
+                {"14", "14", "2"},
+                {"14", "14", "3"},
+                {"14", "14", "0"},
+                {"14", "14", "1"},
+                {"14", "14", "2"},
+                {"14", "14", "3"},
+                {"14", "14", null},
+                {"15", "15", "0"},
+                {"15", "15", "1"},
+                {"15", "15", "2"},
+                {"15", "15", "3"},
+                {"15", "15", "0"},
+                {"15", "15", "1"},
+                {"15", "15", "2"},
+                {"15", "15", "3"},
+                {"15", "15", null},
+                {"15", "15", "0"},
+                {"15", "15", "1"},
+                {"15", "15", "2"},
+                {"15", "15", "3"},
+                {"15", "15", "0"},
+                {"15", "15", "1"},
+                {"15", "15", "2"},
+                {"15", "15", "3"},
+                {"15", "15", null},
+                {"4", null, null},
+                {"5", null, null},
+                {"6", null, null},
+                {"7", null, null},
+                {"12", "12", "0"},
+                {"12", "12", "1"},
+                {"12", "12", "2"},
+                {"12", "12", "3"},
+                {"12", "12", "0"},
+                {"12", "12", "1"},
+                {"12", "12", "2"},
+                {"12", "12", "3"},
+                {"12", "12", null},
+                {"12", "12", "0"},
+                {"12", "12", "1"},
+                {"12", "12", "2"},
+                {"12", "12", "3"},
+                {"12", "12", "0"},
+                {"12", "12", "1"},
+                {"12", "12", "2"},
+                {"12", "12", "3"},
+                {"12", "12", null},
+                {"13", "13", "0"},
+                {"13", "13", "1"},
+                {"13", "13", "2"},
+                {"13", "13", "3"},
+                {"13", "13", "0"},
+                {"13", "13", "1"},
+                {"13", "13", "2"},
+                {"13", "13", "3"},
+                {"13", "13", null},
+                {"13", "13", "0"},
+                {"13", "13", "1"},
+                {"13", "13", "2"},
+                {"13", "13", "3"},
+                {"13", "13", "0"},
+                {"13", "13", "1"},
+                {"13", "13", "2"},
+                {"13", "13", "3"},
+                {"13", "13", null},
+                {"14", "14", "0"},
+                {"14", "14", "1"},
+                {"14", "14", "2"},
+                {"14", "14", "3"},
+                {"14", "14", "0"},
+                {"14", "14", "1"},
+                {"14", "14", "2"},
+                {"14", "14", "3"},
+                {"14", "14", null},
+                {"14", "14", "0"},
+                {"14", "14", "1"},
+                {"14", "14", "2"},
+                {"14", "14", "3"},
+                {"14", "14", "0"},
+                {"14", "14", "1"},
+                {"14", "14", "2"},
+                {"14", "14", "3"},
+                {"14", "14", null},
+                {"15", "15", "0"},
+                {"15", "15", "1"},
+                {"15", "15", "2"},
+                {"15", "15", "3"},
+                {"15", "15", "0"},
+                {"15", "15", "1"},
+                {"15", "15", "2"},
+                {"15", "15", "3"},
+                {"15", "15", null},
+                {"15", "15", "0"},
+                {"15", "15", "1"},
+                {"15", "15", "2"},
+                {"15", "15", "3"},
+                {"15", "15", "0"},
+                {"15", "15", "1"},
+                {"15", "15", "2"},
+                {"15", "15", "3"},
+                {"15", "15", null},
+                {null, null, null}});
+
+        s.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+        // This one *can* be reordered
+        rs = s.executeQuery(
+            "SELECT * FROM (T2 LEFT JOIN (T3 left outer JOIN T4 " +
+            "                                    ON t3.x=t4.x) " +
+            "                      ON T2.X = T3.X)");
+
+        JDBC.assertUnorderedResultSet(
+            rs,
+            new String[][] {
+                {"4", null, null},
+                {"4", null, null},
+                {"5", null, null},
+                {"5", null, null},
+                {"6", null, null},
+                {"6", null, null},
+                {"7", null, null},
+                {"7", null, null},
+                {"12", "12", null},
+                {"12", "12", null},
+                {"12", "12", null},
+                {"12", "12", null},
+                {"13", "13", null},
+                {"13", "13", null},
+                {"13", "13", null},
+                {"13", "13", null},
+                {"14", "14", null},
+                {"14", "14", null},
+                {"14", "14", null},
+                {"14", "14", null},
+                {"15", "15", null},
+                {"15", "15", null},
+                {"15", "15", null},
+                {"15", "15", null},
+                {null, null, null}});
+
+        JDBC.checkPlan(s,
+                  new String[] {
+                      "Hash Left Outer Join ResultSet:",
+                      "Left result set:",
+                      "_Hash Left Outer Join ResultSet:"});
+    }
+
+    /**
+     * Check that ordering actually takes place as well as checking that the
+     * results are correct.
+     */
+    public void testDerby_4471c() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+
+        s.execute("create table t1(c1 int)");
+        s.execute("create table t2(c1 int)");
+        s.execute("create table t3(c1 int)");
+
+        s.execute("insert into t1 values 1, 2, 2, 3, 4");
+        s.execute("insert into t2 values 1, 3, 3, 5, 6");
+        s.execute("insert into t3 values 2, 3, 5, 5, 7");
+
+        s.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+        PreparedStatement ps = prepareStatement(
+            "select * from t1 left outer join " +
+            "     (t2 left outer join t3 on t2.c1 = t3.c1)" +
+            "   on t1.c1 = t2.c1");
+
+        ResultSet rs = ps.executeQuery();
+
+        JDBC.assertUnorderedResultSet(
+            rs,
+            new String[][] {
+                {"1", "1", null},
+                {"2", null, null},
+                {"2", null, null},
+                {"3", "3", "3"},
+                {"3", "3", "3"},
+                {"4", null, null}});
+
+        JDBC.checkPlan(s,
+                  new String[] {
+                      "Hash Left Outer Join ResultSet:",
+                      "Left result set:",
+                      "_Hash Left Outer Join ResultSet:"});
+    }
+
+    /**
+     * Check that ordering actually takes place (more complex: nested
+     * reordering) as well as checking that the results are correct.
+     */
+    public void testDerby_4471d() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+
+        s.execute("create table t1(c1 int)");
+        s.execute("create table t2(c2 int)");
+        s.execute("create table t3(c3 int)");
+
+        s.execute("insert into t1 values 1, 2, 2, 3, 4");
+        s.execute("insert into t2 values 1, 3, 3, 5, 6");
+        s.execute("insert into t3 values 2, 3, 5, 5, 7");
+
+        s.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+
+
+
+        PreparedStatement ps = prepareStatement(
+            "select * from t1 t1_o left outer join " +
+            "     ((t1 left outer join t2 on t1.c1 = t2.c2) left outer join " +
+            "      t3 on t2.c2 = t3.c3)" +
+            "   on t1_o.c1 = t2.c2");
+
+        // Expect one reordering, cf. rtsp.assertSequence below:
+        //
+        //     LOJ1 [t1_o.c1 = t2.c2]               LOJ1 [t2.c2=t3.c3]
+        //     /  \                                  /   \
+        //    /    \                                /    t3
+        //   /      \                             LOJ2 [t1_o.c1 = t2.c2]
+        //  t1_o   LOJ2 [t2.c2=t3.c3]            /    \
+        //          /   \                       /     LOJ3 [t2.c2=t3.c3]
+        //         /     \             =>    t1_o     /  \
+        //        /       t3                         /    \
+        //       /                                  t1    t2
+        //      LOJ3 [t1.c1=t2.c2]
+        //      /  \
+        //     /    \
+        //   t1     t2
+        //
+        // The reason we don't get two reorderings here is that the predicate
+        // on LOJ1, "t1_o.c1 = t2.c2" refrences LOJ3's null producing side,
+        // t2. Contrast with next example below.
+
+        ResultSet rs = ps.executeQuery();
+
+        JDBC.assertUnorderedResultSet(
+            rs,
+            new String[][] {
+                {"1", "1", "1", null},
+                {"2", null, null, null},
+                {"2", null, null, null},
+                {"3", "3", "3", "3"},
+                {"3", "3", "3", "3"},
+                {"4", null, null, null}});
+
+        JDBC.checkPlan(s,
+                  new String[] {
+                      "Hash Left Outer Join ResultSet:",
+                      "Left result set:",
+                      "_Nested Loop Left Outer Join ResultSet:",
+                      "_Left result set:",
+                      "_Right result set:",
+                      "__Source result set:",
+                      "___Hash Left Outer Join ResultSet:"});
+
+        ps = prepareStatement(
+            "select * from " +
+            "    t1 t1_o left outer join " +
+            "        ((t1 t1_i left outer join t2 " +
+            "          on t1_i.c1 = t2.c2) left outer join t3 " +
+            "         on t1_i.c1 = t3.c3)" +
+            "    on t1_o.c1 = t1_i.c1");
+
+        // Expect two reorderings, cf. rtsp.assertSequence below:
+        //
+        //      LOJ1 [t1_o.c1 = t1_i.c1]               LOJ1 [t2.c2=t3.c3]
+        //      /  \                                  /   \
+        //     /    \                                /    t3
+        //    /      \                             LOJ2 [t1_o.c1 = t1_i.c1]
+        //   t1_o   LOJ2 [t1_i.c1=t3.c3]          /    \
+        //           /   \                       /     LOJ3 [t1.c1=t2.c2]
+        //          /     \             =>    t1_o     /  \
+        //         /       t3                         /    \
+        //       LOJ3 [t1_i.c1=t2.c2]               t1_i   t2
+        //       /  \
+        //      /    \
+        //    t1_i    t2                =>
+        //                                          LOJ1 [t2.c2=t3.c3]
+        //                                           /   \
+        //                                          /    t3
+        //                                       LOJ2 [t2.c2=t3.c3]
+        //                                        /  \
+        //                                       /    t2
+        //                                     LOJ3  [t1_o.c1 = t1_i.c1]
+        //                                     /   \
+        //                                   t1_o  t1_i
+        //
+        // In this example, LOJ1's predicate references LOJ3's row preserving
+        // side (t1_i), so we get two reorderings.
+
+        rs = ps.executeQuery();
+
+        JDBC.assertUnorderedResultSet(
+            rs,
+            new String[][] {
+                {"1", "1", "1", null},
+                {"2", "2", null, "2"},
+                {"2", "2", null, "2"},
+                {"2", "2", null, "2"},
+                {"2", "2", null, "2"},
+                {"3", "3", "3", "3"},
+                {"3", "3", "3", "3"},
+                {"4", "4", null, null}});
+
+        JDBC.checkPlan(s,
+                  new String[] {
+                      "Hash Left Outer Join ResultSet:",
+                      "Left result set:",
+                      "_Hash Left Outer Join ResultSet:",
+                      "_Left result set:",
+                      "__Hash Left Outer Join ResultSet:"});
+    }
+
+
    /**
     * This fixture would give:
     * <pre>
@@ -2608,4 +3133,4 @@ public final class OuterJoinTest extends
 
         JDBC.assertFullResultSet(rs, expRS);
     }
- }
+}

Modified: db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=997924&r1=997923&r2=997924&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Thu Sep 16 20:57:47 2010
@@ -77,6 +77,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(InsertTest.suite());
         suite.addTest(JoinTest.suite());
 	  suite.addTest(LangScripts.suite());
+        suite.addTest(LojReorderTest.suite());
         suite.addTest(MathTrigFunctionsTest.suite());
 	  suite.addTest(OuterJoinTest.suite());
         suite.addTest(PredicateTest.suite());

Modified: db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/junit/JDBC.java?rev=997924&r1=997923&r2=997924&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/junit/JDBC.java Thu Sep 16 20:57:47 2010
@@ -907,6 +907,45 @@ public class JDBC {
         Object [][] expectedRows, boolean allAsTrimmedStrings, boolean closeResultSet)
         throws SQLException
     {
+        assertFullResultSetMinion(rs, expectedRows, allAsTrimmedStrings,
+                                  closeResultSet, null);
+    }
+
+
+    /**
+     * assertFullResultSet() using trimmed string comparisions.
+     * Equal to
+     * <code>
+     * assertFullResultSet(rs, expectedRows, true)
+     * </code>
+     *
+     * As a side effect, this method closes the result set.
+     * <p/>
+     * Additionally, also assert that the given warnings are seen.  The array
+     * {@code warnings} should contain null or a warning (SQLState string). The
+     * array entry is asserted against the result set after having read the
+     * corresponding row in the result set. <b>NOTE: only asserted for embedded
+     * result sets, cf DERBY-159</b>
+     * <p/>
+     * For now, we only look at the first warning if there is a chain
+     * of warnings.
+     */
+    public static void assertFullResultSet(ResultSet rs,
+        Object [][] expectedRows, String[] warnings)
+        throws SQLException
+    {
+        assertFullResultSetMinion(rs, expectedRows, true, true, warnings);
+    }
+
+
+    private static void assertFullResultSetMinion(
+        ResultSet rs,
+        Object [][] expectedRows,
+        boolean allAsTrimmedStrings,
+        boolean closeResultSet,
+        String[] warnings)
+        throws SQLException
+    {
         int rows;
         ResultSetMetaData rsmd = rs.getMetaData();
 
@@ -920,6 +959,25 @@ public class JDBC {
 
         for (rows = 0; rs.next(); rows++)
         {
+
+            // Assert warnings on result set, but only for embedded, cf
+            // DERBY-159.
+            if (TestConfiguration.getCurrent().getJDBCClient().isEmbedded() &&
+                warnings != null) {
+
+                SQLWarning w = rs.getWarnings();
+                String wstr = null;
+
+                if (w != null) {
+                    wstr = w.getSQLState();
+                }
+
+                Assert.assertEquals(
+                    "Warning assertion error on row " + (rows+1),
+                    warnings[rows],
+                    wstr);
+            }
+
             /* If we have more actual rows than expected rows, don't
              * try to assert the row.  Instead just keep iterating
              * to see exactly how many rows the actual result set has.
@@ -937,6 +995,7 @@ public class JDBC {
         Assert.assertEquals("Unexpected row count:", expectedRows.length, rows);
     }
 
+
     /**
      * Similar to assertFullResultSet(...) above, except that this
      * method takes a BitSet and checks the received expectedRows
@@ -1434,5 +1493,32 @@ public class JDBC {
                 }
         }
 
-       
+    /**
+     * Get run-time statistics and check that a sequence of string exist in the
+     * statistics, using the given statement.
+     * <p/>
+     * For the format of the strings, see RuntimeStatisticsParser#assertSequence
+     *
+     * @see RuntimeStatisticsParser#assertSequence
+     *
+     * @param s the statement presumed to just have been executed, and for
+     *        which we want to check the run-time statistics
+     * @param sequence the sequnce of strings we expect to see in the run-time
+     *        statistics
+     * @throws SQLException standard
+     */
+    public static void checkPlan(Statement s, String[] sequence)
+            throws SQLException {
+
+        ResultSet rs = s.executeQuery(
+                "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+        rs.next();
+
+        String rts = rs.getString(1);
+        rs.close();
+
+        RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rts);
+        rtsp.assertSequence(sequence);
+    }
+
 }



Mime
View raw message