You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by jc...@apache.org on 2018/11/11 06:03:25 UTC
[6/6] hive git commit: HIVE-20744: Use SQL constraints to improve
join reordering algorithm (Jesus Camacho Rodriguez,
reviewed by Ashutosh Chauhan)
HIVE-20744: Use SQL constraints to improve join reordering algorithm (Jesus Camacho Rodriguez, reviewed by Ashutosh Chauhan)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/f8b4c869
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/f8b4c869
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/f8b4c869
Branch: refs/heads/master
Commit: f8b4c8698a0a7984ca15d73dcac2d6609df3cf8f
Parents: c049cac
Author: Jesus Camacho Rodriguez <jc...@apache.org>
Authored: Fri Nov 2 08:48:00 2018 -0700
Committer: Jesus Camacho Rodriguez <jc...@apache.org>
Committed: Sat Nov 10 22:02:29 2018 -0800
----------------------------------------------------------------------
.../ql/optimizer/calcite/HiveRelOptUtil.java | 267 +++++-
.../calcite/rules/HiveJoinConstraintsRule.java | 7 +-
.../calcite/stats/HiveRelMdRowCount.java | 261 ++++--
.../calcite/stats/HiveRelMdSelectivity.java | 3 +-
.../perf/tez/constraints/cbo_query17.q.out | 58 +-
.../perf/tez/constraints/cbo_query24.q.out | 71 +-
.../perf/tez/constraints/cbo_query25.q.out | 44 +-
.../perf/tez/constraints/cbo_query54.q.out | 98 +-
.../perf/tez/constraints/cbo_query64.q.out | 240 ++---
.../perf/tez/constraints/cbo_query72.q.out | 30 +-
.../perf/tez/constraints/cbo_query84.q.out | 34 +-
.../perf/tez/constraints/query17.q.out | 388 ++++----
.../perf/tez/constraints/query24.q.out | 416 ++++-----
.../perf/tez/constraints/query25.q.out | 392 ++++----
.../perf/tez/constraints/query54.q.out | 450 +++++-----
.../perf/tez/constraints/query64.q.out | 900 ++++++++++---------
.../perf/tez/constraints/query72.q.out | 304 +++----
.../perf/tez/constraints/query84.q.out | 118 +--
18 files changed, 2235 insertions(+), 1846 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/f8b4c869/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRelOptUtil.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRelOptUtil.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRelOptUtil.java
index c955804..e99e6d3 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRelOptUtil.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HiveRelOptUtil.java
@@ -17,12 +17,14 @@
*/
package org.apache.hadoop.hive.ql.optimizer.calcite;
+import com.google.common.collect.HashMultimap;
import com.google.common.collect.Multimap;
import com.google.common.collect.Sets;
import java.util.AbstractList;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
+import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
@@ -64,6 +66,7 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Pair;
+import org.apache.commons.lang3.tuple.Triple;
import org.apache.hadoop.hive.ql.exec.FunctionRegistry;
import org.apache.hadoop.hive.ql.optimizer.calcite.translator.TypeConverter;
import org.apache.hadoop.hive.serde2.typeinfo.TypeInfo;
@@ -538,22 +541,213 @@ public class HiveRelOptUtil extends RelOptUtil {
return false;
}
- public static Pair<Boolean, List<RexNode>> isRewritablePKFKJoin(RelBuilder builder, Join join,
+ /**
+ * Returns a triple where first value represents whether we could extract a FK-PK join
+ * or not, the second value is a pair with the column from left and right input that
+ * are used for the FK-PK join, and the third value are the predicates that are not
+ * part of the FK-PK condition. Currently we can only extract one FK-PK join.
+ */
+ public static PKFKJoinInfo extractPKFKJoin(
+ Join join, List<RexNode> joinFilters, boolean leftInputPotentialFK, RelMetadataQuery mq) {
+ final List<RexNode> residualPreds = new ArrayList<>();
+ final JoinRelType joinType = join.getJoinType();
+ final RelNode fkInput = leftInputPotentialFK ? join.getLeft() : join.getRight();
+ final PKFKJoinInfo cannotExtract =
+ PKFKJoinInfo.of(false, null, null);
+
+ if (joinType != JoinRelType.INNER) {
+ // If it is not an inner, we transform it as the metadata
+ // providers for expressions do not pull information through
+ // outer join (as it would not be correct)
+ join = join.copy(join.getTraitSet(), join.getCluster().getRexBuilder().makeLiteral(true),
+ join.getLeft(), join.getRight(), JoinRelType.INNER, false);
+ }
+
+ // 1) Gather all tables from the FK side and the table from the
+ // non-FK side
+ final Set<RelTableRef> leftTables = mq.getTableReferences(join.getLeft());
+ final Set<RelTableRef> rightTables =
+ Sets.difference(mq.getTableReferences(join), mq.getTableReferences(join.getLeft()));
+ final Set<RelTableRef> fkTables = join.getLeft() == fkInput ? leftTables : rightTables;
+ final Set<RelTableRef> nonFkTables = join.getLeft() == fkInput ? rightTables : leftTables;
+
+ // 2) Check whether there is a FK relationship
+ Set<RexCall> candidatePredicates = new HashSet<>();
+ EquivalenceClasses ec = new EquivalenceClasses();
+ for (RexNode conj : joinFilters) {
+ if (!conj.isA(SqlKind.EQUALS)) {
+ // Not an equality, continue
+ residualPreds.add(conj);
+ continue;
+ }
+ RexCall equiCond = (RexCall) conj;
+ RexNode eqOp1 = equiCond.getOperands().get(0);
+ if (!RexUtil.isReferenceOrAccess(eqOp1, true)) {
+ // Ignore
+ residualPreds.add(conj);
+ continue;
+ }
+ Set<RexNode> eqOp1ExprsLineage = mq.getExpressionLineage(join, eqOp1);
+ if (eqOp1ExprsLineage == null) {
+ // Cannot be mapped, continue
+ residualPreds.add(conj);
+ continue;
+ }
+ RexNode eqOp2 = equiCond.getOperands().get(1);
+ if (!RexUtil.isReferenceOrAccess(eqOp2, true)) {
+ // Ignore
+ residualPreds.add(conj);
+ continue;
+ }
+ Set<RexNode> eqOp2ExprsLineage = mq.getExpressionLineage(join, eqOp2);
+ if (eqOp2ExprsLineage == null) {
+ // Cannot be mapped, continue
+ residualPreds.add(conj);
+ continue;
+ }
+ List<RexTableInputRef> eqOp2ExprsFiltered = null;
+ for (RexNode eqOpExprLineage1 : eqOp1ExprsLineage) {
+ RexTableInputRef inputRef1 = extractTableInputRef(eqOpExprLineage1);
+ if (inputRef1 == null) {
+ // This condition could not be map into an input reference
+ continue;
+ }
+ if (eqOp2ExprsFiltered == null) {
+ // First iteration
+ eqOp2ExprsFiltered = new ArrayList<>();
+ for (RexNode eqOpExprLineage2 : eqOp2ExprsLineage) {
+ RexTableInputRef inputRef2 = extractTableInputRef(eqOpExprLineage2);
+ if (inputRef2 == null) {
+ // Bail out as this condition could not be map into an input reference
+ continue;
+ }
+ // Add to list of expressions for follow-up iterations
+ eqOp2ExprsFiltered.add(inputRef2);
+ // Add to equivalence classes and backwards mapping
+ ec.addEquivalence(inputRef1, inputRef2, equiCond);
+ candidatePredicates.add(equiCond);
+ }
+ } else {
+ // Rest of iterations, only adding, no checking
+ for (RexTableInputRef inputRef2 : eqOp2ExprsFiltered) {
+ ec.addEquivalence(inputRef1, inputRef2, equiCond);
+ }
+ }
+ }
+ if (!candidatePredicates.contains(conj)) {
+ // We add it to residual already
+ residualPreds.add(conj);
+ }
+ }
+ if (ec.getEquivalenceClassesMap().isEmpty()) {
+ // This may be a cartesian product, we bail out
+ return cannotExtract;
+ }
+
+ // 4) For each table, check whether there is a matching on the non-FK side.
+ // If there is and it is the only condition, we are ready to transform
+ for (final RelTableRef nonFkTable : nonFkTables) {
+ final List<String> nonFkTableQName = nonFkTable.getQualifiedName();
+ for (RelTableRef tRef : fkTables) {
+ List<RelReferentialConstraint> constraints = tRef.getTable().getReferentialConstraints();
+ for (RelReferentialConstraint constraint : constraints) {
+ if (constraint.getTargetQualifiedName().equals(nonFkTableQName)) {
+ EquivalenceClasses ecT = EquivalenceClasses.copy(ec);
+ Set<RexNode> removedOriginalPredicates = new HashSet<>();
+ ImmutableBitSet.Builder lBitSet = ImmutableBitSet.builder();
+ ImmutableBitSet.Builder rBitSet = ImmutableBitSet.builder();
+ boolean allContained = true;
+ for (int pos = 0; pos < constraint.getNumColumns(); pos++) {
+ int foreignKeyPos = constraint.getColumnPairs().get(pos).source;
+ RelDataType foreignKeyColumnType =
+ tRef.getTable().getRowType().getFieldList().get(foreignKeyPos).getType();
+ RexTableInputRef foreignKeyColumnRef =
+ RexTableInputRef.of(tRef, foreignKeyPos, foreignKeyColumnType);
+ int uniqueKeyPos = constraint.getColumnPairs().get(pos).target;
+ RexTableInputRef uniqueKeyColumnRef = RexTableInputRef.of(nonFkTable, uniqueKeyPos,
+ nonFkTable.getTable().getRowType().getFieldList().get(uniqueKeyPos).getType());
+ if (ecT.getEquivalenceClassesMap().containsKey(uniqueKeyColumnRef) &&
+ ecT.getEquivalenceClassesMap().get(uniqueKeyColumnRef).contains(foreignKeyColumnRef)) {
+ // Remove this condition from eq classes as we have checked that it is present
+ // in the join condition. In turn, populate the columns that are referenced
+ // from the join inputs
+ for (RexCall originalPred : ecT.removeEquivalence(uniqueKeyColumnRef, foreignKeyColumnRef)) {
+ ImmutableBitSet leftCols = RelOptUtil.InputFinder.bits(originalPred.getOperands().get(0));
+ ImmutableBitSet rightCols = RelOptUtil.InputFinder.bits(originalPred.getOperands().get(1));
+ // Get length and flip column references if join condition specified in
+ // reverse order to join sources
+ int nFieldsLeft = join.getLeft().getRowType().getFieldList().size();
+ int nFieldsRight = join.getRight().getRowType().getFieldList().size();
+ int nSysFields = join.getSystemFieldList().size();
+ ImmutableBitSet rightFieldsBitSet = ImmutableBitSet.range(nSysFields + nFieldsLeft,
+ nSysFields + nFieldsLeft + nFieldsRight);
+ if (rightFieldsBitSet.contains(leftCols)) {
+ ImmutableBitSet t = leftCols;
+ leftCols = rightCols;
+ rightCols = t;
+ }
+ lBitSet.set(leftCols.nextSetBit(0) - nSysFields);
+ rBitSet.set(rightCols.nextSetBit(0) - (nSysFields + nFieldsLeft));
+ removedOriginalPredicates.add(originalPred);
+ }
+ } else {
+ // No relationship, we cannot do anything
+ allContained = false;
+ break;
+ }
+ }
+ if (allContained) {
+ // This is a PK-FK, reassign equivalence classes and remove conditions
+ // TODO: Support inference of multiple PK-FK relationships
+
+ // 4.1) Add to residual whatever is remaining
+ candidatePredicates.removeAll(removedOriginalPredicates);
+ residualPreds.addAll(candidatePredicates);
+ // 4.2) Return result
+ return PKFKJoinInfo.of(true, Pair.of(lBitSet.build(), rBitSet.build()), residualPreds);
+ }
+ }
+ }
+ }
+ }
+
+ return cannotExtract;
+ }
+
+ public static class PKFKJoinInfo {
+ public final boolean isPkFkJoin;
+ public final Pair<ImmutableBitSet, ImmutableBitSet> pkFkJoinColumns;
+ public final List<RexNode> additionalPredicates;
+
+ private PKFKJoinInfo(boolean isPkFkJoin, Pair<ImmutableBitSet, ImmutableBitSet> pkFkJoinColumns,
+ List<RexNode> additionalPredicates) {
+ this.isPkFkJoin = isPkFkJoin;
+ this.pkFkJoinColumns = pkFkJoinColumns;
+ this.additionalPredicates = additionalPredicates == null ? null :
+ ImmutableList.copyOf(additionalPredicates);
+ }
+
+ public static PKFKJoinInfo of(boolean isPkFkJoin, Pair<ImmutableBitSet, ImmutableBitSet> pkFkJoinColumns,
+ List<RexNode> additionalPredicates) {
+ return new PKFKJoinInfo(isPkFkJoin, pkFkJoinColumns, additionalPredicates);
+ }
+ }
+
+ public static RewritablePKFKJoinInfo isRewritablePKFKJoin(Join join,
boolean leftInputPotentialFK, RelMetadataQuery mq) {
final JoinRelType joinType = join.getJoinType();
final RexNode cond = join.getCondition();
final RelNode fkInput = leftInputPotentialFK ? join.getLeft() : join.getRight();
final RelNode nonFkInput = leftInputPotentialFK ? join.getRight() : join.getLeft();
- final Pair<Boolean, List<RexNode>> nonRewritable = Pair.of(false, null);
+ final RewritablePKFKJoinInfo nonRewritable = RewritablePKFKJoinInfo.of(false, null);
if (joinType != JoinRelType.INNER) {
// If it is not an inner, we transform it as the metadata
// providers for expressions do not pull information through
// outer join (as it would not be correct)
- join = (Join) builder
- .push(join.getLeft()).push(join.getRight())
- .join(JoinRelType.INNER, cond)
- .build();
+ join = join.copy(join.getTraitSet(), cond,
+ join.getLeft(), join.getRight(), JoinRelType.INNER,
+ false);
}
// 1) Check whether there is any filtering condition on the
@@ -604,13 +798,13 @@ public class HiveRelOptUtil extends RelOptUtil {
// Add to list of expressions for follow-up iterations
eqOp2ExprsFiltered.add(inputRef2);
// Add to equivalence classes and backwards mapping
- ec.addEquivalenceClass(inputRef1, inputRef2);
+ ec.addEquivalence(inputRef1, inputRef2);
refToRex.put(inputRef2, eqOp2);
}
} else {
// Rest of iterations, only adding, no checking
for (RexTableInputRef inputRef2 : eqOp2ExprsFiltered) {
- ec.addEquivalenceClass(inputRef1, inputRef2);
+ ec.addEquivalence(inputRef1, inputRef2);
}
}
}
@@ -667,14 +861,7 @@ public class HiveRelOptUtil extends RelOptUtil {
}
// Remove this condition from eq classes as we have checked that it is present
// in the join condition
- ecT.getEquivalenceClassesMap().get(uniqueKeyColumnRef).remove(foreignKeyColumnRef);
- if (ecT.getEquivalenceClassesMap().get(uniqueKeyColumnRef).size() == 1) { // self
- ecT.getEquivalenceClassesMap().remove(uniqueKeyColumnRef);
- }
- ecT.getEquivalenceClassesMap().get(foreignKeyColumnRef).remove(uniqueKeyColumnRef);
- if (ecT.getEquivalenceClassesMap().get(foreignKeyColumnRef).size() == 1) { // self
- ecT.getEquivalenceClassesMap().remove(foreignKeyColumnRef);
- }
+ ecT.removeEquivalence(uniqueKeyColumnRef, foreignKeyColumnRef);
} else {
// No relationship, we cannot do anything
allContained = false;
@@ -690,7 +877,22 @@ public class HiveRelOptUtil extends RelOptUtil {
}
}
- return Pair.of(canBeRewritten, nullableNodes);
+ return RewritablePKFKJoinInfo.of(canBeRewritten, nullableNodes);
+ }
+
+ public static class RewritablePKFKJoinInfo {
+ public final boolean rewritable;
+ public final List<RexNode> nullableNodes;
+
+ private RewritablePKFKJoinInfo(boolean rewritable, List<RexNode> nullableNodes) {
+ this.rewritable = rewritable;
+ this.nullableNodes = nullableNodes == null ? null :
+ ImmutableList.copyOf(nullableNodes);
+ }
+
+ public static RewritablePKFKJoinInfo of(boolean rewritable, List<RexNode> nullableNodes) {
+ return new RewritablePKFKJoinInfo(rewritable, nullableNodes);
+ }
}
private static RexTableInputRef extractTableInputRef(RexNode node) {
@@ -713,13 +915,23 @@ public class HiveRelOptUtil extends RelOptUtil {
*/
private static class EquivalenceClasses {
+ // Contains the node to equivalence class nodes
private final Map<RexTableInputRef, Set<RexTableInputRef>> nodeToEquivalenceClass;
+ // Contains the pair of equivalences to original expression that they originate from
+ private final Multimap<Pair<RexTableInputRef, RexTableInputRef>, RexCall> equivalenceToOriginalNode;
protected EquivalenceClasses() {
nodeToEquivalenceClass = new HashMap<>();
+ equivalenceToOriginalNode = HashMultimap.create();
+ }
+
+ protected void addEquivalence(RexTableInputRef p1, RexTableInputRef p2, RexCall originalCond) {
+ addEquivalence(p1, p2);
+ equivalenceToOriginalNode.put(Pair.of(p1, p2), originalCond);
+ equivalenceToOriginalNode.put(Pair.of(p2, p1), originalCond);
}
- protected void addEquivalenceClass(RexTableInputRef p1, RexTableInputRef p2) {
+ protected void addEquivalence(RexTableInputRef p1, RexTableInputRef p2) {
Set<RexTableInputRef> c1 = nodeToEquivalenceClass.get(p1);
Set<RexTableInputRef> c2 = nodeToEquivalenceClass.get(p2);
if (c1 != null && c2 != null) {
@@ -756,11 +968,30 @@ public class HiveRelOptUtil extends RelOptUtil {
return nodeToEquivalenceClass;
}
+ // Returns the original nodes that the equivalences were generated from
+ protected Set<RexCall> removeEquivalence(RexTableInputRef p1, RexTableInputRef p2) {
+ nodeToEquivalenceClass.get(p1).remove(p2);
+ if (nodeToEquivalenceClass.get(p1).size() == 1) { // self
+ nodeToEquivalenceClass.remove(p1);
+ }
+ nodeToEquivalenceClass.get(p2).remove(p1);
+ if (nodeToEquivalenceClass.get(p2).size() == 1) { // self
+ nodeToEquivalenceClass.remove(p2);
+ }
+ Set<RexCall> originalNodes = new HashSet<>();
+ originalNodes.addAll(equivalenceToOriginalNode.removeAll(Pair.of(p1, p2)));
+ originalNodes.addAll(equivalenceToOriginalNode.removeAll(Pair.of(p2, p1)));
+ return originalNodes;
+ }
+
protected static EquivalenceClasses copy(EquivalenceClasses ec) {
final EquivalenceClasses newEc = new EquivalenceClasses();
for (Entry<RexTableInputRef, Set<RexTableInputRef>> e : ec.nodeToEquivalenceClass.entrySet()) {
newEc.nodeToEquivalenceClass.put(e.getKey(), Sets.newLinkedHashSet(e.getValue()));
}
+ for (Entry<Pair<RexTableInputRef, RexTableInputRef>, Collection<RexCall>> e : ec.equivalenceToOriginalNode.asMap().entrySet()) {
+ newEc.equivalenceToOriginalNode.putAll(e.getKey(), e.getValue());
+ }
return newEc;
}
}
http://git-wip-us.apache.org/repos/asf/hive/blob/f8b4c869/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveJoinConstraintsRule.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveJoinConstraintsRule.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveJoinConstraintsRule.java
index 534a5c9..c735df8 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveJoinConstraintsRule.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveJoinConstraintsRule.java
@@ -59,6 +59,7 @@ import org.apache.calcite.util.mapping.MappingType;
import org.apache.calcite.util.mapping.Mappings;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelOptUtil;
+import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelOptUtil.RewritablePKFKJoinInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -220,12 +221,12 @@ public class HiveJoinConstraintsRule extends RelOptRule {
}
// 2) Check whether this join can be rewritten or removed
- Pair<Boolean, List<RexNode>> r = HiveRelOptUtil.isRewritablePKFKJoin(call.builder(),
+ RewritablePKFKJoinInfo r = HiveRelOptUtil.isRewritablePKFKJoin(
join, leftInput == fkInput, call.getMetadataQuery());
// 3) If it is the only condition, we can trigger the rewriting
- if (r.left) {
- List<RexNode> nullableNodes = r.right;
+ if (r.rewritable) {
+ List<RexNode> nullableNodes = r.nullableNodes;
// If we reach here, we trigger the transform
if (mode == Mode.REMOVE) {
if (rightInputPotentialFK) {
http://git-wip-us.apache.org/repos/asf/hive/blob/f8b4c869/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdRowCount.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdRowCount.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdRowCount.java
index 576ed34..563260e 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdRowCount.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdRowCount.java
@@ -34,6 +34,7 @@ import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider;
import org.apache.calcite.rel.metadata.RelMdRowCount;
+import org.apache.calcite.rel.metadata.RelMdUtil;
import org.apache.calcite.rel.metadata.RelMetadataProvider;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rex.RexBuilder;
@@ -46,6 +47,8 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.util.BuiltInMethod;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Pair;
+import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelOptUtil;
+import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelOptUtil.PKFKJoinInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveTableScan;
@@ -63,24 +66,48 @@ public class HiveRelMdRowCount extends RelMdRowCount {
}
public Double getRowCount(Join join, RelMetadataQuery mq) {
- PKFKRelationInfo pkfk = analyzeJoinForPKFK(join, mq);
+ // Try to infer from constraints first
+ final Pair<PKFKRelationInfo, RexNode> constraintBasedResult =
+ constraintsBasedAnalyzeJoinForPKFK(join, mq);
+ if (constraintBasedResult != null) {
+ // We succeeded, we calculate the selectivity based on the inferred information
+ // and any residual predicate
+ double joinSelectivity = Math.min(1.0,
+ constraintBasedResult.left.pkInfo.selectivity * constraintBasedResult.left.ndvScalingFactor);
+ double residualSelectivity = RelMdUtil.guessSelectivity(constraintBasedResult.right);
+ double rowCount = constraintBasedResult.left.fkInfo.rowCount * joinSelectivity * residualSelectivity;
+ if (LOG.isDebugEnabled()) {
+ LOG.debug("Identified Primary - Foreign Key relation from constraints:\n {} {} Row count for join: {}\n" +
+ " Join selectivity: {}\n Residual selectivity: {}\n", RelOptUtil.toString(join), constraintBasedResult.left,
+ rowCount, joinSelectivity, residualSelectivity);
+ }
+ return rowCount;
+ }
+ // Otherwise, try to infer from stats
+ final PKFKRelationInfo pkfk = analyzeJoinForPKFK(join, mq);
if (pkfk != null) {
- double selectivity = (pkfk.pkInfo.selectivity * pkfk.ndvScalingFactor);
+ double selectivity = pkfk.pkInfo.selectivity * pkfk.ndvScalingFactor;
selectivity = Math.min(1.0, selectivity);
if (LOG.isDebugEnabled()) {
- LOG.debug("Identified Primary - Foreign Key relation: {} {}",RelOptUtil.toString(join), pkfk);
+ LOG.debug("Identified Primary - Foreign Key relation: {} {}", RelOptUtil.toString(join), pkfk);
}
return pkfk.fkInfo.rowCount * selectivity;
}
+ // If we cannot infer anything, then we just go to join.estimateRowCount(mq).
// Do not call mq.getRowCount(join), will trigger CyclicMetadataException
- return join.estimateRowCount(mq);
+ final Double rowCount = join.estimateRowCount(mq);
+ if (LOG.isDebugEnabled()) {
+ LOG.debug("No Primary - Foreign Key relation: \n{} Row count for join: {}\n",
+ RelOptUtil.toString(join), rowCount);
+ }
+ return rowCount;
}
@Override
public Double getRowCount(SemiJoin rel, RelMetadataQuery mq) {
PKFKRelationInfo pkfk = analyzeJoinForPKFK(rel, mq);
if (pkfk != null) {
- double selectivity = (pkfk.pkInfo.selectivity * pkfk.ndvScalingFactor);
+ double selectivity = pkfk.pkInfo.selectivity * pkfk.ndvScalingFactor;
selectivity = Math.min(1.0, selectivity);
if (LOG.isDebugEnabled()) {
LOG.debug("Identified Primary - Foreign Key relation: {} {}", RelOptUtil.toString(rel), pkfk);
@@ -217,10 +244,10 @@ public class HiveRelMdRowCount extends RelMdRowCount {
int rightColIdx = joinCols.right;
RexBuilder rexBuilder = joinRel.getCluster().getRexBuilder();
- RexNode leftPred = RexUtil
- .composeConjunction(rexBuilder, leftFilters, true);
- RexNode rightPred = RexUtil.composeConjunction(rexBuilder, rightFilters,
- true);
+ RexNode leftPred = RexUtil.composeConjunction(
+ rexBuilder, leftFilters, true);
+ RexNode rightPred = RexUtil.composeConjunction(
+ rexBuilder, rightFilters, true);
ImmutableBitSet lBitSet = ImmutableBitSet.of(leftColIdx);
ImmutableBitSet rBitSet = ImmutableBitSet.of(rightColIdx);
@@ -228,11 +255,10 @@ public class HiveRelMdRowCount extends RelMdRowCount {
* If the form is Dim loj F or Fact roj Dim or Dim semij Fact then return
* null.
*/
- boolean leftIsKey = (joinRel.getJoinType() == JoinRelType.INNER || joinRel
- .getJoinType() == JoinRelType.RIGHT)
- && !(joinRel instanceof SemiJoin) && isKey(lBitSet, left, mq);
- boolean rightIsKey = (joinRel.getJoinType() == JoinRelType.INNER || joinRel
- .getJoinType() == JoinRelType.LEFT) && isKey(rBitSet, right, mq);
+ boolean leftIsKey = (joinRel.getJoinType() == JoinRelType.INNER || joinRel.getJoinType() == JoinRelType.RIGHT)
+ && isKey(lBitSet, left, mq);
+ boolean rightIsKey = (joinRel.getJoinType() == JoinRelType.INNER || joinRel.getJoinType() == JoinRelType.LEFT)
+ && isKey(rBitSet, right, mq);
if (!leftIsKey && !rightIsKey) {
return null;
@@ -247,41 +273,37 @@ public class HiveRelMdRowCount extends RelMdRowCount {
}
}
- int pkSide = leftIsKey ? 0 : rightIsKey ? 1 : -1;
-
- boolean isPKSideSimpleTree = pkSide != -1 ?
- IsSimpleTreeOnJoinKey.check(
- pkSide == 0 ? left : right,
- pkSide == 0 ? leftColIdx : rightColIdx, mq) : false;
-
- double leftNDV = isPKSideSimpleTree ? mq.getDistinctRowCount(left, lBitSet, leftPred) : -1;
- double rightNDV = isPKSideSimpleTree ? mq.getDistinctRowCount(right, rBitSet, rightPred) : -1;
-
- /*
- * If the ndv of the PK - FK side don't match, and the PK side is a filter
- * on the Key column then scale the NDV on the FK side.
- *
- * As described by Peter Boncz: http://databasearchitects.blogspot.com/
- * in such cases we can be off by a large margin in the Join cardinality
- * estimate. The e.g. he provides is on the join of StoreSales and DateDim
- * on the TPCDS dataset. Since the DateDim is populated for 20 years into
- * the future, while the StoreSales only has 5 years worth of data, there
- * are 40 times fewer distinct dates in StoreSales.
- *
- * In general it is hard to infer the range for the foreign key on an
- * arbitrary expression. For e.g. the NDV for DayofWeek is the same
- * irrespective of NDV on the number of unique days, whereas the
- * NDV of Quarters has the same ratio as the NDV on the keys.
- *
- * But for expressions that apply only on columns that have the same NDV
- * as the key (implying that they are alternate keys) we can apply the
- * ratio. So in the case of StoreSales - DateDim joins for predicate on the
- * d_date column we can apply the scaling factor.
- */
- double ndvScalingFactor = 1.0;
- if ( isPKSideSimpleTree ) {
- ndvScalingFactor = pkSide == 0 ? leftNDV/rightNDV : rightNDV / leftNDV;
- }
+ int pkSide = leftIsKey ? 0 : 1;
+ boolean isPKSideSimpleTree = leftIsKey ? SimpleTreeOnJoinKey.check(false, left, lBitSet, mq) :
+ SimpleTreeOnJoinKey.check(false, right, rBitSet, mq);
+ double leftNDV = isPKSideSimpleTree ? mq.getDistinctRowCount(left, lBitSet, leftPred) : -1;
+ double rightNDV = isPKSideSimpleTree ? mq.getDistinctRowCount(right, rBitSet, rightPred) : -1;
+
+ /*
+ * If the ndv of the PK - FK side don't match, and the PK side is a filter
+ * on the Key column then scale the NDV on the FK side.
+ *
+ * As described by Peter Boncz: http://databasearchitects.blogspot.com/
+ * in such cases we can be off by a large margin in the Join cardinality
+ * estimate. The e.g. he provides is on the join of StoreSales and DateDim
+ * on the TPCDS dataset. Since the DateDim is populated for 20 years into
+ * the future, while the StoreSales only has 5 years worth of data, there
+ * are 40 times fewer distinct dates in StoreSales.
+ *
+ * In general it is hard to infer the range for the foreign key on an
+ * arbitrary expression. For e.g. the NDV for DayofWeek is the same
+ * irrespective of NDV on the number of unique days, whereas the
+ * NDV of Quarters has the same ratio as the NDV on the keys.
+ *
+ * But for expressions that apply only on columns that have the same NDV
+ * as the key (implying that they are alternate keys) we can apply the
+ * ratio. So in the case of StoreSales - DateDim joins for predicate on the
+ * d_date column we can apply the scaling factor.
+ */
+ double ndvScalingFactor = 1.0;
+ if ( isPKSideSimpleTree ) {
+ ndvScalingFactor = pkSide == 0 ? leftNDV/rightNDV : rightNDV / leftNDV;
+ }
if (pkSide == 0) {
FKSideInfo fkInfo = new FKSideInfo(rightRowCount,
@@ -293,9 +315,7 @@ public class HiveRelMdRowCount extends RelMdRowCount {
pkSelectivity);
return new PKFKRelationInfo(1, fkInfo, pkInfo, ndvScalingFactor, isPKSideSimpleTree);
- }
-
- if (pkSide == 1) {
+ } else { // pkSide == 1
FKSideInfo fkInfo = new FKSideInfo(leftRowCount,
leftNDV);
double pkSelectivity = pkSelectivity(joinRel, mq, false, right, rightRowCount);
@@ -304,10 +324,114 @@ public class HiveRelMdRowCount extends RelMdRowCount {
joinRel.getJoinType().generatesNullsOnLeft() ? 1.0 :
pkSelectivity);
- return new PKFKRelationInfo(1, fkInfo, pkInfo, ndvScalingFactor, isPKSideSimpleTree);
+ return new PKFKRelationInfo(0, fkInfo, pkInfo, ndvScalingFactor, isPKSideSimpleTree);
+ }
+ }
+
+ /*
+ *
+ */
+ public static Pair<PKFKRelationInfo, RexNode> constraintsBasedAnalyzeJoinForPKFK(Join join, RelMetadataQuery mq) {
+
+ if (join instanceof SemiJoin) {
+ // TODO: Support semijoin
+ return null;
+ }
+
+ final RelNode left = join.getInputs().get(0);
+ final RelNode right = join.getInputs().get(1);
+
+ // 1) Split filters in conjuncts
+ final List<RexNode> condConjs = RelOptUtil.conjunctions(
+ join.getCondition());
+
+ if (condConjs.isEmpty()) {
+ // Bail out
+ return null;
}
- return null;
+ // 2) Classify filters depending on their provenance
+ final List<RexNode> joinFilters = new ArrayList<>(condConjs);
+ final List<RexNode> leftFilters = new ArrayList<>();
+ final List<RexNode> rightFilters = new ArrayList<>();
+ RelOptUtil.classifyFilters(join, joinFilters, join.getJoinType(),false,
+ !join.getJoinType().generatesNullsOnRight(), !join.getJoinType().generatesNullsOnLeft(),
+ joinFilters, leftFilters, rightFilters);
+
+ // 3) Check if we are joining on PK-FK
+ final PKFKJoinInfo leftInputResult =
+ HiveRelOptUtil.extractPKFKJoin(join, joinFilters, false, mq);
+ final PKFKJoinInfo rightInputResult =
+ HiveRelOptUtil.extractPKFKJoin(join, joinFilters, true, mq);
+ if (leftInputResult == null && rightInputResult == null) {
+ // Nothing to do here, bail out
+ return null;
+ }
+
+ boolean leftIsKey = (join.getJoinType() == JoinRelType.INNER || join.getJoinType() == JoinRelType.RIGHT)
+ && leftInputResult.isPkFkJoin;
+ boolean rightIsKey = (join.getJoinType() == JoinRelType.INNER || join.getJoinType() == JoinRelType.LEFT)
+ && rightInputResult.isPkFkJoin;
+ if (!leftIsKey && !rightIsKey) {
+ // Nothing to do here, bail out
+ return null;
+ }
+ final double leftRowCount = mq.getRowCount(left);
+ final double rightRowCount = mq.getRowCount(right);
+ if (leftIsKey && rightIsKey) {
+ if (rightRowCount < leftRowCount) {
+ leftIsKey = false;
+ }
+ }
+ final ImmutableBitSet lBitSet = leftIsKey ? leftInputResult.pkFkJoinColumns.left : rightInputResult.pkFkJoinColumns.left;
+ final ImmutableBitSet rBitSet = leftIsKey ? leftInputResult.pkFkJoinColumns.right : rightInputResult.pkFkJoinColumns.right;
+ final List<RexNode> residualFilters = leftIsKey ? leftInputResult.additionalPredicates : rightInputResult.additionalPredicates;
+
+ // 4) Extract additional information on the PK-FK relationship
+ int pkSide = leftIsKey ? 0 : 1;
+ boolean isPKSideSimpleTree = leftIsKey ? SimpleTreeOnJoinKey.check(true, left, lBitSet, mq) :
+ SimpleTreeOnJoinKey.check(true, right, rBitSet, mq);
+ RexBuilder rexBuilder = join.getCluster().getRexBuilder();
+ RexNode leftPred = RexUtil.composeConjunction(
+ rexBuilder, leftFilters, true);
+ RexNode rightPred = RexUtil.composeConjunction(
+ rexBuilder, rightFilters, true);
+ double leftNDV = isPKSideSimpleTree ? mq.getDistinctRowCount(left, lBitSet, leftPred) : -1;
+ double rightNDV = isPKSideSimpleTree ? mq.getDistinctRowCount(right, rBitSet, rightPred) : -1;
+
+ // 5) Add the rest of operators back to the join filters
+ // and create residual condition
+ RexNode residualCond = residualFilters.isEmpty() ? null :
+ residualFilters.size() == 1 ? residualFilters.get(0) :
+ rexBuilder.makeCall(SqlStdOperatorTable.AND, residualFilters);
+
+ // 6) Return result
+ if (pkSide == 0) {
+ FKSideInfo fkInfo = new FKSideInfo(rightRowCount,
+ rightNDV);
+ double pkSelectivity = pkSelectivity(join, mq, true, left, leftRowCount);
+ PKSideInfo pkInfo = new PKSideInfo(leftRowCount,
+ leftNDV,
+ join.getJoinType().generatesNullsOnRight() ? 1.0 :
+ pkSelectivity);
+ double ndvScalingFactor = isPKSideSimpleTree ? leftNDV/rightNDV : 1.0;
+ if (isPKSideSimpleTree) {
+ ndvScalingFactor = leftNDV/rightNDV;
+ }
+ return Pair.of(new PKFKRelationInfo(1, fkInfo, pkInfo, ndvScalingFactor, isPKSideSimpleTree),
+ residualCond);
+ } else { // pkSide == 1
+ FKSideInfo fkInfo = new FKSideInfo(leftRowCount,
+ leftNDV);
+ double pkSelectivity = pkSelectivity(join, mq, false, right, rightRowCount);
+ PKSideInfo pkInfo = new PKSideInfo(rightRowCount,
+ rightNDV,
+ join.getJoinType().generatesNullsOnLeft() ? 1.0 :
+ pkSelectivity);
+ double ndvScalingFactor = isPKSideSimpleTree ? rightNDV/leftNDV : 1.0;
+ return Pair.of(new PKFKRelationInfo(0, fkInfo, pkInfo, ndvScalingFactor, isPKSideSimpleTree),
+ residualCond);
+ }
}
private static double pkSelectivity(Join joinRel, RelMetadataQuery mq, boolean leftChild,
@@ -402,20 +526,22 @@ public class HiveRelMdRowCount extends RelMdRowCount {
return new Pair<Integer, Integer>(leftColIdx, rightColIdx);
}
- private static class IsSimpleTreeOnJoinKey extends RelVisitor {
+ private static class SimpleTreeOnJoinKey extends RelVisitor {
- int joinKey;
+ boolean constraintsBased;
+ ImmutableBitSet joinKey;
boolean simpleTree;
RelMetadataQuery mq;
- static boolean check(RelNode r, int joinKey, RelMetadataQuery mq) {
- IsSimpleTreeOnJoinKey v = new IsSimpleTreeOnJoinKey(joinKey, mq);
+ static boolean check(boolean constraintsBased, RelNode r, ImmutableBitSet joinKey, RelMetadataQuery mq) {
+ SimpleTreeOnJoinKey v = new SimpleTreeOnJoinKey(constraintsBased, joinKey, mq);
v.go(r);
return v.simpleTree;
}
- IsSimpleTreeOnJoinKey(int joinKey, RelMetadataQuery mq) {
+ SimpleTreeOnJoinKey(boolean constraintsBased, ImmutableBitSet joinKey, RelMetadataQuery mq) {
super();
+ this.constraintsBased = constraintsBased;
this.joinKey = joinKey;
this.mq = mq;
simpleTree = true;
@@ -444,16 +570,23 @@ public class HiveRelMdRowCount extends RelMdRowCount {
}
private boolean isSimple(Project project) {
- RexNode r = project.getProjects().get(joinKey);
- if (r instanceof RexInputRef) {
- joinKey = ((RexInputRef) r).getIndex();
- return true;
+ ImmutableBitSet.Builder b = ImmutableBitSet.builder();
+ for (int pos : joinKey) {
+ RexNode r = project.getProjects().get(pos);
+ if (!(r instanceof RexInputRef)) {
+ return false;
+ }
+ b.set(((RexInputRef) r).getIndex());
}
- return false;
+ joinKey = b.build();
+ return true;
}
private boolean isSimple(Filter filter, RelMetadataQuery mq) {
ImmutableBitSet condBits = RelOptUtil.InputFinder.bits(filter.getCondition());
+ if (constraintsBased) {
+ return mq.areColumnsUnique(filter, condBits);
+ }
return isKey(condBits, filter, mq);
}
http://git-wip-us.apache.org/repos/asf/hive/blob/f8b4c869/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java
index 575902d..7e92082 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/stats/HiveRelMdSelectivity.java
@@ -98,8 +98,7 @@ public class HiveRelMdSelectivity extends RelMdSelectivity {
} catch (CalciteSemanticException e) {
throw new RuntimeException(e);
}
- ImmutableMap.Builder<Integer, Double> colStatMapBuilder = ImmutableMap
- .builder();
+ ImmutableMap.Builder<Integer, Double> colStatMapBuilder = ImmutableMap.builder();
ImmutableMap<Integer, Double> colStatMap;
int rightOffSet = j.getLeft().getRowType().getFieldCount();
http://git-wip-us.apache.org/repos/asf/hive/blob/f8b4c869/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out
index d100578..3d190e3 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query17.q.out
@@ -104,36 +104,36 @@ CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC], fetch=[100])
HiveProject(i_item_id=[$0], i_item_desc=[$1], s_state=[$2], store_sales_quantitycount=[$3], store_sales_quantityave=[/(CAST($4):DOUBLE, $3)], store_sales_quantitystdev=[POWER(/(-($5, /(*($6, $6), $3)), CASE(=($3, 1), null, -($3, 1))), 0.5)], store_sales_quantitycov=[/(POWER(/(-($5, /(*($6, $6), $3)), CASE(=($3, 1), null, -($3, 1))), 0.5), /(CAST($4):DOUBLE, $3))], as_store_returns_quantitycount=[$7], as_store_returns_quantityave=[/(CAST($8):DOUBLE, $7)], as_store_returns_quantitystdev=[POWER(/(-($9, /(*($10, $10), $7)), CASE(=($7, 1), null, -($7, 1))), 0.5)], store_returns_quantitycov=[/(POWER(/(-($9, /(*($10, $10), $7)), CASE(=($7, 1), null, -($7, 1))), 0.5), /(CAST($8):DOUBLE, $7))], catalog_sales_quantitycount=[$11], catalog_sales_quantityave=[/(CAST($12):DOUBLE, $11)], catalog_sales_quantitystdev=[/(POWER(/(-($13, /(*($14, $14), $11)), CASE(=($11, 1), null, -($11, 1))), 0.5), /(CAST($12):DOUBLE, $11))], catalog_sales_quantitycov=[/(POWER(/(-($13, /(*($14, $14), $11)), CASE(=($
11, 1), null, -($11, 1))), 0.5), /(CAST($12):DOUBLE, $11))])
HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)], agg#1=[sum($3)], agg#2=[sum($7)], agg#3=[sum($6)], agg#4=[count($4)], agg#5=[sum($4)], agg#6=[sum($9)], agg#7=[sum($8)], agg#8=[count($5)], agg#9=[sum($5)], agg#10=[sum($11)], agg#11=[sum($10)])
- HiveProject($f0=[$8], $f1=[$9], $f2=[$22], $f3=[$5], $f4=[$19], $f5=[$13], $f30=[CAST($5):DOUBLE], $f7=[*(CAST($5):DOUBLE, CAST($5):DOUBLE)], $f40=[CAST($19):DOUBLE], $f9=[*(CAST($19):DOUBLE, CAST($19):DOUBLE)], $f50=[CAST($13):DOUBLE], $f11=[*(CAST($13):DOUBLE, CAST($13):DOUBLE)])
- HiveJoin(condition=[=($21, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[AND(AND(=($2, $17), =($1, $16)), =($4, $18))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($7, $1)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_quantity=[$10])
- HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0), IS NOT NULL($7))])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[=($15, _UTF-16LE'2000Q1')])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject($f0=[$6], $f1=[$7], $f2=[$22], $f3=[$13], $f4=[$19], $f5=[$3], $f30=[CAST($13):DOUBLE], $f7=[*(CAST($13):DOUBLE, CAST($13):DOUBLE)], $f40=[CAST($19):DOUBLE], $f9=[*(CAST($19):DOUBLE, CAST($19):DOUBLE)], $f50=[CAST($3):DOUBLE], $f11=[*(CAST($3):DOUBLE, CAST($3):DOUBLE)])
+ HiveJoin(condition=[AND(=($17, $1), =($16, $2))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15], cs_quantity=[$18])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[IN($15, _UTF-16LE'2000Q1', _UTF-16LE'2000Q2', _UTF-16LE'2000Q3')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$2], ss_sold_date_sk=[$3], ss_item_sk=[$4], ss_customer_sk=[$5], ss_store_sk=[$6], ss_ticket_number=[$7], ss_quantity=[$8], d_date_sk=[$9], sr_returned_date_sk=[$10], sr_item_sk=[$11], sr_customer_sk=[$12], sr_ticket_number=[$13], sr_return_quantity=[$14], d_date_sk0=[$15], s_store_sk=[$16], s_state=[$17])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4])
HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2], cs_quantity=[$3], d_date_sk=[$4], sr_returned_date_sk=[$5], sr_item_sk=[$6], sr_customer_sk=[$7], sr_ticket_number=[$8], sr_return_quantity=[$9], d_date_sk0=[$10])
- HiveJoin(condition=[AND(=($7, $1), =($6, $2))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15], cs_quantity=[$18])
- HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[IN($15, _UTF-16LE'2000Q1', _UTF-16LE'2000Q2', _UTF-16LE'2000Q3')])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
- HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$1], sr_customer_sk=[$2], sr_ticket_number=[$3], sr_return_quantity=[$4], d_date_sk=[$5])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$2], sr_customer_sk=[$3], sr_ticket_number=[$9], sr_return_quantity=[$10])
- HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
- HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
+ HiveJoin(condition=[=($13, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(AND(=($2, $9), =($1, $8)), =($4, $10))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_quantity=[$10])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0), IS NOT NULL($7))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[IN($15, _UTF-16LE'2000Q1', _UTF-16LE'2000Q2', _UTF-16LE'2000Q3')])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
- HiveProject(s_store_sk=[$0], s_state=[$24])
- HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveFilter(condition=[=($15, _UTF-16LE'2000Q1')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
+ HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$1], sr_customer_sk=[$2], sr_ticket_number=[$3], sr_return_quantity=[$4], d_date_sk=[$5])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$2], sr_customer_sk=[$3], sr_ticket_number=[$9], sr_return_quantity=[$10])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[IN($15, _UTF-16LE'2000Q1', _UTF-16LE'2000Q2', _UTF-16LE'2000Q3')])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
+ HiveProject(s_store_sk=[$0], s_state=[$24])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
http://git-wip-us.apache.org/repos/asf/hive/blob/f8b4c869/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query24.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query24.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query24.q.out
index 0801f34..1be3932 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query24.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query24.q.out
@@ -1,4 +1,4 @@
-Warning: Shuffle Join MERGEJOIN[298][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 6' is a cross product
+Warning: Shuffle Join MERGEJOIN[298][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 7' is a cross product
PREHOOK: query: explain cbo
with ssales as
(select c_last_name
@@ -117,20 +117,44 @@ CBO PLAN:
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3])
HiveJoin(condition=[>($3, $4)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(c_last_name=[$1], c_first_name=[$0], s_store_name=[$2], $f3=[$3])
- HiveAggregate(group=[{4, 5, 7}], agg#0=[sum($9)])
- HiveProject(i_current_price=[$0], i_size=[$1], i_units=[$2], i_manager_id=[$3], c_first_name=[$4], c_last_name=[$5], ca_state=[$6], s_store_name=[$7], s_state=[$8], $f9=[$9])
- HiveAggregate(group=[{8, 9, 10, 11, 14, 15, 18, 22, 23}], agg#0=[sum($6)])
- HiveJoin(condition=[AND(=($5, $1), =($2, $0))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{0, 1, 7}], agg#0=[sum($9)])
+ HiveProject(c_first_name=[$0], c_last_name=[$1], ca_state=[$2], i_current_price=[$3], i_size=[$4], i_units=[$5], i_manager_id=[$6], s_store_name=[$7], s_state=[$8], $f9=[$9])
+ HiveAggregate(group=[{4, 5, 8, 17, 18, 19, 20, 22, 23}], agg#0=[sum($15)])
+ HiveJoin(condition=[AND(=($14, $1), =($11, $0))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9])
HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
- HiveJoin(condition=[AND(=($1, $10), =($2, $19))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($22, $7), =($11, $19))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($10, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($1, $5), <>($4, $8))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4], c_first_name=[$8], c_last_name=[$9], c_birth_country=[$14])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveProject(ca_address_sk=[$0], ca_state=[$8], ca_zip=[$9], UPPER=[UPPER($10)])
+ HiveFilter(condition=[IS NOT NULL($9)])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveProject(ss_item_sk=[$0], ss_customer_sk=[$1], ss_store_sk=[$2], ss_ticket_number=[$3], ss_sales_price=[$4], i_item_sk=[$5], i_current_price=[$6], i_size=[$7], i_units=[$8], i_manager_id=[$9])
+ HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_sales_price=[$13])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($3))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
+ HiveProject(i_item_sk=[$0], i_current_price=[$5], i_size=[$15], i_units=[$18], i_manager_id=[$20])
+ HiveFilter(condition=[=($17, _UTF-16LE'orchid')])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(s_store_sk=[$0], s_store_name=[$5], s_state=[$24], s_zip=[$25])
+ HiveFilter(condition=[AND(=($10, 7), IS NOT NULL($25))])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveProject(_o__c0=[*(0.05, /($0, $1))])
+ HiveAggregate(group=[{}], agg#0=[sum($10)], agg#1=[count($10)])
+ HiveProject(c_first_name=[$0], c_last_name=[$1], ca_state=[$2], s_store_name=[$3], s_state=[$4], i_current_price=[$5], i_size=[$6], i_color=[$7], i_units=[$8], i_manager_id=[$9], $f10=[$10])
+ HiveAggregate(group=[{9, 10, 13, 17, 18, 21, 22, 23, 24, 25}], agg#0=[sum($4)])
+ HiveJoin(condition=[=($0, $20)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($1, $7), =($2, $16))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($3, $6), =($0, $5))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_sales_price=[$13])
HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($3))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(i_item_sk=[$0], i_current_price=[$5], i_size=[$15], i_units=[$18], i_manager_id=[$20])
- HiveFilter(condition=[=($17, _UTF-16LE'orchid')])
- HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9])
+ HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$1], c_first_name=[$2], c_last_name=[$3], c_birth_country=[$4], ca_address_sk=[$5], ca_state=[$6], ca_zip=[$7], UPPER=[$8], s_store_sk=[$9], s_store_name=[$10], s_state=[$11], s_zip=[$12])
HiveJoin(condition=[AND(=($1, $5), <>($4, $8))], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4], c_first_name=[$8], c_last_name=[$9], c_birth_country=[$14])
@@ -143,29 +167,6 @@ HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3])
HiveProject(s_store_sk=[$0], s_store_name=[$5], s_state=[$24], s_zip=[$25])
HiveFilter(condition=[AND(=($10, 7), IS NOT NULL($25))])
HiveTableScan(table=[[default, store]], table:alias=[store])
- HiveProject(_o__c0=[*(0.05, /($0, $1))])
- HiveAggregate(group=[{}], agg#0=[sum($10)], agg#1=[count($10)])
- HiveProject(c_first_name=[$0], c_last_name=[$1], ca_state=[$2], s_store_name=[$3], s_state=[$4], i_current_price=[$5], i_size=[$6], i_color=[$7], i_units=[$8], i_manager_id=[$9], $f10=[$10])
- HiveAggregate(group=[{9, 10, 13, 17, 18, 21, 22, 23, 24, 25}], agg#0=[sum($6)])
- HiveJoin(condition=[AND(=($5, $1), =($2, $0))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(sr_item_sk=[$2], sr_ticket_number=[$9])
- HiveTableScan(table=[[default, store_returns]], table:alias=[store_returns])
- HiveJoin(condition=[=($0, $18)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[AND(=($17, $12), =($2, $14))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($1, $5)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_sales_price=[$13])
- HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($3))])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveJoin(condition=[AND(=($1, $5), <>($4, $8))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4], c_first_name=[$8], c_last_name=[$9], c_birth_country=[$14])
- HiveFilter(condition=[IS NOT NULL($4)])
- HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveProject(ca_address_sk=[$0], ca_state=[$8], ca_zip=[$9], UPPER=[UPPER($10)])
- HiveFilter(condition=[IS NOT NULL($9)])
- HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
- HiveProject(s_store_sk=[$0], s_store_name=[$5], s_state=[$24], s_zip=[$25])
- HiveFilter(condition=[AND(=($10, 7), IS NOT NULL($25))])
- HiveTableScan(table=[[default, store]], table:alias=[store])
- HiveProject(i_item_sk=[$0], i_current_price=[$5], i_size=[$15], i_color=[$17], i_units=[$18], i_manager_id=[$20])
- HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveProject(i_item_sk=[$0], i_current_price=[$5], i_size=[$15], i_color=[$17], i_units=[$18], i_manager_id=[$20])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
http://git-wip-us.apache.org/repos/asf/hive/blob/f8b4c869/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query25.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query25.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query25.q.out
index a39931a..658410c 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query25.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query25.q.out
@@ -109,28 +109,28 @@ POSTHOOK: Output: hdfs://### HDFS PATH ###
CBO PLAN:
HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100])
HiveProject(i_item_id=[$0], i_item_desc=[$1], s_store_id=[$2], s_store_name=[$3], $f4=[$4], $f5=[$5], $f6=[$6])
- HiveAggregate(group=[{1, 2, 22, 23}], agg#0=[sum($8)], agg#1=[sum($19)], agg#2=[sum($13)])
- HiveJoin(condition=[=($21, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveAggregate(group=[{6, 7, 22, 23}], agg#0=[sum($13)], agg#1=[sum($19)], agg#2=[sum($3)])
+ HiveJoin(condition=[AND(=($17, $1), =($16, $2))], joinType=[inner], algorithm=[none], cost=[not available])
HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveJoin(condition=[AND(AND(=($2, $14), =($1, $13)), =($4, $15))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_net_profit=[$22])
- HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0), IS NOT NULL($7))])
- HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(=($8, 4), =($6, 2000))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2], cs_net_profit=[$3], d_date_sk=[$4], sr_returned_date_sk=[$5], sr_item_sk=[$6], sr_customer_sk=[$7], sr_ticket_number=[$8], sr_net_loss=[$9], d_date_sk0=[$10])
- HiveJoin(condition=[AND(=($7, $1), =($6, $2))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15], cs_net_profit=[$33])
- HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15], cs_net_profit=[$33])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(BETWEEN(false, $8, 4, 10), =($6, 2000))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$2], ss_sold_date_sk=[$3], ss_item_sk=[$4], ss_customer_sk=[$5], ss_store_sk=[$6], ss_ticket_number=[$7], ss_net_profit=[$8], d_date_sk=[$9], sr_returned_date_sk=[$10], sr_item_sk=[$11], sr_customer_sk=[$12], sr_ticket_number=[$13], sr_net_loss=[$14], d_date_sk0=[$15], s_store_sk=[$16], s_store_id=[$17], s_store_name=[$18])
+ HiveJoin(condition=[=($16, $6)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(i_item_sk=[$0], i_item_id=[$1], i_item_desc=[$4])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
+ HiveJoin(condition=[AND(AND(=($2, $9), =($1, $8)), =($4, $10))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_customer_sk=[$3], ss_store_sk=[$7], ss_ticket_number=[$9], ss_net_profit=[$22])
+ HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0), IS NOT NULL($7))])
+ HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(BETWEEN(false, $8, 4, 10), =($6, 2000))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[d3])
+ HiveFilter(condition=[AND(=($8, 4), =($6, 2000))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[d1])
HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$1], sr_customer_sk=[$2], sr_ticket_number=[$3], sr_net_loss=[$4], d_date_sk=[$5])
HiveJoin(condition=[=($0, $5)], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(sr_returned_date_sk=[$0], sr_item_sk=[$2], sr_customer_sk=[$3], sr_ticket_number=[$9], sr_net_loss=[$19])
@@ -139,6 +139,6 @@ HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[AND(BETWEEN(false, $8, 4, 10), =($6, 2000))])
HiveTableScan(table=[[default, date_dim]], table:alias=[d2])
- HiveProject(s_store_sk=[$0], s_store_id=[$1], s_store_name=[$5])
- HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveProject(s_store_sk=[$0], s_store_id=[$1], s_store_name=[$5])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
http://git-wip-us.apache.org/repos/asf/hive/blob/f8b4c869/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out
index 1cf3ce4..5d9dd17 100644
--- a/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out
+++ b/ql/src/test/results/clientpositive/perf/tez/constraints/cbo_query54.q.out
@@ -1,7 +1,7 @@
-Warning: Shuffle Join MERGEJOIN[270][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3]] in Stage 'Reducer 4' is a cross product
-Warning: Shuffle Join MERGEJOIN[271][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3, $hdt$_4]] in Stage 'Reducer 5' is a cross product
-Warning: Shuffle Join MERGEJOIN[269][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 32' is a cross product
-Warning: Shuffle Join MERGEJOIN[272][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 6' is a cross product
+Warning: Shuffle Join MERGEJOIN[264][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 2' is a cross product
+Warning: Shuffle Join MERGEJOIN[273][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3, $hdt$_4]] in Stage 'Reducer 5' is a cross product
+Warning: Shuffle Join MERGEJOIN[272][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 14' is a cross product
+Warning: Shuffle Join MERGEJOIN[274][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 6' is a cross product
PREHOOK: query: explain cbo
with my_customers as (
select distinct c_customer_sk
@@ -139,56 +139,56 @@ HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
HiveFilter(condition=[BETWEEN(false, $2, $3, $4)])
HiveProject(c_customer_sk=[$0], ss_ext_sales_price=[$4], d_month_seq=[$11], _o__c0=[$13], $f0=[$14])
HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject($f0=[$10], $f1=[$11], ss_sold_date_sk=[$0], ss_customer_sk=[$1], ss_ext_sales_price=[$2], ca_address_sk=[$5], ca_county=[$6], ca_state=[$7], s_county=[$8], s_state=[$9], d_date_sk=[$3], d_month_seq=[$4], cnt=[$12], $f00=[$13])
+ HiveProject($f0=[$11], $f1=[$12], ss_sold_date_sk=[$0], ss_customer_sk=[$1], ss_ext_sales_price=[$2], ca_address_sk=[$6], ca_county=[$7], ca_state=[$8], s_county=[$9], s_state=[$10], d_date_sk=[$4], d_month_seq=[$5], cnt=[$3], $f00=[$13])
HiveJoin(condition=[true], joinType=[left], algorithm=[none], cost=[not available])
- HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($10, $1)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($11, $1)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available])
HiveProject(ss_sold_date_sk=[$0], ss_customer_sk=[$3], ss_ext_sales_price=[$15])
HiveFilter(condition=[AND(IS NOT NULL($3), IS NOT NULL($0))])
HiveTableScan(table=[[default, store_sales]], table:alias=[store_sales])
- HiveProject(d_date_sk=[$0], d_month_seq=[$3])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(ca_address_sk=[$0], ca_county=[$1], ca_state=[$2], s_county=[$3], s_state=[$4], c_customer_sk=[$5], c_current_addr_sk=[$6])
- HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[AND(=($1, $3), =($2, $4))], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(ca_address_sk=[$0], ca_county=[$7], ca_state=[$8])
- HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($8))])
- HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
- HiveProject(s_county=[$23], s_state=[$24])
- HiveFilter(condition=[AND(IS NOT NULL($23), IS NOT NULL($24))])
- HiveTableScan(table=[[default, store]], table:alias=[store])
- HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$1])
- HiveAggregate(group=[{0, 1}])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
- HiveFilter(condition=[IS NOT NULL($4)])
- HiveTableScan(table=[[default, customer]], table:alias=[customer])
- HiveJoin(condition=[=($2, $4)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2])
- HiveUnion(all=[true])
- HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15])
- HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($3))])
- HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
- HiveProject(sold_date_sk=[$0], customer_sk=[$4], item_sk=[$3])
- HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
- HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
- HiveProject(d_date_sk=[$0])
- HiveFilter(condition=[AND(=($8, 3), =($6, 1999))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(i_item_sk=[$0])
- HiveFilter(condition=[AND(=($12, _UTF-16LE'Jewelry'), =($10, _UTF-16LE'consignment'))])
- HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(cnt=[$0])
- HiveFilter(condition=[<=(sq_count_check($0), 1)])
HiveProject(cnt=[$0])
- HiveAggregate(group=[{}], cnt=[COUNT()])
- HiveProject($f0=[$0])
- HiveAggregate(group=[{0}])
- HiveProject($f0=[+($3, 1)])
- HiveFilter(condition=[AND(=($6, 1999), =($8, 3))])
- HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveFilter(condition=[<=(sq_count_check($0), 1)])
+ HiveProject(cnt=[$0])
+ HiveAggregate(group=[{}], cnt=[COUNT()])
+ HiveProject($f0=[$0])
+ HiveAggregate(group=[{0}])
+ HiveProject($f0=[+($3, 1)])
+ HiveFilter(condition=[AND(=($6, 1999), =($8, 3))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(d_date_sk=[$0], d_month_seq=[$3])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(ca_address_sk=[$0], ca_county=[$1], ca_state=[$2], s_county=[$3], s_state=[$4], c_customer_sk=[$5], c_current_addr_sk=[$6])
+ HiveJoin(condition=[=($6, $0)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($1, $3), =($2, $4))], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(ca_address_sk=[$0], ca_county=[$7], ca_state=[$8])
+ HiveFilter(condition=[AND(IS NOT NULL($7), IS NOT NULL($8))])
+ HiveTableScan(table=[[default, customer_address]], table:alias=[customer_address])
+ HiveProject(s_county=[$23], s_state=[$24])
+ HiveFilter(condition=[AND(IS NOT NULL($23), IS NOT NULL($24))])
+ HiveTableScan(table=[[default, store]], table:alias=[store])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$1])
+ HiveAggregate(group=[{0, 1}])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(c_customer_sk=[$0], c_current_addr_sk=[$4])
+ HiveFilter(condition=[IS NOT NULL($4)])
+ HiveTableScan(table=[[default, customer]], table:alias=[customer])
+ HiveJoin(condition=[=($2, $4)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $3)], joinType=[inner], algorithm=[none], cost=[not available])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$1], cs_item_sk=[$2])
+ HiveUnion(all=[true])
+ HiveProject(cs_sold_date_sk=[$0], cs_bill_customer_sk=[$3], cs_item_sk=[$15])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($3))])
+ HiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales])
+ HiveProject(sold_date_sk=[$0], customer_sk=[$4], item_sk=[$3])
+ HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
+ HiveTableScan(table=[[default, web_sales]], table:alias=[web_sales])
+ HiveProject(d_date_sk=[$0])
+ HiveFilter(condition=[AND(=($8, 3), =($6, 1999))])
+ HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
+ HiveProject(i_item_sk=[$0])
+ HiveFilter(condition=[AND(=($12, _UTF-16LE'Jewelry'), =($10, _UTF-16LE'consignment'))])
+ HiveTableScan(table=[[default, item]], table:alias=[item])
HiveProject($f0=[$0])
HiveAggregate(group=[{0}])
HiveProject($f0=[+($3, 1)])