You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ma...@apache.org on 2012/09/14 06:28:44 UTC
svn commit: r1384638 - in /db/derby/code/branches/10.8: ./
java/engine/org/apache/derby/impl/sql/compile/
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mamta
Date: Fri Sep 14 04:28:44 2012
New Revision: 1384638
URL: http://svn.apache.org/viewvc?rev=1384638&view=rev
Log:
DERBY-4631 (Wrong join column returned by right outer join with NATURAL or USING and terriptry-based collation)
Backporting to 10.8
Modified:
db/derby/code/branches/10.8/ (props changed)
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromList.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Propchange: db/derby/code/branches/10.8/
------------------------------------------------------------------------------
Merged /db/derby/code/trunk:r1228332,1228777,1229266,1230873,1231296,1245143,1245226,1290888,1290919,1291057,1292108,1292941,1293467,1294275,1341204,1341266
Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromList.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromList.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromList.java Fri Sep 14 04:28:44 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.8/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromTable.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromTable.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/FromTable.java Fri Sep 14 04:28:44 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.8/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java Fri Sep 14 04:28:44 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.8/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java Fri Sep 14 04:28:44 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.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Fri Sep 14 04:28:44 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) );
@@ -1448,6 +1580,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.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Fri Sep 14 04:28:44 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;
@@ -3609,6 +3694,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))
@@ -3689,6 +3787,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.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java Fri Sep 14 04:28:44 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.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Fri Sep 14 04:28:44 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.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=1384638&r1=1384637&r2=1384638&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java Fri Sep 14 04:28:44 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"),