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/02/05 23:27:34 UTC
git commit: Improve optimization of join queries with group-by and
order-by
Updated Branches:
refs/heads/master c2a7b2341 -> 1ef5ecc98
Improve optimization of join queries with group-by and order-by
Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/1ef5ecc9
Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/1ef5ecc9
Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/1ef5ecc9
Branch: refs/heads/master
Commit: 1ef5ecc98925dcdd0ec02dbc9a3648b47b42c69a
Parents: c2a7b23
Author: maryannxue <ma...@apache.org>
Authored: Wed Feb 5 17:27:19 2014 -0500
Committer: maryannxue <ma...@apache.org>
Committed: Wed Feb 5 17:27:19 2014 -0500
----------------------------------------------------------------------
.../apache/phoenix/compile/JoinCompiler.java | 58 ++-
.../apache/phoenix/end2end/HashJoinTest.java | 374 ++++++++++++++++++-
2 files changed, 412 insertions(+), 20 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/1ef5ecc9/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 5f327e4..86e6dfe 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
@@ -40,6 +40,7 @@ import org.apache.phoenix.exception.SQLExceptionInfo;
import org.apache.phoenix.expression.AndExpression;
import org.apache.phoenix.expression.CoerceExpression;
import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.expression.function.CountAggregateFunction;
import org.apache.phoenix.jdbc.PhoenixStatement;
import org.apache.phoenix.join.ScanProjector;
import org.apache.phoenix.parse.AliasedNode;
@@ -897,7 +898,43 @@ public class JoinCompiler {
public static SelectStatement optimize(StatementContext context, SelectStatement select, PhoenixStatement statement) throws SQLException {
ColumnResolver resolver = context.getResolver();
- JoinSpec join = new JoinSpec(select, resolver);
+ TableRef groupByTableRef = null;
+ TableRef orderByTableRef = null;
+ if (select.getGroupBy() != null && !select.getGroupBy().isEmpty()) {
+ ColumnParseNodeVisitor groupByVisitor = new ColumnParseNodeVisitor(resolver);
+ for (ParseNode node : select.getGroupBy()) {
+ node.accept(groupByVisitor);
+ }
+ Set<TableRef> set = groupByVisitor.getTableRefSet();
+ if (set.size() == 1) {
+ groupByTableRef = set.iterator().next();
+ }
+ } else if (select.getOrderBy() != null && !select.getOrderBy().isEmpty()) {
+ ColumnParseNodeVisitor orderByVisitor = new ColumnParseNodeVisitor(resolver);
+ for (OrderByNode node : select.getOrderBy()) {
+ node.getNode().accept(orderByVisitor);
+ }
+ Set<TableRef> set = orderByVisitor.getTableRefSet();
+ if (set.size() == 1) {
+ orderByTableRef = set.iterator().next();
+ }
+ }
+ JoinSpec join = getJoinSpec(context, select);
+ if (groupByTableRef != null || orderByTableRef != null) {
+ QueryCompiler compiler = new QueryCompiler(statement);
+ List<Object> binds = statement.getParameters();
+ StatementContext ctx = new StatementContext(statement, resolver, binds, new Scan());
+ context.setScanHints(select.getHint());
+ QueryPlan plan = compiler.compileJoinQuery(ctx, select, binds, join, false);
+ TableRef table = plan.getTableRef();
+ if (groupByTableRef != null && !groupByTableRef.equals(table)) {
+ groupByTableRef = null;
+ }
+ if (orderByTableRef != null && !orderByTableRef.equals(table)) {
+ orderByTableRef = null;
+ }
+ }
+
Map<TableRef, TableRef> replacement = new HashMap<TableRef, TableRef>();
List<TableNode> from = select.getFrom();
List<TableNode> newFrom = Lists.newArrayListWithExpectedSize(from.size());
@@ -954,7 +991,9 @@ public class JoinCompiler {
if (jTable.getTableNode() != tNode)
continue;
TableRef table = jTable.getTable();
- SelectStatement stmt = getSubqueryForOptimizedPlan(select, table, join.columnRefs, jTable.getPreFiltersCombined());
+ 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);
QueryPlan plan = context.getConnection().getQueryServices().getOptimizer().optimize(stmt, statement);
if (!plan.getTableRef().equals(table)) {
TableNodeRewriter rewriter = new TableNodeRewriter(plan.getTableRef());
@@ -968,7 +1007,9 @@ public class JoinCompiler {
}
// get optimized plan for main table
TableRef table = join.getMainTable();
- SelectStatement stmt = getSubqueryForOptimizedPlan(select, table, join.columnRefs, join.getPreFiltersCombined());
+ 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);
QueryPlan plan = context.getConnection().getQueryServices().getOptimizer().optimize(stmt, statement);
if (!plan.getTableRef().equals(table)) {
TableNodeRewriter rewriter = new TableNodeRewriter(plan.getTableRef());
@@ -985,7 +1026,7 @@ 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) {
+ private static SelectStatement getSubqueryForOptimizedPlan(SelectStatement select, TableRef table, Map<ColumnRef, ColumnRefType> columnRefs, ParseNode where, List<ParseNode> groupBy, List<OrderByNode> orderBy) {
String schemaName = table.getTable().getSchemaName().getString();
TableName tName = TableName.create(schemaName.length() == 0 ? null : schemaName, table.getTable().getTableName().getString());
List<AliasedNode> selectList = new ArrayList<AliasedNode>();
@@ -994,14 +1035,18 @@ public class JoinCompiler {
} else {
for (ColumnRef colRef : columnRefs.keySet()) {
if (colRef.getTableRef().equals(table)) {
- selectList.add(NODE_FACTORY.aliasedNode(null, NODE_FACTORY.column(tName, '"' + colRef.getColumn().getName().getString() + '"', null)));
+ ParseNode node = NODE_FACTORY.column(tName, '"' + colRef.getColumn().getName().getString() + '"', null);
+ if (groupBy != null) {
+ node = NODE_FACTORY.function(CountAggregateFunction.NAME, Collections.singletonList(node));
+ }
+ selectList.add(NODE_FACTORY.aliasedNode(null, node));
}
}
}
String tableAlias = table.getTableAlias();
List<? extends TableNode> from = Collections.singletonList(NODE_FACTORY.namedTable(tableAlias == null ? null : '"' + tableAlias + '"', tName));
- return NODE_FACTORY.select(from, select.getHint(), false, selectList, where, null, null, null, null, 0, false);
+ return NODE_FACTORY.select(from, select.getHint(), false, selectList, where, groupBy, null, orderBy, null, 0, false);
}
/**
@@ -1191,4 +1236,3 @@ public class JoinCompiler {
}
}
-
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/1ef5ecc9/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java b/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java
index 81cd682..39d6c82 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/end2end/HashJoinTest.java
@@ -90,25 +90,148 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
List<Object> testCases = Lists.newArrayList();
testCases.add(new String[][] {
{}, {
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [I.NAME]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED,
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC"
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [I.NAME]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED,
+ /*
+ * testJoinWithWildcard()
+ * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp
+ * ON joinItemTable.supplier_id = supp.supplier_id
+ * ORDER BY item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_NORMALIZED,
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item LEFT JOIN joinSupplierTable supp
+ * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1)
+ * AND (supp.name BETWEEN 'S1' AND 'S5')
+ * WHERE item.name BETWEEN 'T1' AND 'T5'
+ */
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
" SERVER FILTER BY (NAME >= 'T1' AND NAME <= 'T5')\n" +
" PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
" BUILD HASH TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_NORMALIZED + "\n" +
" SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')",
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item INNER JOIN joinSupplierTable supp
+ * ON item.supplier_id = supp.supplier_id
+ * WHERE (item.name = 'T1' OR item.name = 'T5')
+ * AND (supp.name = 'S1' OR supp.name = 'S5')
+ */
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
" SERVER FILTER BY (NAME = 'T1' OR NAME = 'T5')\n" +
" PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
" BUILD HASH TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_NORMALIZED + "\n" +
" SERVER FILTER BY (NAME = 'S1' OR NAME = 'S5')",
+ /*
+ * testJoinWithSkipMergeOptimization()
+ * SELECT s.name FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000
+ * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
+ */
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
" PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
" BUILD HASH TABLE 0 (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED + "\n" +
" SERVER FILTER BY QUANTITY < 5000\n" +
" BUILD HASH TABLE 1\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_NORMALIZED
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_NORMALIZED,
+ /*
+ * testSelfJoin
+ * SELECT i2.item_id, i1.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.item_id
+ * ORDER BY i1.item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testSelfJoin
+ * SELECT i1.name, i2.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id
+ * ORDER BY i1.name, i2.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " SERVER SORTED BY [I1.NAME, I2.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED,
}});
testCases.add(new String[][] {
{
@@ -116,22 +239,147 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
"CREATE INDEX IDX_ITEM ON " + JOIN_ITEM_TABLE + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
"CREATE INDEX IDX_SUPPLIER ON " + JOIN_SUPPLIER_TABLE + " (name)"
}, {
- "CLIENT PARALLEL 4-WAY RANGE SCAN OVER IDX_ITEM ['T1'] - ['T5']\n" +
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [I.NAME]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_ITEM\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC"
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_ITEM\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_ITEM\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED,
+ /*
+ * testJoinWithWildcard()
+ * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp
+ * ON joinItemTable.supplier_id = supp.supplier_id
+ * ORDER BY item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_NORMALIZED,
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item LEFT JOIN joinSupplierTable supp
+ * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1)
+ * AND (supp.name BETWEEN 'S1' AND 'S5')
+ * WHERE item.name BETWEEN 'T1' AND 'T5'
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX_ITEM ['T1'] - ['T5']\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
" BUILD HASH TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX_SUPPLIER ['S1'] - ['S5']",
- "CLIENT PARALLEL 4-WAY SKIP SCAN ON 2 KEYS OVER IDX_ITEM ['T1'] - ['T5']\n" +
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item INNER JOIN joinSupplierTable supp
+ * ON item.supplier_id = supp.supplier_id
+ * WHERE (item.name = 'T1' OR item.name = 'T5')
+ * AND (supp.name = 'S1' OR supp.name = 'S5')
+ */
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER IDX_ITEM ['T1'] - ['T5']\n" +
" PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
" BUILD HASH TABLE 0\n" +
" CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER IDX_SUPPLIER ['S1'] - ['S5']",
- "CLIENT PARALLEL 4-WAY FULL SCAN OVER IDX_ITEM\n" +
+ /*
+ * testJoinWithSkipMergeOptimization()
+ * SELECT s.name FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000
+ * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_ITEM\n" +
" PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
" BUILD HASH TABLE 0 (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_NORMALIZED + "\n" +
" SERVER FILTER BY QUANTITY < 5000\n" +
" BUILD HASH TABLE 1\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_SUPPLIER"
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_SUPPLIER",
+ /*
+ * testSelfJoin
+ * SELECT i2.item_id, i1.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.item_id
+ * ORDER BY i1.item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_NORMALIZED + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_ITEM\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testSelfJoin
+ * SELECT i1.name, i2.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id
+ * ORDER BY i1.name, i2.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_ITEM\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [I1.NAME, I2.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX_ITEM",
}});
return testCases;
}
@@ -817,12 +1065,16 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
@Test
public void testLeftJoinWithAggregation() throws Exception {
- String query = "SELECT i.name, sum(quantity) FROM " + JOIN_ORDER_TABLE + " o LEFT JOIN "
+ String query1 = "SELECT i.name, sum(quantity) FROM " + JOIN_ORDER_TABLE + " o LEFT JOIN "
+ JOIN_ITEM_TABLE + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.name ORDER BY i.name";
+ String query2 = "SELECT i.\"item_id\" iid, sum(quantity) q FROM " + JOIN_ORDER_TABLE + " o LEFT JOIN "
+ + JOIN_ITEM_TABLE + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC";
+ String query3 = "SELECT i.\"item_id\" iid, sum(quantity) q FROM " + JOIN_ITEM_TABLE + " i LEFT JOIN "
+ + JOIN_ORDER_TABLE + " o ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC NULLS LAST, iid";
Properties props = new Properties(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
try {
- PreparedStatement statement = conn.prepareStatement(query);
+ PreparedStatement statement = conn.prepareStatement(query1);
ResultSet rs = statement.executeQuery();
assertTrue (rs.next());
assertEquals(rs.getString(1), "T1");
@@ -838,6 +1090,58 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
assertEquals(rs.getInt(2), 6000);
assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
+ assertEquals(plans[0], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000006");
+ assertEquals(rs.getInt("q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000003");
+ assertEquals(rs.getInt("q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000002");
+ assertEquals(rs.getInt("q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000001");
+ assertEquals(rs.getInt("q"), 1000);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query2);
+ assertEquals(plans[1], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query3);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000006");
+ assertEquals(rs.getInt("q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000003");
+ assertEquals(rs.getInt("q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000002");
+ assertEquals(rs.getInt("q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000001");
+ assertEquals(rs.getInt("q"), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000004");
+ assertEquals(rs.getInt("q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000005");
+ assertEquals(rs.getInt("q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "invalid001");
+ assertEquals(rs.getInt("q"), 0);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query3);
+ assertEquals(plans[2], QueryUtil.getExplainPlan(rs));
} finally {
conn.close();
}
@@ -845,12 +1149,14 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
@Test
public void testRightJoinWithAggregation() throws Exception {
- String query = "SELECT i.name, sum(quantity) FROM " + JOIN_ORDER_TABLE + " o RIGHT JOIN "
+ String query1 = "SELECT i.name, sum(quantity) FROM " + JOIN_ORDER_TABLE + " o RIGHT JOIN "
+ JOIN_ITEM_TABLE + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.name ORDER BY i.name";
+ String query2 = "SELECT i.\"item_id\" iid, sum(quantity) q FROM " + JOIN_ORDER_TABLE + " o RIGHT JOIN "
+ + JOIN_ITEM_TABLE + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC NULLS LAST, iid";
Properties props = new Properties(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
try {
- PreparedStatement statement = conn.prepareStatement(query);
+ PreparedStatement statement = conn.prepareStatement(query1);
ResultSet rs = statement.executeQuery();
assertTrue (rs.next());
assertEquals(rs.getString(1), "INVALID-1");
@@ -875,6 +1181,38 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
assertEquals(rs.getInt(2), 6000);
assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
+ assertEquals(plans[3], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000006");
+ assertEquals(rs.getInt("q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000003");
+ assertEquals(rs.getInt("q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000002");
+ assertEquals(rs.getInt("q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000001");
+ assertEquals(rs.getInt("q"), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000004");
+ assertEquals(rs.getInt("q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000005");
+ assertEquals(rs.getInt("q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "invalid001");
+ assertEquals(rs.getInt("q"), 0);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query2);
+ assertEquals(plans[4], QueryUtil.getExplainPlan(rs));
} finally {
conn.close();
}
@@ -1164,6 +1502,9 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
assertNull(rs.getString("supp.loc_id"));
assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ assertEquals(plans[5], QueryUtil.getExplainPlan(rs));
} finally {
conn.close();
}
@@ -1312,7 +1653,7 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
assertFalse(rs.next());
rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
- assertEquals(plans[0], QueryUtil.getExplainPlan(rs));
+ assertEquals(plans[6], QueryUtil.getExplainPlan(rs));
statement = conn.prepareStatement(query2);
@@ -1331,7 +1672,7 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
assertFalse(rs.next());
rs = conn.createStatement().executeQuery("EXPLAIN " + query2);
- assertEquals(plans[1], QueryUtil.getExplainPlan(rs));
+ assertEquals(plans[7], QueryUtil.getExplainPlan(rs));
} finally {
conn.close();
}
@@ -1359,7 +1700,7 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
assertFalse(rs.next());
rs = conn.createStatement().executeQuery("EXPLAIN " + query);
- assertEquals(plans[2], QueryUtil.getExplainPlan(rs));
+ assertEquals(plans[8], QueryUtil.getExplainPlan(rs));
} finally {
conn.close();
}
@@ -1370,7 +1711,7 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
String query1 = "SELECT i2.\"item_id\", i1.name FROM " + JOIN_ITEM_TABLE + " i1 JOIN "
+ JOIN_ITEM_TABLE + " i2 ON i1.\"item_id\" = i2.\"item_id\" ORDER BY i1.\"item_id\"";
String query2 = "SELECT i1.name, i2.name FROM " + JOIN_ITEM_TABLE + " i1 JOIN "
- + JOIN_ITEM_TABLE + " i2 ON i1.\"item_id\" = i2.\"supplier_id\" ORDER BY i1.name, i2.name";
+ + JOIN_ITEM_TABLE + " i2 ON i1.\"item_id\" = i2.\"supplier_id\" ORDER BY i1.name, i2.name";
Properties props = new Properties(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
try {
@@ -1400,6 +1741,9 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
assertFalse(rs.next());
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
+ assertEquals(plans[9], QueryUtil.getExplainPlan(rs));
+
statement = conn.prepareStatement(query2);
rs = statement.executeQuery();
assertTrue (rs.next());
@@ -1422,6 +1766,9 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
assertEquals(rs.getString(2), "T6");
assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query2);
+ assertEquals(plans[10], QueryUtil.getExplainPlan(rs));
} finally {
conn.close();
}
@@ -1656,3 +2003,4 @@ public class HashJoinTest extends BaseHBaseManagedTimeTest {
}
}
}
+