You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ap...@apache.org on 2026/06/10 05:27:47 UTC
(phoenix) 05/05: PHOENIX-7890 Improve EXPLAIN for joins and unions (#2510)
This is an automated email from the ASF dual-hosted git repository.
asf-gitbox-commits pushed a commit to branch PHOENIX-7876-feature
in repository https://gitbox.apache.org/repos/asf/phoenix.git
commit 1d1bc21cf5425a2123ce484f5ca50de0bccf0697
Author: Andrew Purtell <ap...@apache.org>
AuthorDate: Tue Jun 9 22:26:37 2026 -0700
PHOENIX-7890 Improve EXPLAIN for joins and unions (#2510)
---
.../org/apache/phoenix/compile/QueryCompiler.java | 7 +-
.../org/apache/phoenix/execute/HashJoinPlan.java | 76 +-
.../apache/phoenix/execute/SortMergeJoinPlan.java | 13 +-
.../java/org/apache/phoenix/execute/UnionPlan.java | 52 +-
.../phoenix/iterate/UnionResultIterators.java | 58 +-
.../org/apache/phoenix/jdbc/PhoenixStatement.java | 2 +-
.../phoenix/end2end/CostBasedDecisionIT.java | 61 +-
.../phoenix/end2end/QueryWithTableSampleIT.java | 15 +-
.../org/apache/phoenix/end2end/UnionAllIT.java | 46 +-
.../end2end/index/GlobalIndexOptimizationIT.java | 3 +-
.../apache/phoenix/end2end/index/IndexUsageIT.java | 6 +-
.../end2end/join/HashJoinGlobalIndexIT.java | 125 +--
.../phoenix/end2end/join/HashJoinLocalIndexIT.java | 149 ++--
.../phoenix/end2end/join/HashJoinMoreIT.java | 18 +-
.../phoenix/end2end/join/HashJoinNoIndexIT.java | 121 +--
.../phoenix/end2end/tpcds/TPCDSLikeAssertions.java | 212 +++++
.../phoenix/end2end/tpcds/TPCDSLikeBaseIT.java | 103 +++
.../end2end/tpcds/TPCDSLikeCrossChannelIT.java | 238 ++++++
.../phoenix/end2end/tpcds/TPCDSLikeFixture.java | 922 +++++++++++++++++++++
.../end2end/tpcds/TPCDSLikeSingleChannelIT.java | 395 +++++++++
.../apache/phoenix/compile/QueryCompilerTest.java | 16 +-
.../tpcds/TPCDSLikeExpectedRegenerator.java | 63 ++
.../phoenix/query/explain/ExplainPlanTest.java | 69 +-
23 files changed, 2473 insertions(+), 297 deletions(-)
diff --git a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/QueryCompiler.java b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
index f4d78ce0e6..86f8b500c0 100644
--- a/phoenix-core-client/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
+++ b/phoenix-core-client/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
@@ -486,7 +486,7 @@ public class QueryCompiler {
joinTypes, starJoinVector, tables, fieldPositions, postJoinFilterExpression,
QueryUtil.getOffsetLimit(limit, offset));
return HashJoinPlan.create(joinTable.getOriginalJoinSelectStatement(), plan, joinInfo,
- hashPlans);
+ hashPlans, JoinCompiler.Strategy.HASH_BUILD_RIGHT);
}
case HASH_BUILD_LEFT: {
JoinSpec lastJoinSpec = joinSpecs.get(joinSpecs.size() - 1);
@@ -562,7 +562,8 @@ public class QueryCompiler {
hashExpressions);
return HashJoinPlan.create(joinTable.getOriginalJoinSelectStatement(), rhsPlan, joinInfo,
new HashSubPlan[] { new HashSubPlan(0, lhsPlan, hashExpressions, false,
- usePersistentCache, keyRangeExpressions.getFirst(), keyRangeExpressions.getSecond()) });
+ usePersistentCache, keyRangeExpressions.getFirst(), keyRangeExpressions.getSecond()) },
+ JoinCompiler.Strategy.HASH_BUILD_LEFT);
}
case SORT_MERGE: {
JoinTable lhsJoin = joinTable.createSubJoinTable(statement.getConnection());
@@ -895,7 +896,7 @@ public class QueryCompiler {
subPlans[i++] = new WhereClauseSubPlan(compileSubquery(stmt, false), stmt,
subqueryNode.expectSingleRow());
}
- plan = HashJoinPlan.create(planSelect, plan, null, subPlans);
+ plan = HashJoinPlan.create(planSelect, plan, null, subPlans, null);
}
if (innerPlan != null) {
diff --git a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
index aa18e0e6cc..a558c1832a 100644
--- a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
+++ b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
@@ -43,6 +43,7 @@ import org.apache.phoenix.compile.ExplainPlan;
import org.apache.phoenix.compile.ExplainPlanAttributes;
import org.apache.phoenix.compile.ExplainPlanAttributes.ExplainPlanAttributesBuilder;
import org.apache.phoenix.compile.FromCompiler;
+import org.apache.phoenix.compile.JoinCompiler;
import org.apache.phoenix.compile.QueryPlan;
import org.apache.phoenix.compile.RowProjector;
import org.apache.phoenix.compile.ScanRanges;
@@ -99,6 +100,7 @@ public class HashJoinPlan extends DelegateQueryPlan {
private final SelectStatement statement;
private final HashJoinInfo joinInfo;
+ private JoinCompiler.Strategy strategy;
private final SubPlan[] subPlans;
private final boolean recompileWhereClause;
private final Set<TableRef> tableRefs;
@@ -115,9 +117,10 @@ public class HashJoinPlan extends DelegateQueryPlan {
private boolean hasSubPlansWithPersistentCache;
public static HashJoinPlan create(SelectStatement statement, QueryPlan plan,
- HashJoinInfo joinInfo, SubPlan[] subPlans) throws SQLException {
- if (!(plan instanceof HashJoinPlan)) return new HashJoinPlan(statement, plan, joinInfo,
- subPlans, joinInfo == null, Collections.<ImmutableBytesPtr, ServerCache> emptyMap());
+ HashJoinInfo joinInfo, SubPlan[] subPlans, JoinCompiler.Strategy strategy) throws SQLException {
+ if (!(plan instanceof HashJoinPlan))
+ return new HashJoinPlan(statement, plan, joinInfo, subPlans, joinInfo == null,
+ Collections.<ImmutableBytesPtr, ServerCache> emptyMap(), strategy);
HashJoinPlan hashJoinPlan = (HashJoinPlan) plan;
assert (hashJoinPlan.joinInfo == null && hashJoinPlan.delegate instanceof BaseQueryPlan);
@@ -130,16 +133,18 @@ public class HashJoinPlan extends DelegateQueryPlan {
mergedSubPlans[i++] = subPlan;
}
return new HashJoinPlan(statement, hashJoinPlan.delegate, joinInfo, mergedSubPlans, true,
- hashJoinPlan.dependencies);
+ hashJoinPlan.dependencies, strategy);
}
private HashJoinPlan(SelectStatement statement, QueryPlan plan, HashJoinInfo joinInfo,
SubPlan[] subPlans, boolean recompileWhereClause,
- Map<ImmutableBytesPtr, ServerCache> dependencies) throws SQLException {
+ Map<ImmutableBytesPtr, ServerCache> dependencies, JoinCompiler.Strategy strategy)
+ throws SQLException {
super(plan);
this.dependencies.putAll(dependencies);
this.statement = statement;
this.joinInfo = joinInfo;
+ this.strategy = strategy;
this.subPlans = subPlans;
this.recompileWhereClause = recompileWhereClause;
this.tableRefs = Sets.newHashSetWithExpectedSize(subPlans.length + plan.getSourceRefs().size());
@@ -382,6 +387,14 @@ public class HashJoinPlan extends DelegateQueryPlan {
return joinInfo;
}
+ public JoinCompiler.Strategy getStrategy() {
+ return strategy;
+ }
+
+ public void setStrategy(JoinCompiler.Strategy strategy) {
+ this.strategy = strategy;
+ }
+
public SubPlan[] getSubPlans() {
return subPlans;
}
@@ -683,18 +696,41 @@ public class HashJoinPlan extends DelegateQueryPlan {
}
}
+ /**
+ * Builds the join operator line (without leading indentation) for this subplan, with the chosen
+ * {@link JoinCompiler.Strategy} and the {@code SKIP MERGE} / {@code DELAYED EVALUATION}
+ * decorators rendered as a single trailing SQL comment.
+ */
+ private String buildHeader(HashJoinPlan parent) {
+ boolean isHashJoin = hashExpressions != null;
+ String op = isHashJoin
+ ? "PARALLEL " + parent.joinInfo.getJoinTypes()[index].toString().toUpperCase()
+ + "-JOIN TABLE " + index
+ : "SKIP-SCAN-JOIN TABLE " + index;
+ JoinCompiler.Strategy strategy = parent.getStrategy();
+ if (strategy == null) {
+ return op;
+ }
+ StringBuilder comment = new StringBuilder("/* HASH BUILD ");
+ comment.append(strategy == JoinCompiler.Strategy.HASH_BUILD_LEFT ? "LEFT" : "RIGHT");
+ if (isHashJoin) {
+ boolean skipMerge = parent.joinInfo.getSchemas()[index].getFieldCount() == 0;
+ boolean earlyEvaluation = parent.joinInfo.earlyEvaluation()[index];
+ if (skipMerge) {
+ comment.append(", SKIP MERGE");
+ }
+ if (!earlyEvaluation) {
+ comment.append(", DELAYED EVALUATION");
+ }
+ }
+ comment.append(" */");
+ return op + " " + comment;
+ }
+
@Override
public List<String> getPreSteps(HashJoinPlan parent) throws SQLException {
List<String> steps = Lists.newArrayList();
- boolean earlyEvaluation = parent.joinInfo.earlyEvaluation()[index];
- boolean skipMerge = parent.joinInfo.getSchemas()[index].getFieldCount() == 0;
- if (hashExpressions != null) {
- steps.add(" PARALLEL " + parent.joinInfo.getJoinTypes()[index].toString().toUpperCase()
- + "-JOIN TABLE " + index + (earlyEvaluation ? "" : "(DELAYED EVALUATION)")
- + (skipMerge ? " (SKIP MERGE)" : ""));
- } else {
- steps.add(" SKIP-SCAN-JOIN TABLE " + index);
- }
+ steps.add(" " + buildHeader(parent));
for (String step : plan.getExplainPlan().getPlanSteps()) {
steps.add(" " + step);
}
@@ -703,17 +739,7 @@ public class HashJoinPlan extends DelegateQueryPlan {
@Override
public ExplainPlanAttributes getPreStepsAsAttributes(HashJoinPlan parent) throws SQLException {
- boolean earlyEvaluation = parent.joinInfo.earlyEvaluation()[index];
- boolean skipMerge = parent.joinInfo.getSchemas()[index].getFieldCount() == 0;
- String header;
- if (hashExpressions != null) {
- header = "PARALLEL " + parent.joinInfo.getJoinTypes()[index].toString().toUpperCase()
- + "-JOIN TABLE " + index + (earlyEvaluation ? "" : "(DELAYED EVALUATION)")
- + (skipMerge ? " (SKIP MERGE)" : "");
- } else {
- header = "SKIP-SCAN-JOIN TABLE " + index;
- }
- return subPlanAttributesWithHeader(plan, header);
+ return subPlanAttributesWithHeader(plan, buildHeader(parent));
}
@Override
diff --git a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
index 9e1cd2290b..bf36c03a58 100644
--- a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
+++ b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
@@ -41,6 +41,7 @@ import org.apache.phoenix.compile.ExplainPlan;
import org.apache.phoenix.compile.ExplainPlanAttributes;
import org.apache.phoenix.compile.ExplainPlanAttributes.ExplainPlanAttributesBuilder;
import org.apache.phoenix.compile.GroupByCompiler.GroupBy;
+import org.apache.phoenix.compile.JoinCompiler;
import org.apache.phoenix.compile.OrderByCompiler.OrderBy;
import org.apache.phoenix.compile.QueryCompiler;
import org.apache.phoenix.compile.QueryPlan;
@@ -98,6 +99,7 @@ public class SortMergeJoinPlan implements QueryPlan {
* {@link JoinType#Left}.
*/
private final JoinType joinType;
+ private JoinCompiler.Strategy strategy = JoinCompiler.Strategy.SORT_MERGE;
private final QueryPlan lhsPlan;
private final QueryPlan rhsPlan;
private final List<Expression> lhsKeyExpressions;
@@ -188,7 +190,8 @@ public class SortMergeJoinPlan implements QueryPlan {
@Override
public ExplainPlan getExplainPlan() throws SQLException {
List<String> steps = Lists.newArrayList();
- steps.add("SORT-MERGE-JOIN (" + joinType.toString().toUpperCase() + ") TABLES");
+ steps
+ .add("SORT-MERGE-JOIN (" + joinType.toString().toUpperCase() + ") TABLES /* SORT_MERGE */");
ExplainPlan lhsExplainPlan = lhsPlan.getExplainPlan();
List<String> lhsPlanSteps = lhsExplainPlan.getPlanSteps();
ExplainPlanAttributes lhsPlanAttributes = lhsExplainPlan.getPlanStepsAsAttributes();
@@ -303,6 +306,14 @@ public class SortMergeJoinPlan implements QueryPlan {
return joinType;
}
+ public JoinCompiler.Strategy getStrategy() {
+ return strategy;
+ }
+
+ public void setStrategy(JoinCompiler.Strategy strategy) {
+ this.strategy = strategy;
+ }
+
private static SQLException closeIterators(ResultIterator lhsIterator,
ResultIterator rhsIterator) {
SQLException e = null;
diff --git a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/UnionPlan.java b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/UnionPlan.java
index 1a1e76f690..8d15872e7b 100644
--- a/phoenix-core-client/src/main/java/org/apache/phoenix/execute/UnionPlan.java
+++ b/phoenix-core-client/src/main/java/org/apache/phoenix/execute/UnionPlan.java
@@ -238,18 +238,52 @@ public class UnionPlan implements QueryPlan {
String abstractExplainPlan = "UNION ALL OVER " + this.plans.size() + " QUERIES";
builder.setAbstractExplainPlan(abstractExplainPlan);
steps.add(abstractExplainPlan);
- ResultIterator iterator = iterator();
- iterator.explain(steps, builder);
- // Indent plans steps nested under union, except last client-side merge/concat step (if there is
- // one)
- int offset =
- !orderBy.getOrderByExpressions().isEmpty() && limit != null ? 2 : limit != null ? 1 : 0;
- for (int i = 1; i < steps.size() - offset; i++) {
- steps.set(i, " " + steps.get(i));
- }
+ // Compose each branch from its own getExplainPlan() so the full sub-plan structure of every
+ // branch is preserved and explaining the union does not trigger sub-plan execution.
+ UnionResultIterators.explainBranches(plans, steps, builder);
+ addUnionTailLines(steps, builder);
return new ExplainPlan(steps, builder.build());
}
+ /**
+ * Appends the client-side merge/offset/limit lines that follow the union branches, mirroring the
+ * iterator chain assembled in {@link #iterator(ParallelScanGrouper, Scan)}. These lines stay at
+ * column 0, after the indented branch steps.
+ */
+ private void addUnionTailLines(List<String> steps, ExplainPlanAttributesBuilder builder) {
+ if (!orderBy.isEmpty() || this.supportOrderByOptimize) {
+ String mergeSort = "CLIENT MERGE SORT";
+ steps.add(mergeSort);
+ builder.setClientSortAlgo(mergeSort);
+ builder.addClientStep(mergeSort);
+ if (offset != null && offset > 0) {
+ String step = "CLIENT OFFSET " + offset;
+ steps.add(step);
+ builder.setClientOffset(offset);
+ builder.addClientStep(step);
+ }
+ if (limit != null && limit > 0) {
+ String step = "CLIENT LIMIT " + limit;
+ steps.add(step);
+ builder.setClientRowLimit(limit);
+ builder.addClientStep(step);
+ }
+ } else {
+ if (offset != null) {
+ String step = "CLIENT OFFSET " + offset;
+ steps.add(step);
+ builder.setClientOffset(offset);
+ builder.addClientStep(step);
+ }
+ if (limit != null) {
+ String step = "CLIENT " + limit + " ROW LIMIT";
+ steps.add(step);
+ builder.setClientRowLimit(limit);
+ builder.addClientStep(step);
+ }
+ }
+ }
+
@Override
public long getEstimatedSize() {
return DEFAULT_ESTIMATED_SIZE;
diff --git a/phoenix-core-client/src/main/java/org/apache/phoenix/iterate/UnionResultIterators.java b/phoenix-core-client/src/main/java/org/apache/phoenix/iterate/UnionResultIterators.java
index e5b981e7a6..d31e38df6d 100644
--- a/phoenix-core-client/src/main/java/org/apache/phoenix/iterate/UnionResultIterators.java
+++ b/phoenix-core-client/src/main/java/org/apache/phoenix/iterate/UnionResultIterators.java
@@ -21,6 +21,7 @@ import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import org.apache.hadoop.hbase.client.Scan;
+import org.apache.phoenix.compile.ExplainPlan;
import org.apache.phoenix.compile.ExplainPlanAttributes;
import org.apache.phoenix.compile.ExplainPlanAttributes.ExplainPlanAttributesBuilder;
import org.apache.phoenix.compile.QueryPlan;
@@ -43,10 +44,12 @@ public class UnionResultIterators implements ResultIterators {
private final List<OverAllQueryMetrics> overAllQueryMetricsList;
private boolean closed;
private final StatementContext parentStmtCtx;
+ private final List<QueryPlan> plans;
public UnionResultIterators(List<QueryPlan> plans, StatementContext parentStmtCtx)
throws SQLException {
this.parentStmtCtx = parentStmtCtx;
+ this.plans = plans;
int nPlans = plans.size();
iterators = Lists.newArrayListWithExpectedSize(nPlans);
splits = Lists.newArrayListWithExpectedSize(nPlans * 30);
@@ -125,8 +128,10 @@ public class UnionResultIterators implements ResultIterators {
@Override
public void explain(List<String> planSteps) {
- for (PeekingResultIterator iterator : iterators) {
- iterator.explain(planSteps);
+ try {
+ explainBranches(plans, planSteps, null);
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
}
}
@@ -138,22 +143,41 @@ public class UnionResultIterators implements ResultIterators {
@Override
public void explain(List<String> planSteps,
ExplainPlanAttributesBuilder explainPlanAttributesBuilder) {
- boolean moreThanOneIters = false;
- ExplainPlanAttributesBuilder lhsPointer = null;
- // For more than one iterators, explainPlanAttributes will create
- // chain of objects as lhs and rhs query plans.
- for (PeekingResultIterator iterator : iterators) {
- if (moreThanOneIters) {
- ExplainPlanAttributesBuilder rhsBuilder = new ExplainPlanAttributesBuilder();
- iterator.explain(planSteps, rhsBuilder);
- ExplainPlanAttributes rhsPlans = rhsBuilder.build();
- lhsPointer.setRhsJoinQueryExplainPlan(rhsPlans);
- lhsPointer = rhsBuilder;
- } else {
- iterator.explain(planSteps, explainPlanAttributesBuilder);
- lhsPointer = explainPlanAttributesBuilder;
+ try {
+ explainBranches(plans, planSteps, explainPlanAttributesBuilder);
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ /**
+ * Single source of truth for composing a union's branches into the explain output. Each branch is
+ * rendered from its own {@link QueryPlan#getExplainPlan()} (rather than from its iterator) so the
+ * full sub-plan structure of each branch is preserved, and so explaining a union does not trigger
+ * sub-plan execution. The branch text steps are indented one level and, when a builder is
+ * supplied, the branches' structured attributes are collected onto the builder's {@code subPlans}
+ * list.
+ * <p>
+ * Every branch contributes exactly one entry in branch order, so {@code getSubPlans().size()}
+ * always equals {@code plans.size()}.
+ */
+ public static void explainBranches(List<QueryPlan> plans, List<String> planSteps,
+ ExplainPlanAttributesBuilder builder) throws SQLException {
+ List<ExplainPlanAttributes> subPlans =
+ builder == null ? null : Lists.newArrayListWithExpectedSize(plans.size());
+ for (QueryPlan plan : plans) {
+ ExplainPlan explainPlan = plan.getExplainPlan();
+ for (String step : explainPlan.getPlanSteps()) {
+ planSteps.add(" " + step);
+ }
+ if (subPlans != null) {
+ ExplainPlanAttributes attributes = explainPlan.getPlanStepsAsAttributes();
+ subPlans
+ .add(attributes != null ? attributes : ExplainPlanAttributes.getDefaultExplainPlan());
}
- moreThanOneIters = true;
+ }
+ if (subPlans != null) {
+ builder.setSubPlans(subPlans);
}
}
}
diff --git a/phoenix-core-client/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java b/phoenix-core-client/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
index 20edcbe34d..2a76e8a6fa 100644
--- a/phoenix-core-client/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
+++ b/phoenix-core-client/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
@@ -1263,7 +1263,7 @@ public class PhoenixStatement implements PhoenixMonitoredStatement, SQLCloseable
return new BaseMutationPlan(context, this.getOperation()) {
@Override
public ExplainPlan getExplainPlan() throws SQLException {
- return new ExplainPlan(Collections.singletonList("Truncate Table"));
+ return new ExplainPlan(Collections.singletonList("TRUNCATE TABLE"));
}
@Override
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CostBasedDecisionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CostBasedDecisionIT.java
index 8e9518db9b..15fe361aaa 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/CostBasedDecisionIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/CostBasedDecisionIT.java
@@ -334,13 +334,13 @@ public class CostBasedDecisionIT extends BaseTest {
+ " where rowkey <= 'z' GROUP BY c1 " + "UNION ALL SELECT c1, max(rowkey), max(c2) FROM "
+ tableName + " where rowkey >= 'a' GROUP BY c1";
// Use the default plan when stats are not available.
- assertPlan(conn, query).abstractExplainPlan("UNION ALL OVER 2 QUERIES")
- .iteratorType("PARALLEL").scanType("RANGE SCAN").table(tableName).keyRanges(" [*] - ['z']")
- .serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [C1]")
- .clientSortAlgo("CLIENT MERGE SORT").rhs().iteratorType("PARALLEL").scanType("RANGE SCAN")
- .table(tableName).keyRanges(" ['a'] - [*]")
+ assertPlan(conn, query).abstractExplainPlan("UNION ALL OVER 2 QUERIES").subPlanCount(2)
+ .subPlan(0).iteratorType("PARALLEL").scanType("RANGE SCAN").table(tableName)
+ .keyRanges(" [*] - ['z']").serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [C1]")
+ .clientSortAlgo("CLIENT MERGE SORT").end().subPlan(1).iteratorType("PARALLEL")
+ .scanType("RANGE SCAN").table(tableName).keyRanges(" ['a'] - [*]")
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [C1]")
- .clientSortAlgo("CLIENT MERGE SORT");
+ .clientSortAlgo("CLIENT MERGE SORT").end();
PreparedStatement stmt =
conn.prepareStatement("UPSERT INTO " + tableName + " (rowkey, c1, c2) VALUES (?, ?, ?)");
@@ -355,16 +355,17 @@ public class CostBasedDecisionIT extends BaseTest {
conn.createStatement().execute("UPDATE STATISTICS " + tableName);
// Use the optimal plan based on cost when stats become available.
- assertPlan(conn, query).abstractExplainPlan("UNION ALL OVER 2 QUERIES")
- .iteratorType("PARALLEL").scanType("RANGE SCAN").table(indexName + "(" + tableName + ")")
- .keyRanges(" [1]").serverMergeColumns("[0.C2]").serverFirstKeyOnlyProjection(true)
- .serverWhereFilter("SERVER FILTER BY \"ROWKEY\" <= 'z'")
- .serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"C1\"]")
- .clientSortAlgo("CLIENT MERGE SORT").rhs().iteratorType("PARALLEL").scanType("RANGE SCAN")
+ assertPlan(conn, query).abstractExplainPlan("UNION ALL OVER 2 QUERIES").subPlanCount(2)
+ .subPlan(0).iteratorType("PARALLEL").scanType("RANGE SCAN")
.table(indexName + "(" + tableName + ")").keyRanges(" [1]").serverMergeColumns("[0.C2]")
- .serverFirstKeyOnlyProjection(true).serverWhereFilter("SERVER FILTER BY \"ROWKEY\" >= 'a'")
+ .serverFirstKeyOnlyProjection(true).serverWhereFilter("SERVER FILTER BY \"ROWKEY\" <= 'z'")
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"C1\"]")
- .clientSortAlgo("CLIENT MERGE SORT");
+ .clientSortAlgo("CLIENT MERGE SORT").end().subPlan(1).iteratorType("PARALLEL")
+ .scanType("RANGE SCAN").table(indexName + "(" + tableName + ")").keyRanges(" [1]")
+ .serverMergeColumns("[0.C2]").serverFirstKeyOnlyProjection(true)
+ .serverWhereFilter("SERVER FILTER BY \"ROWKEY\" >= 'a'")
+ .serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"C1\"]")
+ .clientSortAlgo("CLIENT MERGE SORT").end();
} finally {
conn.close();
}
@@ -391,7 +392,7 @@ public class CostBasedDecisionIT extends BaseTest {
assertPlan(conn, query).iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(tableName)
.serverWhereFilter("SERVER FILTER BY C1 LIKE 'X0%'")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY T1.ROWKEY IN (T2.MRK)").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.iteratorType("PARALLEL 1-WAY").scanType("RANGE SCAN").table(tableName)
.keyRanges(" [*] - ['z']").serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [C1]")
.clientSortAlgo("CLIENT MERGE SORT");
@@ -414,7 +415,7 @@ public class CostBasedDecisionIT extends BaseTest {
.serverMergeColumns("[0.C2]").serverFirstKeyOnlyProjection(true)
.serverSortedBy("[\"T1.:ROWKEY\"]").clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"T1.:ROWKEY\" IN (T2.MRK)").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.iteratorType("PARALLEL 1-WAY").scanType("RANGE SCAN")
.table(indexName + "(" + tableName + ")").keyRanges(" [1]").serverMergeColumns("[0.C2]")
.serverFirstKeyOnlyProjection(true).serverWhereFilter("SERVER FILTER BY \"ROWKEY\" <= 'z'")
@@ -517,7 +518,7 @@ public class CostBasedDecisionIT extends BaseTest {
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
assertPlan(conn, q).iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable1000)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY T2.ID IN (T1.COL1)").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
.iteratorType("PARALLEL 1-WAY").scanType("RANGE SCAN").table(testTable500)
.keyRanges(" [201] - [*]");
}
@@ -535,7 +536,7 @@ public class CostBasedDecisionIT extends BaseTest {
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
assertPlan(conn, q).iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable990)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY T1.ID IN (T2.COL1)").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable1000);
}
}
@@ -548,7 +549,8 @@ public class CostBasedDecisionIT extends BaseTest {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
assertPlan(conn, q).iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable1000)
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
.iteratorType("PARALLEL 1-WAY").scanType("RANGE SCAN").table(testTable500)
.keyRanges(" [201] - [*]");
}
@@ -562,7 +564,8 @@ public class CostBasedDecisionIT extends BaseTest {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
assertPlan(conn, q).iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable1000)
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
.iteratorType("PARALLEL 1-WAY").scanType("RANGE SCAN").table(testTable500)
.keyRanges(" [201] - [*]");
}
@@ -582,8 +585,8 @@ public class CostBasedDecisionIT extends BaseTest {
assertPlan(conn, q).iteratorType("PARALLEL 1001-WAY").scanType("FULL SCAN")
.table(testTable1000).serverSortedBy("[T1.COL1]").clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY T2.ID IN (T1.ID)").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").iteratorType("PARALLEL 1-WAY")
- .scanType("FULL SCAN").table(testTable500);
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable500);
}
}
@@ -637,7 +640,7 @@ public class CostBasedDecisionIT extends BaseTest {
assertPlan(conn, q).abstractExplainPlan("SORT-MERGE-JOIN (INNER)").lhs()
.iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable1000)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY T1.ID IN (T2.COL1)").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.iteratorType("PARALLEL 1-WAY").scanType("RANGE SCAN").table(testTable500)
.keyRanges(" [201] - [*]").end().end().rhs().iteratorType("PARALLEL 1-WAY")
.scanType("RANGE SCAN").table(testTable990).keyRanges(" [*] - [100]");
@@ -656,11 +659,13 @@ public class CostBasedDecisionIT extends BaseTest {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
assertPlan(conn, q).iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable1000)
- .subPlanCount(2).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(2).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.iteratorType("PARALLEL 1-WAY").scanType("RANGE SCAN").table(testTable500)
.keyRanges(" [201] - [*]").end().subPlan(1)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1").iteratorType("PARALLEL 1-WAY")
- .scanType("RANGE SCAN").table(testTable990).keyRanges(" [*] - [100]");
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT */")
+ .iteratorType("PARALLEL 1-WAY").scanType("RANGE SCAN").table(testTable990)
+ .keyRanges(" [*] - [100]");
}
}
@@ -677,8 +682,8 @@ public class CostBasedDecisionIT extends BaseTest {
assertPlan(conn, q).abstractExplainPlan("SORT-MERGE-JOIN (INNER)").lhs()
.iteratorType("PARALLEL 1001-WAY").scanType("FULL SCAN").table(testTable1000)
.serverSortedBy("[T1.COL1]").clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").iteratorType("PARALLEL 1-WAY")
- .scanType("FULL SCAN").table(testTable990).end().end().rhs()
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .iteratorType("PARALLEL 1-WAY").scanType("FULL SCAN").table(testTable990).end().end().rhs()
.iteratorType("PARALLEL 991-WAY").scanType("FULL SCAN").table(testTable990)
.serverSortedBy("[T3.COL2]").clientSortAlgo("CLIENT MERGE SORT");
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithTableSampleIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithTableSampleIT.java
index 93702373e9..bccd30040f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithTableSampleIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithTableSampleIT.java
@@ -225,11 +225,11 @@ public class QueryWithTableSampleIT extends ParallelStatsEnabledIT {
String query =
"SELECT * FROM " + tableName + " tablesample (100) where i1<2 union all SELECT * FROM "
+ tableName + " tablesample (2) where i2<6000";
- assertPlan(conn, query).abstractExplainPlan("UNION ALL OVER 2 QUERIES").scanType("RANGE SCAN")
- .table(tableName).keyRanges(" [*] - [2]").samplingRate(1.0d)
- .serverFirstKeyOnlyProjection(true).rhs().scanType("FULL SCAN").table(tableName)
- .samplingRate(0.02d).serverFirstKeyOnlyProjection(true)
- .serverWhereFilter("SERVER FILTER BY I2 < 6000").end();
+ assertPlan(conn, query).abstractExplainPlan("UNION ALL OVER 2 QUERIES").subPlanCount(2)
+ .subPlan(0).scanType("RANGE SCAN").table(tableName).keyRanges(" [*] - [2]")
+ .samplingRate(1.0d).serverFirstKeyOnlyProjection(true).end().subPlan(1)
+ .scanType("FULL SCAN").table(tableName).samplingRate(0.02d)
+ .serverFirstKeyOnlyProjection(true).serverWhereFilter("SERVER FILTER BY I2 < 6000").end();
} finally {
conn.close();
}
@@ -247,8 +247,9 @@ public class QueryWithTableSampleIT extends ParallelStatsEnabledIT {
assertPlan(conn, query).scanType("FULL SCAN").table(tableName).samplingRate(0.45d)
.serverFirstKeyOnlyProjection(true).serverAggregate("SERVER AGGREGATE INTO SINGLE ROW")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY A.I1 IN (B.I1)").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)").scanType("FULL SCAN")
- .table(joinedTableName).samplingRate(0.75d).serverFirstKeyOnlyProjection(true).end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
+ .scanType("FULL SCAN").table(joinedTableName).samplingRate(0.75d)
+ .serverFirstKeyOnlyProjection(true).end();
} finally {
conn.close();
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java
index e63972ba38..699c197391 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java
@@ -30,6 +30,7 @@ import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.List;
import java.util.Properties;
import org.apache.phoenix.compile.ExplainPlan;
import org.apache.phoenix.compile.ExplainPlanAttributes;
@@ -624,15 +625,23 @@ public class UnionAllIT extends ParallelStatsDisabledIT {
ExplainPlan plan = conn.prepareStatement(ddl).unwrap(PhoenixPreparedStatement.class)
.optimizeQuery().getExplainPlan();
ExplainPlanAttributes explainPlanAttributes = plan.getPlanStepsAsAttributes();
+ // The union composes each branch recursively into subPlans. The root carries only the union
+ // level
+ // client side, and each branch's scan attributes live on its own subPlan entry.
assertEquals("UNION ALL OVER 2 QUERIES", explainPlanAttributes.getAbstractExplainPlan());
- assertEquals("PARALLEL 1-WAY", explainPlanAttributes.getIteratorTypeAndScanSize());
- assertEquals("FULL SCAN ", explainPlanAttributes.getExplainScanType());
- assertEquals(tableName1, explainPlanAttributes.getTableName());
- assertEquals("[COL1]", explainPlanAttributes.getServerSortedBy());
- assertEquals(1L, explainPlanAttributes.getServerRowLimit().longValue());
- assertEquals(1, explainPlanAttributes.getClientRowLimit().intValue());
assertEquals("CLIENT MERGE SORT", explainPlanAttributes.getClientSortAlgo());
- ExplainPlanAttributes rhsPlanAttributes = explainPlanAttributes.getRhsJoinQueryExplainPlan();
+ assertEquals(1, explainPlanAttributes.getClientRowLimit().intValue());
+ List<ExplainPlanAttributes> subPlans = explainPlanAttributes.getSubPlans();
+ assertEquals(2, subPlans.size());
+ ExplainPlanAttributes lhsPlanAttributes = subPlans.get(0);
+ assertEquals("PARALLEL 1-WAY", lhsPlanAttributes.getIteratorTypeAndScanSize());
+ assertEquals("FULL SCAN ", lhsPlanAttributes.getExplainScanType());
+ assertEquals(tableName1, lhsPlanAttributes.getTableName());
+ assertEquals("[COL1]", lhsPlanAttributes.getServerSortedBy());
+ assertEquals(1L, lhsPlanAttributes.getServerRowLimit().longValue());
+ assertEquals(1, lhsPlanAttributes.getClientRowLimit().intValue());
+ assertEquals("CLIENT MERGE SORT", lhsPlanAttributes.getClientSortAlgo());
+ ExplainPlanAttributes rhsPlanAttributes = subPlans.get(1);
assertEquals("PARALLEL 1-WAY", rhsPlanAttributes.getIteratorTypeAndScanSize());
assertEquals("FULL SCAN ", rhsPlanAttributes.getExplainScanType());
assertEquals(tableName2, rhsPlanAttributes.getTableName());
@@ -642,15 +651,15 @@ public class UnionAllIT extends ParallelStatsDisabledIT {
assertEquals("CLIENT MERGE SORT", rhsPlanAttributes.getClientSortAlgo());
// Each branch emits a SERIAL 1-WAY FULL SCAN with SERVER 2 ROW LIMIT and an inner CLIENT 2
- // ROW LIMIT, and the union root carries an outer CLIENT 2 ROW LIMIT. The outer wrap shows
- // up as a second "CLIENT 2 ROW LIMIT" entry in the root's clientSteps.
+ // ROW LIMIT. The union root carries the single outer CLIENT 2 ROW LIMIT.
ddl = "select a_string, col1 from " + tableName1 + " union all select a_string, col1 from "
+ tableName2 + " limit 2";
- assertPlan(conn, ddl).abstractExplainPlan("UNION ALL OVER 2 QUERIES").iteratorType("SERIAL")
+ assertPlan(conn, ddl).abstractExplainPlan("UNION ALL OVER 2 QUERIES").clientRowLimit(2)
+ .clientSteps("CLIENT 2 ROW LIMIT").subPlanCount(2).subPlan(0).iteratorType("SERIAL")
.scanType("FULL SCAN").table(tableName1).serverSortedBy(null).serverRowLimit(2L)
- .clientRowLimit(2).clientSteps("CLIENT 2 ROW LIMIT", "CLIENT 2 ROW LIMIT").rhs()
- .iteratorType("SERIAL").scanType("FULL SCAN").table(tableName2).serverSortedBy(null)
- .serverRowLimit(2L).clientRowLimit(2).clientSteps("CLIENT 2 ROW LIMIT");
+ .clientRowLimit(2).clientSteps("CLIENT 2 ROW LIMIT").end().subPlan(1).iteratorType("SERIAL")
+ .scanType("FULL SCAN").table(tableName2).serverSortedBy(null).serverRowLimit(2L)
+ .clientRowLimit(2).clientSteps("CLIENT 2 ROW LIMIT").end();
ddl = "select a_string, col1 from " + tableName1 + " union all select a_string, col1 from "
+ tableName2;
@@ -658,10 +667,13 @@ public class UnionAllIT extends ParallelStatsDisabledIT {
.getExplainPlan();
explainPlanAttributes = plan.getPlanStepsAsAttributes();
assertEquals("UNION ALL OVER 2 QUERIES", explainPlanAttributes.getAbstractExplainPlan());
- assertEquals("PARALLEL 1-WAY", explainPlanAttributes.getIteratorTypeAndScanSize());
- assertEquals("FULL SCAN ", explainPlanAttributes.getExplainScanType());
- assertEquals(tableName1, explainPlanAttributes.getTableName());
- rhsPlanAttributes = explainPlanAttributes.getRhsJoinQueryExplainPlan();
+ subPlans = explainPlanAttributes.getSubPlans();
+ assertEquals(2, subPlans.size());
+ lhsPlanAttributes = subPlans.get(0);
+ assertEquals("PARALLEL 1-WAY", lhsPlanAttributes.getIteratorTypeAndScanSize());
+ assertEquals("FULL SCAN ", lhsPlanAttributes.getExplainScanType());
+ assertEquals(tableName1, lhsPlanAttributes.getTableName());
+ rhsPlanAttributes = subPlans.get(1);
assertEquals("PARALLEL 1-WAY", rhsPlanAttributes.getIteratorTypeAndScanSize());
assertEquals("FULL SCAN ", rhsPlanAttributes.getExplainScanType());
assertEquals(tableName2, rhsPlanAttributes.getTableName());
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
index 1f900e2f5f..39800cf2c4 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/GlobalIndexOptimizationIT.java
@@ -240,7 +240,8 @@ public class GlobalIndexOptimizationIT extends ParallelStatsDisabledIT {
.table(dataTableName).serverWhereFilter("SERVER FILTER BY K3 > 1")
.serverSortedBy("[" + dataTableName + ".V1, " + dataTableName + ".T_ID]")
.clientSortAlgo("CLIENT MERGE SORT");
- skipScanJoinPlan.subPlanCount(1).subPlan(0).abstractExplainPlan("SKIP-SCAN-JOIN TABLE 0")
+ skipScanJoinPlan.subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("SKIP-SCAN-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("RANGE SCAN").table(indexTableName).keyRanges(" [*] - ['z']")
.serverFirstKeyOnlyProjection(true).end();
// The dynamic server filter references compiler-generated positional aliases ($N.$N) whose
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
index 9acbc4848e..f1fbc4175c 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/IndexUsageIT.java
@@ -782,12 +782,14 @@ public class IndexUsageIT extends ParallelStatsDisabledIT {
.serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1)
.subPlan(0).scanType("RANGE SCAN").tableContains(indexName + "(" + tableName + ")")
.keyRanges(" [1]").serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT")
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)").end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
+ .end();
} else {
assertPlan(conn, query).scanType("RANGE SCAN").tableContains(indexName)
.keyRanges(" ['1David']").serverFirstKeyOnlyProjection(true).subPlanCount(1).subPlan(0)
.scanType("FULL SCAN").tableContains(indexName).serverFirstKeyOnlyProjection(true)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)").end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
+ .end();
}
rs = conn.createStatement().executeQuery(query);
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinGlobalIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinGlobalIndexIT.java
index a3ee5b48be..a6319944e0 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinGlobalIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinGlobalIndexIT.java
@@ -62,8 +62,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.0:NAME\"]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(idxItem)
- .serverFirstKeyOnlyProjection(true).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true).end();
}
@Override
@@ -73,8 +73,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.:item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").clientSortedBy("[SUM(O.QUANTITY) DESC]").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN")
- .table(idxItem).serverFirstKeyOnlyProjection(true).end();
+ .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true).end();
}
@Override
@@ -84,7 +84,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]")
.clientSortedBy("[SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -93,7 +94,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.0:NAME\"]")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
.scanType("FULL SCAN").table(order).end();
}
@@ -104,7 +106,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]")
.clientSortedBy("[SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -112,8 +115,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String item = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
String supplier = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(supplier)
- .end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(supplier).end();
}
@Override
@@ -122,8 +125,9 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String idxSupplier = getSchemaName() + ".idx_supplier";
assertPlan(conn, query).scanType("RANGE SCAN").table(idxItem).keyRanges(" ['T1'] - ['T5']")
.serverFirstKeyOnlyProjection(true).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("RANGE SCAN").table(idxSupplier)
- .keyRanges(" ['S1'] - ['S5']").serverFirstKeyOnlyProjection(true).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(idxSupplier).keyRanges(" ['S1'] - ['S5']")
+ .serverFirstKeyOnlyProjection(true).end();
}
@Override
@@ -132,8 +136,9 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String idxSupplier = getSchemaName() + ".idx_supplier";
assertPlan(conn, query).scanType("SKIP SCAN ON 2 KEYS").table(idxItem)
.keyRanges(" ['T1'] - ['T5']").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("SKIP SCAN ON 2 KEYS")
- .table(idxSupplier).keyRanges(" ['S1'] - ['S5']").serverFirstKeyOnlyProjection(true).end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("SKIP SCAN ON 2 KEYS").table(idxSupplier).keyRanges(" ['S1'] - ['S5']")
+ .serverFirstKeyOnlyProjection(true).end();
}
@Override
@@ -142,10 +147,10 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
String idxSupplier = getSchemaName() + ".idx_supplier";
assertPlan(conn, query).scanType("FULL SCAN").table(idxItem).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)").scanType("FULL SCAN")
- .table(order).serverWhereFilter("SERVER FILTER BY QUANTITY < 5000").end().subPlan(1)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1").scanType("FULL SCAN").table(idxSupplier)
- .serverFirstKeyOnlyProjection(true).end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
+ .scanType("FULL SCAN").table(order).serverWhereFilter("SERVER FILTER BY QUANTITY < 5000")
+ .end().subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxSupplier).serverFirstKeyOnlyProjection(true).end();
}
@Override
@@ -154,7 +159,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String idxItem = getSchemaName() + ".idx_item";
assertPlan(conn, query).scanType("FULL SCAN").table(item)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.:item_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true).end();
}
@@ -163,7 +169,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String idxItem = getSchemaName() + ".idx_item";
assertPlan(conn, query).scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true)
.serverSortedBy("[\"I1.0:NAME\", \"I2.0:NAME\"]").clientSortAlgo("CLIENT MERGE SORT")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(idxItem).end();
}
@@ -175,16 +182,17 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String idxItem = getSchemaName() + ".idx_item";
if (!noStarJoin) {
assertPlan(conn, query).scanType("FULL SCAN").table(order).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(idxCustomer)
- .serverFirstKeyOnlyProjection(true).end().subPlan(1)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1").scanType("FULL SCAN").table(idxItem)
- .serverFirstKeyOnlyProjection(true).end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxCustomer).serverFirstKeyOnlyProjection(true).end()
+ .subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true).end();
} else {
assertPlan(conn, query).scanType("FULL SCAN").table(idxItem)
.serverFirstKeyOnlyProjection(true).serverSortedBy("[\"O.order_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(order)
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order).subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(idxCustomer).serverFirstKeyOnlyProjection(true).end().end();
}
}
@@ -199,13 +207,15 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
.keyRanges(" [*] - ['0000000005']").serverSortedBy("[\"C.customer_id\", \"I.0:NAME\"]")
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(order)
.serverWhereFilter("SERVER FILTER BY \"order_id\" != '000000000000003'").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN")
- .table(idxItem).serverWhereFilter("SERVER FILTER BY \"NAME\" != 'T3'").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN")
- .table(supplier).end().end().end();
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).serverWhereFilter("SERVER FILTER BY \"NAME\" != 'T3'")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(supplier).end().end().end();
}
@Override
@@ -215,8 +225,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(idxItem)
- .serverFirstKeyOnlyProjection(true).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true).end();
}
@Override
@@ -226,7 +236,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]")
.clientSortAlgo("CLIENT MERGE SORT").clientSortedBy("[SUM(O.QUANTITY) DESC]").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)")
+ .subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
.scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true).end();
}
@@ -236,7 +247,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true)
.serverSortedBy("[O.Q DESC NULLS LAST, I.IID]").clientSortAlgo("CLIENT MERGE SORT")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").end();
@@ -248,8 +260,9 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true)
.serverSortedBy("[O.Q DESC, I.IID]").clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN")
- .table(order).serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order)
+ .serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").end();
}
@@ -262,12 +275,14 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("RANGE SCAN").table(customer)
.keyRanges(" [*] - ['0000000005']").serverSortedBy("[C.CID, QO.INAME]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(order)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order)
.serverWhereFilter("SERVER FILTER BY \"order_id\" != '000000000000003'").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN")
- .table(idxItem).serverWhereFilter("SERVER FILTER BY \"NAME\" != 'T3'").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN")
- .table(supplier).end().end().end();
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).serverWhereFilter("SERVER FILTER BY \"NAME\" != 'T3'")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(supplier).end().end().end();
}
@Override
@@ -277,8 +292,9 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).iteratorType("SERIAL").scanType("FULL SCAN").table(supplier)
.serverRowLimit(4L).clientRowLimit(4).joinScannerLimit(4L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(idxItem).end()
- .subPlan(1).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).end().subPlan(1)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
@@ -290,8 +306,10 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(supplier).clientRowLimit(4)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")")
.joinScannerLimit(4L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(idxItem).end()
- .subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).end().subPlan(1)
+ .abstractExplainPlan(
+ "PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
@@ -306,7 +324,8 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
statement.optimizeQuery().getExplainPlan().getPlanStepsAsAttributes();
assertPlan(attributes).scanType("FULL SCAN").table(idxItem).serverFirstKeyOnlyProjection(true)
.clientRowLimit(4).joinScannerLimit(4L).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -316,10 +335,10 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).iteratorType("SERIAL").scanType("FULL SCAN").table(supplier)
.serverOffset(2).serverRowLimit(3L).clientRowLimit(1).joinScannerLimit(3L).subPlanCount(2)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN")
- .table(idxItem).end().subPlan(1)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)").scanType("FULL SCAN")
- .table(order).end();
+ .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).end().subPlan(1)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -331,8 +350,10 @@ public class HashJoinGlobalIndexIT extends HashJoinIT {
.serverOffset(2).clientRowLimit(1)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")")
.joinScannerLimit(3L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(idxItem).end()
- .subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(idxItem).end().subPlan(1)
+ .abstractExplainPlan(
+ "PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinLocalIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinLocalIndexIT.java
index 9f46bea9a8..2ae8c4d42f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinLocalIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinLocalIndexIT.java
@@ -77,9 +77,9 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.0:NAME\"]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]").serverFirstKeyOnlyProjection(true)
- .clientSortAlgo("CLIENT MERGE SORT").end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
}
@Override
@@ -90,9 +90,9 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.:item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").clientSortedBy("[SUM(O.QUANTITY) DESC]").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]").serverFirstKeyOnlyProjection(true)
- .clientSortAlgo("CLIENT MERGE SORT").end();
+ .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
}
@Override
@@ -102,7 +102,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]")
.clientSortedBy("[SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -114,7 +115,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
.keyRanges(" [1]").serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.0:NAME\"]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -124,7 +126,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]")
.clientSortedBy("[SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -132,8 +135,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
String item = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
String supplier = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(supplier)
- .end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(supplier).end();
}
@Override
@@ -145,9 +148,10 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("RANGE SCAN").table(itemIndex + "(" + item + ")")
.keyRanges(" [1,'T1'] - [1,'T5']").serverFirstKeyOnlyProjection(true)
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(supplierIndex + "(" + supplier + ")").keyRanges(" [1,'S1'] - [1,'S5']")
- .serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(supplierIndex + "(" + supplier + ")")
+ .keyRanges(" [1,'S1'] - [1,'S5']").serverFirstKeyOnlyProjection(true)
+ .clientSortAlgo("CLIENT MERGE SORT").end();
}
@Override
@@ -158,9 +162,10 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
String supplierIndex = SchemaUtil.getTableName(getSchemaName(), JOIN_SUPPLIER_INDEX);
assertPlan(conn, query).scanType("SKIP SCAN ON 2 KEYS").table(itemIndex + "(" + item + ")")
.keyRanges(" [1,'T1'] - [1,'T5']").clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("SKIP SCAN ON 2 KEYS")
- .table(supplierIndex + "(" + supplier + ")").keyRanges(" [1,'S1'] - [1,'S5']")
- .serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("SKIP SCAN ON 2 KEYS").table(supplierIndex + "(" + supplier + ")")
+ .keyRanges(" [1,'S1'] - [1,'S5']").serverFirstKeyOnlyProjection(true)
+ .clientSortAlgo("CLIENT MERGE SORT").end();
}
@Override
@@ -173,10 +178,11 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("RANGE SCAN").table(itemIndex + "(" + item + ")")
.keyRanges(" [1]").clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")")
- .subPlanCount(2).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)")
+ .subPlanCount(2).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
.scanType("FULL SCAN").table(order).serverWhereFilter("SERVER FILTER BY QUANTITY < 5000")
- .end().subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1").scanType("RANGE SCAN")
- .table(supplierIndex + "(" + supplier + ")").keyRanges(" [1]")
+ .end().subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(supplierIndex + "(" + supplier + ")").keyRanges(" [1]")
.serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
}
@@ -186,7 +192,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
String itemIndex = SchemaUtil.getTableName(getSchemaName(), JOIN_ITEM_INDEX);
assertPlan(conn, query).scanType("FULL SCAN").table(item)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.:item_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
.serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
}
@@ -199,7 +206,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
.keyRanges(" [1]").serverFirstKeyOnlyProjection(true)
.serverSortedBy("[\"I1.0:NAME\", \"I2.0:NAME\"]").clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I1.:item_id\" IN (\"I2.0:supplier_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
.clientSortAlgo("CLIENT MERGE SORT").end();
}
@@ -214,21 +222,22 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
String customerIndex = SchemaUtil.getTableName(getSchemaName(), JOIN_CUSTOMER_INDEX);
if (!noStarJoin) {
assertPlan(conn, query).scanType("FULL SCAN").table(order).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(customerIndex + "(" + customer + ")").keyRanges(" [1]")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(customerIndex + "(" + customer + ")").keyRanges(" [1]")
.serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end().subPlan(1)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]").serverFirstKeyOnlyProjection(true)
- .clientSortAlgo("CLIENT MERGE SORT").end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
} else {
assertPlan(conn, query).scanType("RANGE SCAN").table(itemIndex + "(" + item + ")")
.keyRanges(" [1]").serverFirstKeyOnlyProjection(true).serverSortedBy("[\"O.order_id\"]")
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
.scanType("FULL SCAN").table(order).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(customerIndex + "(" + customer + ")").keyRanges(" [1]")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(customerIndex + "(" + customer + ")").keyRanges(" [1]")
.serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end().end();
}
}
@@ -244,13 +253,15 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
.keyRanges(" [*] - ['0000000005']").serverSortedBy("[\"C.customer_id\", \"I.0:NAME\"]")
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(order)
.serverWhereFilter("SERVER FILTER BY \"order_id\" != '000000000000003'").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
.serverWhereFilter("SERVER FILTER BY \"NAME\" != 'T3'").clientSortAlgo("CLIENT MERGE SORT")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
.scanType("FULL SCAN").table(supplier).end().end().end();
}
@@ -262,9 +273,9 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]").serverFirstKeyOnlyProjection(true)
- .clientSortAlgo("CLIENT MERGE SORT").end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
}
@Override
@@ -275,7 +286,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]")
.clientSortAlgo("CLIENT MERGE SORT").clientSortedBy("[SUM(O.QUANTITY) DESC]").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)")
+ .subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
.scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
.serverFirstKeyOnlyProjection(true).clientSortAlgo("CLIENT MERGE SORT").end();
}
@@ -288,7 +300,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("RANGE SCAN").table(itemIndex + "(" + item + ")")
.keyRanges(" [1]").serverFirstKeyOnlyProjection(true)
.serverSortedBy("[O.Q DESC NULLS LAST, I.IID]").clientSortAlgo("CLIENT MERGE SORT")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").end();
@@ -302,7 +315,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("RANGE SCAN").table(itemIndex + "(" + item + ")")
.keyRanges(" [1]").serverFirstKeyOnlyProjection(true).serverSortedBy("[O.Q DESC, I.IID]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(order)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").end();
}
@@ -317,12 +331,14 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("RANGE SCAN").table(customer)
.keyRanges(" [*] - ['0000000005']").serverSortedBy("[C.CID, QO.INAME]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(order)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order)
.serverWhereFilter("SERVER FILTER BY \"order_id\" != '000000000000003'").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
.serverWhereFilter("SERVER FILTER BY \"NAME\" != 'T3'").clientSortAlgo("CLIENT MERGE SORT")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
.scanType("FULL SCAN").table(supplier).end().end().end();
}
@@ -334,9 +350,10 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).iteratorType("SERIAL").scanType("FULL SCAN").table(supplier)
.serverRowLimit(4L).clientRowLimit(4).joinScannerLimit(4L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]").clientSortAlgo("CLIENT MERGE SORT")
- .end().subPlan(1).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .clientSortAlgo("CLIENT MERGE SORT").end().subPlan(1)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
@@ -349,9 +366,11 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(supplier).clientRowLimit(4)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")")
.joinScannerLimit(4L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]").clientSortAlgo("CLIENT MERGE SORT")
- .end().subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .clientSortAlgo("CLIENT MERGE SORT").end().subPlan(1)
+ .abstractExplainPlan(
+ "PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
@@ -370,7 +389,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
.clientRowLimit(4)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")")
.joinScannerLimit(4L).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -381,9 +401,10 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).iteratorType("SERIAL").scanType("FULL SCAN").table(supplier)
.serverOffset(2).serverRowLimit(3L).clientRowLimit(1).joinScannerLimit(3L).subPlanCount(2)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]").clientSortAlgo("CLIENT MERGE SORT")
- .end().subPlan(1).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)")
+ .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .clientSortAlgo("CLIENT MERGE SORT").end().subPlan(1)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
@@ -397,9 +418,11 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
.serverOffset(2).clientRowLimit(1)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")")
.joinScannerLimit(3L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + item + ")").keyRanges(" [1]").clientSortAlgo("CLIENT MERGE SORT")
- .end().subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + item + ")").keyRanges(" [1]")
+ .clientSortAlgo("CLIENT MERGE SORT").end().subPlan(1)
+ .abstractExplainPlan(
+ "PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
@@ -442,7 +465,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
.serverMergeColumns("[0.PHONE]").serverFirstKeyOnlyProjection(true)
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"S.:supplier_id\" IN (\"I.0:supplier_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("RANGE SCAN").table(itemIndex + "(" + itemTable + ")")
.keyRanges(" [1,*] - [1,'T6']").clientSortAlgo("CLIENT MERGE SORT").end();
@@ -460,7 +484,8 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"S.PHONE\"]")
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"S.:supplier_id\" IN (\"I.0:supplier_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("RANGE SCAN").table(itemIndex + "(" + itemTable + ")")
.keyRanges(" [1,*] - [1,'T6']").clientSortAlgo("CLIENT MERGE SORT").end();
@@ -476,9 +501,9 @@ public class HashJoinLocalIndexIT extends HashJoinIT {
.table(supplierIndex + "(" + supplierTable + ")").keyRanges(" [1,*] - [1,'S3']")
.serverMergeColumns("[0.PHONE]").serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO SINGLE ROW").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("RANGE SCAN")
- .table(itemIndex + "(" + itemTable + ")").keyRanges(" [1,*] - [1,'T6']")
- .clientSortAlgo("CLIENT MERGE SORT").end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table(itemIndex + "(" + itemTable + ")")
+ .keyRanges(" [1,*] - [1,'T6']").clientSortAlgo("CLIENT MERGE SORT").end();
} finally {
conn.close();
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinMoreIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinMoreIT.java
index 4e338f31d5..d5bb67cd48 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinMoreIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinMoreIT.java
@@ -294,8 +294,9 @@ public class HashJoinMoreIT extends ParallelStatsDisabledIT {
assertPlan(conn, query).scanType("FULL SCAN").table(tempTableWithCompositePK)
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN")
- .table(tempTableWithCompositePK).clientSortAlgo("CLIENT MERGE SORT").end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(tempTableWithCompositePK).clientSortAlgo("CLIENT MERGE SORT")
+ .end();
// Two parts of PK but only one leading part
query =
@@ -319,8 +320,9 @@ public class HashJoinMoreIT extends ParallelStatsDisabledIT {
assertPlan(conn, query).scanType("FULL SCAN").table(tempTableWithCompositePK)
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY LHS.COL0 IN (RHS.COL2)").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN")
- .table(tempTableWithCompositePK).clientSortAlgo("CLIENT MERGE SORT").end();
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(tempTableWithCompositePK).clientSortAlgo("CLIENT MERGE SORT")
+ .end();
// Two leading parts of PK
query =
@@ -354,7 +356,8 @@ public class HashJoinMoreIT extends ParallelStatsDisabledIT {
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter(
"DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1) IN ((RHS.COL1, RHS.COL2))")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(tempTableWithCompositePK).clientSortAlgo("CLIENT MERGE SORT")
.end();
@@ -390,7 +393,8 @@ public class HashJoinMoreIT extends ParallelStatsDisabledIT {
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter(
"DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1, LHS.COL2) IN ((RHS.COL1, RHS.COL2, TO_INTEGER((RHS.COL3 - 1))))")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(tempTableWithCompositePK).clientSortAlgo("CLIENT MERGE SORT")
.end();
} finally {
@@ -785,7 +789,7 @@ public class HashJoinMoreIT extends ParallelStatsDisabledIT {
.serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"E.TIMESTAMP\", E.BUCKET]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
.scanType("SKIP SCAN ON 2 RANGES").table(t[i]).keyRanges(innerKeyRanges)
.serverFirstKeyOnlyProjection(true)
.serverWhereFilter("SERVER FILTER BY SRC_LOCATION = DST_LOCATION")
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinNoIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinNoIndexIT.java
index 87da5547da..337adbf7f5 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinNoIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinNoIndexIT.java
@@ -62,7 +62,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(item).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).end();
}
@Override
@@ -72,8 +73,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").clientSortedBy("[SUM(O.QUANTITY) DESC]").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN")
- .table(item).serverFirstKeyOnlyProjection(true).end();
+ .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true).end();
}
@Override
@@ -83,7 +84,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]")
.clientSortedBy("[SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -93,7 +95,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(item)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -103,7 +106,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true)
.serverAggregate("SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]")
.clientSortedBy("[SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -111,8 +115,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String item = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
String supplier = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(supplier)
- .end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(supplier).end();
}
@Override
@@ -121,8 +125,9 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String supplier = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item)
.serverWhereFilter("SERVER FILTER BY (NAME >= 'T1' AND NAME <= 'T5')").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN")
- .table(supplier).serverWhereFilter("SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')").end();
+ .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(supplier)
+ .serverWhereFilter("SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')").end();
}
@Override
@@ -131,7 +136,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String supplier = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item)
.serverWhereFilter("SERVER FILTER BY (NAME = 'T1' OR NAME = 'T5')").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(supplier)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(supplier)
.serverWhereFilter("SERVER FILTER BY (NAME = 'S1' OR NAME = 'S5')").end();
}
@@ -142,10 +148,11 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String supplier = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")")
- .subPlanCount(2).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)")
+ .subPlanCount(2).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
.scanType("FULL SCAN").table(order).serverWhereFilter("SERVER FILTER BY QUANTITY < 5000")
- .end().subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1").scanType("FULL SCAN")
- .table(supplier).end();
+ .end().subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(supplier).end();
}
@Override
@@ -153,7 +160,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String item = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.item_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true).end();
}
@@ -163,7 +171,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverSortedBy("[I1.NAME, I2.NAME]")
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.supplier_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(item).end();
}
@@ -175,17 +184,19 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String item = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
if (!noStarJoin) {
assertPlan(conn, query).scanType("FULL SCAN").table(order).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(customer)
- .end().subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1").scanType("FULL SCAN")
- .table(item).end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(customer).end().subPlan(1)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).end();
} else {
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverSortedBy("[\"O.order_id\"]")
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
.scanType("FULL SCAN").table(order).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(customer)
- .end().end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(customer).end().end();
}
}
@@ -199,13 +210,15 @@ public class HashJoinNoIndexIT extends HashJoinIT {
.keyRanges(" [*] - ['0000000005']").serverSortedBy("[\"C.customer_id\", I.NAME]")
.clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(order)
.serverWhereFilter("SERVER FILTER BY \"order_id\" != '000000000000003'").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN")
- .table(item).serverWhereFilter("SERVER FILTER BY NAME != 'T3'").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(supplier).end()
- .end().end();
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).serverWhereFilter("SERVER FILTER BY NAME != 'T3'")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(supplier).end().end().end();
}
@Override
@@ -215,7 +228,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(item).end();
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).end();
}
@Override
@@ -225,7 +239,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]")
.clientSortAlgo("CLIENT MERGE SORT").clientSortedBy("[SUM(O.QUANTITY) DESC]").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)")
+ .subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT, SKIP MERGE */")
.scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true).end();
}
@@ -235,7 +250,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String item = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true)
.serverSortedBy("[O.Q DESC NULLS LAST, I.IID]").clientSortAlgo("CLIENT MERGE SORT")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("FULL SCAN").table(order)
.serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").end();
@@ -247,8 +263,9 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String item = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
assertPlan(conn, query).scanType("FULL SCAN").table(item).serverFirstKeyOnlyProjection(true)
.serverSortedBy("[O.Q DESC, I.IID]").clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN")
- .table(order).serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]")
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(order)
+ .serverAggregate("SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]")
.clientSortAlgo("CLIENT MERGE SORT").end();
}
@@ -261,12 +278,14 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("RANGE SCAN").table(customer)
.keyRanges(" [*] - ['0000000005']").serverSortedBy("[C.CID, QO.INAME]")
.clientSortAlgo("CLIENT MERGE SORT").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(order)
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order)
.serverWhereFilter("SERVER FILTER BY \"order_id\" != '000000000000003'").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN")
- .table(item).serverWhereFilter("SERVER FILTER BY NAME != 'T3'").subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(supplier).end()
- .end().end();
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).serverWhereFilter("SERVER FILTER BY NAME != 'T3'")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD LEFT */")
+ .scanType("FULL SCAN").table(supplier).end().end().end();
}
@Override
@@ -276,8 +295,9 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).iteratorType("SERIAL").scanType("FULL SCAN").table(supplier)
.serverRowLimit(4L).clientRowLimit(4).joinScannerLimit(4L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN").table(item).end()
- .subPlan(1).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).end().subPlan(1)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
@@ -289,8 +309,10 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(conn, query).scanType("FULL SCAN").table(supplier).clientRowLimit(4)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.supplier_id\")")
.joinScannerLimit(4L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(item).end()
- .subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).end().subPlan(1)
+ .abstractExplainPlan(
+ "PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
@@ -306,7 +328,8 @@ public class HashJoinNoIndexIT extends HashJoinIT {
assertPlan(attributes).scanType("FULL SCAN").table(item).clientRowLimit(4)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")")
.joinScannerLimit(4L).subPlanCount(1).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(order).end();
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -316,10 +339,10 @@ public class HashJoinNoIndexIT extends HashJoinIT {
String order = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
assertPlan(conn, query).iteratorType("SERIAL").scanType("FULL SCAN").table(supplier)
.serverOffset(2).serverRowLimit(3L).clientRowLimit(1).joinScannerLimit(3L).subPlanCount(2)
- .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0").scanType("FULL SCAN")
- .table(item).end().subPlan(1)
- .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)").scanType("FULL SCAN")
- .table(order).end();
+ .subPlan(0).abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).end().subPlan(1)
+ .abstractExplainPlan("PARALLEL LEFT-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
+ .scanType("FULL SCAN").table(order).end();
}
@Override
@@ -331,8 +354,10 @@ public class HashJoinNoIndexIT extends HashJoinIT {
.serverOffset(2).clientRowLimit(1)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.supplier_id\")")
.joinScannerLimit(3L).subPlanCount(2).subPlan(0)
- .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("FULL SCAN").table(item).end()
- .subPlan(1).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)")
+ .abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("FULL SCAN").table(item).end().subPlan(1)
+ .abstractExplainPlan(
+ "PARALLEL INNER-JOIN TABLE 1 /* HASH BUILD RIGHT, DELAYED EVALUATION */")
.scanType("FULL SCAN").table(order).end();
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeAssertions.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeAssertions.java
new file mode 100644
index 0000000000..e2a43291be
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeAssertions.java
@@ -0,0 +1,212 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import static org.junit.Assert.fail;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import org.apache.phoenix.query.explain.ExplainPlanTestUtil;
+import org.apache.phoenix.query.explain.ExplainPlanTestUtil.ExplainPlanAssert;
+
+/**
+ * Result and EXPLAIN assertion helpers for the TPC-DS derived ITs.
+ * <p>
+ * {@link #assertResult(Connection, String, String, String[][])} compares the full, ordered result
+ * set of {@code sql} against an embedded {@code expected} array captured from the deterministic
+ * {@link TPCDSLikeFixture}. Each adapted query carries a total {@code ORDER BY} so the row order is
+ * stable. Cell values are compared via {@link ResultSet#getString(int)}.
+ * <p>
+ * <b>Regenerating expected arrays.</b> Run either IT with
+ * {@code -D}{@value #REGENERATE_PROPERTY}{@code =true}. In that mode {@code assertResult} prints a
+ * paste-ready literal to stdout for each query. Paste the printed blocks back into the IT and
+ * commit.
+ */
+public final class TPCDSLikeAssertions {
+
+ public static final String REGENERATE_PROPERTY = "phoenix.tpcds.regenerate";
+
+ public static final String REGENERATE_ENV = "PHOENIX_TPCDS_REGENERATE";
+
+ private static final boolean REGENERATE = Boolean.getBoolean(REGENERATE_PROPERTY)
+ || "true".equalsIgnoreCase(System.getenv(REGENERATE_ENV));
+
+ private TPCDSLikeAssertions() {
+ }
+
+ public static boolean isRegenerating() {
+ return REGENERATE;
+ }
+
+ /**
+ * Execute {@code sql} and either assert its rows equal {@code expected} (normal mode) or print a
+ * paste-ready expected-array literal (regenerate mode).
+ * @param conn a Phoenix connection
+ * @param label the query label, e.g. {@code "Q03"}; only used to name the printed literal
+ * @param sql the query to run
+ * @param expected the embedded expected rows (ignored in capture mode)
+ * @return {@code true} if the call captured/printed instead of asserting (regenerate mode, or an
+ * empty {@code expected} that has not yet been populated); {@code false} if it asserted
+ */
+ public static boolean assertResult(Connection conn, String label, String sql, String[][] expected)
+ throws SQLException {
+ List<String[]> actual = run(conn, sql);
+ // Capture mode: either forced via the flag, or bootstrapping an as-yet-unpopulated query.
+ if (REGENERATE || expected == null || expected.length == 0) {
+ System.out.println((expected == null || expected.length == 0
+ ? "// NOTE: " + label + " has no embedded expected rows yet; paste the block below.\n"
+ : "") + format(label, actual));
+ return true;
+ }
+ String mismatch = diff(expected, actual);
+ if (mismatch != null) {
+ fail("Result mismatch for " + label + ": " + mismatch + "\nRe-run with -D"
+ + REGENERATE_PROPERTY + "=true (or env " + REGENERATE_ENV
+ + "=true) to capture the current output.\nFull query:\n" + sql);
+ }
+ return false;
+ }
+
+ public static String captureLiteral(Connection conn, String label, String sql)
+ throws SQLException {
+ return format(label, run(conn, sql));
+ }
+
+ /** Begin EXPLAIN-plan assertions for {@code sql} (delegates to {@link ExplainPlanTestUtil}). */
+ public static ExplainPlanAssert assertPlan(Connection conn, String sql) throws SQLException {
+ return ExplainPlanTestUtil.assertPlan(conn, sql);
+ }
+
+ /** Assert the rendered EXPLAIN plan text for {@code sql} contains every {@code needle}. */
+ public static void assertPlanContains(Connection conn, String sql, String... needles)
+ throws SQLException {
+ List<String> steps = ExplainPlanTestUtil.getPlanSteps(conn, sql);
+ String text = String.join("\n", steps);
+ for (String needle : needles) {
+ if (!text.contains(needle)) {
+ fail("EXPLAIN plan missing expected fragment:\n expected to contain: " + needle
+ + "\nactual plan:\n" + text + "\nquery:\n" + sql);
+ }
+ }
+ }
+
+ /**
+ * Assert the rendered EXPLAIN plan text for {@code sql} contains at least one of {@code needles}.
+ */
+ public static void assertPlanContainsAny(Connection conn, String sql, String... needles)
+ throws SQLException {
+ List<String> steps = ExplainPlanTestUtil.getPlanSteps(conn, sql);
+ String text = String.join("\n", steps);
+ for (String needle : needles) {
+ if (text.contains(needle)) {
+ return;
+ }
+ }
+ fail("EXPLAIN plan missing all expected fragments " + Arrays.toString(needles) + ":\n" + text
+ + "\nquery:\n" + sql);
+ }
+
+ private static List<String[]> run(Connection conn, String sql) throws SQLException {
+ List<String[]> rows = new ArrayList<>();
+ try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
+ ResultSetMetaData md = rs.getMetaData();
+ int n = md.getColumnCount();
+ while (rs.next()) {
+ String[] row = new String[n];
+ for (int i = 1; i <= n; i++) {
+ row[i - 1] = canonical(rs.getObject(i));
+ }
+ rows.add(row);
+ }
+ }
+ return rows;
+ }
+
+ /**
+ * Canonicalize a JDBC value into a stable locale independent string. Numbers are normalized via
+ * {@link BigDecimal} with trailing zeros stripped (so {@code 37.00 -> "37"}, {@code 1081.50 ->
+ * "1081.5"}, {@code 0 -> "0"}). Everything else uses {@code toString()}.
+ */
+ private static String canonical(Object value) {
+ if (value == null) {
+ return null;
+ }
+ if (value instanceof Number) {
+ BigDecimal b = new BigDecimal(value.toString());
+ return b.signum() == 0 ? "0" : b.stripTrailingZeros().toPlainString();
+ }
+ return value.toString();
+ }
+
+ private static String diff(String[][] expected, List<String[]> actual) {
+ if (expected == null) {
+ expected = new String[0][];
+ }
+ if (expected.length != actual.size()) {
+ return "row count expected=" + expected.length + " actual=" + actual.size();
+ }
+ for (int r = 0; r < expected.length; r++) {
+ String[] e = expected[r];
+ String[] a = actual.get(r);
+ if (e.length != a.length) {
+ return "row " + r + " column count expected=" + e.length + " actual=" + a.length;
+ }
+ for (int c = 0; c < e.length; c++) {
+ if (!eq(e[c], a[c])) {
+ return "row " + r + " col " + c + " expected=" + lit(e[c]) + " actual=" + lit(a[c])
+ + "\n expectedRow=" + Arrays.toString(e) + "\n actualRow= " + Arrays.toString(a);
+ }
+ }
+ }
+ return null;
+ }
+
+ private static boolean eq(String a, String b) {
+ return a == null ? b == null : a.equals(b);
+ }
+
+ private static String lit(String s) {
+ return s == null ? "null" : "\"" + s + "\"";
+ }
+
+ private static String format(String label, List<String[]> rows) {
+ StringBuilder sb = new StringBuilder();
+ sb.append("\n // ---- ").append(label).append(" (").append(rows.size())
+ .append(" rows) ----\n");
+ sb.append(" private static final String[][] ").append(label).append("_EXPECTED = {\n");
+ for (String[] row : rows) {
+ sb.append(" { ");
+ for (int c = 0; c < row.length; c++) {
+ if (c > 0) {
+ sb.append(", ");
+ }
+ sb.append(lit(row[c]));
+ }
+ sb.append(" },\n");
+ }
+ sb.append(" };\n");
+ return sb.toString();
+ }
+}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeBaseIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeBaseIT.java
new file mode 100644
index 0000000000..cdeb2310e6
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeBaseIT.java
@@ -0,0 +1,103 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Properties;
+import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.util.PropertiesUtil;
+
+/**
+ * Common plumbing for the TPC-DS derived ITs. The suite is parameterized over the two schemas
+ * materialized by {@link TPCDSLikeFixture}.
+ * <p>
+ * Adapted query constants are written against {@link TPCDSLikeFixture#SCHEMA}.
+ */
+public abstract class TPCDSLikeBaseIT extends ParallelStatsDisabledIT {
+
+ protected final String schema;
+ protected final boolean noIndex;
+
+ protected TPCDSLikeBaseIT(String label, String schema, boolean noIndex) {
+ this.schema = schema;
+ this.noIndex = noIndex;
+ }
+
+ protected static Collection<Object[]> indexParameters() {
+ return Arrays.asList(new Object[][] { { "NO_INDEX", TPCDSLikeFixture.SCHEMA, true },
+ { "GLOBAL_INDEX", TPCDSLikeFixture.SCHEMA_INDEXED, false } });
+ }
+
+ protected static void loadFixture() throws SQLException {
+ try (Connection conn = newConnection()) {
+ TPCDSLikeFixture.load(conn);
+ }
+ }
+
+ protected static Connection newConnection() throws SQLException {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ return DriverManager.getConnection(getUrl(), props);
+ }
+
+ /** Rewrite the canonical {@code TPCDS.} table prefix to the parameter's schema. */
+ protected String sql(String base) {
+ if (TPCDSLikeFixture.SCHEMA.equals(schema)) {
+ return base;
+ }
+ return base.replace(TPCDSLikeFixture.SCHEMA + ".", schema + ".");
+ }
+
+ private static final String[] NO_INDEXES = new String[0];
+
+ /** Assert a query's full result. */
+ protected void check(String label, String baseSql, String[][] expected, String... markers)
+ throws SQLException {
+ check(label, baseSql, expected, markers, NO_INDEXES);
+ }
+
+ /**
+ * As {@link #check(String, String, String[][], String...)}, but additionally asserts that the
+ * plan scans each covering index in {@code indexNames}.
+ */
+ protected void check(String label, String baseSql, String[][] expected, String[] markers,
+ String[] indexNames) throws SQLException {
+ String resolved = sql(baseSql);
+ try (Connection conn = newConnection()) {
+ boolean captured = TPCDSLikeAssertions.assertResult(conn, label, resolved, expected);
+ if (captured) {
+ return;
+ }
+ if (markers.length > 0) {
+ TPCDSLikeAssertions.assertPlanContains(conn, resolved, markers);
+ }
+ if (!noIndex && indexNames.length > 0) {
+ String[] qualified = new String[indexNames.length];
+ for (int i = 0; i < indexNames.length; i++) {
+ qualified[i] = schema + "." + indexNames[i];
+ }
+ TPCDSLikeAssertions.assertPlanContains(conn, resolved, qualified);
+ }
+ }
+ }
+}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeCrossChannelIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeCrossChannelIT.java
new file mode 100644
index 0000000000..7b32b98a91
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeCrossChannelIT.java
@@ -0,0 +1,238 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import java.sql.SQLException;
+import java.util.Collection;
+import java.util.LinkedHashMap;
+import java.util.Map;
+import org.apache.phoenix.end2end.ParallelStatsDisabledTest;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+/**
+ * TPC-DS derived integration tests, exercising unions and sort-merge joins.
+ * <p>
+ * See {@link TPCDSLikeFixture} for the schema and the catalog of queries intentionally not ported,
+ * and {@link TPCDSLikeSingleChannelIT} for the single channel queries.
+ */
+@Category(ParallelStatsDisabledTest.class)
+@RunWith(Parameterized.class)
+public class TPCDSLikeCrossChannelIT extends TPCDSLikeBaseIT {
+
+ public TPCDSLikeCrossChannelIT(String label, String schema, boolean noIndex) {
+ super(label, schema, noIndex);
+ }
+
+ @Parameters(name = "{0}")
+ public static Collection<Object[]> params() {
+ return indexParameters();
+ }
+
+ @BeforeClass
+ public static synchronized void setup() throws SQLException {
+ loadFixture();
+ }
+
+ // TPC-DS Q56: total ext sales price by item across store/catalog/web for a color set and region.
+ static final String Q56 = "SELECT i_item_id, SUM(total_sales) agg_total_sales FROM ("
+ + " SELECT i.i_item_id i_item_id, SUM(ss.ss_ext_sales_price) total_sales"
+ + " FROM TPCDS.store_sales ss, TPCDS.date_dim d, TPCDS.customer_address ca, TPCDS.item i"
+ + " WHERE ss.ss_sold_date_sk = d.d_date_sk AND ss.ss_addr_sk = ca.ca_address_sk"
+ + " AND ss.ss_item_sk = i.i_item_sk AND i.i_color IN ('red', 'blue', 'green')"
+ + " AND d.d_year = 2000 AND ca.ca_gmt_offset = -5 GROUP BY i.i_item_id" + " UNION ALL"
+ + " SELECT i.i_item_id i_item_id, SUM(cs.cs_ext_sales_price) total_sales"
+ + " FROM TPCDS.catalog_sales cs, TPCDS.date_dim d, TPCDS.customer_address ca, TPCDS.item i"
+ + " WHERE cs.cs_sold_date_sk = d.d_date_sk AND cs.cs_ship_addr_sk = ca.ca_address_sk"
+ + " AND cs.cs_item_sk = i.i_item_sk AND i.i_color IN ('red', 'blue', 'green')"
+ + " AND d.d_year = 2000 AND ca.ca_gmt_offset = -5 GROUP BY i.i_item_id" + " UNION ALL"
+ + " SELECT i.i_item_id i_item_id, SUM(ws.ws_ext_sales_price) total_sales"
+ + " FROM TPCDS.web_sales ws, TPCDS.date_dim d, TPCDS.customer_address ca, TPCDS.item i"
+ + " WHERE ws.ws_sold_date_sk = d.d_date_sk AND ws.ws_ship_addr_sk = ca.ca_address_sk"
+ + " AND ws.ws_item_sk = i.i_item_sk AND i.i_color IN ('red', 'blue', 'green')"
+ + " AND d.d_year = 2000 AND ca.ca_gmt_offset = -5 GROUP BY i.i_item_id"
+ + " ) tmp GROUP BY i_item_id ORDER BY agg_total_sales, i_item_id LIMIT 100";
+
+ // TPC-DS Q60: cross-channel total ext sales price by item for a category set and region.
+ static final String Q60 = "SELECT i_item_id, SUM(total_sales) agg_total_sales FROM ("
+ + " SELECT i.i_item_id i_item_id, SUM(ss.ss_ext_sales_price) total_sales"
+ + " FROM TPCDS.store_sales ss, TPCDS.date_dim d, TPCDS.customer_address ca, TPCDS.item i"
+ + " WHERE ss.ss_sold_date_sk = d.d_date_sk AND ss.ss_addr_sk = ca.ca_address_sk"
+ + " AND ss.ss_item_sk = i.i_item_sk AND i.i_category IN ('Books', 'Music', 'Home')"
+ + " AND d.d_year = 2001 AND ca.ca_gmt_offset = -6 GROUP BY i.i_item_id" + " UNION ALL"
+ + " SELECT i.i_item_id i_item_id, SUM(cs.cs_ext_sales_price) total_sales"
+ + " FROM TPCDS.catalog_sales cs, TPCDS.date_dim d, TPCDS.customer_address ca, TPCDS.item i"
+ + " WHERE cs.cs_sold_date_sk = d.d_date_sk AND cs.cs_ship_addr_sk = ca.ca_address_sk"
+ + " AND cs.cs_item_sk = i.i_item_sk AND i.i_category IN ('Books', 'Music', 'Home')"
+ + " AND d.d_year = 2001 AND ca.ca_gmt_offset = -6 GROUP BY i.i_item_id" + " UNION ALL"
+ + " SELECT i.i_item_id i_item_id, SUM(ws.ws_ext_sales_price) total_sales"
+ + " FROM TPCDS.web_sales ws, TPCDS.date_dim d, TPCDS.customer_address ca, TPCDS.item i"
+ + " WHERE ws.ws_sold_date_sk = d.d_date_sk AND ws.ws_ship_addr_sk = ca.ca_address_sk"
+ + " AND ws.ws_item_sk = i.i_item_sk AND i.i_category IN ('Books', 'Music', 'Home')"
+ + " AND d.d_year = 2001 AND ca.ca_gmt_offset = -6 GROUP BY i.i_item_id"
+ + " ) tmp GROUP BY i_item_id ORDER BY agg_total_sales, i_item_id LIMIT 100";
+
+ // TPC-DS Q83: cross-channel return quantities by item.
+ static final String Q83 = "SELECT sr.item_id, sr.sr_item_qty, cr.cr_item_qty, wr.wr_item_qty"
+ + " FROM (SELECT i.i_item_id item_id, SUM(srt.sr_return_quantity) sr_item_qty"
+ + " FROM TPCDS.store_returns srt, TPCDS.item i, TPCDS.date_dim d"
+ + " WHERE srt.sr_item_sk = i.i_item_sk AND srt.sr_returned_date_sk = d.d_date_sk"
+ + " AND d.d_year IN (2000, 2001) GROUP BY i.i_item_id) sr"
+ + " JOIN (SELECT i.i_item_id item_id, SUM(crt.cr_return_quantity) cr_item_qty"
+ + " FROM TPCDS.catalog_returns crt, TPCDS.item i, TPCDS.date_dim d"
+ + " WHERE crt.cr_item_sk = i.i_item_sk AND crt.cr_returned_date_sk = d.d_date_sk"
+ + " AND d.d_year IN (2000, 2001) GROUP BY i.i_item_id) cr ON sr.item_id = cr.item_id"
+ + " JOIN (SELECT i.i_item_id item_id, SUM(wrt.wr_return_quantity) wr_item_qty"
+ + " FROM TPCDS.web_returns wrt, TPCDS.item i, TPCDS.date_dim d"
+ + " WHERE wrt.wr_item_sk = i.i_item_sk AND wrt.wr_returned_date_sk = d.d_date_sk"
+ + " AND d.d_year IN (2000, 2001) GROUP BY i.i_item_id) wr ON sr.item_id = wr.item_id"
+ + " ORDER BY sr.item_id LIMIT 100";
+
+ // TPC-DS Q97: store-only / catalog-only / both customer-item pairs via a FULL OUTER JOIN.
+ // The USE_SORT_MERGE_JOIN hint forces the sort-merge strategy Phoenix requires for FULL OUTER.
+ static final String Q97 = "SELECT /*+ USE_SORT_MERGE_JOIN */"
+ + " SUM(CASE WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NULL"
+ + " THEN 1 ELSE 0 END) store_only,"
+ + " SUM(CASE WHEN ssci.customer_sk IS NULL AND csci.customer_sk IS NOT NULL"
+ + " THEN 1 ELSE 0 END) catalog_only,"
+ + " SUM(CASE WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NOT NULL"
+ + " THEN 1 ELSE 0 END) store_and_catalog"
+ + " FROM (SELECT ss.ss_customer_sk customer_sk, ss.ss_item_sk item_sk"
+ + " FROM TPCDS.store_sales ss, TPCDS.date_dim d"
+ + " WHERE ss.ss_sold_date_sk = d.d_date_sk AND d.d_year = 2000"
+ + " GROUP BY ss.ss_customer_sk, ss.ss_item_sk) ssci"
+ + " FULL OUTER JOIN (SELECT cs.cs_bill_customer_sk customer_sk, cs.cs_item_sk item_sk"
+ + " FROM TPCDS.catalog_sales cs, TPCDS.date_dim d"
+ + " WHERE cs.cs_sold_date_sk = d.d_date_sk AND d.d_year = 2000"
+ + " GROUP BY cs.cs_bill_customer_sk, cs.cs_item_sk) csci"
+ + " ON ssci.customer_sk = csci.customer_sk AND ssci.item_sk = csci.item_sk LIMIT 100";
+
+ // TPC-DS Q88: counts of store sales in several time-of-day buckets, one per derived table,
+ // combined by cross join.
+ static final String Q88 = "SELECT * FROM"
+ + " (SELECT COUNT(*) h8 FROM TPCDS.store_sales ss, TPCDS.household_demographics hd,"
+ + " TPCDS.time_dim t, TPCDS.store s"
+ + " WHERE ss.ss_sold_time_sk = t.t_time_sk AND ss.ss_hdemo_sk = hd.hd_demo_sk"
+ + " AND ss.ss_store_sk = s.s_store_sk AND t.t_hour = 8 AND hd.hd_dep_count >= 0"
+ + " AND s.s_company_id = 1) s1,"
+ + " (SELECT COUNT(*) h10 FROM TPCDS.store_sales ss, TPCDS.household_demographics hd,"
+ + " TPCDS.time_dim t, TPCDS.store s"
+ + " WHERE ss.ss_sold_time_sk = t.t_time_sk AND ss.ss_hdemo_sk = hd.hd_demo_sk"
+ + " AND ss.ss_store_sk = s.s_store_sk AND t.t_hour = 10 AND hd.hd_dep_count >= 0"
+ + " AND s.s_company_id = 1) s2,"
+ + " (SELECT COUNT(*) h12 FROM TPCDS.store_sales ss, TPCDS.household_demographics hd,"
+ + " TPCDS.time_dim t, TPCDS.store s"
+ + " WHERE ss.ss_sold_time_sk = t.t_time_sk AND ss.ss_hdemo_sk = hd.hd_demo_sk"
+ + " AND ss.ss_store_sk = s.s_store_sk AND t.t_hour = 12 AND hd.hd_dep_count >= 0"
+ + " AND s.s_company_id = 1) s3";
+
+ // TPC-DS Q1: customers whose store-return total exceeds 1.2x their store's average.
+ static final String Q01 = "SELECT c.c_customer_id"
+ + " FROM (SELECT sr.sr_customer_sk ctr_customer_sk, sr.sr_store_sk ctr_store_sk,"
+ + " SUM(sr.sr_return_amt) ctr_total_return" + " FROM TPCDS.store_returns sr, TPCDS.date_dim d"
+ + " WHERE sr.sr_returned_date_sk = d.d_date_sk AND d.d_year = 2000"
+ + " GROUP BY sr.sr_customer_sk, sr.sr_store_sk) ctr1,"
+ + " (SELECT x.ctr_store_sk ctr_store_sk, AVG(x.ctr_total_return) * 1.2 avg_return FROM"
+ + " (SELECT sr.sr_customer_sk ctr_customer_sk, sr.sr_store_sk ctr_store_sk,"
+ + " SUM(sr.sr_return_amt) ctr_total_return" + " FROM TPCDS.store_returns sr, TPCDS.date_dim d"
+ + " WHERE sr.sr_returned_date_sk = d.d_date_sk AND d.d_year = 2000"
+ + " GROUP BY sr.sr_customer_sk, sr.sr_store_sk) x GROUP BY x.ctr_store_sk) ctr2,"
+ + " TPCDS.store s, TPCDS.customer c"
+ + " WHERE ctr1.ctr_total_return > ctr2.avg_return AND s.s_store_sk = ctr1.ctr_store_sk"
+ + " AND ctr2.ctr_store_sk = ctr1.ctr_store_sk AND ctr1.ctr_customer_sk = c.c_customer_sk"
+ + " ORDER BY c.c_customer_id LIMIT 100";
+
+ private static final String[][] Q56_EXPECTED =
+ { { "ITEM0000000024", "47" }, { "ITEM0000000012", "237.5" }, { "ITEM0000000020", "758" },
+ { "ITEM0000000002", "962" }, { "ITEM0000000018", "1081.5" }, { "ITEM0000000001", "1442.5" },
+ { "ITEM0000000007", "1818" }, { "ITEM0000000008", "1957.5" }, { "ITEM0000000014", "2092" },
+ { "ITEM0000000006", "2725" }, { "ITEM0000000013", "2854.5" }, { "ITEM0000000019", "5817" }, };
+ private static final String[][] Q60_EXPECTED = { { "ITEM0000000001", "849" },
+ { "ITEM0000000007", "897" }, { "ITEM0000000018", "1293.5" }, { "ITEM0000000013", "1650" },
+ { "ITEM0000000019", "1784.5" }, { "ITEM0000000006", "2093.5" }, { "ITEM0000000024", "2345" },
+ { "ITEM0000000020", "3031.5" }, { "ITEM0000000012", "3220" }, { "ITEM0000000002", "3226.5" },
+ { "ITEM0000000014", "3287.5" }, { "ITEM0000000008", "3913.5" }, };
+ private static final String[][] Q83_EXPECTED =
+ { { "ITEM0000000001", "17", "13", "11" }, { "ITEM0000000002", "20", "17", "10" },
+ { "ITEM0000000003", "16", "12", "14" }, { "ITEM0000000004", "17", "12", "12" },
+ { "ITEM0000000005", "13", "12", "13" }, { "ITEM0000000006", "18", "9", "7" },
+ { "ITEM0000000007", "16", "15", "13" }, { "ITEM0000000008", "17", "9", "12" },
+ { "ITEM0000000009", "23", "10", "12" }, { "ITEM0000000010", "16", "15", "17" },
+ { "ITEM0000000011", "17", "13", "9" }, { "ITEM0000000012", "15", "14", "10" },
+ { "ITEM0000000013", "12", "11", "5" }, { "ITEM0000000014", "18", "15", "10" },
+ { "ITEM0000000015", "21", "16", "14" }, { "ITEM0000000016", "15", "15", "15" },
+ { "ITEM0000000017", "19", "11", "12" }, { "ITEM0000000018", "14", "10", "11" },
+ { "ITEM0000000019", "17", "11", "10" }, { "ITEM0000000020", "21", "8", "11" },
+ { "ITEM0000000021", "19", "7", "11" }, { "ITEM0000000022", "19", "9", "11" },
+ { "ITEM0000000023", "19", "13", "15" }, { "ITEM0000000024", "20", "15", "13" }, };
+ private static final String[][] Q97_EXPECTED = { { "130", "82", "14" }, };
+ private static final String[][] Q88_EXPECTED = { { "24", "24", "24" }, };
+ private static final String[][] Q01_EXPECTED = { { "CUST000000000003" }, { "CUST000000000005" },
+ { "CUST000000000006" }, { "CUST000000000009" }, { "CUST000000000014" }, { "CUST000000000014" },
+ { "CUST000000000014" }, { "CUST000000000015" }, { "CUST000000000016" }, { "CUST000000000019" },
+ { "CUST000000000020" }, { "CUST000000000021" }, { "CUST000000000023" }, { "CUST000000000023" },
+ { "CUST000000000024" }, { "CUST000000000024" }, { "CUST000000000025" }, { "CUST000000000025" },
+ { "CUST000000000026" }, { "CUST000000000027" }, { "CUST000000000029" }, };
+
+ /** For {@link TPCDSLikeExpectedRegenerator}. */
+ public static Map<String, String> queries() {
+ Map<String, String> q = new LinkedHashMap<>();
+ q.put("Q56", Q56);
+ q.put("Q60", Q60);
+ q.put("Q83", Q83);
+ q.put("Q97", Q97);
+ q.put("Q88", Q88);
+ q.put("Q01", Q01);
+ return q;
+ }
+
+ @Test
+ public void testQ56() throws SQLException {
+ check("Q56", Q56, Q56_EXPECTED, "UNION ALL OVER 3 QUERIES");
+ }
+
+ @Test
+ public void testQ60() throws SQLException {
+ check("Q60", Q60, Q60_EXPECTED, "UNION ALL OVER 3 QUERIES");
+ }
+
+ @Test
+ public void testQ83() throws SQLException {
+ check("Q83", Q83, Q83_EXPECTED, "HASH BUILD");
+ }
+
+ @Test
+ public void testQ97() throws SQLException {
+ check("Q97", Q97, Q97_EXPECTED, new String[] { "SORT-MERGE-JOIN (FULL)" },
+ new String[] { "SS_I", "CS_I" });
+ }
+
+ @Test
+ public void testQ88() throws SQLException {
+ check("Q88", Q88, Q88_EXPECTED, "HASH BUILD");
+ }
+
+ @Test
+ public void testQ01() throws SQLException {
+ check("Q01", Q01, Q01_EXPECTED, "HASH BUILD");
+ }
+}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeFixture.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeFixture.java
new file mode 100644
index 0000000000..c50cbc555a
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeFixture.java
@@ -0,0 +1,922 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Random;
+
+/**
+ * Shared deterministic TPC-DS derived fixture for {@link TPCDSLikeSingleChannelIT} and
+ * {@link TPCDSLikeCrossChannelIT}.
+ * <p>
+ * The schema is a trimmed subset of the TPC-DS v4.0.0 schema, only the tables and columns touched
+ * by the adapted queries, with the following uniform Phoenix adaptation rules applied:
+ * <ul>
+ * <li>Surrogate keys ({@code *_sk}) become single-column {@code BIGINT NOT NULL PRIMARY KEY}
+ * columns on the dimension tables.</li>
+ * <li>Sales and returns fact tables use a composite primary key on
+ * {@code (*_item_sk, *_ticket_number)} or {@code *_order_number}. {@code inventory} uses
+ * {@code (inv_date_sk, inv_item_sk, inv_warehouse_sk)}.</li>
+ * <li>Fact tables are salted ({@code SALT_BUCKETS=4}).</li>
+ * <li>Foreign key columns are plain nullable columns. Referential consistency is guaranteed by this
+ * loader, not by the schema.</li>
+ * </ul>
+ * <p>
+ * The data is generated with a fixed {@link Random} seed so that every adapted query has a stable
+ * reference answer.
+ * <p>
+ * The fixture is materialized into two schemas that hold identical data:
+ * <ul>
+ * <li>{@link #SCHEMA} ({@value #SCHEMA}) -- base tables, no secondary indexes.</li>
+ * <li>{@link #SCHEMA_INDEXED} ({@value #SCHEMA_INDEXED}) -- the same, plus a covering global index
+ * per fact table.</li>
+ * </ul>
+ */
+public final class TPCDSLikeFixture {
+
+ /** Base schema with no secondary indexes */
+ public static final String SCHEMA = "TPCDS";
+ /** Schema holding identical data plus a covering global index per fact table. */
+ public static final String SCHEMA_INDEXED = "TPCDSI";
+
+ private static final long SEED = 42L;
+
+ private static volatile boolean loaded = false;
+
+ private TPCDSLikeFixture() {
+ }
+
+ /** All base (dimension + fact) table short names, in dependency order, dimensions first. */
+ static final String[] TABLES =
+ { "DATE_DIM", "TIME_DIM", "ITEM", "CUSTOMER_ADDRESS", "CUSTOMER_DEMOGRAPHICS", "INCOME_BAND",
+ "HOUSEHOLD_DEMOGRAPHICS", "CUSTOMER", "STORE", "CALL_CENTER", "CATALOG_PAGE", "WEB_SITE",
+ "WEB_PAGE", "WAREHOUSE", "SHIP_MODE", "PROMOTION", "REASON", "STORE_SALES", "STORE_RETURNS",
+ "CATALOG_SALES", "CATALOG_RETURNS", "WEB_SALES", "WEB_RETURNS", "INVENTORY" };
+
+ /** Create both schemas and populate them with deterministic data. */
+ public static synchronized void load(Connection conn) throws SQLException {
+ if (loaded && tablesPopulated(conn)) {
+ return;
+ }
+ conn.setAutoCommit(false);
+ createTables(conn, SCHEMA);
+ createTables(conn, SCHEMA_INDEXED);
+ if (!tablesPopulated(conn)) {
+ loadData(conn, SCHEMA);
+ copyData(conn, SCHEMA, SCHEMA_INDEXED);
+ }
+ createIndexes(conn, SCHEMA_INDEXED);
+ conn.commit();
+ loaded = true;
+ }
+
+ /**
+ * Both schemas must carry data. A partial prior run or manual cleanup can leave the base schema
+ * populated while {@link #SCHEMA_INDEXED} is empty.
+ */
+ private static boolean tablesPopulated(Connection conn) {
+ return rowsPresent(conn, SCHEMA) && rowsPresent(conn, SCHEMA_INDEXED);
+ }
+
+ private static boolean rowsPresent(Connection conn, String s) {
+ try (Statement st = conn.createStatement();
+ ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM " + s + ".STORE_SALES")) {
+ return rs.next() && rs.getInt(1) > 0;
+ } catch (SQLException e) {
+ return false;
+ }
+ }
+
+ private static void createTables(Connection conn, String s) throws SQLException {
+ try (Statement st = conn.createStatement()) {
+ for (String ddl : ddl(s)) {
+ st.execute(ddl);
+ }
+ }
+ }
+
+ private static void createIndexes(Connection conn, String s) throws SQLException {
+ try (Statement st = conn.createStatement()) {
+ for (String ddl : indexDdl(s)) {
+ st.execute(ddl);
+ }
+ }
+ }
+
+ private static String[] ddl(String s) {
+ return new String[] {
+ // Dimensions
+ "CREATE TABLE IF NOT EXISTS " + s + ".DATE_DIM (" + "d_date_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "d_date DATE, d_year INTEGER, d_moy INTEGER, d_dom INTEGER, d_qoy INTEGER,"
+ + "d_dow INTEGER, d_week_seq INTEGER, d_day_name VARCHAR(16), d_quarter_name VARCHAR(8))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".TIME_DIM (" + "t_time_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "t_hour INTEGER, t_minute INTEGER, t_am_pm VARCHAR(2), t_shift VARCHAR(16),"
+ + "t_meal_time VARCHAR(16))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".ITEM (" + "i_item_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "i_item_id VARCHAR(16), i_item_desc VARCHAR(100), i_current_price DECIMAL(7,2),"
+ + "i_brand_id INTEGER, i_brand VARCHAR(50), i_class_id INTEGER, i_class VARCHAR(50),"
+ + "i_category_id INTEGER, i_category VARCHAR(50), i_manufact_id INTEGER,"
+ + "i_manufact VARCHAR(50), i_manager_id INTEGER, i_size VARCHAR(20), i_color VARCHAR(20),"
+ + "i_units VARCHAR(10), i_product_name VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CUSTOMER_ADDRESS ("
+ + "ca_address_sk BIGINT NOT NULL PRIMARY KEY, ca_city VARCHAR(60), ca_county VARCHAR(30),"
+ + "ca_state VARCHAR(2), ca_zip VARCHAR(10), ca_country VARCHAR(20),"
+ + "ca_gmt_offset DECIMAL(5,2), ca_street_number VARCHAR(10), ca_street_name VARCHAR(60))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CUSTOMER_DEMOGRAPHICS ("
+ + "cd_demo_sk BIGINT NOT NULL PRIMARY KEY, cd_gender VARCHAR(1),"
+ + "cd_marital_status VARCHAR(1), cd_education_status VARCHAR(20),"
+ + "cd_purchase_estimate INTEGER, cd_credit_rating VARCHAR(10), cd_dep_count INTEGER,"
+ + "cd_dep_employed_count INTEGER, cd_dep_college_count INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".INCOME_BAND ("
+ + "ib_income_band_sk BIGINT NOT NULL PRIMARY KEY, ib_lower_bound INTEGER,"
+ + "ib_upper_bound INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".HOUSEHOLD_DEMOGRAPHICS ("
+ + "hd_demo_sk BIGINT NOT NULL PRIMARY KEY, hd_income_band_sk BIGINT,"
+ + "hd_buy_potential VARCHAR(15), hd_dep_count INTEGER, hd_vehicle_count INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CUSTOMER ("
+ + "c_customer_sk BIGINT NOT NULL PRIMARY KEY, c_customer_id VARCHAR(16),"
+ + "c_current_cdemo_sk BIGINT, c_current_hdemo_sk BIGINT, c_current_addr_sk BIGINT,"
+ + "c_first_name VARCHAR(20), c_last_name VARCHAR(30), c_birth_country VARCHAR(20),"
+ + "c_birth_year INTEGER, c_preferred_cust_flag VARCHAR(1), c_salutation VARCHAR(10),"
+ + "c_email_address VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".STORE (" + "s_store_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "s_store_id VARCHAR(16), s_store_name VARCHAR(50), s_company_id INTEGER,"
+ + "s_company_name VARCHAR(50), s_state VARCHAR(2), s_zip VARCHAR(10),"
+ + "s_gmt_offset DECIMAL(5,2), s_city VARCHAR(60), s_county VARCHAR(30),"
+ + "s_manager VARCHAR(40), s_number_employees INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CALL_CENTER ("
+ + "cc_call_center_sk BIGINT NOT NULL PRIMARY KEY, cc_call_center_id VARCHAR(16),"
+ + "cc_name VARCHAR(50), cc_manager VARCHAR(40), cc_mkt_id INTEGER, cc_class VARCHAR(50),"
+ + "cc_company INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CATALOG_PAGE ("
+ + "cp_catalog_page_sk BIGINT NOT NULL PRIMARY KEY, cp_catalog_page_id VARCHAR(16),"
+ + "cp_catalog_number INTEGER, cp_catalog_page_number INTEGER, cp_department VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WEB_SITE (" + "web_site_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "web_site_id VARCHAR(16), web_name VARCHAR(50), web_company_id INTEGER,"
+ + "web_company_name VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WEB_PAGE ("
+ + "wp_web_page_sk BIGINT NOT NULL PRIMARY KEY, wp_web_page_id VARCHAR(16),"
+ + "wp_char_count INTEGER, wp_type VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WAREHOUSE ("
+ + "w_warehouse_sk BIGINT NOT NULL PRIMARY KEY, w_warehouse_id VARCHAR(16),"
+ + "w_warehouse_name VARCHAR(50), w_state VARCHAR(2), w_country VARCHAR(20),"
+ + "w_gmt_offset DECIMAL(5,2))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".SHIP_MODE ("
+ + "sm_ship_mode_sk BIGINT NOT NULL PRIMARY KEY, sm_ship_mode_id VARCHAR(16),"
+ + "sm_type VARCHAR(30), sm_carrier VARCHAR(20))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".PROMOTION ("
+ + "p_promo_sk BIGINT NOT NULL PRIMARY KEY, p_promo_id VARCHAR(16),"
+ + "p_channel_email VARCHAR(1), p_channel_event VARCHAR(1), p_channel_tv VARCHAR(1),"
+ + "p_channel_dmail VARCHAR(1), p_channel_catalog VARCHAR(1))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".REASON (" + "r_reason_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "r_reason_id VARCHAR(16), r_reason_desc VARCHAR(100))",
+ // Facts
+ "CREATE TABLE IF NOT EXISTS " + s + ".STORE_SALES (" + "ss_item_sk BIGINT NOT NULL,"
+ + "ss_ticket_number BIGINT NOT NULL, ss_sold_date_sk BIGINT, ss_sold_time_sk BIGINT,"
+ + "ss_customer_sk BIGINT, ss_cdemo_sk BIGINT, ss_hdemo_sk BIGINT, ss_addr_sk BIGINT,"
+ + "ss_store_sk BIGINT, ss_promo_sk BIGINT, ss_quantity INTEGER, ss_sales_price DECIMAL(7,2),"
+ + "ss_ext_sales_price DECIMAL(7,2), ss_ext_discount_amt DECIMAL(7,2),"
+ + "ss_list_price DECIMAL(7,2), ss_ext_list_price DECIMAL(7,2), ss_coupon_amt DECIMAL(7,2),"
+ + "ss_net_profit DECIMAL(7,2), ss_net_paid DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (ss_item_sk, ss_ticket_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".STORE_RETURNS (" + "sr_item_sk BIGINT NOT NULL,"
+ + "sr_ticket_number BIGINT NOT NULL, sr_returned_date_sk BIGINT, sr_customer_sk BIGINT,"
+ + "sr_cdemo_sk BIGINT, sr_hdemo_sk BIGINT, sr_addr_sk BIGINT, sr_store_sk BIGINT,"
+ + "sr_reason_sk BIGINT, sr_return_quantity INTEGER, sr_return_amt DECIMAL(7,2),"
+ + "sr_net_loss DECIMAL(7,2), sr_fee DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (sr_item_sk, sr_ticket_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CATALOG_SALES (" + "cs_item_sk BIGINT NOT NULL,"
+ + "cs_order_number BIGINT NOT NULL, cs_sold_date_sk BIGINT, cs_bill_customer_sk BIGINT,"
+ + "cs_bill_cdemo_sk BIGINT, cs_ship_customer_sk BIGINT, cs_ship_addr_sk BIGINT,"
+ + "cs_call_center_sk BIGINT, cs_catalog_page_sk BIGINT, cs_warehouse_sk BIGINT,"
+ + "cs_ship_mode_sk BIGINT, cs_promo_sk BIGINT, cs_quantity INTEGER,"
+ + "cs_sales_price DECIMAL(7,2), cs_ext_sales_price DECIMAL(7,2),"
+ + "cs_ext_discount_amt DECIMAL(7,2), cs_list_price DECIMAL(7,2),"
+ + "cs_ext_list_price DECIMAL(7,2), cs_coupon_amt DECIMAL(7,2), cs_net_profit DECIMAL(7,2),"
+ + "cs_net_paid DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (cs_item_sk, cs_order_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CATALOG_RETURNS (" + "cr_item_sk BIGINT NOT NULL,"
+ + "cr_order_number BIGINT NOT NULL, cr_returned_date_sk BIGINT,"
+ + "cr_returning_customer_sk BIGINT, cr_call_center_sk BIGINT, cr_catalog_page_sk BIGINT,"
+ + "cr_warehouse_sk BIGINT, cr_reason_sk BIGINT, cr_return_quantity INTEGER,"
+ + "cr_return_amount DECIMAL(7,2), cr_net_loss DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (cr_item_sk, cr_order_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WEB_SALES (" + "ws_item_sk BIGINT NOT NULL,"
+ + "ws_order_number BIGINT NOT NULL, ws_sold_date_sk BIGINT, ws_sold_time_sk BIGINT,"
+ + "ws_bill_customer_sk BIGINT, ws_ship_customer_sk BIGINT, ws_ship_addr_sk BIGINT,"
+ + "ws_web_site_sk BIGINT, ws_web_page_sk BIGINT, ws_warehouse_sk BIGINT,"
+ + "ws_ship_mode_sk BIGINT, ws_promo_sk BIGINT, ws_quantity INTEGER,"
+ + "ws_sales_price DECIMAL(7,2), ws_ext_sales_price DECIMAL(7,2),"
+ + "ws_ext_discount_amt DECIMAL(7,2), ws_list_price DECIMAL(7,2),"
+ + "ws_ext_list_price DECIMAL(7,2), ws_coupon_amt DECIMAL(7,2), ws_net_profit DECIMAL(7,2),"
+ + "ws_net_paid DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (ws_item_sk, ws_order_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WEB_RETURNS (" + "wr_item_sk BIGINT NOT NULL,"
+ + "wr_order_number BIGINT NOT NULL, wr_returned_date_sk BIGINT,"
+ + "wr_returning_customer_sk BIGINT, wr_web_page_sk BIGINT, wr_reason_sk BIGINT,"
+ + "wr_return_quantity INTEGER, wr_return_amt DECIMAL(7,2), wr_net_loss DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (wr_item_sk, wr_order_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".INVENTORY (" + "inv_date_sk BIGINT NOT NULL,"
+ + "inv_item_sk BIGINT NOT NULL, inv_warehouse_sk BIGINT NOT NULL,"
+ + "inv_quantity_on_hand INTEGER "
+ + "CONSTRAINT pk PRIMARY KEY (inv_date_sk, inv_item_sk, inv_warehouse_sk)) SALT_BUCKETS=4" };
+ }
+
+ private static String[] indexDdl(String s) {
+ String suffix = "_I";
+ return new String[] {
+ "CREATE INDEX IF NOT EXISTS SS" + suffix + " ON " + s
+ + ".STORE_SALES (ss_sold_date_sk, ss_item_sk) INCLUDE (ss_store_sk, ss_customer_sk,"
+ + " ss_quantity, ss_sales_price, ss_ext_sales_price, ss_ext_discount_amt, ss_list_price,"
+ + " ss_net_profit, ss_net_paid)",
+ "CREATE INDEX IF NOT EXISTS CS" + suffix + " ON " + s
+ + ".CATALOG_SALES (cs_sold_date_sk, cs_item_sk) INCLUDE (cs_bill_customer_sk,"
+ + " cs_warehouse_sk, cs_quantity, cs_sales_price, cs_ext_sales_price, cs_ext_discount_amt,"
+ + " cs_list_price, cs_net_profit, cs_net_paid)",
+ "CREATE INDEX IF NOT EXISTS WS" + suffix + " ON " + s
+ + ".WEB_SALES (ws_sold_date_sk, ws_item_sk) INCLUDE (ws_bill_customer_sk, ws_warehouse_sk,"
+ + " ws_quantity, ws_sales_price, ws_ext_sales_price, ws_ext_discount_amt, ws_list_price,"
+ + " ws_net_profit, ws_net_paid)",
+ "CREATE INDEX IF NOT EXISTS INV" + suffix + " ON " + s
+ + ".INVENTORY (inv_item_sk, inv_date_sk) INCLUDE (inv_warehouse_sk, inv_quantity_on_hand)" };
+ }
+
+ static final int[] YEARS = { 2000, 2001 };
+ static final String[] CATEGORIES =
+ { "Books", "Home", "Electronics", "Sports", "Jewelry", "Music" };
+ static final String[] STATES = { "CA", "TX", "NY", "WA", "OR" };
+ static final String[] GENDERS = { "M", "F" };
+ static final String[] MARITAL = { "M", "S", "D", "W", "U" };
+ static final String[] EDUCATION =
+ { "Primary", "Secondary", "College", "2 yr Degree", "4 yr Degree", "Advanced Degree" };
+ static final String[] BUY_POTENTIAL =
+ { ">10000", "5001-10000", "1001-5000", "501-1000", "0-500", "Unknown" };
+ static final String[] DAY_NAMES =
+ { "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" };
+ static final double[] GMT_OFFSETS = { -5, -6, -7, -8 };
+
+ private static final int N_ITEMS = 24;
+ private static final int N_CUSTOMERS = 30;
+ private static final int N_ADDR = 20;
+ private static final int N_CDEMO = 12;
+ private static final int N_HDEMO = 12;
+ private static final int N_STORES = 6;
+ private static final int N_CALL_CENTERS = 4;
+ private static final int N_CATALOG_PAGES = 6;
+ private static final int N_WEB_SITES = 4;
+ private static final int N_WEB_PAGES = 6;
+ private static final int N_WAREHOUSES = 4;
+ private static final int N_SHIP_MODES = 5;
+ private static final int N_PROMOS = 10;
+ private static final int N_REASONS = 6;
+
+ // date_dim: 24 rows = 12 months x 2 years, d_date_sk = 1..24.
+ private static final int N_DATES = YEARS.length * 12;
+ private static final int N_TIMES = 12;
+
+ private static void loadData(Connection conn, String s) throws SQLException {
+ Random rnd = new Random(SEED);
+ loadDateDim(conn, s);
+ loadTimeDim(conn, s);
+ loadItem(conn, s, rnd);
+ loadCustomerAddress(conn, s, rnd);
+ loadCustomerDemographics(conn, s);
+ loadIncomeBand(conn, s);
+ loadHouseholdDemographics(conn, s, rnd);
+ loadCustomer(conn, s, rnd);
+ loadStore(conn, s, rnd);
+ loadCallCenter(conn, s);
+ loadCatalogPage(conn, s);
+ loadWebSite(conn, s);
+ loadWebPage(conn, s);
+ loadWarehouse(conn, s, rnd);
+ loadShipMode(conn, s);
+ loadPromotion(conn, s, rnd);
+ loadReason(conn, s);
+ conn.commit();
+ loadStoreSales(conn, s, rnd);
+ loadStoreReturns(conn, s, rnd);
+ loadCatalogSales(conn, s, rnd);
+ loadCatalogReturns(conn, s, rnd);
+ loadWebSales(conn, s, rnd);
+ loadWebReturns(conn, s, rnd);
+ loadInventory(conn, s, rnd);
+ conn.commit();
+ }
+
+ private static void copyData(Connection conn, String from, String to) throws SQLException {
+ try (Statement st = conn.createStatement()) {
+ for (String t : TABLES) {
+ st.executeUpdate("UPSERT INTO " + to + "." + t + " SELECT * FROM " + from + "." + t);
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadDateDim(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".DATE_DIM (d_date_sk, d_date, d_year, d_moy, d_dom, d_qoy,"
+ + " d_dow, d_week_seq, d_day_name, d_quarter_name) VALUES (?,?,?,?,?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ int sk = 0;
+ for (int yi = 0; yi < YEARS.length; yi++) {
+ int year = YEARS[yi];
+ for (int moy = 1; moy <= 12; moy++) {
+ sk++;
+ int dom = 15;
+ int qoy = (moy - 1) / 3 + 1;
+ int dow = (sk % 7);
+ int weekSeq = (year - 2000) * 52 + moy * 4;
+ ps.setLong(1, sk);
+ ps.setDate(2, java.sql.Date.valueOf(String.format("%04d-%02d-%02d", year, moy, dom)));
+ ps.setInt(3, year);
+ ps.setInt(4, moy);
+ ps.setInt(5, dom);
+ ps.setInt(6, qoy);
+ ps.setInt(7, dow);
+ ps.setInt(8, weekSeq);
+ ps.setString(9, DAY_NAMES[dow]);
+ ps.setString(10, year + "Q" + qoy);
+ ps.executeUpdate();
+ }
+ }
+ }
+ }
+
+ private static void loadTimeDim(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".TIME_DIM (t_time_sk, t_hour, t_minute, t_am_pm, t_shift,"
+ + " t_meal_time) VALUES (?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_TIMES; i++) {
+ int hour = (i * 2) % 24;
+ ps.setLong(1, i);
+ ps.setInt(2, hour);
+ ps.setInt(3, 0);
+ ps.setString(4, hour < 12 ? "AM" : "PM");
+ ps.setString(5, hour < 8 ? "third" : hour < 16 ? "first" : "second");
+ ps.setString(6,
+ hour >= 6 && hour < 10 ? "breakfast"
+ : hour >= 11 && hour < 14 ? "lunch"
+ : hour >= 17 && hour < 21 ? "dinner"
+ : null);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadItem(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".ITEM (i_item_sk, i_item_id, i_item_desc, i_current_price,"
+ + " i_brand_id, i_brand, i_class_id, i_class, i_category_id, i_category, i_manufact_id,"
+ + " i_manufact, i_manager_id, i_size, i_color, i_units, i_product_name)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ String[] sizes = { "small", "medium", "large", "petite", "economy", "N/A" };
+ String[] colors = { "red", "blue", "green", "white", "black", "almond" };
+ String[] units = { "Each", "Dozen", "Case", "Box", "Pallet" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_ITEMS; i++) {
+ int catId = (i - 1) % CATEGORIES.length;
+ int classId = (i - 1) % 5 + 1;
+ int brandId = 1000 + ((i - 1) % 8);
+ int manufactId = (i - 1) % 5 + 1;
+ int managerId = (i - 1) % 10 + 1;
+ ps.setLong(1, i);
+ ps.setString(2, String.format("ITEM%010d", i));
+ ps.setString(3, "Item description " + i);
+ ps.setBigDecimal(4, bd(10 + (i % 90) + 0.99));
+ ps.setInt(5, brandId);
+ ps.setString(6, "brand#" + brandId);
+ ps.setInt(7, classId);
+ ps.setString(8, "class#" + classId);
+ ps.setInt(9, catId + 1);
+ ps.setString(10, CATEGORIES[catId]);
+ ps.setInt(11, manufactId);
+ ps.setString(12, "manufact#" + manufactId);
+ ps.setInt(13, managerId);
+ ps.setString(14, sizes[i % sizes.length]);
+ ps.setString(15, colors[i % colors.length]);
+ ps.setString(16, units[i % units.length]);
+ ps.setString(17, "product#" + i);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCustomerAddress(Connection conn, String s, Random rnd)
+ throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CUSTOMER_ADDRESS (ca_address_sk, ca_city, ca_county,"
+ + " ca_state, ca_zip, ca_country, ca_gmt_offset, ca_street_number, ca_street_name)"
+ + " VALUES (?,?,?,?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_ADDR; i++) {
+ String state = STATES[(i - 1) % STATES.length];
+ ps.setLong(1, i);
+ ps.setString(2, "City" + ((i - 1) % 7));
+ ps.setString(3, "County" + ((i - 1) % 4));
+ ps.setString(4, state);
+ ps.setString(5, String.format("%05d", 10000 + i));
+ ps.setString(6, "United States");
+ ps.setBigDecimal(7, bd(GMT_OFFSETS[(i - 1) % GMT_OFFSETS.length]));
+ ps.setString(8, Integer.toString(100 + i));
+ ps.setString(9, "Street " + i);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCustomerDemographics(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CUSTOMER_DEMOGRAPHICS (cd_demo_sk, cd_gender,"
+ + " cd_marital_status, cd_education_status, cd_purchase_estimate, cd_credit_rating,"
+ + " cd_dep_count, cd_dep_employed_count, cd_dep_college_count) VALUES (?,?,?,?,?,?,?,?,?)";
+ String[] credit = { "Low Risk", "Good", "High Risk", "Unknown" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_CDEMO; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, GENDERS[(i - 1) % GENDERS.length]);
+ ps.setString(3, MARITAL[(i - 1) % MARITAL.length]);
+ ps.setString(4, EDUCATION[(i - 1) % EDUCATION.length]);
+ ps.setInt(5, 500 * (((i - 1) % 6) + 1));
+ ps.setString(6, credit[(i - 1) % credit.length]);
+ ps.setInt(7, (i - 1) % 4);
+ ps.setInt(8, (i - 1) % 3);
+ ps.setInt(9, (i - 1) % 2);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadIncomeBand(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".INCOME_BAND (ib_income_band_sk, ib_lower_bound,"
+ + " ib_upper_bound) VALUES (?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= 10; i++) {
+ ps.setLong(1, i);
+ ps.setInt(2, (i - 1) * 10000);
+ ps.setInt(3, i * 10000);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadHouseholdDemographics(Connection conn, String s, Random rnd)
+ throws SQLException {
+ String sql = "UPSERT INTO " + s + ".HOUSEHOLD_DEMOGRAPHICS (hd_demo_sk, hd_income_band_sk,"
+ + " hd_buy_potential, hd_dep_count, hd_vehicle_count) VALUES (?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_HDEMO; i++) {
+ ps.setLong(1, i);
+ ps.setLong(2, ((i - 1) % 10) + 1);
+ ps.setString(3, BUY_POTENTIAL[(i - 1) % BUY_POTENTIAL.length]);
+ ps.setInt(4, (i - 1) % 5);
+ ps.setInt(5, (i - 1) % 4);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCustomer(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CUSTOMER (c_customer_sk, c_customer_id, c_current_cdemo_sk,"
+ + " c_current_hdemo_sk, c_current_addr_sk, c_first_name, c_last_name, c_birth_country,"
+ + " c_birth_year, c_preferred_cust_flag, c_salutation, c_email_address)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
+ String[] countries = { "UNITED STATES", "CANADA", "MEXICO", "BRAZIL" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_CUSTOMERS; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("CUST%012d", i));
+ ps.setLong(3, ((i - 1) % N_CDEMO) + 1);
+ ps.setLong(4, ((i - 1) % N_HDEMO) + 1);
+ ps.setLong(5, ((i - 1) % N_ADDR) + 1);
+ ps.setString(6, "First" + i);
+ ps.setString(7, "Last" + i);
+ ps.setString(8, countries[(i - 1) % countries.length]);
+ ps.setInt(9, 1950 + (i % 40));
+ ps.setString(10, (i % 2 == 0) ? "Y" : "N");
+ ps.setString(11, (i % 2 == 0) ? "Mr." : "Ms.");
+ ps.setString(12, "cust" + i + "@example.com");
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadStore(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".STORE (s_store_sk, s_store_id, s_store_name, s_company_id,"
+ + " s_company_name, s_state, s_zip, s_gmt_offset, s_city, s_county, s_manager,"
+ + " s_number_employees) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_STORES; i++) {
+ String state = STATES[(i - 1) % STATES.length];
+ ps.setLong(1, i);
+ ps.setString(2, String.format("STORE%010d", i));
+ ps.setString(3, "Store " + i);
+ ps.setInt(4, 1);
+ ps.setString(5, "company#1");
+ ps.setString(6, state);
+ ps.setString(7, String.format("%05d", 20000 + i));
+ ps.setBigDecimal(8, bd(GMT_OFFSETS[(i - 1) % GMT_OFFSETS.length]));
+ ps.setString(9, "City" + ((i - 1) % 7));
+ ps.setString(10, "County" + ((i - 1) % 4));
+ ps.setString(11, "Manager" + i);
+ ps.setInt(12, 100 + i * 10);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCallCenter(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CALL_CENTER (cc_call_center_sk, cc_call_center_id, cc_name,"
+ + " cc_manager, cc_mkt_id, cc_class, cc_company) VALUES (?,?,?,?,?,?,?)";
+ String[] classes = { "small", "medium", "large" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_CALL_CENTERS; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("CC%014d", i));
+ ps.setString(3, "Call Center " + i);
+ ps.setString(4, "CCManager" + i);
+ ps.setInt(5, (i - 1) % 3 + 1);
+ ps.setString(6, classes[(i - 1) % classes.length]);
+ ps.setInt(7, 1);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCatalogPage(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CATALOG_PAGE (cp_catalog_page_sk, cp_catalog_page_id,"
+ + " cp_catalog_number, cp_catalog_page_number, cp_department) VALUES (?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_CATALOG_PAGES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("CP%014d", i));
+ ps.setInt(3, (i - 1) % 3 + 1);
+ ps.setInt(4, i);
+ ps.setString(5, "DEPARTMENT");
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadWebSite(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".WEB_SITE (web_site_sk, web_site_id, web_name,"
+ + " web_company_id, web_company_name) VALUES (?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_WEB_SITES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("WS%014d", i));
+ ps.setString(3, "Web Site " + i);
+ ps.setInt(4, 1);
+ ps.setString(5, "company#1");
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadWebPage(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".WEB_PAGE (wp_web_page_sk, wp_web_page_id, wp_char_count,"
+ + " wp_type) VALUES (?,?,?,?)";
+ String[] types = { "welcome", "protected", "feedback", "general", "ad", "order" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_WEB_PAGES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("WP%014d", i));
+ ps.setInt(3, 1000 + i * 100);
+ ps.setString(4, types[(i - 1) % types.length]);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadWarehouse(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".WAREHOUSE (w_warehouse_sk, w_warehouse_id,"
+ + " w_warehouse_name, w_state, w_country, w_gmt_offset) VALUES (?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_WAREHOUSES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("WH%014d", i));
+ ps.setString(3, "Warehouse " + i);
+ ps.setString(4, STATES[(i - 1) % STATES.length]);
+ ps.setString(5, "United States");
+ ps.setBigDecimal(6, bd(GMT_OFFSETS[(i - 1) % GMT_OFFSETS.length]));
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadShipMode(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".SHIP_MODE (sm_ship_mode_sk, sm_ship_mode_id, sm_type,"
+ + " sm_carrier) VALUES (?,?,?,?)";
+ String[] types = { "EXPRESS", "NEXT DAY", "OVERNIGHT", "TWO DAY", "LIBRARY" };
+ String[] carriers = { "DHL", "FEDEX", "UPS", "USPS", "ZHOU" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_SHIP_MODES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("SM%014d", i));
+ ps.setString(3, types[(i - 1) % types.length]);
+ ps.setString(4, carriers[(i - 1) % carriers.length]);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadPromotion(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".PROMOTION (p_promo_sk, p_promo_id, p_channel_email,"
+ + " p_channel_event, p_channel_tv, p_channel_dmail, p_channel_catalog) VALUES (?,?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_PROMOS; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("PROMO%010d", i));
+ ps.setString(3, (i % 2 == 0) ? "Y" : "N");
+ ps.setString(4, (i % 3 == 0) ? "Y" : "N");
+ ps.setString(5, (i % 2 == 1) ? "Y" : "N");
+ ps.setString(6, (i % 4 == 0) ? "Y" : "N");
+ ps.setString(7, (i % 5 == 0) ? "Y" : "N");
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadReason(Connection conn, String s) throws SQLException {
+ String sql =
+ "UPSERT INTO " + s + ".REASON (r_reason_sk, r_reason_id, r_reason_desc)" + " VALUES (?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_REASONS; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("REASON%010d", i));
+ ps.setString(3, "Reason " + i);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadStoreSales(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".STORE_SALES (ss_item_sk, ss_ticket_number, ss_sold_date_sk,"
+ + " ss_sold_time_sk, ss_customer_sk, ss_cdemo_sk, ss_hdemo_sk, ss_addr_sk, ss_store_sk,"
+ + " ss_promo_sk, ss_quantity, ss_sales_price, ss_ext_sales_price, ss_ext_discount_amt,"
+ + " ss_list_price, ss_ext_list_price, ss_coupon_amt, ss_net_profit, ss_net_paid)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ long ticket = 0;
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ // Each item sold on a handful of dates, giving broad coverage of (year, moy).
+ for (int dateSk = 1; dateSk <= N_DATES; dateSk += 2) {
+ ticket++;
+ int qty = 1 + rnd.nextInt(20);
+ double salesPrice = 5 + rnd.nextInt(95) + 0.5;
+ double listPrice = salesPrice + rnd.nextInt(20);
+ long cust = 1 + ((item + dateSk) % N_CUSTOMERS);
+ ps.setLong(1, item);
+ ps.setLong(2, ticket);
+ ps.setLong(3, dateSk);
+ ps.setLong(4, 1 + (ticket % N_TIMES));
+ ps.setLong(5, cust);
+ ps.setLong(6, 1 + ((cust - 1) % N_CDEMO));
+ ps.setLong(7, 1 + ((cust - 1) % N_HDEMO));
+ ps.setLong(8, 1 + ((cust - 1) % N_ADDR));
+ ps.setLong(9, 1 + (int) (ticket % N_STORES));
+ ps.setLong(10, 1 + (int) (ticket % N_PROMOS));
+ ps.setInt(11, qty);
+ ps.setBigDecimal(12, bd(salesPrice));
+ ps.setBigDecimal(13, bd(salesPrice * qty));
+ ps.setBigDecimal(14, bd(rnd.nextInt(50)));
+ ps.setBigDecimal(15, bd(listPrice));
+ ps.setBigDecimal(16, bd(listPrice * qty));
+ ps.setBigDecimal(17, bd(rnd.nextInt(20)));
+ ps.setBigDecimal(18, bd((salesPrice - 3) * qty));
+ ps.setBigDecimal(19, bd(salesPrice * qty));
+ ps.executeUpdate();
+ }
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadStoreReturns(Connection conn, String s, Random rnd) throws SQLException {
+ // Return every other store_sales row (even tickets), reusing its (item, ticket) so the natural
+ // join key lines up with store_sales.
+ String sql = "UPSERT INTO " + s + ".STORE_RETURNS (sr_item_sk, sr_ticket_number,"
+ + " sr_returned_date_sk, sr_customer_sk, sr_cdemo_sk, sr_hdemo_sk, sr_addr_sk, sr_store_sk,"
+ + " sr_reason_sk, sr_return_quantity, sr_return_amt, sr_net_loss, sr_fee)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ long ticket = 0;
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ for (int dateSk = 1; dateSk <= N_DATES; dateSk += 2) {
+ ticket++;
+ if (ticket % 2 != 0) {
+ continue;
+ }
+ long cust = 1 + ((item + dateSk) % N_CUSTOMERS);
+ int retDate = Math.min(N_DATES, dateSk + 1);
+ ps.setLong(1, item);
+ ps.setLong(2, ticket);
+ ps.setLong(3, retDate);
+ ps.setLong(4, cust);
+ ps.setLong(5, 1 + ((cust - 1) % N_CDEMO));
+ ps.setLong(6, 1 + ((cust - 1) % N_HDEMO));
+ ps.setLong(7, 1 + ((cust - 1) % N_ADDR));
+ ps.setLong(8, 1 + (int) (ticket % N_STORES));
+ ps.setLong(9, 1 + (int) (ticket % N_REASONS));
+ ps.setInt(10, 1 + rnd.nextInt(5));
+ ps.setBigDecimal(11, bd(5 + rnd.nextInt(50)));
+ ps.setBigDecimal(12, bd(rnd.nextInt(30)));
+ ps.setBigDecimal(13, bd(rnd.nextInt(10)));
+ ps.executeUpdate();
+ }
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadCatalogSales(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CATALOG_SALES (cs_item_sk, cs_order_number,"
+ + " cs_sold_date_sk, cs_bill_customer_sk, cs_bill_cdemo_sk, cs_ship_customer_sk,"
+ + " cs_ship_addr_sk, cs_call_center_sk, cs_catalog_page_sk, cs_warehouse_sk, cs_ship_mode_sk,"
+ + " cs_promo_sk, cs_quantity, cs_sales_price, cs_ext_sales_price, cs_ext_discount_amt,"
+ + " cs_list_price, cs_ext_list_price, cs_coupon_amt, cs_net_profit, cs_net_paid)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ long order = 0;
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ for (int dateSk = 2; dateSk <= N_DATES; dateSk += 3) {
+ order++;
+ int qty = 1 + rnd.nextInt(20);
+ double salesPrice = 5 + rnd.nextInt(95) + 0.5;
+ double listPrice = salesPrice + rnd.nextInt(20);
+ long cust = 1 + ((item * 2 + dateSk) % N_CUSTOMERS);
+ ps.setLong(1, item);
+ ps.setLong(2, order);
+ ps.setLong(3, dateSk);
+ ps.setLong(4, cust);
+ ps.setLong(5, 1 + ((cust - 1) % N_CDEMO));
+ ps.setLong(6, 1 + (cust % N_CUSTOMERS));
+ ps.setLong(7, 1 + ((cust - 1) % N_ADDR));
+ ps.setLong(8, 1 + (int) (order % N_CALL_CENTERS));
+ ps.setLong(9, 1 + (int) (order % N_CATALOG_PAGES));
+ ps.setLong(10, 1 + (int) (order % N_WAREHOUSES));
+ ps.setLong(11, 1 + (int) (order % N_SHIP_MODES));
+ ps.setLong(12, 1 + (int) (order % N_PROMOS));
+ ps.setInt(13, qty);
+ ps.setBigDecimal(14, bd(salesPrice));
+ ps.setBigDecimal(15, bd(salesPrice * qty));
+ ps.setBigDecimal(16, bd(rnd.nextInt(50)));
+ ps.setBigDecimal(17, bd(listPrice));
+ ps.setBigDecimal(18, bd(listPrice * qty));
+ ps.setBigDecimal(19, bd(rnd.nextInt(20)));
+ ps.setBigDecimal(20, bd((salesPrice - 3) * qty));
+ ps.setBigDecimal(21, bd(salesPrice * qty));
+ ps.executeUpdate();
+ }
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadCatalogReturns(Connection conn, String s, Random rnd)
+ throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CATALOG_RETURNS (cr_item_sk, cr_order_number,"
+ + " cr_returned_date_sk, cr_returning_customer_sk, cr_call_center_sk, cr_catalog_page_sk,"
+ + " cr_warehouse_sk, cr_reason_sk, cr_return_quantity, cr_return_amount, cr_net_loss)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?)";
+ long order = 0;
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ for (int dateSk = 2; dateSk <= N_DATES; dateSk += 3) {
+ order++;
+ if (order % 2 != 0) {
+ continue;
+ }
+ long cust = 1 + ((item * 2 + dateSk) % N_CUSTOMERS);
+ int retDate = Math.min(N_DATES, dateSk + 1);
+ ps.setLong(1, item);
+ ps.setLong(2, order);
+ ps.setLong(3, retDate);
+ ps.setLong(4, cust);
+ ps.setLong(5, 1 + (int) (order % N_CALL_CENTERS));
+ ps.setLong(6, 1 + (int) (order % N_CATALOG_PAGES));
+ ps.setLong(7, 1 + (int) (order % N_WAREHOUSES));
+ ps.setLong(8, 1 + (int) (order % N_REASONS));
+ ps.setInt(9, 1 + rnd.nextInt(5));
+ ps.setBigDecimal(10, bd(5 + rnd.nextInt(50)));
+ ps.setBigDecimal(11, bd(rnd.nextInt(10)));
+ ps.executeUpdate();
+ }
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadWebSales(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".WEB_SALES (ws_item_sk, ws_order_number, ws_sold_date_sk,"
+ + " ws_sold_time_sk, ws_bill_customer_sk, ws_ship_customer_sk, ws_ship_addr_sk,"
+ + " ws_web_site_sk, ws_web_page_sk, ws_warehouse_sk, ws_ship_mode_sk, ws_promo_sk,"
+ + " ws_quantity, ws_sales_price, ws_ext_sales_price, ws_ext_discount_amt, ws_list_price,"
+ + " ws_ext_list_price, ws_coupon_amt, ws_net_profit, ws_net_paid)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ long order = 0;
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ for (int dateSk = 3; dateSk <= N_DATES; dateSk += 3) {
+ order++;
+ int qty = 1 + rnd.nextInt(20);
+ double salesPrice = 5 + rnd.nextInt(95) + 0.5;
+ double listPrice = salesPrice + rnd.nextInt(20);
+ long cust = 1 + ((item * 3 + dateSk) % N_CUSTOMERS);
+ ps.setLong(1, item);
+ ps.setLong(2, order);
+ ps.setLong(3, dateSk);
+ ps.setLong(4, 1 + (order % N_TIMES));
+ ps.setLong(5, cust);
+ ps.setLong(6, 1 + (cust % N_CUSTOMERS));
+ ps.setLong(7, 1 + ((cust - 1) % N_ADDR));
+ ps.setLong(8, 1 + (int) (order % N_WEB_SITES));
+ ps.setLong(9, 1 + (int) (order % N_WEB_PAGES));
+ ps.setLong(10, 1 + (int) (order % N_WAREHOUSES));
+ ps.setLong(11, 1 + (int) (order % N_SHIP_MODES));
+ ps.setLong(12, 1 + (int) (order % N_PROMOS));
+ ps.setInt(13, qty);
+ ps.setBigDecimal(14, bd(salesPrice));
+ ps.setBigDecimal(15, bd(salesPrice * qty));
+ ps.setBigDecimal(16, bd(rnd.nextInt(50)));
+ ps.setBigDecimal(17, bd(listPrice));
+ ps.setBigDecimal(18, bd(listPrice * qty));
+ ps.setBigDecimal(19, bd(rnd.nextInt(20)));
+ ps.setBigDecimal(20, bd((salesPrice - 3) * qty));
+ ps.setBigDecimal(21, bd(salesPrice * qty));
+ ps.executeUpdate();
+ }
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadWebReturns(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".WEB_RETURNS (wr_item_sk, wr_order_number,"
+ + " wr_returned_date_sk, wr_returning_customer_sk, wr_web_page_sk, wr_reason_sk,"
+ + " wr_return_quantity, wr_return_amt, wr_net_loss) VALUES (?,?,?,?,?,?,?,?,?)";
+ long order = 0;
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ for (int dateSk = 3; dateSk <= N_DATES; dateSk += 3) {
+ order++;
+ if (order % 2 != 0) {
+ continue;
+ }
+ long cust = 1 + ((item * 3 + dateSk) % N_CUSTOMERS);
+ int retDate = Math.min(N_DATES, dateSk + 1);
+ ps.setLong(1, item);
+ ps.setLong(2, order);
+ ps.setLong(3, retDate);
+ ps.setLong(4, cust);
+ ps.setLong(5, 1 + (int) (order % N_WEB_PAGES));
+ ps.setLong(6, 1 + (int) (order % N_REASONS));
+ ps.setInt(7, 1 + rnd.nextInt(5));
+ ps.setBigDecimal(8, bd(5 + rnd.nextInt(50)));
+ ps.setBigDecimal(9, bd(rnd.nextInt(10)));
+ ps.executeUpdate();
+ }
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadInventory(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".INVENTORY (inv_date_sk, inv_item_sk, inv_warehouse_sk,"
+ + " inv_quantity_on_hand) VALUES (?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ // Weekly-ish snapshots: every 3rd date, all items, all warehouses.
+ for (int dateSk = 1; dateSk <= N_DATES; dateSk += 3) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ for (int wh = 1; wh <= N_WAREHOUSES; wh++) {
+ ps.setLong(1, dateSk);
+ ps.setLong(2, item);
+ ps.setLong(3, wh);
+ ps.setInt(4, 50 + rnd.nextInt(600));
+ ps.executeUpdate();
+ }
+ }
+ }
+ }
+ conn.commit();
+ }
+
+ private static java.math.BigDecimal bd(double v) {
+ return new java.math.BigDecimal(v).setScale(2, java.math.RoundingMode.HALF_UP);
+ }
+}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeSingleChannelIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeSingleChannelIT.java
new file mode 100644
index 0000000000..5c91e48bd7
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeSingleChannelIT.java
@@ -0,0 +1,395 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import java.sql.SQLException;
+import java.util.Collection;
+import java.util.LinkedHashMap;
+import java.util.Map;
+import org.apache.phoenix.end2end.ParallelStatsDisabledTest;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+/**
+ * TPC-DS derived integration tests over a single sales and returns channel. Each {@code @Test}
+ * adapts a public TPC-DS query to Phoenix, asserts the full ordered result, and asserts the
+ * rendered EXPLAIN plan.
+ */
+@Category(ParallelStatsDisabledTest.class)
+@RunWith(Parameterized.class)
+public class TPCDSLikeSingleChannelIT extends TPCDSLikeBaseIT {
+
+ public TPCDSLikeSingleChannelIT(String label, String schema, boolean noIndex) {
+ super(label, schema, noIndex);
+ }
+
+ @Parameters(name = "{0}")
+ public static Collection<Object[]> params() {
+ return indexParameters();
+ }
+
+ @BeforeClass
+ public static synchronized void setup() throws SQLException {
+ loadFixture();
+ }
+
+ // TPC-DS Q3: store sales discount by brand for a manufacturer/month.
+ static final String Q03 = "SELECT d.d_year, i.i_brand_id brand_id, i.i_brand brand,"
+ + " SUM(ss.ss_ext_discount_amt) sum_agg"
+ + " FROM TPCDS.date_dim d, TPCDS.store_sales ss, TPCDS.item i"
+ + " WHERE d.d_date_sk = ss.ss_sold_date_sk AND ss.ss_item_sk = i.i_item_sk"
+ + " AND i.i_manufact_id = 1 AND d.d_moy = 11" + " GROUP BY d.d_year, i.i_brand, i.i_brand_id"
+ + " ORDER BY d.d_year, sum_agg DESC, brand_id LIMIT 100";
+
+ // TPC-DS Q7: average sale measures by item for a gender/marital/promo segment.
+ static final String Q07 = "SELECT i.i_item_id, AVG(ss.ss_quantity) agg1,"
+ + " AVG(ss.ss_list_price) agg2, AVG(ss.ss_coupon_amt) agg3, AVG(ss.ss_sales_price) agg4"
+ + " FROM TPCDS.store_sales ss, TPCDS.customer_demographics cd, TPCDS.date_dim d,"
+ + " TPCDS.item i, TPCDS.promotion p"
+ + " WHERE ss.ss_sold_date_sk = d.d_date_sk AND ss.ss_item_sk = i.i_item_sk"
+ + " AND ss.ss_cdemo_sk = cd.cd_demo_sk AND ss.ss_promo_sk = p.p_promo_sk"
+ + " AND cd.cd_gender = 'M' AND cd.cd_marital_status = 'S'"
+ + " AND (p.p_channel_email = 'N' OR p.p_channel_event = 'N') AND d.d_year = 2000"
+ + " GROUP BY i.i_item_id ORDER BY i.i_item_id LIMIT 100";
+
+ // TPC-DS Q42: store ext sales price by category for a manager/month/year.
+ static final String Q42 =
+ "SELECT d.d_year, i.i_category_id, i.i_category," + " SUM(ss.ss_ext_sales_price) sum_price"
+ + " FROM TPCDS.date_dim d, TPCDS.store_sales ss, TPCDS.item i"
+ + " WHERE d.d_date_sk = ss.ss_sold_date_sk AND ss.ss_item_sk = i.i_item_sk"
+ + " AND i.i_manager_id = 1 AND d.d_moy = 11 AND d.d_year = 2000"
+ + " GROUP BY d.d_year, i.i_category_id, i.i_category"
+ + " ORDER BY sum_price DESC, d.d_year, i.i_category_id, i.i_category LIMIT 100";
+
+ // TPC-DS Q52: store ext sales price by brand for a manager/month/year.
+ static final String Q52 = "SELECT d.d_year, i.i_brand_id brand_id, i.i_brand brand,"
+ + " SUM(ss.ss_ext_sales_price) ext_price"
+ + " FROM TPCDS.date_dim d, TPCDS.store_sales ss, TPCDS.item i"
+ + " WHERE d.d_date_sk = ss.ss_sold_date_sk AND ss.ss_item_sk = i.i_item_sk"
+ + " AND i.i_manager_id = 1 AND d.d_moy = 11 AND d.d_year = 2000"
+ + " GROUP BY d.d_year, i.i_brand, i.i_brand_id"
+ + " ORDER BY d.d_year, ext_price DESC, brand_id LIMIT 100";
+
+ // TPC-DS Q55: store ext sales price by brand for a manager/month/year.
+ static final String Q55 =
+ "SELECT i.i_brand_id brand_id, i.i_brand brand," + " SUM(ss.ss_ext_sales_price) ext_price"
+ + " FROM TPCDS.date_dim d, TPCDS.store_sales ss, TPCDS.item i"
+ + " WHERE d.d_date_sk = ss.ss_sold_date_sk AND ss.ss_item_sk = i.i_item_sk"
+ + " AND i.i_manager_id = 2 AND d.d_moy = 11 AND d.d_year = 2001"
+ + " GROUP BY i.i_brand, i.i_brand_id ORDER BY ext_price DESC, brand_id LIMIT 100";
+
+ // TPC-DS Q43: store sales by day of week (CASE pivot) for a region/year.
+ static final String Q43 = "SELECT s.s_store_name, s.s_store_id,"
+ + " SUM(CASE WHEN d.d_day_name = 'Sunday' THEN ss.ss_sales_price ELSE 0 END) sun_sales,"
+ + " SUM(CASE WHEN d.d_day_name = 'Monday' THEN ss.ss_sales_price ELSE 0 END) mon_sales,"
+ + " SUM(CASE WHEN d.d_day_name = 'Tuesday' THEN ss.ss_sales_price ELSE 0 END) tue_sales,"
+ + " SUM(CASE WHEN d.d_day_name = 'Wednesday' THEN ss.ss_sales_price ELSE 0 END) wed_sales,"
+ + " SUM(CASE WHEN d.d_day_name = 'Thursday' THEN ss.ss_sales_price ELSE 0 END) thu_sales,"
+ + " SUM(CASE WHEN d.d_day_name = 'Friday' THEN ss.ss_sales_price ELSE 0 END) fri_sales,"
+ + " SUM(CASE WHEN d.d_day_name = 'Saturday' THEN ss.ss_sales_price ELSE 0 END) sat_sales"
+ + " FROM TPCDS.date_dim d, TPCDS.store_sales ss, TPCDS.store s"
+ + " WHERE d.d_date_sk = ss.ss_sold_date_sk AND ss.ss_store_sk = s.s_store_sk"
+ + " AND s.s_gmt_offset = -5 AND d.d_year = 2000" + " GROUP BY s.s_store_name, s.s_store_id"
+ + " ORDER BY s.s_store_name, s.s_store_id, sun_sales, mon_sales, tue_sales, wed_sales,"
+ + " thu_sales, fri_sales, sat_sales LIMIT 100";
+
+ // TPC-DS Q96: count of store sales in an hour band / demographic / company.
+ static final String Q96 = "SELECT COUNT(*) cnt"
+ + " FROM TPCDS.store_sales ss, TPCDS.household_demographics hd, TPCDS.time_dim t,"
+ + " TPCDS.store s"
+ + " WHERE ss.ss_sold_time_sk = t.t_time_sk AND ss.ss_hdemo_sk = hd.hd_demo_sk"
+ + " AND ss.ss_store_sk = s.s_store_sk AND t.t_hour >= 8 AND hd.hd_dep_count >= 0"
+ + " AND s.s_company_id = 1 ORDER BY cnt LIMIT 100";
+
+ // TPC-DS Q19: brand revenue where customer zip differs from store zip.
+ static final String Q19 = "SELECT i.i_brand_id brand_id, i.i_brand brand, i.i_manufact_id,"
+ + " i.i_manufact, SUM(ss.ss_ext_sales_price) ext_price"
+ + " FROM TPCDS.date_dim d, TPCDS.store_sales ss, TPCDS.item i, TPCDS.customer c,"
+ + " TPCDS.customer_address ca, TPCDS.store s"
+ + " WHERE d.d_date_sk = ss.ss_sold_date_sk AND ss.ss_item_sk = i.i_item_sk"
+ + " AND ss.ss_customer_sk = c.c_customer_sk AND c.c_current_addr_sk = ca.ca_address_sk"
+ + " AND ss.ss_store_sk = s.s_store_sk AND i.i_manager_id = 1 AND d.d_moy = 11"
+ + " AND d.d_year = 2000 AND SUBSTR(ca.ca_zip, 1, 5) <> SUBSTR(s.s_zip, 1, 5)"
+ + " GROUP BY i.i_brand, i.i_brand_id, i.i_manufact_id, i.i_manufact"
+ + " ORDER BY ext_price DESC, i.i_brand, i.i_brand_id, i.i_manufact_id, i.i_manufact LIMIT 100";
+
+ // TPC-DS Q46: coupon amount and profit by customer/city for a demographic segment.
+ static final String Q46 = "SELECT c.c_last_name, c.c_first_name, ca.ca_city,"
+ + " SUM(ss.ss_coupon_amt) amt, SUM(ss.ss_net_profit) profit"
+ + " FROM TPCDS.store_sales ss, TPCDS.date_dim d, TPCDS.store s,"
+ + " TPCDS.household_demographics hd, TPCDS.customer_address ca, TPCDS.customer c"
+ + " WHERE ss.ss_sold_date_sk = d.d_date_sk AND ss.ss_store_sk = s.s_store_sk"
+ + " AND ss.ss_hdemo_sk = hd.hd_demo_sk AND ss.ss_addr_sk = ca.ca_address_sk"
+ + " AND ss.ss_customer_sk = c.c_customer_sk"
+ + " AND (hd.hd_dep_count = 2 OR hd.hd_vehicle_count = 1) AND d.d_year = 2000"
+ + " AND s.s_city IN ('City0', 'City1', 'City2', 'City3', 'City4')"
+ + " GROUP BY c.c_last_name, c.c_first_name, ca.ca_city"
+ + " ORDER BY c.c_last_name, c.c_first_name, ca.ca_city, amt, profit LIMIT 100";
+
+ // TPC-DS Q50: store return lag buckets by store (self-join on date_dim).
+ static final String Q50 = "SELECT s.s_store_name, s.s_store_id,"
+ + " SUM(CASE WHEN (d2.d_date_sk - d1.d_date_sk <= 1) THEN 1 ELSE 0 END) days_le_1,"
+ + " SUM(CASE WHEN (d2.d_date_sk - d1.d_date_sk > 1 AND d2.d_date_sk - d1.d_date_sk <= 3)"
+ + " THEN 1 ELSE 0 END) days_2_3,"
+ + " SUM(CASE WHEN (d2.d_date_sk - d1.d_date_sk > 3) THEN 1 ELSE 0 END) days_gt_3"
+ + " FROM TPCDS.store_sales ss, TPCDS.store_returns sr, TPCDS.date_dim d1,"
+ + " TPCDS.date_dim d2, TPCDS.store s"
+ + " WHERE ss.ss_ticket_number = sr.sr_ticket_number AND ss.ss_item_sk = sr.sr_item_sk"
+ + " AND ss.ss_sold_date_sk = d1.d_date_sk AND sr.sr_returned_date_sk = d2.d_date_sk"
+ + " AND ss.ss_store_sk = s.s_store_sk AND d2.d_year = 2000"
+ + " GROUP BY s.s_store_name, s.s_store_id ORDER BY s.s_store_name, s.s_store_id LIMIT 100";
+
+ // TPC-DS Q73: tickets per customer via a grouped derived table joined to customer.
+ static final String Q73 = "SELECT c.c_last_name, c.c_first_name, dj.ss_ticket_number, dj.cnt"
+ + " FROM (SELECT ss.ss_ticket_number ss_ticket_number, ss.ss_customer_sk ss_customer_sk,"
+ + " COUNT(*) cnt FROM TPCDS.store_sales ss, TPCDS.date_dim d, TPCDS.store s,"
+ + " TPCDS.household_demographics hd"
+ + " WHERE ss.ss_sold_date_sk = d.d_date_sk AND ss.ss_store_sk = s.s_store_sk"
+ + " AND ss.ss_hdemo_sk = hd.hd_demo_sk AND d.d_year = 2000 AND hd.hd_vehicle_count >= 0"
+ + " AND s.s_company_id = 1 GROUP BY ss.ss_ticket_number, ss.ss_customer_sk) dj,"
+ + " TPCDS.customer c" + " WHERE dj.ss_customer_sk = c.c_customer_sk AND dj.cnt BETWEEN 1 AND 5"
+ + " ORDER BY dj.cnt DESC, c.c_last_name, c.c_first_name, dj.ss_ticket_number LIMIT 20";
+
+ // TPC-DS Q82: items in a price range with on-hand inventory that were also sold in store.
+ static final String Q82 = "SELECT i.i_item_id, i.i_item_desc, i.i_current_price"
+ + " FROM TPCDS.item i, TPCDS.inventory inv, TPCDS.date_dim d, TPCDS.store_sales ss"
+ + " WHERE i.i_current_price BETWEEN 20 AND 80 AND inv.inv_item_sk = i.i_item_sk"
+ + " AND d.d_date_sk = inv.inv_date_sk AND inv.inv_quantity_on_hand BETWEEN 100 AND 500"
+ + " AND i.i_manufact_id IN (1, 2, 3, 4, 5) AND inv.inv_item_sk = ss.ss_item_sk"
+ + " GROUP BY i.i_item_id, i.i_item_desc, i.i_current_price ORDER BY i.i_item_id LIMIT 100";
+
+ // TPC-DS Q21: inventory before/after a pivot date by warehouse/item (CASE pivot).
+ static final String Q21 = "SELECT w.w_warehouse_name, i.i_item_id,"
+ + " SUM(CASE WHEN d.d_date_sk < 12 THEN inv.inv_quantity_on_hand ELSE 0 END) inv_before,"
+ + " SUM(CASE WHEN d.d_date_sk >= 12 THEN inv.inv_quantity_on_hand ELSE 0 END) inv_after"
+ + " FROM TPCDS.inventory inv, TPCDS.warehouse w, TPCDS.item i, TPCDS.date_dim d"
+ + " WHERE i.i_item_sk = inv.inv_item_sk AND inv.inv_warehouse_sk = w.w_warehouse_sk"
+ + " AND inv.inv_date_sk = d.d_date_sk AND i.i_current_price BETWEEN 10 AND 100"
+ + " AND i.i_manufact_id IN (1, 2, 3) AND w.w_warehouse_sk IN (1, 2)"
+ + " GROUP BY w.w_warehouse_name, i.i_item_id"
+ + " ORDER BY w.w_warehouse_name, i.i_item_id LIMIT 100";
+
+ // TPC-DS Q26: average catalog sale measures by item for a gender/marital/promo segment.
+ static final String Q26 = "SELECT i.i_item_id, AVG(cs.cs_quantity) agg1,"
+ + " AVG(cs.cs_list_price) agg2, AVG(cs.cs_coupon_amt) agg3, AVG(cs.cs_sales_price) agg4"
+ + " FROM TPCDS.catalog_sales cs, TPCDS.customer_demographics cd, TPCDS.date_dim d,"
+ + " TPCDS.item i, TPCDS.promotion p"
+ + " WHERE cs.cs_sold_date_sk = d.d_date_sk AND cs.cs_item_sk = i.i_item_sk"
+ + " AND cs.cs_bill_cdemo_sk = cd.cd_demo_sk AND cs.cs_promo_sk = p.p_promo_sk"
+ + " AND cd.cd_gender = 'F' AND cd.cd_marital_status = 'M'"
+ + " AND (p.p_channel_email = 'N' OR p.p_channel_event = 'N') AND d.d_year = 2000"
+ + " GROUP BY i.i_item_id ORDER BY i.i_item_id LIMIT 100";
+
+ private static final String[][] Q03_EXPECTED =
+ { { "2000", "1002", "brand#1002", "41" }, { "2000", "1000", "brand#1000", "37" },
+ { "2000", "1005", "brand#1005", "36" }, { "2000", "1007", "brand#1007", "18" },
+ { "2000", "1004", "brand#1004", "5" }, { "2001", "1004", "brand#1004", "36" },
+ { "2001", "1002", "brand#1002", "21" }, { "2001", "1007", "brand#1007", "18" },
+ { "2001", "1000", "brand#1000", "11" }, { "2001", "1005", "brand#1005", "7" }, };
+ private static final String[][] Q07_EXPECTED = { { "ITEM0000000001", "3", "73.5", "16", "57.5" },
+ { "ITEM0000000003", "13", "30.5", "2", "27.5" }, { "ITEM0000000005", "7", "37.5", "8", "26.5" },
+ { "ITEM0000000007", "8", "72.5", "1", "67.5" },
+ { "ITEM0000000009", "14", "92.5", "10", "81.5" },
+ { "ITEM0000000011", "2", "106.5", "15", "98.5" },
+ { "ITEM0000000013", "5", "66.5", "10", "55.5" }, { "ITEM0000000015", "9", "34.5", "2", "15.5" },
+ { "ITEM0000000017", "8", "51.5", "14", "49.5" }, };
+ private static final String[][] Q42_EXPECTED = { { "2000", "5", "Jewelry", "748" },
+ { "2000", "3", "Electronics", "591" }, { "2000", "1", "Books", "61" }, };
+ private static final String[][] Q52_EXPECTED = { { "2000", "1002", "brand#1002", "748" },
+ { "2000", "1004", "brand#1004", "591" }, { "2000", "1000", "brand#1000", "61" }, };
+ private static final String[][] Q55_EXPECTED = { { "1001", "brand#1001", "456.5" },
+ { "1003", "brand#1003", "257.5" }, { "1005", "brand#1005", "82.5" }, };
+ private static final String[][] Q43_EXPECTED =
+ { { "Store 1", "STORE0000000001", "0", "0", "0", "0", "1638", "0", "0" },
+ { "Store 5", "STORE0000000005", "1229", "0", "0", "0", "0", "0", "0" }, };
+ private static final String[][] Q96_EXPECTED = { { "192" }, };
+ private static final String[][] Q19_EXPECTED =
+ { { "1002", "brand#1002", "1", "manufact#1", "748" },
+ { "1004", "brand#1004", "1", "manufact#1", "591" },
+ { "1000", "brand#1000", "1", "manufact#1", "61" }, };
+ private static final String[][] Q46_EXPECTED = { { "Last10", "First10", "City2", "45", "2338.5" },
+ { "Last14", "First14", "City6", "48", "2291.5" },
+ { "Last15", "First15", "City0", "29", "2357" }, { "Last18", "First18", "City3", "31", "2724" },
+ { "Last2", "First2", "City1", "24", "632" }, { "Last20", "First20", "City5", "45", "3686" },
+ { "Last22", "First22", "City1", "35", "2991" }, { "Last26", "First26", "City5", "57", "3988" },
+ { "Last27", "First27", "City6", "62", "1133" }, { "Last3", "First3", "City2", "26", "908.5" },
+ { "Last30", "First30", "City2", "27", "542" }, { "Last6", "First6", "City5", "42", "1937.5" },
+ { "Last8", "First8", "City0", "24", "701.5" }, };
+ private static final String[][] Q50_EXPECTED = { { "Store 1", "STORE0000000001", "24", "0", "0" },
+ { "Store 3", "STORE0000000003", "24", "0", "0" },
+ { "Store 5", "STORE0000000005", "24", "0", "0" }, };
+ private static final String[][] Q73_EXPECTED =
+ { { "Last1", "First1", "222", "1" }, { "Last1", "First1", "245", "1" },
+ { "Last1", "First1", "268", "1" }, { "Last10", "First10", "16", "1" },
+ { "Last10", "First10", "39", "1" }, { "Last10", "First10", "62", "1" },
+ { "Last10", "First10", "85", "1" }, { "Last11", "First11", "5", "1" },
+ { "Last11", "First11", "28", "1" }, { "Last11", "First11", "51", "1" },
+ { "Last11", "First11", "74", "1" }, { "Last11", "First11", "97", "1" },
+ { "Last12", "First12", "17", "1" }, { "Last12", "First12", "40", "1" },
+ { "Last12", "First12", "63", "1" }, { "Last12", "First12", "86", "1" },
+ { "Last12", "First12", "109", "1" }, { "Last13", "First13", "6", "1" },
+ { "Last13", "First13", "29", "1" }, { "Last13", "First13", "52", "1" }, };
+ private static final String[][] Q82_EXPECTED =
+ { { "ITEM0000000010", "Item description 10", "20.99" },
+ { "ITEM0000000011", "Item description 11", "21.99" },
+ { "ITEM0000000012", "Item description 12", "22.99" },
+ { "ITEM0000000013", "Item description 13", "23.99" },
+ { "ITEM0000000014", "Item description 14", "24.99" },
+ { "ITEM0000000015", "Item description 15", "25.99" },
+ { "ITEM0000000016", "Item description 16", "26.99" },
+ { "ITEM0000000017", "Item description 17", "27.99" },
+ { "ITEM0000000018", "Item description 18", "28.99" },
+ { "ITEM0000000019", "Item description 19", "29.99" },
+ { "ITEM0000000020", "Item description 20", "30.99" },
+ { "ITEM0000000021", "Item description 21", "31.99" },
+ { "ITEM0000000022", "Item description 22", "32.99" },
+ { "ITEM0000000023", "Item description 23", "33.99" },
+ { "ITEM0000000024", "Item description 24", "34.99" }, };
+ private static final String[][] Q21_EXPECTED =
+ { { "Warehouse 1", "ITEM0000000001", "1091", "1203" },
+ { "Warehouse 1", "ITEM0000000002", "1053", "1257" },
+ { "Warehouse 1", "ITEM0000000003", "1681", "1517" },
+ { "Warehouse 1", "ITEM0000000006", "1631", "1011" },
+ { "Warehouse 1", "ITEM0000000007", "1237", "1972" },
+ { "Warehouse 1", "ITEM0000000008", "1187", "1271" },
+ { "Warehouse 1", "ITEM0000000011", "1306", "1307" },
+ { "Warehouse 1", "ITEM0000000012", "587", "1511" },
+ { "Warehouse 1", "ITEM0000000013", "1212", "1372" },
+ { "Warehouse 1", "ITEM0000000016", "1611", "1589" },
+ { "Warehouse 1", "ITEM0000000017", "1242", "1589" },
+ { "Warehouse 1", "ITEM0000000018", "1012", "1618" },
+ { "Warehouse 1", "ITEM0000000021", "1582", "948" },
+ { "Warehouse 1", "ITEM0000000022", "1074", "1872" },
+ { "Warehouse 1", "ITEM0000000023", "1792", "1537" },
+ { "Warehouse 2", "ITEM0000000001", "831", "1683" },
+ { "Warehouse 2", "ITEM0000000002", "1462", "899" },
+ { "Warehouse 2", "ITEM0000000003", "1873", "1837" },
+ { "Warehouse 2", "ITEM0000000006", "1010", "1437" },
+ { "Warehouse 2", "ITEM0000000007", "1107", "1515" },
+ { "Warehouse 2", "ITEM0000000008", "1255", "1714" },
+ { "Warehouse 2", "ITEM0000000011", "1073", "941" },
+ { "Warehouse 2", "ITEM0000000012", "1936", "1490" },
+ { "Warehouse 2", "ITEM0000000013", "1015", "2189" },
+ { "Warehouse 2", "ITEM0000000016", "1331", "1388" },
+ { "Warehouse 2", "ITEM0000000017", "1721", "1268" },
+ { "Warehouse 2", "ITEM0000000018", "1434", "900" },
+ { "Warehouse 2", "ITEM0000000021", "1306", "1858" },
+ { "Warehouse 2", "ITEM0000000022", "946", "1806" },
+ { "Warehouse 2", "ITEM0000000023", "1159", "1383" }, };
+ private static final String[][] Q26_EXPECTED = { { "ITEM0000000003", "2", "109.5", "13", "92.5" },
+ { "ITEM0000000006", "8", "77.5", "6", "68.5" },
+ { "ITEM0000000009", "11", "76.5", "18", "57.5" }, { "ITEM0000000012", "16", "63.5", "9", "54" },
+ { "ITEM0000000015", "6", "32.5", "6", "18.5" },
+ { "ITEM0000000018", "18", "62.5", "11", "52.5" },
+ { "ITEM0000000021", "18", "9.5", "13", "5.5" },
+ { "ITEM0000000024", "17", "77.5", "12", "67.5" }, };
+
+ /** Label for {@link TPCDSLikeExpectedRegenerator}. */
+ public static Map<String, String> queries() {
+ Map<String, String> q = new LinkedHashMap<>();
+ q.put("Q03", Q03);
+ q.put("Q07", Q07);
+ q.put("Q42", Q42);
+ q.put("Q52", Q52);
+ q.put("Q55", Q55);
+ q.put("Q43", Q43);
+ q.put("Q96", Q96);
+ q.put("Q19", Q19);
+ q.put("Q46", Q46);
+ q.put("Q50", Q50);
+ q.put("Q73", Q73);
+ q.put("Q82", Q82);
+ q.put("Q21", Q21);
+ q.put("Q26", Q26);
+ return q;
+ }
+
+ @Test
+ public void testQ03() throws SQLException {
+ check("Q03", Q03, Q03_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "SS_I" });
+ }
+
+ @Test
+ public void testQ07() throws SQLException {
+ check("Q07", Q07, Q07_EXPECTED, "HASH BUILD");
+ }
+
+ @Test
+ public void testQ42() throws SQLException {
+ check("Q42", Q42, Q42_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "SS_I" });
+ }
+
+ @Test
+ public void testQ52() throws SQLException {
+ check("Q52", Q52, Q52_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "SS_I" });
+ }
+
+ @Test
+ public void testQ55() throws SQLException {
+ check("Q55", Q55, Q55_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "SS_I" });
+ }
+
+ @Test
+ public void testQ43() throws SQLException {
+ check("Q43", Q43, Q43_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "SS_I" });
+ }
+
+ @Test
+ public void testQ96() throws SQLException {
+ check("Q96", Q96, Q96_EXPECTED, "HASH BUILD");
+ }
+
+ @Test
+ public void testQ19() throws SQLException {
+ check("Q19", Q19, Q19_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "SS_I" });
+ }
+
+ @Test
+ public void testQ46() throws SQLException {
+ check("Q46", Q46, Q46_EXPECTED, "HASH BUILD");
+ }
+
+ @Test
+ public void testQ50() throws SQLException {
+ check("Q50", Q50, Q50_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "SS_I" });
+ }
+
+ @Test
+ public void testQ73() throws SQLException {
+ check("Q73", Q73, Q73_EXPECTED, "HASH BUILD");
+ }
+
+ @Test
+ public void testQ82() throws SQLException {
+ check("Q82", Q82, Q82_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "INV_I" });
+ }
+
+ @Test
+ public void testQ21() throws SQLException {
+ check("Q21", Q21, Q21_EXPECTED, new String[] { "HASH BUILD" }, new String[] { "INV_I" });
+ }
+
+ @Test
+ public void testQ26() throws SQLException {
+ check("Q26", Q26, Q26_EXPECTED, "HASH BUILD");
+ }
+}
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
index 8100742e67..495363b3b5 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
@@ -2552,14 +2552,16 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
assertPlan(conn, "SELECT k,j from t3 b join t1 a ON k = j where a.col1 || a.col2 = 'foobar'")
.scanType("FULL SCAN").table("T3").serverFirstKeyOnlyProjection(true)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY B.J IN (\"A.:K\")").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("RANGE SCAN")
- .table("IDX").keyRanges(" ['foobar']").serverFirstKeyOnlyProjection(true).end();
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table("IDX").keyRanges(" ['foobar']")
+ .serverFirstKeyOnlyProjection(true).end();
assertPlan(conn,
"SELECT a.k,b.k from t2 b join t1 a ON a.k = b.k where a.col1 || a.col2 = 'foobar'")
.scanType("FULL SCAN").table("T2").serverFirstKeyOnlyProjection(true)
.dynamicServerFilter("DYNAMIC SERVER FILTER BY B.K IN (\"A.:K\")").subPlanCount(1)
- .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0").scanType("RANGE SCAN")
- .table("IDX").keyRanges(" ['foobar']").serverFirstKeyOnlyProjection(true).end();
+ .subPlan(0).abstractExplainPlan("PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .scanType("RANGE SCAN").table("IDX").keyRanges(" ['foobar']")
+ .serverFirstKeyOnlyProjection(true).end();
} finally {
conn.close();
}
@@ -7215,7 +7217,8 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
.serverSortedBy("[D.V2]").clientSortAlgo("CLIENT MERGE SORT")
.dynamicServerFilter("DYNAMIC SERVER FILTER BY (\"D.K1\", \"D.K2\", \"D.K3\", \"D.K4\")"
+ " IN (($2.$4, $2.$5, $2.$6, $2.$7))")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("SKIP-SCAN-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("SKIP-SCAN-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("RANGE SCAN").table("I").keyRanges(" ['XXX']").serverFirstKeyOnlyProjection(true)
.end();
}
@@ -7244,7 +7247,8 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
.dynamicServerFilter("DYNAMIC SERVER FILTER BY (\"TAB_PHOENIX_6986.K1\","
+ " \"TAB_PHOENIX_6986.K2\", \"TAB_PHOENIX_6986.K3\", \"TAB_PHOENIX_6986.K4\")"
+ " IN (($2.$4, $2.$5, $2.$6, $2.$7))")
- .subPlanCount(1).subPlan(0).abstractExplainPlan("SKIP-SCAN-JOIN TABLE 0")
+ .subPlanCount(1).subPlan(0)
+ .abstractExplainPlan("SKIP-SCAN-JOIN TABLE 0 /* HASH BUILD RIGHT */")
.scanType("RANGE SCAN").table("IDX_PHOENIX_6986").keyRanges(" ['XXX']")
.serverFirstKeyOnlyProjection(true).end();
}
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeExpectedRegenerator.java b/phoenix-core/src/test/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeExpectedRegenerator.java
new file mode 100644
index 0000000000..e6f424939c
--- /dev/null
+++ b/phoenix-core/src/test/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeExpectedRegenerator.java
@@ -0,0 +1,63 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.util.LinkedHashMap;
+import java.util.Map;
+
+/**
+ * Developer only tool that prints the embedded {@code <LABEL>_EXPECTED} arrays for every query in
+ * {@link TPCDSLikeSingleChannelIT} and {@link TPCDSLikeCrossChannelIT}.
+ */
+public final class TPCDSLikeExpectedRegenerator {
+
+ private TPCDSLikeExpectedRegenerator() {
+ }
+
+ public static void main(String[] args) throws Exception {
+ if (args.length == 0) {
+ System.out.println(usage());
+ return;
+ }
+ String url = args[0];
+ Map<String, String> all = new LinkedHashMap<>();
+ all.putAll(TPCDSLikeSingleChannelIT.queries());
+ all.putAll(TPCDSLikeCrossChannelIT.queries());
+ try (Connection conn = DriverManager.getConnection(url)) {
+ TPCDSLikeFixture.load(conn);
+ System.out.println("// ===== TPCDSLikeSingleChannelIT / TPCDSLikeCrossChannelIT expected"
+ + " arrays (regenerated) =====");
+ for (Map.Entry<String, String> e : all.entrySet()) {
+ // Queries are written against TPCDSLikeFixture.SCHEMA, which is what load() populates.
+ System.out.println(TPCDSLikeAssertions.captureLiteral(conn, e.getKey(), e.getValue()));
+ }
+ }
+ }
+
+ private static String usage() {
+ return "TPCDSLikeExpectedRegenerator: prints paste-ready *_EXPECTED arrays.\n\n"
+ + "Recommended (uses the test mini-cluster, no external setup):\n"
+ + " mvn -pl phoenix-core verify \\\n"
+ + " -Dit.test=TPCDSLikeSingleChannelIT,TPCDSLikeCrossChannelIT \\\n" + " -D"
+ + TPCDSLikeAssertions.REGENERATE_PROPERTY + "=true\n\n"
+ + "Against a standalone Phoenix cluster:\n" + " java ... "
+ + TPCDSLikeExpectedRegenerator.class.getName() + " <jdbc-url>\n";
+ }
+}
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/explain/ExplainPlanTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/explain/ExplainPlanTest.java
index 5445b5b855..8434f2040e 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/query/explain/ExplainPlanTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/query/explain/ExplainPlanTest.java
@@ -483,7 +483,7 @@ public class ExplainPlanTest extends BaseConnectionlessQueryTest {
"SELECT /*+ USE_SORT_MERGE_JOIN */ a.a_string, b.a_string FROM atable a"
+ " JOIN atable b ON a.organization_id = b.organization_id"
+ " WHERE a.organization_id = '00D000000000001'",
- text("SORT-MERGE-JOIN (INNER) TABLES",
+ text("SORT-MERGE-JOIN (INNER) TABLES /* SORT_MERGE */",
" CLIENT PARALLEL <N>-WAY RANGE SCAN OVER ATABLE ['00D000000000001']",
" INDEX ATABLE", " REGIONS PLANNED <N>", "AND",
" CLIENT PARALLEL <N>-WAY FULL SCAN OVER ATABLE", " INDEX ATABLE",
@@ -496,7 +496,7 @@ public class ExplainPlanTest extends BaseConnectionlessQueryTest {
// HashJoinPlan root attributes come from the delegate scan. Each hash/skip-scan child
// is recorded under subPlans with its join header on abstractExplainPlan.
ObjectNode child = scanAttrs("FULL SCAN ", "ATABLE", "").put("abstractExplainPlan",
- "PARALLEL INNER-JOIN TABLE 0");
+ "PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */");
ObjectNode expected = scanAttrs("RANGE SCAN ", "ATABLE", " ['00D000000000001']");
expected.set("subPlans", mapper.createArrayNode().add(child));
expected.put("dynamicServerFilter",
@@ -506,7 +506,7 @@ public class ExplainPlanTest extends BaseConnectionlessQueryTest {
+ " JOIN atable b ON a.organization_id = b.organization_id"
+ " WHERE a.organization_id = '00D000000000001'",
text("CLIENT PARALLEL <N>-WAY RANGE SCAN OVER ATABLE ['00D000000000001']", " INDEX ATABLE",
- " REGIONS PLANNED <N>", " PARALLEL INNER-JOIN TABLE 0",
+ " REGIONS PLANNED <N>", " PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */",
" CLIENT PARALLEL <N>-WAY FULL SCAN OVER ATABLE", " INDEX ATABLE",
" REGIONS PLANNED <N>",
" DYNAMIC SERVER FILTER BY A.ORGANIZATION_ID IN (B.ORGANIZATION_ID)"),
@@ -517,10 +517,10 @@ public class ExplainPlanTest extends BaseConnectionlessQueryTest {
public void testHashJoinSemiInSubquery() throws Exception {
// Phoenix temp aliases are renamed by first appearance so this case asserts on the canonical
// "$1.$2" form.
- ObjectNode child =
- scanAttrs("FULL SCAN ", "ATABLE", "").put("abstractExplainPlan", "SKIP-SCAN-JOIN TABLE 0")
- .put("serverWhereFilter", "SERVER FILTER BY A_INTEGER = 1")
- .put("serverAggregate", "SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [ORGANIZATION_ID]");
+ ObjectNode child = scanAttrs("FULL SCAN ", "ATABLE", "")
+ .put("abstractExplainPlan", "SKIP-SCAN-JOIN TABLE 0 /* HASH BUILD RIGHT */")
+ .put("serverWhereFilter", "SERVER FILTER BY A_INTEGER = 1")
+ .put("serverAggregate", "SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [ORGANIZATION_ID]");
ObjectNode expected = scanAttrs("FULL SCAN ", "ATABLE", "");
expected.set("subPlans", mapper.createArrayNode().add(child));
expected.put("dynamicServerFilter",
@@ -529,7 +529,7 @@ public class ExplainPlanTest extends BaseConnectionlessQueryTest {
"SELECT a_string FROM atable"
+ " WHERE organization_id IN (SELECT organization_id FROM atable WHERE a_integer = 1)",
text("CLIENT PARALLEL <N>-WAY FULL SCAN OVER ATABLE", " INDEX ATABLE",
- " REGIONS PLANNED <N>", " SKIP-SCAN-JOIN TABLE 0",
+ " REGIONS PLANNED <N>", " SKIP-SCAN-JOIN TABLE 0 /* HASH BUILD RIGHT */",
" CLIENT PARALLEL <N>-WAY FULL SCAN OVER ATABLE", " INDEX ATABLE",
" REGIONS PLANNED <N>", " SERVER FILTER BY A_INTEGER = 1",
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [ORGANIZATION_ID]",
@@ -539,7 +539,13 @@ public class ExplainPlanTest extends BaseConnectionlessQueryTest {
@Test
public void testUnionAll() throws Exception {
+ // The union composes each branch recursively from its own getExplainPlan() into the subPlans
+ // list.
+ ObjectNode lhs = scanAttrs("RANGE SCAN ", "ATABLE", " ['00D000000000001']");
ObjectNode rhs = scanAttrs("RANGE SCAN ", "ATABLE", " ['00D000000000002']");
+ ObjectNode expected = defaultAttrs();
+ expected.put("abstractExplainPlan", "UNION ALL OVER 2 QUERIES");
+ expected.set("subPlans", mapper.createArrayNode().add(lhs).add(rhs));
verifyQuery("unionAll",
"SELECT a_string FROM atable WHERE organization_id = '00D000000000001'" + " UNION ALL"
+ " SELECT a_string FROM atable WHERE organization_id = '00D000000000002'",
@@ -548,9 +554,50 @@ public class ExplainPlanTest extends BaseConnectionlessQueryTest {
" INDEX ATABLE", " REGIONS PLANNED <N>",
" CLIENT PARALLEL <N>-WAY RANGE SCAN OVER ATABLE ['00D000000000002']",
" INDEX ATABLE", " REGIONS PLANNED <N>"),
- scanAttrs("RANGE SCAN ", "ATABLE", " ['00D000000000001']")
- .put("abstractExplainPlan", "UNION ALL OVER 2 QUERIES")
- .set("rhsJoinQueryExplainPlan", rhs));
+ expected);
+ }
+
+ @Test
+ public void testUnionAllOfHashJoins() throws Exception {
+ // A UNION ALL whose branches are each hash joins. Exercises recursive explain composition end
+ // to end and
+ // confirms each branch carries its own subPlans and dynamicServerFilter.
+ ObjectNode joinChild1 = scanAttrs("FULL SCAN ", "ATABLE", "").put("abstractExplainPlan",
+ "PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */");
+ ObjectNode branch1 = scanAttrs("RANGE SCAN ", "ATABLE", " ['00D000000000001']");
+ branch1.set("subPlans", mapper.createArrayNode().add(joinChild1));
+ branch1.put("dynamicServerFilter",
+ "DYNAMIC SERVER FILTER BY A.ORGANIZATION_ID IN (B.ORGANIZATION_ID)");
+ ObjectNode joinChild2 = scanAttrs("FULL SCAN ", "ATABLE", "").put("abstractExplainPlan",
+ "PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */");
+ ObjectNode branch2 = scanAttrs("RANGE SCAN ", "ATABLE", " ['00D000000000002']");
+ branch2.set("subPlans", mapper.createArrayNode().add(joinChild2));
+ branch2.put("dynamicServerFilter",
+ "DYNAMIC SERVER FILTER BY A.ORGANIZATION_ID IN (B.ORGANIZATION_ID)");
+ ObjectNode expected = defaultAttrs();
+ expected.put("abstractExplainPlan", "UNION ALL OVER 2 QUERIES");
+ expected.set("subPlans", mapper.createArrayNode().add(branch1).add(branch2));
+ verifyQuery("unionAllOfHashJoins",
+ "SELECT a.a_string AS s1, b.a_string AS s2 FROM atable a"
+ + " JOIN atable b ON a.organization_id = b.organization_id"
+ + " WHERE a.organization_id = '00D000000000001'" + " UNION ALL"
+ + " SELECT a.a_string AS s1, b.a_string AS s2 FROM atable a"
+ + " JOIN atable b ON a.organization_id = b.organization_id"
+ + " WHERE a.organization_id = '00D000000000002'",
+ text("UNION ALL OVER 2 QUERIES",
+ " CLIENT PARALLEL <N>-WAY RANGE SCAN OVER ATABLE ['00D000000000001']",
+ " INDEX ATABLE", " REGIONS PLANNED <N>",
+ " PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */",
+ " CLIENT PARALLEL <N>-WAY FULL SCAN OVER ATABLE", " INDEX ATABLE",
+ " REGIONS PLANNED <N>",
+ " DYNAMIC SERVER FILTER BY A.ORGANIZATION_ID IN (B.ORGANIZATION_ID)",
+ " CLIENT PARALLEL <N>-WAY RANGE SCAN OVER ATABLE ['00D000000000002']",
+ " INDEX ATABLE", " REGIONS PLANNED <N>",
+ " PARALLEL INNER-JOIN TABLE 0 /* HASH BUILD RIGHT */",
+ " CLIENT PARALLEL <N>-WAY FULL SCAN OVER ATABLE", " INDEX ATABLE",
+ " REGIONS PLANNED <N>",
+ " DYNAMIC SERVER FILTER BY A.ORGANIZATION_ID IN (B.ORGANIZATION_ID)"),
+ expected);
}
@Test