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/01/13 04:04:54 UTC
svn commit: r1230873 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang:
GroupByTest.java JoinTest.java
Author: mamta
Date: Fri Jan 13 03:04:54 2012
New Revision: 1230873
URL: http://svn.apache.org/viewvc?rev=1230873&view=rev
Log:
DERBY-4631 Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
I am adding more tests for this jira to show the kind of joins and queries that are impacted by this defect.
In short, any join query which is either a NATURAL join or has a USING clause can run into the two problems as described in this jira.
Problem 1)As per SQL spec, the join column's value should be derived by
COALESCE(leftTable.joinColumn, rightTable.joinColumn).
But Derby has it's own rule for deriving the join column's value. Derby's implementation, for a right outer join, gets the join column's value from the right table and for left outer join, it gets the value from the left table. This logic works for most cases, but it can give incorrect value for a right outer join (with NATURAL JOIN or USING clause) in case of a territory based database. Additionally, the join column in the SELECT list(with NATURAL JOIN or USING clause) gets associated with the left table's join column(for inner joins and left outer joins) or it gets associated with the right table's join column(for rihgt outer joins). Since SQL spec requires the join column to be COALESCE ( leftTable.C, rightTable.C ) AS C, the join column should not be really associated with any of the 2 join tables.
Problem 2)The Derby's assocation of join column to left or right table as described in problem 1) causes it to allow incorrect queries. eg query
select i from t1_D3880 inner join t2_D3880 USING(i) group by t1_D3880.i;
The query above works because join column i got associated with left table which is t1_D3880. If the query was rewritten to do the group by on right table, it would fail.
select i from t1_D3880 inner join t2_D3880 USING(i) group by t2_D3880.i;
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=1230873&r1=1230872&r2=1230873&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Fri Jan 13 03:04:54 2012
@@ -1463,6 +1463,195 @@ 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.
+ //
+ //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.
+ 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);
+ 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.
+ rs = s.executeQuery("select i from t1_D3880 " +
+ "LEFT OUTER JOIN t2_D3880 USING(i) GROUP BY t1_D3880.i");
+ JDBC.assertFullResultSet(rs,expRs);
+ 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.
+ rs = s.executeQuery("select i from t1_D3880 " +
+ "RIGHT OUTER JOIN t2_D3880 USING(i) GROUP BY t2_D3880.i");
+ JDBC.assertFullResultSet(rs,expRs);
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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);
+ 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);
+ 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);
+ 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);
+ 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=1230873&r1=1230872&r2=1230873&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java Fri Jan 13 03:04:54 2012
@@ -227,6 +227,10 @@ public class JoinTest extends BaseJDBCTe
s.executeQuery("select * from t1 cross join t2"),
cross(T1, T2));
+ // Cross Join does not allow USING clause
+ assertStatementError(
+ SYNTAX_ERROR, s, "select * from t1 cross join t1 USING(c1)");
+
// Self join
JDBC.assertUnorderedResultSet(
s.executeQuery("select * from t1 a cross join t1 b"),