You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ma...@apache.org on 2014/03/14 20:36:34 UTC
git commit: PHOENIX-839 Joining on a dynamic column doesn't work
Repository: incubator-phoenix
Updated Branches:
refs/heads/3.0 1a7ca5aad -> a8362a0ff
PHOENIX-839 Joining on a dynamic column doesn't work
Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/a8362a0f
Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/a8362a0f
Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/a8362a0f
Branch: refs/heads/3.0
Commit: a8362a0ff85620f1bac77fe3fd861b164adb4114
Parents: 1a7ca5a
Author: maryannxue <ma...@apache.org>
Authored: Fri Mar 14 15:33:22 2014 -0400
Committer: maryannxue <ma...@apache.org>
Committed: Fri Mar 14 15:33:22 2014 -0400
----------------------------------------------------------------------
.../org/apache/phoenix/end2end/HashJoinIT.java | 70 ++++++++++++++++++
.../apache/phoenix/compile/JoinCompiler.java | 74 +++++++++++++++++---
2 files changed, 133 insertions(+), 11 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/a8362a0f/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
index 123ff35..cfb553f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
@@ -2238,5 +2238,75 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
conn.close();
}
}
+
+ @Test
+ public void testJoinOnDynamicColumns() throws Exception {
+ String tableA = "tableA";
+ String tableB = "tableB";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = null;
+ PreparedStatement stmt = null;
+ try {
+ conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ String ddlA = "CREATE TABLE " + tableA + " (pkA INTEGER NOT NULL, " + " colA1 INTEGER, "
+ + " colA2 VARCHAR " + "CONSTRAINT PK PRIMARY KEY" + "(pkA)" + ")";
+
+ String ddlB = "CREATE TABLE " + tableB + " (pkB INTEGER NOT NULL PRIMARY KEY, " + " colB INTEGER)";
+ stmt = conn.prepareStatement(ddlA);
+ stmt.execute();
+ stmt.close();
+
+ stmt = conn.prepareStatement(ddlB);
+ stmt.execute();
+ stmt.close();
+
+ String upsertA = "UPSERT INTO TABLEA (pkA, colA1, colA2) VALUES(?, ?, ?)";
+ stmt = conn.prepareStatement(upsertA);
+ int i = 0;
+ for (i = 0; i < 5; i++) {
+ stmt.setInt(1, i);
+ stmt.setInt(2, i + 10);
+ stmt.setString(3, "00" + i);
+ stmt.executeUpdate();
+ }
+ conn.commit();
+ stmt.close();
+
+ // upsert select dynamic columns in tableB
+ conn.createStatement().execute("CREATE SEQUENCE SEQB");
+ String upsertBSelectA = "UPSERT INTO TABLEB (pkB, pkA INTEGER)"
+ + "SELECT NEXT VALUE FOR SEQB, pkA FROM TABLEA";
+ stmt = conn.prepareStatement(upsertBSelectA);
+ stmt.executeUpdate();
+ stmt.close();
+ conn.commit();
+ conn.createStatement().execute("DROP SEQUENCE SEQB");
+
+ // perform a join between tableB and tableA by joining on the dynamic column that we upserted in
+ // tableB. This join should return all the rows from table A.
+ String joinSql = "SELECT A.pkA, A.COLA1, A.colA2 FROM TABLEB B(pkA INTEGER) JOIN TABLEA A ON a.pkA = b.pkA";
+ stmt = conn.prepareStatement(joinSql);
+ ResultSet rs = stmt.executeQuery();
+ i = 0;
+ while (rs.next()) {
+ // check that we get back all the rows that we upserted for tableA above.
+ assertEquals(rs.getInt(1), i);
+ assertEquals(rs.getInt(2), i + 10);
+ assertEquals(rs.getString(3), "00" + i);
+ i++;
+ }
+ assertEquals(5,i);
+ } finally {
+ if (stmt != null) {
+ stmt.close();
+ }
+ if (conn != null) {
+ conn.close();
+ }
+
+ }
+
+ }
+
}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/a8362a0f/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
index e3d4222..bc57ac7 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
@@ -59,6 +59,7 @@ import org.apache.phoenix.parse.InListParseNode;
import org.apache.phoenix.parse.IsNullParseNode;
import org.apache.phoenix.parse.JoinTableNode;
import org.apache.phoenix.parse.JoinTableNode.JoinType;
+import org.apache.phoenix.parse.ColumnDef;
import org.apache.phoenix.parse.LikeParseNode;
import org.apache.phoenix.parse.NamedTableNode;
import org.apache.phoenix.parse.NotParseNode;
@@ -104,6 +105,7 @@ public class JoinCompiler {
public static class JoinSpec {
private ColumnResolver origResolver;
private TableNode mainTableNode;
+ private List<ColumnDef> dynamicColumns;
private TableRef mainTable;
private List<AliasedNode> select; // all basic nodes related to mainTable, no aggregation.
private List<ParseNode> preFilters;
@@ -121,6 +123,9 @@ public class JoinCompiler {
Iterator<TableNode> iter = tableNodes.iterator();
Iterator<TableRef> tableRefIter = resolver.getTables().iterator();
this.mainTableNode = iter.next();
+ DynamicColumnsVisitor v = new DynamicColumnsVisitor();
+ this.mainTableNode.accept(v);
+ this.dynamicColumns = v.getDynamicColumns();
this.mainTable = tableRefIter.next();
this.select = extractFromSelect(selectList, mainTable, resolver);
this.joinTables = new ArrayList<JoinTable>(tableNodes.size() - 1);
@@ -209,10 +214,11 @@ public class JoinCompiler {
}
}
- private JoinSpec(ColumnResolver resolver, TableNode tableNode, TableRef table, List<AliasedNode> select, List<ParseNode> preFilters,
+ private JoinSpec(ColumnResolver resolver, TableNode tableNode, List<ColumnDef> dynamicColumns, TableRef table, List<AliasedNode> select, List<ParseNode> preFilters,
List<ParseNode> postFilters, List<JoinTable> joinTables, boolean useStarJoin, Map<TableRef, JoinTable> tableRefToJoinTableMap, Map<ColumnRef, ColumnRefType> columnRefs) {
this.origResolver = resolver;
this.mainTableNode = tableNode;
+ this.dynamicColumns = dynamicColumns;
this.mainTable = table;
this.select = select;
this.preFilters = preFilters;
@@ -231,6 +237,10 @@ public class JoinCompiler {
return mainTableNode;
}
+ public List<ColumnDef> getDynamicColumns() {
+ return dynamicColumns;
+ }
+
public TableRef getMainTable() {
return mainTable;
}
@@ -530,13 +540,14 @@ public class JoinCompiler {
}
public static JoinSpec getSubJoinSpecWithoutPostFilters(JoinSpec join) {
- return new JoinSpec(join.origResolver, join.mainTableNode, join.mainTable, join.select, join.preFilters, new ArrayList<ParseNode>(),
+ return new JoinSpec(join.origResolver, join.mainTableNode, join.dynamicColumns, join.mainTable, join.select, join.preFilters, new ArrayList<ParseNode>(),
join.joinTables.subList(0, join.joinTables.size() - 1), join.useStarJoin, join.tableRefToJoinTableMap, join.columnRefs);
}
public static class JoinTable {
private JoinType type;
private TableNode tableNode; // original table node
+ private List<ColumnDef> dynamicColumns;
private TableRef table;
private List<AliasedNode> select; // all basic nodes related to this table, no aggregation.
private HintNode hint;
@@ -552,6 +563,9 @@ public class JoinCompiler {
this.type = node.getType();
this.tableNode = node.getTable();
+ DynamicColumnsVisitor v = new DynamicColumnsVisitor();
+ this.tableNode.accept(v);
+ this.dynamicColumns = v.getDynamicColumns();
this.table = tableRef;
this.select = extractFromSelect(statement.getSelect(),tableRef,resolver);
this.hint = statement.getHint();
@@ -569,6 +583,10 @@ public class JoinCompiler {
return tableNode;
}
+ public List<ColumnDef> getDynamicColumns() {
+ return dynamicColumns;
+ }
+
public TableRef getTable() {
return table;
}
@@ -1042,7 +1060,7 @@ public class JoinCompiler {
TableRef table = jTable.getTable();
List<ParseNode> groupBy = table.equals(groupByTableRef) ? select.getGroupBy() : null;
List<OrderByNode> orderBy = table.equals(orderByTableRef) ? select.getOrderBy() : null;
- SelectStatement stmt = getSubqueryForOptimizedPlan(select, table, join.columnRefs, jTable.getPreFiltersCombined(), groupBy, orderBy, join.isWildCardSelect(table));
+ SelectStatement stmt = getSubqueryForOptimizedPlan(select.getHint(), join.tableRefToJoinTableMap.get(table).getDynamicColumns(), table, join.columnRefs, jTable.getPreFiltersCombined(), groupBy, orderBy, join.isWildCardSelect(table));
QueryPlan plan = context.getConnection().getQueryServices().getOptimizer().optimize(statement, stmt);
if (!plan.getTableRef().equals(table)) {
TableNodeRewriter rewriter = new TableNodeRewriter(plan.getTableRef());
@@ -1058,7 +1076,7 @@ public class JoinCompiler {
TableRef table = join.getMainTable();
List<ParseNode> groupBy = table.equals(groupByTableRef) ? select.getGroupBy() : null;
List<OrderByNode> orderBy = table.equals(orderByTableRef) ? select.getOrderBy() : null;
- SelectStatement stmt = getSubqueryForOptimizedPlan(select, table, join.columnRefs, join.getPreFiltersCombined(), groupBy, orderBy, join.isWildCardSelect(table));
+ SelectStatement stmt = getSubqueryForOptimizedPlan(select.getHint(), join.dynamicColumns, table, join.columnRefs, join.getPreFiltersCombined(), groupBy, orderBy, join.isWildCardSelect(table));
QueryPlan plan = context.getConnection().getQueryServices().getOptimizer().optimize(statement, stmt);
if (!plan.getTableRef().equals(table)) {
TableNodeRewriter rewriter = new TableNodeRewriter(plan.getTableRef());
@@ -1075,15 +1093,15 @@ public class JoinCompiler {
return IndexStatementRewriter.translate(NODE_FACTORY.select(select, newFrom), resolver, replacement);
}
- private static SelectStatement getSubqueryForOptimizedPlan(SelectStatement select, TableRef table, Map<ColumnRef, ColumnRefType> columnRefs, ParseNode where, List<ParseNode> groupBy, List<OrderByNode> orderBy, boolean isWildCardSelect) {
- String schemaName = table.getTable().getSchemaName().getString();
- TableName tName = TableName.create(schemaName.length() == 0 ? null : schemaName, table.getTable().getTableName().getString());
+ private static SelectStatement getSubqueryForOptimizedPlan(HintNode hintNode, List<ColumnDef> dynamicCols, TableRef tableRef, Map<ColumnRef, ColumnRefType> columnRefs, ParseNode where, List<ParseNode> groupBy, List<OrderByNode> orderBy, boolean isWildCardSelect) {
+ String schemaName = tableRef.getTable().getSchemaName().getString();
+ TableName tName = TableName.create(schemaName.length() == 0 ? null : schemaName, tableRef.getTable().getTableName().getString());
List<AliasedNode> selectList = new ArrayList<AliasedNode>();
if (isWildCardSelect) {
selectList.add(NODE_FACTORY.aliasedNode(null, WildcardParseNode.INSTANCE));
} else {
for (ColumnRef colRef : columnRefs.keySet()) {
- if (colRef.getTableRef().equals(table)) {
+ if (colRef.getTableRef().equals(tableRef)) {
ParseNode node = NODE_FACTORY.column(tName, '"' + colRef.getColumn().getName().getString() + '"', null);
if (groupBy != null) {
node = NODE_FACTORY.function(CountAggregateFunction.NAME, Collections.singletonList(node));
@@ -1092,10 +1110,10 @@ public class JoinCompiler {
}
}
}
- String tableAlias = table.getTableAlias();
- List<? extends TableNode> from = Collections.singletonList(NODE_FACTORY.namedTable(tableAlias == null ? null : '"' + tableAlias + '"', tName));
+ String tableAlias = tableRef.getTableAlias();
+ List<? extends TableNode> from = Collections.singletonList(NODE_FACTORY.namedTable(tableAlias == null ? null : '"' + tableAlias + '"', tName, dynamicCols));
- return NODE_FACTORY.select(from, select.getHint(), false, selectList, where, groupBy, null, orderBy, null, 0, false);
+ return NODE_FACTORY.select(from, hintNode, false, selectList, where, groupBy, null, orderBy, null, 0, false);
}
public static SelectStatement getSubqueryWithoutJoin(SelectStatement statement, JoinSpec join) {
@@ -1255,6 +1273,40 @@ public class JoinCompiler {
String colName) {
return SchemaUtil.getColumnName(SchemaUtil.getTableName(schemaName, tableName), colName);
}
+
+ private static class DynamicColumnsVisitor implements TableNodeVisitor {
+ private List<ColumnDef> dynamicCols;
+
+ public DynamicColumnsVisitor() {
+ }
+
+ public List<ColumnDef> getDynamicColumns() {
+ return dynamicCols == null ? Collections.<ColumnDef> emptyList() : dynamicCols;
+ }
+
+ @Override
+ public void visit(BindTableNode boundTableNode) throws SQLException {
+ }
+
+ @Override
+ public void visit(JoinTableNode joinNode) throws SQLException {
+ // should not expect to see this node.
+ assert(false);
+ }
+
+ @Override
+ public void visit(NamedTableNode namedTableNode)
+ throws SQLException {
+ this.dynamicCols = namedTableNode.getDynamicColumns();
+ }
+
+ @Override
+ public void visit(DerivedTableNode subselectNode)
+ throws SQLException {
+ throw new SQLFeatureNotSupportedException();
+ }
+ };
+
}