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 ka...@apache.org on 2009/10/08 13:09:20 UTC
svn commit: r823126 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: kahatlen
Date: Thu Oct 8 11:09:19 2009
New Revision: 823126
URL: http://svn.apache.org/viewvc?rev=823126&view=rev
Log:
DERBY-4370: Implement JOIN ... USING syntax
Made the parser accept USING and added tests for the new syntax.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
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=823126&r1=823125&r2=823126&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 Thu Oct 8 11:09:19 2009
@@ -3724,6 +3724,7 @@
* @return A list of the join columns from this list
*/
ResultColumnList getJoinColumns(ResultColumnList joinColumns)
+ throws StandardException
{
ResultColumnList newRCL = new ResultColumnList();
@@ -3745,6 +3746,11 @@
ResultColumn xferRC = getResultColumn(columnName);
+ if (xferRC == null) {
+ throw StandardException.newException(
+ SQLState.LANG_COLUMN_NOT_FOUND, columnName);
+ }
+
// Add the RC to the new list.
newRCL.addElement(xferRC);
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=823126&r1=823125&r2=823126&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Thu Oct 8 11:09:19 2009
@@ -9250,6 +9250,13 @@
onOrUsingClause[USING_CLAUSE] = usingClause;
return onOrUsingClause;
}
+|
+ usingClause = namedColumnsJoin()
+ {
+ onOrUsingClause[ON_CLAUSE] = joinClause;
+ onOrUsingClause[USING_CLAUSE] = usingClause;
+ return onOrUsingClause;
+ }
}
ValueNode
@@ -9265,6 +9272,19 @@
}
}
+ResultColumnList
+namedColumnsJoin() throws StandardException :
+{
+ ResultColumnList usingClause = (ResultColumnList) nodeFactory.getNode(
+ C_NodeTypes.RESULT_COLUMN_LIST, getContextManager());
+}
+{
+ <USING> <LEFT_PAREN> columnNameList(usingClause) <RIGHT_PAREN>
+ {
+ return usingClause;
+ }
+}
+
/*
* <A NAME="tableValueConstructor">tableValueConstructor</A>
*/
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?rev=823126&r1=823125&r2=823126&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out Thu Oct 8 11:09:19 2009
@@ -1189,24 +1189,6 @@
1 row inserted/updated/deleted
ij> INSERT INTO t2 VALUES(3,4);
1 row inserted/updated/deleted
-ij> -- (2) USING should be disabled in INNER JOIN of SELECT statement
-SELECT * FROM t1 INNER JOIN t2 USING (col1);
-ERROR 42X01: Syntax error: Encountered "USING" at line 2, column 32.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> -- (3) USING should be disabled in INNER JOIN of SELECT statement
-SELECT * FROM t1 LEFT OUTER JOIN t2 USING (col1);
-ERROR 42X01: Syntax error: Encountered "USING" at line 2, column 37.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> -- (4) USING should be disabled in INNER JOIN of SELECT statement
-SELECT * FROM t1 RIGHT OUTER JOIN t2 USING (col1);
-ERROR 42X01: Syntax error: Encountered "USING" at line 2, column 38.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- (5) TRUE and FALSE constants should be disabled in WHERE clause of SELECT statement
SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE true;
ERROR 42X01: Syntax error: true.
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out?rev=823126&r1=823125&r2=823126&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/innerjoin.out Thu Oct 8 11:09:19 2009
@@ -51,7 +51,7 @@
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- empty column list
select * from t1 join t2 using ();
-ERROR 42X01: Syntax error: Encountered "using" at line 2, column 26.
+ERROR 42X01: Syntax error: Encountered ")" at line 2, column 33.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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=823126&r1=823125&r2=823126&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 Thu Oct 8 11:09:19 2009
@@ -36,6 +36,8 @@
public class JoinTest extends BaseJDBCTestCase {
private static final String SYNTAX_ERROR = "42X01";
private static final String AMBIGUOUS_COLNAME = "42X03";
+ private static final String COLUMN_NOT_IN_SCOPE = "42X04";
+ private static final String NON_COMPARABLE = "42818";
public JoinTest(String name) {
super(name);
@@ -401,4 +403,243 @@
}
return result;
}
+
+ /**
+ * Tests for the USING clause added in DERBY-4370.
+ */
+ public void testUsingClause() throws SQLException {
+ // No auto-commit to make it easier to clean up the test tables.
+ setAutoCommit(false);
+
+ Statement s = createStatement();
+
+ s.execute("create table t1(a int, b int, c int)");
+ s.execute("create table t2(b int, c int, d int)");
+ s.execute("create table t3(d int, e varchar(5), f int)");
+
+ s.execute("insert into t1 values (1,2,3),(2,3,4),(4,4,4)");
+ s.execute("insert into t2 values (1,2,3),(2,3,4),(5,5,5)");
+ s.execute("insert into t3 values " +
+ "(2,'abc',3),(4,'def',5),(null,null,null)");
+
+ // Simple one-column USING clauses for the different joins. Expected
+ // column order: First, the columns from the USING clause. Then,
+ // non-join columns from left side followed by non-join columns from
+ // right side.
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 join t2 using (b)"),
+ new String[][]{{"2", "1", "3", "3", "4"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 inner join t2 using (b)"),
+ new String[][]{{"2", "1", "3", "3", "4"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 left join t2 using (b)"),
+ new String[][]{
+ {"2", "1", "3", "3", "4"},
+ {"3", "2", "4", null, null},
+ {"4", "4", "4", null, null}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 left outer join t2 using (b)"),
+ new String[][]{
+ {"2", "1", "3", "3", "4"},
+ {"3", "2", "4", null, null},
+ {"4", "4", "4", null, null}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 right join t2 using (b)"),
+ new String[][]{
+ {"2", "1", "3", "3", "4"},
+ {"1", null, null, "2", "3"},
+ {"5", null, null, "5", "5"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 right outer join t2 using (b)"),
+ new String[][]{
+ {"2", "1", "3", "3", "4"},
+ {"1", null, null, "2", "3"},
+ {"5", null, null, "5", "5"}});
+
+ // Two-column clauses
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 join t2 using (b, c)"),
+ new String[][]{{"2", "3", "1", "4"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 join t2 using (c, b)"),
+ new String[][]{{"3", "2", "1", "4"}});
+
+ // Qualified asterisks should expand to all non-join columns
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select t1.* from t1 join t2 using (b, c)"),
+ "1");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select t2.* from t1 join t2 using (b, c)"),
+ "4");
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select t1.*, t2.* from t1 join t2 using (b, c)"),
+ new String[][]{{"1", "4"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select t1.* from t1 left join t2 using (b, c)"),
+ new String[][]{{"1"}, {"2"}, {"4"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select t1.* from t1 right join t2 using (b, c)"),
+ new String[][]{{"1"}, {null}, {null}});
+
+ // USING clause can be in between joins or at the end
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery(
+ "select t3.e from t1 join t2 using (b) join t3 using (d)"),
+ "def");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery(
+ "select t3.e from t1 join t2 join t3 using (d) using (b)"),
+ "def");
+
+ // USING can be placed in between or after outer joins as well, but
+ // then the results are different (different nesting of the joins).
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 left join t2 using (b) " +
+ "right join t3 using (d)"),
+ new String[][] {
+ {"2", null, null, null, null, "abc", "3"},
+ {"4", "2", "1", "3", "3", "def", "5"},
+ {null, null, null, null, null, null, null}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 left join t2 " +
+ "right join t3 using (d) using (b)"),
+ new String[][] {
+ {"2", "1", "3", "4", "3", "def", "5"},
+ {"3", "2", "4", null, null, null, null},
+ {"4", "4", "4", null, null, null, null}});
+
+ // Should be able to reference a non-join column without qualifier if
+ // it's unambiguous.
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select a from t1 join t2 using (b, c)"),
+ "1");
+
+ // USING clause should accept quoted identifiers.
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 join t2 using (\"B\")"),
+ new String[][]{{"2", "1", "3", "3", "4"}});
+
+ // When referencing a join column X without a table qualifier in an
+ // outer join, the value should be coalesce(t1.x, t2.x). That is, the
+ // value should be non-null if one of the qualified columns is non-null.
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select b from t1 left join t2 using (b)"),
+ new String[][]{{"2"}, {"3"}, {"4"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select b from t1 right join t2 using (b)"),
+ new String[][]{{"1"}, {"2"}, {"5"}});
+ JDBC.assertUnorderedResultSet(s.executeQuery(
+ "select d, t2.d, t3.d from t2 left join t3 using (d)"),
+ new String[][] {
+ {"3", "3", null},
+ {"4", "4", "4"},
+ {"5", "5", null}});
+ JDBC.assertUnorderedResultSet(s.executeQuery(
+ "select d, t2.d, t3.d from t2 right join t3 using (d)"),
+ new String[][] {
+ {"2", null, "2"},
+ {"4", "4", "4"},
+ {null, null, null}});
+ JDBC.assertEmpty(s.executeQuery(
+ "select * from t2 left join t3 using (d) where d is null"));
+ JDBC.assertUnorderedResultSet(s.executeQuery(
+ "select * from t2 right join t3 using (d) where d is null"),
+ new String[][]{{null, null, null, null, null}});
+
+ // Verify that ORDER BY picks up the correct column.
+ JDBC.assertFullResultSet(
+ s.executeQuery("select c from t1 left join t2 using (b, c) " +
+ "order by c desc nulls last"),
+ new String[][]{{"4"}, {"4"}, {"3"}});
+ JDBC.assertFullResultSet(
+ s.executeQuery("select c from t1 left join t2 using (b, c) " +
+ "order by t1.c desc nulls last"),
+ new String[][]{{"4"}, {"4"}, {"3"}});
+ JDBC.assertFullResultSet(
+ s.executeQuery("select c from t1 left join t2 using (b, c) " +
+ "order by t2.c desc nulls last"),
+ new String[][]{{"3"}, {"4"}, {"4"}});
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select c from t1 right join t2 using (b, c) " +
+ "order by c desc nulls last fetch next row only"),
+ "5");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select c from t1 right join t2 using (b, c) " +
+ "order by t1.c desc nulls last fetch next row only"),
+ "3");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select c from t1 right join t2 using (b, c) " +
+ "order by t2.c desc nulls last fetch next row only"),
+ "5");
+
+ // Aggregate + GROUP BY
+ JDBC.assertFullResultSet(
+ s.executeQuery("select b, count(t2.b) from t1 left join t2 " +
+ "using (b) group by b order by b"),
+ new String[][]{{"2", "1"}, {"3", "0"}, {"4", "0"}});
+
+ // Using aliases to construct common column names.
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 table_a(col1, col2, col3) " +
+ "inner join t3 table_b(col1, col2, col3) " +
+ "using (col1)"),
+ new String[][] {
+ {"2", "3", "4", "abc", "3"},
+ {"4", "4", "4", "def", "5"}});
+
+ // ***** Negative tests *****
+
+ // Use of unqualified non-join columns should result in errors if
+ // columns with that name exist in both tables.
+ assertStatementError(AMBIGUOUS_COLNAME, s,
+ "select b from t1 join t2 using (b) join t3 using(c)");
+ assertStatementError(AMBIGUOUS_COLNAME, s,
+ "select b from t1 join t2 using (c)");
+ assertStatementError(AMBIGUOUS_COLNAME, s,
+ "select * from t1 join t2 using (b) order by c");
+
+ // Column names in USING should not be qualified.
+ assertStatementError(SYNTAX_ERROR, s,
+ "select * from t1 join t2 using (t1.b)");
+
+ // USING needs parens even if only one column is specified.
+ assertStatementError(SYNTAX_ERROR, s,
+ "select * from t1 join t2 using b");
+
+ // Empty column list is not allowed.
+ assertStatementError(SYNTAX_ERROR, s,
+ "select * from t1 join t2 using ()");
+
+ // Join columns with non-comparable data types should fail (trying to
+ // compare INT and VARCHAR).
+ assertStatementError(NON_COMPARABLE, s,
+ "select * from t2 a(x,y,z) join t3 b(x,y,z) using(y)");
+
+ // The two using clauses come in the wrong order, so expect that
+ // column B is not found.
+ assertStatementError(COLUMN_NOT_IN_SCOPE, s,
+ "select t3.e from t1 join t2 join t3 using (b) using (d)");
+
+ // References to non-common or non-existent columns in the using clause
+ // should result in an error.
+ assertStatementError(COLUMN_NOT_IN_SCOPE, s,
+ "select * from t1 join t2 using (a)");
+ assertStatementError(COLUMN_NOT_IN_SCOPE, s,
+ "select * from t1 join t2 using (d)");
+ assertStatementError(COLUMN_NOT_IN_SCOPE, s,
+ "select * from t1 join t2 using (a,d)");
+ assertStatementError(COLUMN_NOT_IN_SCOPE, s,
+ "select * from t1 join t2 using (a,b,c)");
+ assertStatementError(COLUMN_NOT_IN_SCOPE, s,
+ "select * from t1 join t2 using (x)");
+ assertStatementError(COLUMN_NOT_IN_SCOPE, s,
+ "select * from t1 join t2 using (b,c,x)");
+
+ // If two columns in the left table are named B, we should get an
+ // error when specifying B as a join column, since we don't know which
+ // of the columns to use.
+ assertStatementError(AMBIGUOUS_COLNAME, s,
+ "select * from (t1 cross join t2) join t2 tt2 using(b)");
+ }
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql?rev=823126&r1=823125&r2=823126&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql Thu Oct 8 11:09:19 2009
@@ -606,15 +606,6 @@
INSERT INTO t1 VALUES(3,4);
INSERT INTO t2 VALUES(3,4);
--- (2) USING should be disabled in INNER JOIN of SELECT statement
-SELECT * FROM t1 INNER JOIN t2 USING (col1);
-
--- (3) USING should be disabled in INNER JOIN of SELECT statement
-SELECT * FROM t1 LEFT OUTER JOIN t2 USING (col1);
-
--- (4) USING should be disabled in INNER JOIN of SELECT statement
-SELECT * FROM t1 RIGHT OUTER JOIN t2 USING (col1);
-
-- (5) TRUE and FALSE constants should be disabled in WHERE clause of SELECT statement
SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE true;
SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1 WHERE false;