Author: kahatlen
Date: Fri Jan 8 12:37:01 2010
New Revision: 897190
URL: http://svn.apache.org/viewvc?rev=897190&view=rev
Log:
DERBY-4495: Add NATURAL JOIN syntax
Support NATURAL JOIN by adding an implicit USING clause with all the
common columns.
Modified:
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/sqlgrammar.jj
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
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=897190&r1=897189&r2=897190&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 Fri Jan
8 12:37:01 2010
@@ -62,6 +62,9 @@
import org.apache.derby.iapi.util.PropertyUtil;
import org.apache.derby.iapi.services.classfile.VMOpcode;
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
import java.util.Properties;
import java.util.Vector;
@@ -85,6 +88,9 @@
public static final int FULLOUTERJOIN = 5;
public static final int UNIONJOIN = 6;
+ /** If this flag is true, this node represents a natural join. */
+ private boolean naturalJoin;
+
private boolean optimized;
private PredicateList leftPredicateList;
@@ -620,6 +626,22 @@
return resultColumn;
}
+ /**
+ * Bind the expressions under this node.
+ */
+ public void bindExpressions(FromList fromListParam)
+ throws StandardException
+ {
+ super.bindExpressions(fromListParam);
+
+ // Now that both the left and the right side of the join have been
+ // bound, we know the column names and can transform a natural join
+ // into a join with a USING clause.
+ if (naturalJoin) {
+ usingClause = getCommonColumnsForNaturalJoin();
+ }
+ }
+
/**
* Bind the result columns of this ResultSetNode when there is no
* base table to bind them to. This is useful for SELECT statements,
@@ -830,13 +852,7 @@
* We need to bind the CRs a side at a time to ensure that
* we don't find an bogus ambiguous column reference. (Bug 377)
*/
- joinClause = (AndNode) getNodeFactory().getNode(
- C_NodeTypes.AND_NODE,
- null,
- null,
- getContextManager());
- AndNode currAnd = (AndNode) joinClause;
- ValueNode trueNode = (ValueNode) getNodeFactory().getNode(
+ joinClause = (ValueNode) getNodeFactory().getNode(
C_NodeTypes.BOOLEAN_CONSTANT_NODE,
Boolean.TRUE,
getContextManager());
@@ -849,20 +865,6 @@
ColumnReference rightCR;
ResultColumn rc = (ResultColumn) usingClause.elementAt(index);
- /* currAnd starts as first point of insertion (leftOperand == null)
- * and becomes last point of insertion.
- */
- if (currAnd.getLeftOperand() != null)
- {
- currAnd.setRightOperand(
- (AndNode) getNodeFactory().getNode(
- C_NodeTypes.AND_NODE,
- null,
- null,
- getContextManager()));
- currAnd = (AndNode) currAnd.getRightOperand();
- }
-
/* Create and bind the left CR */
fromListParam.insertElementAt(leftResultSet, 0);
leftCR = (ColumnReference) getNodeFactory().getNode(
@@ -896,15 +898,17 @@
getContextManager());
equalsNode.bindComparisonOperator();
- currAnd.setLeftOperand(equalsNode);
- /* The right deep chain of AndNodes ends in a BinaryTrueNode.
- * NOTE: We set it for every AndNode, even though we will
- * overwrite it if this is not the last column in the list,
- * because postBindFixup() expects both the AndNode to have
- * both the left and right operands.
- */
- currAnd.setRightOperand(trueNode);
- currAnd.postBindFixup();
+ // Create a new join clause by ANDing the new = condition and
+ // the old join clause.
+ AndNode newJoinClause = (AndNode) getNodeFactory().getNode(
+ C_NodeTypes.AND_NODE,
+ equalsNode,
+ joinClause,
+ getContextManager());
+
+ newJoinClause.postBindFixup();
+
+ joinClause = newJoinClause;
}
}
@@ -947,6 +951,58 @@
}
}
+ /**
+ * Generate a result column list with all the column names that appear on
+ * both sides of the join operator. Those are the columns to use as join
+ * columns in a natural join.
+ *
+ * @return RCL with all the common columns
+ * @throws StandardException on error
+ */
+ private ResultColumnList getCommonColumnsForNaturalJoin()
+ throws StandardException {
+ ResultColumnList leftRCL =
+ getLeftResultSet().getAllResultColumns(null);
+ ResultColumnList rightRCL =
+ getRightResultSet().getAllResultColumns(null);
+
+ List columnNames = extractColumnNames(leftRCL);
+ columnNames.retainAll(extractColumnNames(rightRCL));
+
+ ResultColumnList commonColumns =
+ (ResultColumnList) getNodeFactory().getNode(
+ C_NodeTypes.RESULT_COLUMN_LIST,
+ getContextManager());
+
+ for (Iterator it = columnNames.iterator(); it.hasNext(); ) {
+ String name = (String) it.next();
+ ResultColumn rc = (ResultColumn) getNodeFactory().getNode(
+ C_NodeTypes.RESULT_COLUMN,
+ name,
+ null,
+ getContextManager());
+ commonColumns.addResultColumn(rc);
+ }
+
+ return commonColumns;
+ }
+
+ /**
+ * Extract all the column names from a result column list.
+ *
+ * @param rcl the result column list to extract the names from
+ * @return a list of all the column names in the RCL
+ */
+ private static List extractColumnNames(ResultColumnList rcl) {
+ ArrayList names = new ArrayList();
+
+ for (int i = 0; i < rcl.size(); i++) {
+ ResultColumn rc = (ResultColumn) rcl.elementAt(i);
+ names.add(rc.getName());
+ }
+
+ return names;
+ }
/**
* Put a ProjectRestrictNode on top of each FromTable in the FromList.
@@ -1879,6 +1935,14 @@
this.aggregateVector = aggregateVector;
}
+ /**
+ * Flag this as a natural join so that an implicit USING clause will
+ * be generated in the bind phase.
+ */
+ void setNaturalJoin() {
+ naturalJoin = true;
+ }
+
/**
* Return the logical left result set for this qualified
* join node.
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=897190&r1=897189&r2=897190&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 Fri Jan
8 12:37:01 2010
@@ -1430,6 +1430,7 @@
* CROSS JOIN
* LEFT OUTER JOIN
* RIGHT OUTER JOIN
+ * NATURAL [ { RIGHT | LEFT } [ OUTER ] | INNER ] JOIN
*
* @return TRUE iff the next set of tokens is the beginning of a
* joinedTableExpression().
@@ -1453,6 +1454,10 @@
{
retval = true;
}
+ else if (tokKind1 == NATURAL)
+ {
+ retval = true;
+ }
else if ((tokKind1 == LEFT || tokKind1 == RIGHT) && tokKind2 == OUTER)
{
if (getToken(3).kind == JOIN)
@@ -1757,6 +1762,68 @@
(expression instanceof UnionNode &&
((UnionNode)expression).tableConstructor());
}
+
+ /**
+ * Construct a new join node.
+ *
+ * @param leftRSN the left side of the join
+ * @param rightRSN the right side of the join
+ * @param onClause the ON clause, or null if there is no ON clause
+ * @param usingClause the USING clause, or null if there is no USING clause
+ * @param joinType the type of the join (one of the constants INNERJOIN,
+ * LEFTOUTERJOIN or RIGHTOUTERJOIN in JoinNode)
+ * @return a new join node
+ */
+ private JoinNode newJoinNode(ResultSetNode leftRSN, ResultSetNode rightRSN,
+ ValueNode onClause, ResultColumnList usingClause,
+ int joinType)
+ throws StandardException
+ {
+ switch(joinType)
+ {
+ case JoinNode.INNERJOIN:
+ return (JoinNode) nodeFactory.getNode(
+ C_NodeTypes.JOIN_NODE,
+ leftRSN,
+ rightRSN,
+ onClause,
+ usingClause,
+ null,
+ null,
+ null,
+ getContextManager());
+
+ case JoinNode.LEFTOUTERJOIN:
+ return (JoinNode) nodeFactory.getNode(
+ C_NodeTypes.HALF_OUTER_JOIN_NODE,
+ leftRSN,
+ rightRSN,
+ onClause,
+ usingClause,
+ Boolean.FALSE,
+ null,
+ getContextManager());
+
+ case JoinNode.RIGHTOUTERJOIN:
+ return (JoinNode) nodeFactory.getNode(
+ C_NodeTypes.HALF_OUTER_JOIN_NODE,
+ leftRSN,
+ rightRSN,
+ onClause,
+ usingClause,
+ Boolean.TRUE,
+ null,
+ getContextManager());
+
+ default:
+ if (SanityManager.DEBUG)
+ {
+ SanityManager.THROWASSERT("Unexpected joinType: " + joinType);
+ }
+ return null;
+ }
+ }
+
}
PARSER_END(SQLParser)
@@ -9210,6 +9277,11 @@
{
return joinNode;
}
+|
+ joinNode = naturalJoin(leftRSN, nestedInParens)
+ {
+ return joinNode;
+ }
}
TableOperatorNode
@@ -9221,16 +9293,12 @@
{
<CROSS> <JOIN> rightRSN = tableFactor()
{
- TableOperatorNode ton = (TableOperatorNode) nodeFactory.getNode(
- C_NodeTypes.JOIN_NODE,
+ TableOperatorNode ton = newJoinNode(
leftRSN,
rightRSN,
null, // no ON clause in CROSS JOIN
null, // no USING clause in CROSS JOIN
- null,
- null,
- null,
- getContextManager());
+ JoinNode.INNERJOIN);
ton.setNestedInParens(nestedInParens);
return ton;
}
@@ -9267,53 +9335,7 @@
JoinNode.joinTypeToString(joinType));
}
- switch(joinType)
- {
- case JoinNode.INNERJOIN:
- ton = (TableOperatorNode) nodeFactory.getNode(
- C_NodeTypes.JOIN_NODE,
- leftRSN,
- rightRSN,
- onClause,
- usingClause,
- null,
- null,
- null,
- getContextManager());
- break;
-
- case JoinNode.LEFTOUTERJOIN:
- ton = (TableOperatorNode) nodeFactory.getNode(
- C_NodeTypes.HALF_OUTER_JOIN_NODE,
- leftRSN,
- rightRSN,
- onClause,
- usingClause,
- Boolean.FALSE,
- null,
- getContextManager());
- break;
-
- case JoinNode.RIGHTOUTERJOIN:
- ton = (TableOperatorNode) nodeFactory.getNode(
- C_NodeTypes.HALF_OUTER_JOIN_NODE,
- leftRSN,
- rightRSN,
- onClause,
- usingClause,
- Boolean.TRUE,
- null,
- getContextManager());
- break;
-
-
- default:
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(false, "Unexpected joinType");
- }
- return null;
- }
+ ton = newJoinNode(leftRSN, rightRSN, onClause, usingClause, joinType);
/* Mark whether or not we are nested within parens */
ton.setNestedInParens(nestedInParens);
@@ -9321,6 +9343,22 @@
}
}
+TableOperatorNode naturalJoin(ResultSetNode leftRSN, boolean nestedInParens)
+throws StandardException :
+{
+ int joinType = JoinNode.INNERJOIN;
+ ResultSetNode rightRSN;
+}
+{
+ <NATURAL> [ joinType = joinType() ] <JOIN> rightRSN = tableFactor()
+ {
+ JoinNode node = newJoinNode(leftRSN, rightRSN, null, null, joinType);
+ node.setNestedInParens(nestedInParens);
+ node.setNaturalJoin();
+ return node;
+ }
+}
+
int
joinType() throws StandardException :
{
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=897190&r1=897189&r2=897190&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 8 12:37:01 2010
@@ -701,6 +701,233 @@
}
/**
+ * Tests for the NATURAL JOIN syntax added in DERBY-4495.
+ */
+ public void testNaturalJoin() throws SQLException {
+ // No auto-commit to make it easier to clean up the test tables.
+ setAutoCommit(false);
+
+ final String[][] T1 = {
+ {"1", "2", "3"}, {"4", "5", "6"}, {"7", "8", "9"}
+ };
+
+ final String[][] T2 = {
+ {"4", "3", "2"}, {"1", "2", "3"}, {"3", "2", "1"}
+ };
+
+ final String[][] T3 = {{"4", "100"}};
+
+ Statement s = createStatement();
+ s.execute("create table t1(a int, b int, c int)");
+ s.execute("create table t2(d int, c int, b int)");
+ s.execute("create table t3(d int, e int)");
+
+ fillTable("insert into t1 values (?,?,?)", T1);
+ fillTable("insert into t2 values (?,?,?)", T2);
+ fillTable("insert into t3 values (?,?)", T3);
+
+ // Join on single common column (D)
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t2 natural join t3"),
+ new String[][] {{"4", "3", "2", "100"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t3 natural join t2"),
+ new String[][] {{"4", "100", "3", "2"}});
+
+ // Join on two common columns (B and C). Expected column ordering:
+ // 1) all common columns, same order as in left table
+ // 2) all non-common columns from left table
+ // 3) all non-common columns from right table
+ ResultSet rs = s.executeQuery("select * from t1 natural join t2");
+ JDBC.assertColumnNames(rs, new String[] {"B", "C", "A", "D"});
+ JDBC.assertUnorderedResultSet(
+ rs, new String[][] {{"2", "3", "1", "4"}});
+
+ rs = s.executeQuery("select * from t2 natural join t1");
+ JDBC.assertColumnNames(rs, new String[] {"C", "B", "D", "A"});
+ JDBC.assertUnorderedResultSet(
+ rs, new String[][] {{"3", "2", "4", "1"}});
+
+ // No common column names means cross join
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 natural join t3"),
+ cross(T1, T3));
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 as a(c1, c2, c3) " +
+ "natural join t2 as b(c4, c5, c6)"),
+ cross(T1, T2));
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from (values 1,2) v1(x) " +
+ "natural join (values 'a','b') v2(y)"),
+ new String[][] {{"1","a"}, {"1","b"}, {"2","a"}, {"2","b"}});
+
+ // Join two sub-queries
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from (select * from t1) table1 " +
+ "natural join (select * from t2) table2"),
+ new String[][] {{"2", "3", "1", "4"}});
+
+ // Expressions with no explicit names are not common columns because
+ // we give them different implicit names (typically 1, 2, 3, etc...)
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from (select b+c from t1) as x " +
+ "natural join (select b+c from t2) as y"),
+ cross(new String[][] {{"5"}, {"11"}, {"17"}}, // b+c in t1
+ new String[][] {{"5"}, {"5"}, {"3"}})); // b+c in t2
+
+ // Expressions with explicit names may be common columns, if the
+ // names are equal
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from (select b+c c1 from t1) as x " +
+ "natural join (select b+c c1 from t2) as y"),
+ new String[][] {{"5"}, {"5"}});
+
+ // Multiple JOIN operators
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 natural join t2 " +
+ "natural join t3"),
+ new String[][] {{"4", "2", "3", "1", "100"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from (t1 natural join t2) " +
+ "natural join t3"),
+ new String[][] {{"4", "2", "3", "1", "100"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 natural join " +
+ "(t2 natural join t3)"),
+ new String[][] {{"2", "3", "1", "4", "100"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery(
+ "select * from t1 natural join t2 cross join t3"),
+ new String[][] {{"2", "3", "1", "4", "4", "100"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery(
+ "select * from t1 natural join t2 inner join t3 on 1=1"),
+ new String[][] {{"2", "3", "1", "4", "4", "100"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery(
+ "select * from t1 cross join t2 natural join t3"),
+ new String[][] {
+ {"4", "1", "2", "3", "3", "2", "100"},
+ {"4", "4", "5", "6", "3", "2", "100"},
+ {"4", "7", "8", "9", "3", "2", "100"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery(
+ "select * from t1 inner join t2 on 1=1 natural join t3"),
+ new String[][] {
+ {"4", "1", "2", "3", "3", "2", "100"},
+ {"4", "4", "5", "6", "3", "2", "100"},
+ {"4", "7", "8", "9", "3", "2", "100"}});
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery(
+ "select * from t1 inner join t2 natural join t3 on 1=1"),
+ new String[][] {
+ {"1", "2", "3", "4", "3", "2", "100"},
+ {"4", "5", "6", "4", "3", "2", "100"},
+ {"7", "8", "9", "4", "3", "2", "100"}});
+
+ // NATURAL JOIN in INSERT context
+ s.execute("create table insert_src (c1 int, c2 int, c3 int, c4 int)");
+ s.execute("insert into insert_src select * from t1 natural join t2");
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from insert_src"),
+ new String[][] {{"2", "3", "1", "4"}});
+
+ // Asterisked identifier chains (common columns should not be included)
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select t1.* from t1 natural join t2"),
+ "1");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select t2.* from t1 natural join t2"),
+ "4");
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select t1.*, t2.* from t1 natural join t2"),
+ new String[][] {{"1", "4"}});
+
+ // NATURAL INNER JOIN (same as NATURAL JOIN because INNER is default)
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 natural inner join t2"),
+ new String[][] {{"2", "3", "1", "4"}});
+
+ // NATURAL LEFT (OUTER) JOIN
+ String[][] ljRows = {
+ {"2", "3", "1", "4"},
+ {"5", "6", "4", null},
+ {"8", "9", "7", null}
+ };
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 natural left join t2"),
+ ljRows);
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 natural left outer join t2"),
+ ljRows);
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery(
+ "select b, t1.b, t2.b from t1 natural left join t2"),
+ new String[][] {
+ {"2", "2", "2"},
+ {"5", "5", null},
+ {"8", "8", null}});
+
+ // NATURAL RIGHT (OUTER) JOIN
+ String[][] rjRows = {
+ {"1", "2", null, "3"},
+ {"2", "3", "1", "4"},
+ {"3", "2", null, "1"}
+ };
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 natural right join t2"),
+ rjRows);
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery("select * from t1 natural right outer join t2"),
+ rjRows);
+ JDBC.assertUnorderedResultSet(
+ s.executeQuery(
+ "select b, t1.b, t2.b from t1 natural right join t2"),
+ new String[][] {
+ {"1", null, "1"},
+ {"2", "2", "2"},
+ {"3", null, "3"}});
+
+ // ***** Negative tests *****
+
+ // ON or USING clause not allowed with NATURAL
+ assertStatementError(
+ SYNTAX_ERROR, s,
+ "select * from t1 natural join t2 on t1.b=t2.b");
+ assertStatementError(
+ SYNTAX_ERROR, s,
+ "select * from t1 natural join t2 using (b)");
+
+ // CROSS JOIN cannot be used together with NATURAL
+ assertStatementError(
+ SYNTAX_ERROR, s,
+ "select * from t1 natural cross join t2");
+
+ // T has one column named D, T2 CROSS JOIN T3 has two columns named D,
+ // so it's not clear which columns to join on
+ assertStatementError(
+ AMBIGUOUS_COLNAME, s,
+ "select * from t1 t(d,x,y) natural join (t2 cross join t3)");
+
+ // Only common columns, so asterisked identifier chains expand to
+ // zero columns
+ assertStatementError(
+ NO_COLUMNS, s,
+ "select x.* from t1 x natural join t1 y");
+ assertStatementError(
+ NO_COLUMNS, s,
+ "select y.* from t1 x natural join t1 y");
+ assertStatementError(
+ NO_COLUMNS, s,
+ "select x.*, y.* from t1 x natural join t1 y");
+
+ // Incompatible types
+ assertStatementError(
+ NON_COMPARABLE, s,
+ "select * from t1 natural join (values ('one', 'two')) v1(a,b)");
+ }
+
+ /**
* Test that ON clauses can contain subqueries (DERBY-4380).
*/
public void testSubqueryInON() throws SQLException {
|