db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1384814 - in /db/derby/code/branches/10.7: ./ java/engine/org/apache/derby/impl/sql/compile/ java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Fri, 14 Sep 2012 15:17:55 GMT
Author: mamta
Date: Fri Sep 14 15:17:54 2012
New Revision: 1384814

URL: http://svn.apache.org/viewvc?rev=1384814&view=rev
Log:
DERBY-4631 (Wrong join column returned by right outer join with NATURAL or USING and terriptry-based collation)

Backporting to 10.7


Modified:
    db/derby/code/branches/10.7/   (props changed)
    db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromList.java
    db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
    db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java
    db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
    db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
    db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
    db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
    db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java

Propchange: db/derby/code/branches/10.7/
------------------------------------------------------------------------------
  Merged /db/derby/code/trunk:r1228332,1228777,1229266,1230873,1231296,1245143,1245226,1290888,1290919,1291057,1292108,1292941,1293467,1294275,1341204,1341266
  Merged /db/derby/code/branches/10.8:r1384638

Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromList.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromList.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromList.java Fri Sep 14 15:17:54 2012
@@ -286,16 +286,21 @@ public class FromList extends QueryTreeN
 	}
 
 	/**
-	 * Bind the tables in this FromList.  This includes looking them up in
-	 * the DataDictionary, getting their TableDescriptors and assigning the
-	 * table numbers.
-	 *
-	 * @param dataDictionary	The DataDictionary to use for binding
-	 * @param fromListParam		FromList to use/append to.
-	 *
-	 * @exception StandardException		Thrown on error
+	 * Go through the list of the tables and see if the passed ResultColumn
+	 *  is a join column for a right outer join with USING/NATURAL clause.
+	 * @see HalfOuterJoinNode#isJoinColumnForRightOuterJoin
 	 */
 
+	public void isJoinColumnForRightOuterJoin(ResultColumn rc) 
+	{
+		FromTable	fromTable;
+		int size = size();
+		for (int index = 0; index < size; index++)
+		{
+			fromTable = (FromTable) elementAt(index);
+			fromTable.isJoinColumnForRightOuterJoin(rc);
+		}
+	}
 	public void bindTables(DataDictionary dataDictionary, 
 							FromList fromListParam) 
 			throws StandardException

Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromTable.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromTable.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/FromTable.java Fri Sep 14 15:17:54 2012
@@ -915,6 +915,16 @@ abstract class FromTable extends ResultS
         int intRowCount = (rowCount > Integer.MAX_VALUE) ? Integer.MAX_VALUE : (int) rowCount;
         return intRowCount <= maxCapacity( getCurrentAccessPath().getJoinStrategy(), maxMemoryPerTable);
     }
+    
+	/**
+	 * No-op in FromTable.
+	 * 
+	 * @see HalfOuterJoinNode#isJoinColumnForRightOuterJoin
+	 */
+	public void isJoinColumnForRightOuterJoin(ResultColumn rc) 
+	{
+		return;
+	}
 
 	/**
 	 * @see Optimizable#legalJoinOrder

Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java Fri Sep 14 15:17:54 2012
@@ -892,6 +892,20 @@ private boolean isNullRejecting (
 	{
 		return rightOuterJoin;
 	}
+    
+	/**
+	 * If this is a right outer join node with USING/NATURAL clause, then
+	 *  check if the passed ResultColumn is a join column. If yes, then 
+	 *  ResultColumn should be marked such. DERBY-4631
+	 */
+	public void isJoinColumnForRightOuterJoin(ResultColumn rc) 
+	{
+		if (isRightOuterJoin() && usingClause != null &&  
+				usingClause.getResultColumn(rc.getUnderlyingOrAliasName()) != null) {
+			rc.setRightOuterJoinUsingClause(true);
+			rc.setJoinResultset(this);
+		}
+	}
 
 	// return the Null-producing table references
 	public JBitSet LOJgetNPReferencedTables(int numTables)

Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java Fri Sep 14 15:17:54 2012
@@ -575,6 +575,20 @@ public class JoinNode extends TableOpera
 		if (usingRC == null)
 		{
 			rightRC = logicalRightRS.getMatchingColumn(columnReference);
+		} else {
+			//If this column represents the join column from the
+			// right table for predicate generated for USING/NATURAL 
+			// of RIGHT OUTER JOIN then flag it such by setting 
+			// rightOuterJoinUsingClause to true.
+			// eg
+			//     select c from t1 right join t2 using (c)
+			//For "using(c)", a join predicate will be created as 
+			// follows t1.c=t2.c
+			//We are talking about column t2.c of the join predicate.
+			if (this instanceof HalfOuterJoinNode && ((HalfOuterJoinNode)this).isRightOuterJoin()) 
+			{
+    			leftRC.setRightOuterJoinUsingClause(true);
+			}
 		}
 
 		if (rightRC != null)

Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Fri Sep 14 15:17:54 2012
@@ -55,10 +55,10 @@ import org.apache.derby.iapi.util.String
  * for example in certain joins the ResultColumn can be nullable even if
  * its underlying column is not. In an INSERT or UPDATE the ResultColumn
  * will represent the type of the column in the table, the type of
- * the underlying expresion will be the type of the source of the
+ * the underlying expression will be the type of the source of the
  * value to be insert or updated. The method columnTypeAndLengthMatch()
  * can be used to detect when normalization is required between
- * the expression and the tyoe of ResultColumn. This class does
+ * the expression and the type of ResultColumn. This class does
  * not implement any type normalization (conversion), this is
  * typically handled by a NormalizeResultSetNode.
  *
@@ -89,6 +89,26 @@ public class ResultColumn extends ValueN
 	boolean			updatableByCursor;
 	private boolean defaultColumn;
     private boolean wasDefault;
+    //Following 2 fields have been added for DERBY-4631. 
+    //rightOuterJoinUsingClause will be set to true for following 2 cases
+    //1)if this column represents the join column which is part of the 
+    //  SELECT list of a RIGHT OUTER JOIN with USING/NATURAL. eg
+    //     select c from t1 right join t2 using (c)
+    //  This case is talking about column c as in "select c"
+    //2)if this column represents the join column from the right table 
+    //  for predicates generated for the USING/NATURAL of RIGHT OUTER JOIN
+    //  eg
+    //     select c from t1 right join t2 using (c)
+    //  For "using(c)", a join predicate will be created as follows
+    //    t1.c=t2.c
+    //  This case is talking about column t2.c of the join predicate.
+    private boolean rightOuterJoinUsingClause;
+    //Following will be non-null for the case 1) above. It will show the
+    // association of this result column to the join resultset created
+    // for the RIGHT OUTER JOIN with USING/NATURAL. This information along
+    // with rightOuterJoinUsingClause will be used during the code generation
+    // time.
+    private JoinNode joinResultSet = null;
 
 	// tells us if this ResultColumn is a placeholder for a generated
 	// autoincrement value for an insert statement.
@@ -193,8 +213,74 @@ public class ResultColumn extends ValueN
 			expression.isInstanceOf(C_NodeTypes.DEFAULT_NODE))
 			defaultColumn = true;
 	}
+	
+	/**
+	 * Returns TRUE if the ResultColumn is join column for a RIGHT OUTER 
+	 *  JOIN with USING/NATURAL. More comments at the top of this class
+	 *  where rightOuterJoinUsingClause is defined.
+	 */
+	public boolean isRightOuterJoinUsingClause()
+	{
+		return rightOuterJoinUsingClause;
+	}
 
 	/**
+	 * Will be set to TRUE if this ResultColumn is join column for a 
+	 *  RIGHT OUTER JOIN with USING/NATURAL. More comments at the top of 
+	 *  this class where rightOuterJoinUsingClause is defined. 2 eg cases
+	 * 1)select c from t1 right join t2 using (c)
+	 *   This case is talking about column c as in "select c"
+	 * 2)select c from t1 right join t2 using (c)
+	 *   For "using(c)", a join predicate will be created as follows
+	 *     t1.c=t2.c
+	 *   This case is talking about column t2.c of the join predicate.
+	 *   
+	 * This method gets called for Case 1) during the bind phase of
+	 *  ResultColumn(ResultColumn.bindExpression).
+	 *   
+	 * This method gets called for Case 2) during the bind phase of
+	 *  JoinNode while we are going through the list of join columns
+	 *  for a NATURAL JOIN or user supplied list of join columns for
+	 *  USING clause(JoinNode.getMatchingColumn).
+	 *  
+	 * @param value True/False
+	 */
+	public void setRightOuterJoinUsingClause(boolean value)
+	{
+		rightOuterJoinUsingClause = value;
+	}
+
+	/**
+	 * Returns a non-null value if the ResultColumn represents the join
+	 * column which is part of the SELECT list of a RIGHT OUTER JOIN with
+	 * USING/NATURAL. eg
+	 *      select c from t1 right join t2 using (c)
+	 * The join column we are talking about is column c as in "select c"
+	 * The return value of following method will show the association of this 
+	 * result column to the join resultset created for the RIGHT OUTER JOIN 
+	 * with USING/NATURAL. This information along with 
+	 * rightOuterJoinUsingClause will be used during the code generation 
+	 * time.
+	 */
+	public JoinNode getJoinResultSet() {
+		return joinResultSet;
+	}
+
+	/**
+	 * This method gets called during the bind phase of a ResultColumn if it
+	 *  is determined that the ResultColumn represents the join column which
+	 *  is part of the SELECT list of a RIGHT OUTER JOIN with 
+	 *  USING/NATURAL. eg
+	 *      select c from t1 right join t2 using (c)
+	 *   This case is talking about column c as in "select c"
+	 * @param resultSet - The ResultColumn belongs to this JoinNode
+	 */
+	public void setJoinResultset(JoinNode resultSet)
+	{
+		joinResultSet = resultSet;
+	}
+	
+	/**
 	 * Returns TRUE if the ResultColumn is standing in for a DEFAULT keyword in
 	 * an insert/update statement.
 	 */
@@ -241,6 +327,36 @@ public class ResultColumn extends ValueN
 			columnName.equals(name) ||
 			columnName.equals(getSourceColumnName());
 	}
+	
+	/**
+	 * Get non-null column name. This method is called during the bind phase
+	 *  to see if we are dealing with ResultColumn in the SELECT list that 
+	 *  belongs to a RIGHT OUTER JOIN(NATURAL OR USING)'s join column.
+	 * 					
+	 * For a query like following, we want to use column name x and not the
+	 *  alias x1 when looking in the JoinNode for join column
+	 *   SELECT x x1
+	 *   	 FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1;
+	 * For a query like following, getSourceColumnName() will return null
+	 *  because we are dealing with a function for the column. For this
+	 *  case, "name" will return the alias name cx
+	 *   SELECT coalesce(derby4631_t2.x, derby4631_t1.x) cx
+	 *   	 FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1;	
+	 * For a query like following, getSourceColumnName() and name will 
+	 *  return null and hence need to use the generated name
+	 *   SELECT ''dummy="'|| TRIM(CHAR(x))|| '"'
+	 *        FROM (derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1);
+	 */
+	String getUnderlyingOrAliasName() 
+	{
+		if (getSourceColumnName() != null)
+			return getSourceColumnName();
+		else if (name != null)
+			return name;
+		else
+			return exposedName;
+	}
+	
 	/**
 	 * Returns the underlying source column name, if this ResultColumn
 	 * is a simple direct reference to a table column, or NULL otherwise.
@@ -531,6 +647,8 @@ public class ResultColumn extends ValueN
 				"isGroupingColumn: " + isGroupingColumn + "\n" +
 				"isReferenced: " + isReferenced + "\n" +
 				"isRedundant: " + isRedundant + "\n" +
+				"rightOuterJoinUsingClause: " + rightOuterJoinUsingClause + "\n" +
+				"joinResultSet: " + joinResultSet + "\n" +
 				"virtualColumnId: " + virtualColumnId + "\n" +
 				"resultSetNumber: " + resultSetNumber + "\n" +
 				super.toString();
@@ -602,6 +720,20 @@ public class ResultColumn extends ValueN
 			}
 		}
 
+		//DERBY-4631
+		//Following code is for a join column(which obviously will not be 
+		// qualified with a table name because join columns are not
+		// associated with left or right table) of RIGHT OUTER JOIN  
+		// with USING/NATURAL join. For such columns, 
+		// isJoinColumnForRightOuterJoin() call will set 
+		// rightOuterJoinUsingClause to true and associate the  
+		// JoinResultSet with it. eg
+		//      select c from t1 right join t2 using (c)
+		// Here, we are talking about column c as in "select c"
+		if (expression.getTableName() == null) {
+			fromList.isJoinColumnForRightOuterJoin(this);
+		}
+
 		setExpression( expression.bindExpression(fromList, subqueryList,
                                                  aggregateVector) );
 
@@ -1458,6 +1590,14 @@ public class ResultColumn extends ValueN
   		if (isGroupingColumn()) 
   			newResultColumn.markAsGroupingColumn();
   		
+  		if (isRightOuterJoinUsingClause()) {
+  			newResultColumn.setRightOuterJoinUsingClause(true);
+  		}
+
+  		if (getJoinResultSet() != null) {
+  	  		newResultColumn.setJoinResultset(getJoinResultSet());
+  		}
+  		
   		if (isGenerated()) {
   			newResultColumn.markGenerated();
   		}

Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Fri Sep 14 15:17:54 2012
@@ -1240,6 +1240,91 @@ public class ResultColumnList extends Qu
 					continue;
 				}
 
+				//DERBY-4631 - For INNER JOINs and LEFT OUTER
+				// JOINs, Derby retrieves the join column values 
+				// from the left table's join column. But for 
+				// RIGHT OUTER JOINs, the join column's value
+				// will be picked up based on following logic.
+				//1)if the left table's column value is null
+				// then pick up the right table's column's value.
+				//2)If the left table's column value is non-null, 
+				// then pick up that value 
+				if (rc.getJoinResultSet() != null) {
+					//We are dealing with a join column for 
+					// RIGHT OUTER JOIN with USING/NATURAL eg
+					//	 select c from t1 right join t2 using (c)
+					//We are talking about column c as in "select c"
+					ResultColumnList jnRCL = 
+							rc.getJoinResultSet().getResultColumns();
+					ResultColumn joinColumn;
+					int joinResultSetNumber = 
+							rc.getJoinResultSet().getResultSetNumber();
+
+					//We need to know the column positions of left
+					// table's join column and right table's join
+					// column to generate the code explained above
+					int virtualColumnIdRightTable = -1;
+					int virtualColumnIdLeftTable = -1;
+					for (int i=0; i< jnRCL.size(); i++) {
+						joinColumn = (ResultColumn) jnRCL.elementAt(i);
+						if (joinColumn.getName().equals(rc.getUnderlyingOrAliasName())) {
+							if (joinColumn.isRightOuterJoinUsingClause())
+								virtualColumnIdRightTable = joinColumn.getVirtualColumnId();
+							else
+								virtualColumnIdLeftTable = joinColumn.getVirtualColumnId();
+						}
+					}
+					
+					userExprFun.getField(field); // instance
+					userExprFun.push(index + 1); // arg1
+
+					String resultTypeName = 
+							getTypeCompiler(
+									DataTypeDescriptor.getBuiltInDataTypeDescriptor(
+											Types.BOOLEAN).getTypeId()).interfaceName();
+					String	receiverType = ClassName.DataValueDescriptor;
+
+					//Our plan is to generate DERBY-4631
+					//  if(lefTablJoinColumnValue is null) 
+					//  then
+					//    use rightTablJoinColumnValue 
+					//   else
+					//    use lefTablJoinColumnValue 
+					
+					//Following will generate 
+					//  if(lefTablJoinColumnValue is null) 
+				    acb.pushColumnReference(userExprFun, joinResultSetNumber,
+				    		virtualColumnIdLeftTable);
+				    userExprFun.cast(rc.getTypeCompiler().interfaceName());
+				    userExprFun.cast(receiverType);
+				    userExprFun.callMethod(VMOpcode.INVOKEINTERFACE, (String) null,
+							"isNullOp",resultTypeName, 0);
+					//Then call generateExpression on left Table's column
+				    userExprFun.cast(ClassName.BooleanDataValue);
+				    userExprFun.push(true);
+				    userExprFun.callMethod(
+				    		VMOpcode.INVOKEINTERFACE, (String) null, "equals", "boolean", 1);
+					//Following will generate 
+					//  then
+					//    use rightTablJoinColumnValue 
+				    userExprFun.conditionalIf();
+				    acb.pushColumnReference(userExprFun, joinResultSetNumber,
+				    		virtualColumnIdRightTable);
+				    userExprFun.cast(rc.getTypeCompiler().interfaceName());
+					//Following will generate 
+					//   else
+					//    use lefTablJoinColumnValue 
+				    userExprFun.startElseCode();
+				    acb.pushColumnReference(userExprFun, joinResultSetNumber, 
+				    		virtualColumnIdLeftTable);
+				    userExprFun.cast(rc.getTypeCompiler().interfaceName());
+				    userExprFun.completeConditional();
+					userExprFun.cast(ClassName.DataValueDescriptor);
+					userExprFun.callMethod(
+							VMOpcode.INVOKEINTERFACE, ClassName.Row, "setColumn", "void", 2);
+					continue;
+				}
+
 				if (sourceExpr instanceof ColumnReference && ! ( ((ColumnReference) sourceExpr).getCorrelated()))
 				{
 					continue;
@@ -3615,6 +3700,19 @@ public class ResultColumnList extends Qu
 			ValueNode		expr;
 
 			resultColumn = (ResultColumn) elementAt(index);
+			//DERBY-4631
+			//Following if condition if true means that the 
+			// ResultColumn is a join column for a RIGHT OUTER
+			// JOIN with USING/NATURAL clause. At execution 
+			// time, a join column's value should be determined 
+			// by generated code which is equivalent to 
+			// COALESCE(leftTableJoinColumn,rightTableJoinColumn).
+			// By returning false here, we allow Derby to generate
+			// code for functionality equivalent to COALESCE to
+			// determine join column's value. 
+			if (resultColumn.isRightOuterJoinUsingClause())
+				return false;
+			
 			expr = resultColumn.getExpression();
 			if (! (expr instanceof VirtualColumnNode) &&
 				! (expr instanceof ColumnReference))
@@ -3695,6 +3793,10 @@ public class ResultColumnList extends Qu
 
 				}
 			}
+			else if (resultColumn.isRightOuterJoinUsingClause())
+			{
+				mapArray[index] = -1;
+			}
 			else if (resultColumn.getExpression() instanceof ColumnReference)
 			{
 				ColumnReference cr = (ColumnReference) resultColumn.getExpression();

Modified: db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java (original)
+++ db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java Fri Sep 14 15:17:54 2012
@@ -476,7 +476,6 @@ public void testNorwayCollation() throws
       s.close();
 
       }
-  
 
 /**
  * DERBY-5531 Assert failure when inserting NULL into indexed column with 
@@ -513,6 +512,654 @@ public void testNullColumnInInsert() thr
 }
 
   /**
+   * Test USING clause and NATURAL JOIN for case insensitive and
+   * 	case sensitive collations.
+   * As per the SQL spec, "the join columns in a natural join or in a named 
+   *    columns join should be added to the select list by coalescing the 
+   *    column from the left table with the column from the right table. "
+   * DERBY-4631 - Derby did not coalesce as suggested by SQL spec, instead
+   * 	it picked up join column's value from the left table when working with 
+   *    natural left outer join and it picked up the join column's value from 
+   *    the right table when working with natural right outer join. This worked
+   *    ok with non-territory based databases. For territory based databases,
+   *    it worked ok for natural left outer join but depending on the data 
+   *    value, it did not always work for natural right outer join in a 
+   *    territory based database. DERBY-4631 fixes that by using following 
+   *    logic to pick up the correct value in case of RIGHT OUTER JOIN with 
+   *    USING/NATURAL clause
+   *    1)if the left table's column value is null then pick up the
+   *      right table's column's value.
+   *    2)If the left table's column value is non-null, then pick up
+   *      that value
+   * @throws SQLException
+  */
+public void testUsingClauseAndNaturalJoin() throws SQLException {
+      getConnection().setAutoCommit(false);
+      Statement s = createStatement();
+      String collation; 
+
+      ResultSet rs = null;
+      rs = s.executeQuery("VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation')");
+      rs.next();
+      collation = rs.getString(1); 
+
+      //Test arithmetic operation on join columns
+      s.executeUpdate("CREATE TABLE derby4631_t1(x int)");
+      s.executeUpdate("CREATE TABLE derby4631_t2(x int)");
+      s.executeUpdate("INSERT INTO derby4631_t1 VALUES 1,2");
+      s.executeUpdate("INSERT INTO derby4631_t2 VALUES 2,3");
+      checkLangBasedQuery(s, "SELECT x+2, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x)+2 cx " +
+    		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
+      		new String[][] {{"4","4"},{"5","5"}});
+      checkLangBasedQuery(s, "SELECT x+2, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x)+2 cx " +
+    		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+      		"USING(x)",
+      		new String[][] {{"4","4"},{"5","5"}});
+      checkLangBasedQuery(s, "SELECT x*2, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x)*2 cx " +
+    		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
+      		new String[][] {{"2","2"},{"4","4"}});
+      checkLangBasedQuery(s, "SELECT x*2, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x)*2 cx " +
+    		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+      		"USING(x)",
+      		new String[][] {{"2","2"},{"4","4"}});
+   	  s.executeUpdate("DROP TABLE derby4631_t1");
+      s.executeUpdate("DROP TABLE derby4631_t2");
+
+      //Do the testing with one join column but with various combination
+      // of left or right table being empty
+      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5))");
+      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5))");
+      s.executeUpdate("INSERT INTO derby4631_t2 VALUES 'A','B'");
+      checkLangBasedQuery(s, "SELECT x, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+    		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
+      		new String[][] {{"A","A"},{"B","B"}});
+      checkLangBasedQuery(s, "SELECT x, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+      		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+      		"USING(x)",
+      		new String[][] {{"A","A"},{"B","B"}});
+      checkLangBasedQuery(s, "SELECT x, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+    		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
+      		null);
+      checkLangBasedQuery(s, "SELECT x, " +
+    		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+    		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+      		"USING(x)",
+    		null);
+   	  s.executeUpdate("DROP TABLE derby4631_t1");
+      s.executeUpdate("DROP TABLE derby4631_t2");
+
+      //Do the testing with one join column
+      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5))");
+      s.executeUpdate("INSERT INTO derby4631_t1 VALUES 'A','B'");
+      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5))");
+      s.executeUpdate("INSERT INTO derby4631_t2 VALUES 'b','c'");
+      //Temp table for testing
+      s.executeUpdate("CREATE TABLE derby4631_t3" +
+    	      "(x1 varchar(5), x2 varchar(5), x3 int default 11)");
+      
+      //Derby always picks up the join column's value from the left table
+      // when working with LEFT OUTER JOIN. This logic does not cause any
+      // issue with territory or non-territory based databases. We get 
+      // correct results even though Derby is not doing a coalesce on left 
+      // table's column value and right table's column value as specified
+      // by SQL spec. This is because, in case of LEFT OUTER JOIN, if the
+      // left table's column value is null THEN right table's column value 
+      // will also be null and hence it is ok for Derby to always pick up  
+      // left table's column value for join columns in case of LEFT OUTER
+      // JOIN.
+      //
+      //Test NATURAL LEFT OUTER JOIN
+      checkLangBasedQuery(s, "SELECT x x1, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+    		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
+      		new String[][] {{"b","b"},{"c","c"}});
+      //Do the same test as above, but this time using the USING clause
+      // rather the NATURAL join
+      checkLangBasedQuery(s, "SELECT x x1, " +
+        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+        		"USING(x)",
+        		new String[][] {{"b","b"},{"c","c"}});
+      //Test insert into a table with data from NATURAL LEFT OUTER JOIN
+      s.executeUpdate("INSERT INTO derby4631_t3(x1, x2) " +
+        		"SELECT x, " +
+        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+          		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1");
+      checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
+      		new String[][] {{"b","b","11"},{"c","c","11"}});
+      s.executeUpdate("UPDATE derby4631_t3 SET x3=22 where x1 in "+
+        		"(SELECT " +
+        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+        		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1)");
+      checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
+      		new String[][] {{"b","b","22"},{"c","c","22"}});
+      s.executeUpdate("DELETE FROM derby4631_t3 where x1 in "+
+        		"(SELECT " +
+        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+        		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1)");
+        checkLangBasedQuery(s, "SELECT * FROM derby4631_t3",
+          		null);
+      //Do the same test as above, but this time using the USING clause
+      // rather the NATURAL join
+        s.executeUpdate("INSERT INTO derby4631_t3(x1, x2) " +
+      		"SELECT x, " +
+      		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+      		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 USING(x)");
+      checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
+      		new String[][] {{"b","b","11"},{"c","c","11"}});
+      s.executeUpdate("UPDATE derby4631_t3 SET x3=22 where x1 in "+
+        		"(SELECT " +
+        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+        		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 USING(x))");
+      checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
+      		new String[][] {{"b","b","22"},{"c","c","22"}});
+      s.executeUpdate("DELETE FROM derby4631_t3 where x1 in "+
+        		"(SELECT " +
+        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+        		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 USING(x))");
+        checkLangBasedQuery(s, "SELECT * FROM derby4631_t3",
+          		null);
+
+        //Test create view with insert from join
+        s.executeUpdate("create view derby4631_v2 as " +
+        		"(SELECT x," +
+        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+        		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1)");
+        checkLangBasedQuery(s, "SELECT * FROM derby4631_v2 ",
+        		new String[][] {{"b","b"},{"c","c"}});
+        s.executeUpdate("drop view derby4631_v2 ");
+        //Do the same test as above, but this time using the USING clause
+        // rather the NATURAL join
+        s.executeUpdate("create view derby4631_v2 as " +
+        		"(SELECT x," +
+        		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+        		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 USING(x))");
+        checkLangBasedQuery(s, "SELECT * FROM derby4631_v2 ",
+        		new String[][] {{"b","b"},{"c","c"}});
+        s.executeUpdate("drop view derby4631_v2 ");
+
+        //Test nested NATURAL LEFT OUTER JOIN. They will return correct data
+        // with both territory and non-territory based dbs.
+        checkLangBasedQuery(s, "SELECT x " +
+          		"FROM (values ('b')) v2(x) " +
+        		"NATURAL LEFT OUTER JOIN " +
+        		"derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1 ",
+          		new String[][] {{"b"}});
+      //Test nested LEFT OUTER JOIN with USING clause They will return correct
+      // data with both territory and non-territory based dbs.
+      checkLangBasedQuery(s, "SELECT x " +
+        		"FROM (values ('b')) v2(x) " +
+          		"LEFT OUTER JOIN " +
+          		"derby4631_t2 USING(x) " +
+          		"LEFT OUTER JOIN derby4631_t1 USING(x) ",
+        		new String[][] {{"b"}});
+      
+      if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
+    	  //We are working with a database with case-insensitive collation.
+    	  // Hence row 'b' in derby4631_t2 will find a match in derby4631_t1
+    	  // with row 'B'.
+    	  
+    	  //Derby used to always pick up the join column's value from the right
+    	  // table when working with RIGHT OUTER JOIN. This could cause issues 
+    	  // with case-sensitive collation databases and it would give wrong
+    	  // results for join columns for RIGHT OUTER JOIN with USING/NATURAL.
+    	  //After DERBY-4631 got fixed, now a query like following returns the
+    	  // correct results. As per the SQL spec, the join column's value 
+    	  // should always be the value resulting from coalescing the left 
+    	  // table's column value with the right table's column value.
+          checkLangBasedQuery(s, "SELECT x x1, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+        		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
+          		new String[][] {{"A","A"},{"b","b"}});
+          //Do the same test as above, but this time using the USING clause
+          // rather the NATURAL join
+          checkLangBasedQuery(s, "SELECT x x1, " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+              		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+            		"USING(x)",
+            		new String[][] {{"A","A"},{"b","b"}});
+          
+          //Test insert into a table with data from NATURAL RIGHT OUTER JOIN
+          s.executeUpdate("INSERT INTO derby4631_t3(x1, x2) " +
+            		"SELECT x xx, " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+              		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1");
+          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
+          		new String[][] {{"A","A","11"},{"b","b","11"}});
+          s.executeUpdate("UPDATE derby4631_t3 SET x3=22 where x1 in "+
+          		"(SELECT " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+          		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1)");
+          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
+        		new String[][] {{"A","A","22"},{"b","b","22"}});
+          s.executeUpdate("DELETE FROM derby4631_t3 where x1 in "+
+            		"(SELECT " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+            		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1)");
+          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3",
+            		null);
+          //Do the same test as above, but this time using the USING clause
+          // rather the NATURAL join
+          s.executeUpdate("INSERT INTO derby4631_t3(x1, x2) " +
+          		"SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+          		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 USING(x)");
+          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
+          		new String[][] {{"A","A","11"},{"b","b","11"}});
+          s.executeUpdate("UPDATE derby4631_t3 SET x3=22 where x1 in "+
+            		"(SELECT " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+            		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 USING(x))");
+          checkLangBasedQuery(s, "SELECT * FROM derby4631_t3 ",
+          		new String[][] {{"A","A","22"},{"b","b","22"}});
+          s.executeUpdate("DELETE FROM derby4631_t3 where x1 in "+
+            		"(SELECT " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+            		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 USING(x))");
+            checkLangBasedQuery(s, "SELECT * FROM derby4631_t3",
+              		null);
+
+            //Test create view with insert from join
+            s.executeUpdate("create view derby4631_v2 as " +
+            		"(SELECT x," +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+            		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1)");
+            checkLangBasedQuery(s, "SELECT * FROM derby4631_v2 ",
+            		new String[][] {{"A","A"},{"b","b"}});
+            s.executeUpdate("drop view derby4631_v2 ");
+            //Do the same test as above, but this time using the USING clause
+            // rather the NATURAL join
+            s.executeUpdate("create view derby4631_v2 as " +
+            		"(SELECT x," +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+            		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 USING(x))");
+            checkLangBasedQuery(s, "SELECT * FROM derby4631_v2 ",
+            		new String[][] {{"A","A"},{"b","b"}});
+            s.executeUpdate("drop view derby4631_v2 ");
+
+          //Test nested NATURAL RIGHT OUTER JOIN
+          checkLangBasedQuery(s, "SELECT x " +
+            		"FROM (values ('b')) v2(x) " +
+              		"NATURAL RIGHT OUTER JOIN " +
+              		"derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1 ",
+              		new String[][] {{"A"},{"b"}});
+          //Test nested LEFT OUTER JOIN with USING clause
+          checkLangBasedQuery(s, "SELECT x " +
+            		"FROM (values ('b')) v2(x) " +
+              		"RIGHT OUTER JOIN " +
+              		"derby4631_t2 USING(x) " +
+              		"RIGHT OUTER JOIN derby4631_t1 USING(x) ",
+            		new String[][] {{"A"},{"b"}});
+      } else {
+    	  //Case-sensitive collation will not run into any problems for the
+    	  // given data set and hence following is returning correct results.
+          checkLangBasedQuery(s, "SELECT x x1, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+        		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
+          		new String[][] {{"A","A"},{"B","B"}});
+          //Do the same test as above, but this time using the USING clause
+          // rather the NATURAL join
+    	  //
+    	  //Case-sensitive collation will not run into any problems for the
+    	  // given data set and hence following is returning correct results.
+          checkLangBasedQuery(s, "SELECT x x1, " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
+              		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+            		"USING(x)",
+            		new String[][] {{"A","A"},{"B","B"}});
+      }
+   	  s.executeUpdate("DROP TABLE derby4631_t1");
+      s.executeUpdate("DROP TABLE derby4631_t2");
+      
+      //Now do the testing with 3 join columns
+      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5), "+
+      	      "y varchar(2), z int, a int)");
+      s.executeUpdate("INSERT INTO derby4631_t1 VALUES " +
+      	      "('A','z',1,11),('B','y',2,22)");
+      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5), "+
+      	      "y varchar(2), z int)");
+      s.executeUpdate("INSERT INTO derby4631_t2 VALUES  " +
+      	      "('b','Y',2),('c','Y',2)");
+      if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
+    	  //For case-insensitive-territory based db, there will be a match for
+    	  // one of the rows in derby4631_t2 with derby4631_t1 and that is why 
+    	  // column a from derby4631_t1 will be non-null for that row for the 
+    	  // LEFT OUTER JOIN query
+          checkLangBasedQuery(s, "SELECT x, " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+            		"a " +
+            		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
+            		new String[][] {{"b","b","Y","Y","2","2","22"},
+          		  {"c","c","Y","Y","2","2",null}});
+          //test with USING clause
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+          		"USING(x,y,z)",
+          		new String[][] {{"b","b","Y","Y","2","2","22"},
+          		  {"c","c","Y","Y","2","2",null}});
+          //Test joining on only 2 of the 3 columns
+          checkLangBasedQuery(s, "SELECT " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+            		"a " +
+            		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+            		"USING(y,z)",
+            		new String[][] {{"b","Y","Y","2","2","22"},
+          		  {"c","Y","Y","2","2","22"}});
+          //Test RIGHT OUTER JOIN using only 2 of the 3 columns
+          checkLangBasedQuery(s, "SELECT " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+          		"USING(y,z)",
+          		new String[][] {{"A","z","z","1","1","11"},
+        		  {"b","Y","Y","2","2","22"},
+          		  {"c","Y","Y","2","2","22"}});
+          //Test NATURAL RIGHT OUTER JOIN
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
+          		new String[][] {{"A","A","z","z","1","1","11"},
+        		  {"b","b","Y","Y","2","2","22"}});
+        //test with USING clause
+          checkLangBasedQuery(s, "SELECT x, " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+            		"a " +
+              		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+              		"USING(x,y,z)",
+              		new String[][] {{"A","A","z","z","1","1","11"},
+                  		  {"b","b","Y","Y","2","2","22"}});
+      } else {
+    	  //For non-territory based db, there will be no match for both the
+    	  // rows in derby4631_t2 with derby4631_t1 and that is why column
+    	  // a from derby4631_t1 will be null for the LEFT OUTER JOIN
+    	  // query
+          checkLangBasedQuery(s, "SELECT x, " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+            		"a " +
+            		"FROM derby4631_t2 NATURAL LEFT OUTER JOIN derby4631_t1",
+            		new String[][] {{"b","b","Y","Y","2","2",null},
+          		  {"c","c","Y","Y","2","2",null}});
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+          		"USING(x,y,z)",
+          		new String[][] {{"b","b","Y","Y","2","2",null},
+          		  {"c","c","Y","Y","2","2",null}});
+          //Test LEFT OUTER JOIN using only 2 of the 3 columns
+          checkLangBasedQuery(s, "SELECT " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 LEFT OUTER JOIN derby4631_t1 " +
+          		"USING(y,z)",
+          		new String[][] {{"b","Y","Y","2","2",null},
+        		  {"c","Y","Y","2","2",null}});
+
+          //Test NATURAL RIGHT OUTER JOIN
+          checkLangBasedQuery(s, "SELECT x, " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1",
+          		new String[][] {{"A","A","z","z","1","1","11"},
+        		  {"B","B","y","y","2","2","22"}});
+        //test with USING clause
+          checkLangBasedQuery(s, "SELECT x, " +
+            		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+            		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+            		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+            		"a " +
+              		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+              		"USING(x,y,z)",
+              		new String[][] {{"A","A","z","z","1","1","11"},
+                  		  {"B","B","y","y","2","2","22"}});
+          //Test RIGHT OUTER JOIN using only 2 of the 3 columns
+          checkLangBasedQuery(s, "SELECT " +
+          		"coalesce(derby4631_t2.x, derby4631_t1.x) cx, " +
+          		"y, coalesce(derby4631_t2.y, derby4631_t1.y) cy, " +
+          		"z, coalesce(derby4631_t2.z, derby4631_t1.z) cz, " +
+          		"a " +
+          		"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
+          		"USING(y,z)",
+          		new String[][] {{"A","z","z","1","1","11"},
+        		  {"B","y","y","2","2","22"}});
+      }
+      s.executeUpdate("DROP TABLE derby4631_t1");
+      s.executeUpdate("DROP TABLE derby4631_t2");
+      
+      //Now do the testing with 2 join columns
+      s.executeUpdate("CREATE TABLE derby4631_t1(x varchar(5), y varchar(2))");
+      s.executeUpdate("INSERT INTO derby4631_t1 VALUES ('A','z'),('B','y')");
+      s.executeUpdate("CREATE TABLE derby4631_t2(x varchar(5), y varchar(2))");
+      s.executeUpdate("INSERT INTO derby4631_t2 VALUES ('b','Y'),('c','x')");
+      //Test with views too
+      s.executeUpdate("create view derby4631_v1 as select * from derby4631_t1");
+      s.executeUpdate("create view derby4631_v2 as select * from derby4631_t2");
+      //Test with global temporary tables too
+      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE gt1 " +
+    	      "(x varchar(5), y varchar(2)) " +
+    	      "on commit delete rows not logged");
+      s.executeUpdate("INSERT INTO session.gt1 VALUES ('A','z'),('B','y')");
+      s.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE gt2" +
+    	      "(x varchar(5), y varchar(2)) " +
+    	      "on commit delete rows not logged");
+      s.executeUpdate("INSERT INTO session.gt2 VALUES ('b','Y'),('c','x')");
+
+      //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+      // and hence following is returning the correct results for both
+      // territory and non-territory based databases
+	  joinTesting(s,"derby4631_t2", "derby4631_t1",
+			  "derby4631_t2", "derby4631_t1",
+			  " NATURAL LEFT OUTER JOIN ", "",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      //Test with views
+	  joinTesting(s,"derby4631_v2", "derby4631_v1",
+			  "derby4631_v2", "derby4631_v1",
+			  " NATURAL LEFT OUTER JOIN ", "",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      //Test with global temporary tables
+	  joinTesting(s,"gt2", "gt1",
+			  "session.gt2 gt2", "session.gt1 gt1",
+			  " NATURAL LEFT OUTER JOIN ", "",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      //Test with VALUES
+	  joinTesting(s,"v2", "v1",
+			  " (values ('b','Y'),('c','x')) v2(x,y) ",
+			  " (values('A','z'),('B','y')) v1(x,y) ", 
+			  " NATURAL LEFT OUTER JOIN ", "",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      //Test subqueries.
+	  joinTesting(s,"t2", "t1",
+			  " (select * from derby4631_t2) t2(x,y) ",
+			  " (select * from derby4631_t1) t1(x,y) ", 
+			  " NATURAL LEFT OUTER JOIN ", "",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+	  
+      //Do the same test as above, but this time using the USING clause
+      // rather the NATURAL join
+      //
+      //LEFT OUTER JOIN's join column value is not impacted by DERBY-4631 
+      // and hence following is returning the correct results.
+	  joinTesting(s,"derby4631_t2", "derby4631_t1",
+			  "derby4631_t2", "derby4631_t1",
+			  "  LEFT OUTER JOIN ", " USING(x,y)",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      //Test with views
+	  joinTesting(s,"derby4631_v2", "derby4631_v1",
+			  "derby4631_v2", "derby4631_v1",
+			  "  LEFT OUTER JOIN ", " USING(x,y)",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      //Test with global temporary tables
+	  joinTesting(s,"gt2", "gt1",
+			  "session.gt2 gt2", "session.gt1 gt1",
+			  "  LEFT OUTER JOIN ", " USING(x,y)",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      //Test with VALUES
+	  joinTesting(s,"v2", "v1",
+			  " (values ('b','Y'),('c','x')) v2(x,y) ",
+			  " (values('A','z'),('B','y')) v1(x,y) ", 
+			  " LEFT OUTER JOIN ", " USING(x,y)",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      //Test subqueries.
+	  joinTesting(s,"t2", "t1",
+			  " (select * from derby4631_t2) t2(x,y) ",
+			  " (select * from derby4631_t1) t1(x,y) ", 
+			  " LEFT OUTER JOIN ", " USING(x,y)",
+			  new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
+      
+      if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
+    	  //Following query is returning correct data because DERBY-4631 is
+    	  // fixed
+    	  joinTesting(s,"derby4631_t2", "derby4631_t1",
+    			  "derby4631_t2", "derby4631_t1",
+    			  " NATURAL RIGHT OUTER JOIN ", "",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Test with views
+    	  joinTesting(s,"derby4631_v2", "derby4631_v1",
+    			  "derby4631_v2", "derby4631_v1",
+    			  " NATURAL RIGHT OUTER JOIN ", "",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Test with global temporary tables
+    	  joinTesting(s,"gt2", "gt1",
+    			  "session.gt2 gt2", "session.gt1 gt1",
+    			  " NATURAL RIGHT OUTER JOIN ", "",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Test with VALUES
+    	  joinTesting(s,"v2", "v1",
+    			  " (values ('b','Y'),('c','x')) v2(x,y) ",
+    			  " (values('A','z'),('B','y')) v1(x,y) ", 
+    			  " NATURAL RIGHT OUTER JOIN ", "",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Test subqueries.
+    	  joinTesting(s,"t2", "t1",
+    			  " (select * from derby4631_t2) t2(x,y) ",
+    			  " (select * from derby4631_t1) t1(x,y) ", 
+    			  " NATURAL RIGHT OUTER JOIN ", "",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Do the same test as above, but this time using the USING clause
+          // rather the NATURAL join
+    	  joinTesting(s,"derby4631_t2", "derby4631_t1",
+    			  "derby4631_t2", "derby4631_t1",
+    			  " RIGHT OUTER JOIN ", " USING(x,y)",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Test with views
+    	  joinTesting(s,"derby4631_v2", "derby4631_v1",
+    			  "derby4631_v2", "derby4631_v1",
+    			  " RIGHT OUTER JOIN ", " USING(x,y)",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Test with global temporary tables
+    	  joinTesting(s,"gt2", "gt1",
+    			  "session.gt2 gt2", "session.gt1 gt1",
+    			  " RIGHT OUTER JOIN ", " USING(x,y)",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Test with VALUES
+    	  joinTesting(s,"v2", "v1",
+    			  " (values ('b','Y'),('c','x')) v2(x,y) ",
+    			  " (values('A','z'),('B','y')) v1(x,y) ", 
+    			  " RIGHT OUTER JOIN ", " USING(x,y) ",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+          //Test subqueries.
+    	  joinTesting(s,"t2", "t1",
+    			  " (select * from derby4631_t2) t2(x,y) ",
+    			  " (select * from derby4631_t1) t1(x,y) ", 
+    			  " RIGHT OUTER JOIN ", " USING(x,y)",
+    			  new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+      } else {
+    	  //Case-sensitive collation will not run into any problems for the
+    	  // given data set and hence following is returning correct results.
+    	  joinTesting(s,"derby4631_t2", "derby4631_t1",
+    			  "derby4631_t2", "derby4631_t1",
+    			  " NATURAL RIGHT OUTER JOIN ", "",
+            		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
+          //Test with views
+    	  joinTesting(s,"derby4631_v2", "derby4631_v1",
+    			  "derby4631_v2", "derby4631_v1",
+    			  " NATURAL RIGHT OUTER JOIN ", "",
+            		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
+          //Test with VALUES
+    	  joinTesting(s,"v2", "v1",
+    			  " (values ('b','Y'),('c','x')) v2(x,y) ",
+    			  " (values('A','z'),('B','y')) v1(x,y) ", 
+    			  " NATURAL RIGHT OUTER JOIN ", "",
+          		new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
+          //Do the same test as above, but this time using the USING clause
+          // rather the NATURAL join
+          //
+    	  //Case-sensitive collation will not run into any problems for the
+    	  // given data set and hence following is returning correct results.
+    	  joinTesting(s,"derby4631_t2", "derby4631_t1",
+    			  "derby4631_t2", "derby4631_t1",
+    			  " RIGHT OUTER JOIN ", " USING(x,y)",
+    			  new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
+          //Test with views
+    	  joinTesting(s,"derby4631_v2", "derby4631_v1",
+    			  "derby4631_v2", "derby4631_v1",
+    			  " RIGHT OUTER JOIN ", " USING(x,y)",
+    			  new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
+          //Test with VALUES
+    	  joinTesting(s,"v2", "v1",
+    			  " (values ('b','Y'),('c','x')) v2(x,y) ",
+    			  " (values('A','z'),('B','y')) v1(x,y) ", 
+    			  " RIGHT OUTER JOIN ", " USING(x,y) ",
+    			  new String[][] {{"A","z","A","z"},{"B","y","B","y"}});
+      }
+
+      s.executeUpdate("DROP TABLE session.gt1");
+      s.executeUpdate("DROP TABLE session.gt2");
+      s.executeUpdate("DROP VIEW derby4631_v1");
+      s.executeUpdate("DROP VIEW derby4631_v2");
+      s.executeUpdate("DROP TABLE derby4631_t1");
+      s.executeUpdate("DROP TABLE derby4631_t2");
+      
+}
+
+private void joinTesting(Statement s, 
+		String leftTableName, String rightTableName, 
+		String leftTableSource, String rightTableSource,
+		String joinSpecification, String usingClause, 
+		String[][] expectedResults) 
+				throws SQLException{
+	String query = "SELECT x, y," +
+      		"coalesce("+leftTableName+".x, "+
+			rightTableName+".x) cx, " +
+      		"coalesce("+leftTableName+".y, "+
+      		rightTableName+".y) cy " +
+    		"FROM "+leftTableSource+joinSpecification+
+    		rightTableSource+usingClause;
+	checkLangBasedQuery(s, query,
+    		expectedResults);
+    
+}
+  /**
    * Test order by with English collation
    *
   * @throws SQLException
@@ -1397,6 +2044,8 @@ public void testMissingCollatorSupport()
         suite.addTest(new CleanDatabaseTestSetup(
                 new CollationTest("testDefaultCollation")));
         suite.addTest(collatedSuite("en", false, "testEnglishCollation"));
+        suite.addTest(collatedSuite("en", true, "testUsingClauseAndNaturalJoin"));
+        suite.addTest(collatedSuite("en", false, "testUsingClauseAndNaturalJoin"));
         suite.addTest(collatedSuite("en", true, "testNullColumnInInsert"));
         suite.addTest(collatedSuite("en", false, "testNullColumnInInsert"));
          

Modified: db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java (original)
+++ db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Fri Sep 14 15:17:54 2012
@@ -1463,6 +1463,259 @@ public class GroupByTest extends BaseJDB
     }
 
 	/**
+	 * DERBY-4631: Wrong join column returned by right outer join with NATURAL 
+	 *  or USING and territory-based collation
+	 *  
+	 * The tests below show that GROUP BY and HAVING clauses are able to use a 
+	 *  column which is not part of the SELECT list. This happens for USING
+	 *  clause & NATURAL joins with queries using INNER JOINS and OUTER JOINS.
+	 *  When using the JOIN with ON clause, we do not run into this problem 
+	 *  because we are expected to qualify the JOIN column with table name 
+	 *  in the SELECT list when using thw ON clause.
+	 * 
+	 * @throws SQLException
+	 */
+	public void testGroupByWithUsingClause() throws SQLException {
+		Statement s = createStatement();
+		//JOIN queries with ON clause do not cause ambiguity on join columns
+		// because such queries require that join columns in SELECT query
+		// should be qualified with left or right table name. Just a note
+		// that ON clause is not allowed on CROSS and NATURAL JOINS.
+		//
+        //The join queries with ON clause are not impacted by DERBY-4631 and 
+        // hence following tests are showing the correct behavior.
+		//
+		//Try INNER JOIN with ON clause.
+        assertStatementError("42X03", s,
+                "select i from t1_D3880 " +
+				"inner join t2_D3880 ON t1_D3880.i = t2_D3880.i " +
+                "group by t1_D3880.i");
+		ResultSet rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"inner join t2_D3880 ON t1_D3880.i = t2_D3880.i " +
+                "group by t1_D3880.i");
+		String[][] expRs = new String[][] {{"1"},{"2"}};
+		JDBC.assertFullResultSet(rs,expRs);
+		//Try LEFT OUTER JOIN with ON clause.
+        assertStatementError("42X03", s,
+                "select i from t1_D3880 " +
+				"LEFT OUTER JOIN t2_D3880 ON t1_D3880.i = t2_D3880.i " +
+                "group by t1_D3880.i");
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"LEFT OUTER JOIN t2_D3880 ON t1_D3880.i = t2_D3880.i " +
+                "group by t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//Try RIGHT OUTER JOIN with ON clause.
+        assertStatementError("42X03", s,
+                "select i from t1_D3880 " +
+				"RIGHT OUTER JOIN t2_D3880 ON t1_D3880.i = t2_D3880.i " +
+                "group by t1_D3880.i");
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"RIGHT OUTER JOIN t2_D3880 ON t1_D3880.i = t2_D3880.i " +
+                "group by t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+
+		//Test group by on a column which is not part of SELECT query (query 
+		// uses USING clause). We see the incorrect behavior where the group 
+		// by does not raise an error for using 
+		// leftTable(orRightTable).joinColumn even though that column is not 
+		// part of the SELECT list. Just a note that ON clause is not allowed 
+		// on CROSS and NATURAL JOINS.
+		//
+		//Try INNER JOIN with USING clause.
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"inner join t2_D3880 USING(i) group by t1_D3880.i");
+		expRs = new String[][] {{"1"},{"2"}};
+		JDBC.assertFullResultSet(rs,expRs);
+		//Following query does not allow t2_D3880.i in group by clause
+		// because join column i the select query gets associated with
+		// left table in case of INNER JOIN.
+        assertStatementError("42Y36", s,
+        		"select i from t1_D3880 " +
+				"inner join t2_D3880 USING(i) group by t2_D3880.i");
+
+        //Test the GROUP BY problem with LEFT OUTER JOIN and USING clause.
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"LEFT OUTER JOIN t2_D3880 USING(i) GROUP BY t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//Following query does not allow t2_D3880.i in group by clause
+		// because join column i the select query gets associated with
+		// left table in case of LEFT OUTER JOIN.
+        assertStatementError("42Y36", s,
+        		"select i from t1_D3880 " +
+				"LEFT OUTER JOIN t2_D3880 USING(i) GROUP BY t2_D3880.i");
+
+        //Test the GROUP BY problem with RIGHT OUTER JOIN and USING clause.
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"RIGHT OUTER JOIN t2_D3880 USING(i) GROUP BY t2_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//Following query does not allow t1_D3880.i in group by clause
+		// because join column i the select query gets associated with
+		// right table in case of RIGHT OUTER JOIN.
+        assertStatementError("42Y36", s,
+        		"select i from t1_D3880 " +
+				"RIGHT OUTER JOIN t2_D3880 USING(i) GROUP BY t1_D3880.i");
+		
+		//The correct queries for GROUP BY and USING clause
+		//
+		//INNER JOIN with USING clause.
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"inner join t2_D3880 USING(i) group by t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//GROUP BY with LEFT OUTER JOIN and USING clause.
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"LEFT OUTER JOIN t2_D3880 USING(i) GROUP BY t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//GROUP BY with RIGHT OUTER JOIN and USING clause.
+		rs = s.executeQuery("select t2_D3880.i from t1_D3880 " +
+				"RIGHT OUTER JOIN t2_D3880 USING(i) GROUP BY t2_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		
+		//Test group by on a column which is not part of SELECT query (query 
+		// uses NATURAL JOIN). We see the incorrect behavior where the group 
+		// by does not raise an error for using 		
+		// leftTable(orRightTable).joinColumn even though that column is not
+		// part of the SELECT list. Just a note that a CROSS JOIN can't be a 
+		// NATURAL JOIN.
+		//
+		//Try the GROUP BY problem with NATURAL INNER JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"NATURAL inner join t2_D3880 group by t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//Test the GROUP BY problem with NATURAL LEFT OUTER JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"NATURAL LEFT OUTER JOIN t2_D3880 GROUP BY t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//Test the GROUP BY problem with NATURAL RIGHT OUTER JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"NATURAL RIGHT OUTER JOIN t2_D3880 GROUP BY t2_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		
+		//The correct queries for GROUP BY and NATURAL JOIN
+		//
+		//NATURAL INNER JOIN
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"NATURAL inner join t2_D3880 group by t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//NATURAL LEFT OUTER JOIN
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"NATURAL LEFT OUTER JOIN t2_D3880 GROUP BY t1_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+		//NATURAL RIGHT OUTER JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select t2_D3880.i from t1_D3880 " +
+				"NATURAL RIGHT OUTER JOIN t2_D3880 GROUP BY t2_D3880.i");
+		JDBC.assertFullResultSet(rs,expRs);
+
+		//Similar query for HAVING clause. HAVING clause should not be able
+		// to use a column which is not part of the SELECT column list.
+		// Doing this testing with USING clause
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"inner join t2_D3880 USING(i) group by t1_D3880.i " +
+				"HAVING t1_D3880.i > 1");
+		expRs = new String[][] {{"2"}};
+		JDBC.assertFullResultSet(rs,expRs);
+		// Doing the same test as above with NATURAL JOIN
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"NATURAL inner join  t2_D3880 group by t1_D3880.i " +
+				"HAVING t1_D3880.i > 1");
+		expRs = new String[][] {{"2"}};
+		JDBC.assertFullResultSet(rs,expRs);
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"LEFT OUTER join t2_D3880 USING(i) group by t1_D3880.i " +
+				"HAVING t1_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t1_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"NATURAL LEFT OUTER join t2_D3880 group by t1_D3880.i " +
+				"HAVING t1_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"RIGHT OUTER join t2_D3880 USING(i) group by t2_D3880.i " +
+				"HAVING t2_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		//Following query should have given compile time error. 
+		//Once DERBY-4631 is fixed, this query will run into compile time
+		// error for using t2_D3880.i in group by clause because that column
+		// is not part of the SELECT list. 
+		rs = s.executeQuery("select i from t1_D3880 " +
+				"NATURAL RIGHT OUTER join t2_D3880 group by t2_D3880.i " +
+				"HAVING t2_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		
+		//The correct query for HAVING should be written as follows
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"inner join t2_D3880 USING(i) group by t1_D3880.i " +
+				"HAVING t1_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"NATURAL inner join  t2_D3880 group by t1_D3880.i " +
+				"HAVING t1_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"LEFT OUTER join t2_D3880 USING(i) group by t1_D3880.i " +
+				"HAVING t1_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		rs = s.executeQuery("select t1_D3880.i from t1_D3880 " +
+				"NATURAL LEFT OUTER join t2_D3880 group by t1_D3880.i " +
+				"HAVING t1_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		rs = s.executeQuery("select t2_D3880.i from t1_D3880 " +
+				"RIGHT OUTER join t2_D3880 USING(i) group by t2_D3880.i " +
+				"HAVING t2_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+		rs = s.executeQuery("select t2_D3880.i from t1_D3880 " +
+				"NATURAL RIGHT OUTER join t2_D3880 group by t2_D3880.i " +
+				"HAVING t2_D3880.i > 1");
+		JDBC.assertFullResultSet(rs,expRs);
+    }
+
+	/**
 	 * DERBY-578: select with group by on a temp table caused NPE
 	 * @throws SQLException
 	 */

Modified: db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=1384814&r1=1384813&r2=1384814&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java (original)
+++ db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java Fri Sep 14 15:17:54 2012
@@ -227,6 +227,10 @@ public class JoinTest extends BaseJDBCTe
             s.executeQuery("select * from t1 cross join t2"),
             cross(T1, T2));
 
+        // Cross Join does not allow USING clause
+        assertStatementError(
+        		SYNTAX_ERROR, s, "select * from t1 cross join t1 USING(c1)");
+        
         // Self join
         JDBC.assertUnorderedResultSet(
             s.executeQuery("select * from t1 a cross join t1 b"),



Mime
View raw message