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)])