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/05/21 23:02:28 UTC
svn commit: r1341204 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mamta
Date: Mon May 21 21:02:27 2012
New Revision: 1341204
URL: http://svn.apache.org/viewvc?rev=1341204&view=rev
Log:
DERBY-4631 Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
To recap this issue, SQL:2003 says that 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 has it's on logic to retrieve the join column values. It always picks up join column's value from the left table when we are working with natural left outer join and it picks up the join column's value from the right table when we are working with natural right outer join.
But this logic does not work for all cases for right outer join. The fix provided in this patch will pick the join column's value based on following logic(this logic mimics the functionality of COALESCE)
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
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java?rev=1341204&r1=1341203&r2=1341204&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java Mon May 21 21:02:27 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/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java?rev=1341204&r1=1341203&r2=1341204&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java Mon May 21 21:02:27 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/trunk/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java?rev=1341204&r1=1341203&r2=1341204&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HalfOuterJoinNode.java Mon May 21 21:02:27 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/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=1341204&r1=1341203&r2=1341204&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java Mon May 21 21:02:27 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/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=1341204&r1=1341203&r2=1341204&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Mon May 21 21:02:27 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,7 +213,75 @@ 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.
+ *
+ * @param value True/False
+ */
+ 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 +329,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 +649,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 +722,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 +1582,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/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=1341204&r1=1341203&r2=1341204&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Mon May 21 21:02:27 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;
@@ -3603,6 +3688,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))
@@ -3683,6 +3781,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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?rev=1341204&r1=1341203&r2=1341204&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java Mon May 21 21:02:27 2012
@@ -483,14 +483,20 @@ public void testNorwayCollation() throws
* 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 does not coalesce as suggested by SQL spec, instead
- * it picks up join column's value from the left table when working with
- * natural left outer join and it picks up the join column's value from
- * the right table when working with natural right outer join. This works
- * ok with non-territory based databases. It works ok for natural left
- * outer join for territory based database but depending on the data
- * value, it does not always work for natural right outer join in a
- * territory based database as shown in the test cases below.
+ * 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 {
@@ -503,6 +509,58 @@ public void testUsingClauseAndNaturalJoi
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'");
@@ -510,13 +568,13 @@ public void testUsingClauseAndNaturalJoi
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))");
+ "(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 required
+ // 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
@@ -524,24 +582,30 @@ public void testUsingClauseAndNaturalJoi
// JOIN.
//
//Test NATURAL LEFT OUTER JOIN
- checkLangBasedQuery(s, "SELECT x, " +
+ 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, " +
+ 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 " +
+ 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"},{"c","c"}});
+ 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 " +
@@ -550,44 +614,50 @@ public void testUsingClauseAndNaturalJoi
null);
//Do the same test as above, but this time using the USING clause
// rather the NATURAL join
- s.executeUpdate("INSERT INTO derby4631_t3 " +
+ 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"},{"c","c"}});
- 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);
+ 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 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 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 " +
@@ -602,97 +672,101 @@ public void testUsingClauseAndNaturalJoi
// Hence row 'b' in derby4631_t2 will find a match in derby4631_t1
// with row 'B'.
- //Derby always picks up the join column's value from the right table
- // when working with RIGHT OUTER JOIN. This causes as issue with
- // case-sensitive collation for the given data in this test case.
- // We get wrong results below for the 1st column in 2nd row which is
- // 'B'. 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 but Derby instead always picks
- // up right table's column value
- //
- //Following query is returning INCORRECT data and once DERBY-4631 is
- // fixed, we should get the expected results as
- // new String[][] {{"A","A"},{"b","b"}});
- checkLangBasedQuery(s, "SELECT x, " +
+ //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"}});
+ new String[][] {{"A","A"},{"b","b"}});
//Do the same test as above, but this time using the USING clause
// rather the NATURAL join
- //
- //Following query is returning INCORRECT data and once DERBY-4631 is
- // fixed, we should get the expected results as
- // new String[][] {{"A","A"},{"b","b"}});
- checkLangBasedQuery(s, "SELECT x, " +
+ 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"}});
+ new String[][] {{"A","A"},{"b","b"}});
//Test insert into a table with data from NATURAL RIGHT OUTER JOIN
- s.executeUpdate("INSERT INTO derby4631_t3 " +
- "SELECT x, " +
+ 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"},{"B","b"}});
+ 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);
+ 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 " +
+ 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"},{"B","b"}});
+ 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);
+ "(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 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"}});
+ 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"}});
+ 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, " +
+ 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"}});
@@ -701,7 +775,7 @@ public void testUsingClauseAndNaturalJoi
//
//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, " +
+ checkLangBasedQuery(s, "SELECT x x1, " +
"coalesce(derby4631_t2.x, derby4631_t1.x) cx " +
"FROM derby4631_t2 RIGHT OUTER JOIN derby4631_t1 " +
"USING(x)",
@@ -725,13 +799,13 @@ public void testUsingClauseAndNaturalJoi
// 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}});
+ "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, " +
@@ -742,16 +816,16 @@ public void testUsingClauseAndNaturalJoi
"USING(x,y,z)",
new String[][] {{"b","b","Y","Y","2","2","22"},
{"c","c","Y","Y","2","2",null}});
- //Test LEFT OUTER JOIN using only 2 of the 3 columns
+ //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"}});
+ "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, " +
@@ -761,8 +835,8 @@ public void testUsingClauseAndNaturalJoi
"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"}});
+ {"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, " +
@@ -771,30 +845,30 @@ public void testUsingClauseAndNaturalJoi
"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"}});
+ {"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"}});
+ "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}});
+ "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, " +
@@ -826,14 +900,14 @@ public void testUsingClauseAndNaturalJoi
{"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"}});
+ "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, " +
@@ -929,67 +1003,62 @@ public void testUsingClauseAndNaturalJoi
new String[][] {{"b","Y","b","Y"},{"c","x","c","x"}});
if (collation != null && collation.equals("TERRITORY_BASED:SECONDARY")) {
- //Following query is returning INCORRECT data and once DERBY-4631 is
- // fixed, we should get the expected results as
- // new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
+ //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"}});
+ 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"}});
+ 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"}});
+ 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"}});
+ 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"}});
+ 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
- //
- //Following query is returning INCORRECT data and once DERBY-4631 is
- // fixed, we should get the expected results as
- // new String[][] {{"A","z","A","z"},{"b","Y","b","Y"}});
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"}});
+ 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"}});
+ 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"}});
+ 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"}});
+ 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"}});
+ 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.
@@ -1036,6 +1105,7 @@ public void testUsingClauseAndNaturalJoi
s.executeUpdate("DROP VIEW derby4631_v2");
s.executeUpdate("DROP TABLE derby4631_t1");
s.executeUpdate("DROP TABLE derby4631_t2");
+
}
private void joinTesting(Statement s,