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/09/17 15:16:44 UTC
[4/4] hive git commit: HIVE-17040: Join elimination in the presence
of FK relationship (Jesus Camacho Rodriguez, reviewed by Vineet Garg)
HIVE-17040: Join elimination in the presence of FK relationship (Jesus Camacho Rodriguez, reviewed by Vineet Garg)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/befee74d
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/befee74d
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/befee74d
Branch: refs/heads/master
Commit: befee74df09a5545f196c57cce32a686d39fd918
Parents: 7ee7a54
Author: Jesus Camacho Rodriguez <jc...@apache.org>
Authored: Fri Aug 10 20:23:20 2018 -0700
Committer: Jesus Camacho Rodriguez <jc...@apache.org>
Committed: Mon Sep 17 08:14:16 2018 -0700
----------------------------------------------------------------------
.../org/apache/hadoop/hive/conf/HiveConf.java | 3 +
.../test/resources/testconfiguration.properties | 3 +
.../ql/optimizer/calcite/HiveRelOptUtil.java | 64 +
.../calcite/rules/HiveJoinConstraintsRule.java | 497 ++++++++
.../rules/HiveProjectJoinTransposeRule.java | 33 +
.../calcite/rules/HiveProjectMergeRule.java | 21 +
.../hadoop/hive/ql/parse/CalcitePlanner.java | 47 +-
.../join_constraints_optimization.q | 136 +++
.../materialized_view_rewrite_1.q | 6 +-
.../materialized_view_rewrite_3.q | 6 +-
.../materialized_view_rewrite_4.q | 9 +-
.../materialized_view_rewrite_5.q | 6 +-
.../materialized_view_rewrite_6.q | 6 +-
.../materialized_view_rewrite_7.q | 4 +-
.../materialized_view_rewrite_no_join_opt.q | 132 ++
.../materialized_view_rewrite_no_join_opt_2.q | 144 +++
.../materialized_view_rewrite_part_1.q | 6 +-
.../results/clientpositive/ambiguitycheck.q.out | 2 +-
.../materialized_view_create_rewrite.q.out | 2 +-
.../clientpositive/druid/druidmini_mv.q.out | 6 +-
.../clientpositive/list_bucket_dml_2.q.out | 2 +-
.../clientpositive/list_bucket_dml_4.q.out | 2 +-
.../clientpositive/list_bucket_dml_9.q.out | 2 +-
.../list_bucket_query_multiskew_1.q.out | 4 +-
.../list_bucket_query_multiskew_2.q.out | 2 +-
.../list_bucket_query_multiskew_3.q.out | 4 +-
.../list_bucket_query_oneskew_1.q.out | 6 +-
.../list_bucket_query_oneskew_2.q.out | 2 +-
.../llap/acid_bucket_pruning.q.out | 2 +-
.../clientpositive/llap/bucketpruning1.q.out | 4 +-
.../llap/current_date_timestamp.q.out | 2 +-
.../llap/join_constraints_optimization.q.out | 746 +++++++++++
.../llap/materialized_view_create_rewrite.q.out | 2 +-
...materialized_view_create_rewrite_dummy.q.out | 2 +-
...erialized_view_create_rewrite_multi_db.q.out | 2 +-
.../llap/materialized_view_rewrite_1.q.out | 111 +-
.../llap/materialized_view_rewrite_2.q.out | 57 +-
.../llap/materialized_view_rewrite_3.q.out | 24 +-
.../llap/materialized_view_rewrite_4.q.out | 40 +-
.../llap/materialized_view_rewrite_5.q.out | 95 +-
.../llap/materialized_view_rewrite_6.q.out | 92 +-
.../llap/materialized_view_rewrite_7.q.out | 228 ++--
.../materialized_view_rewrite_no_join_opt.q.out | 755 ++++++++++++
...aterialized_view_rewrite_no_join_opt_2.q.out | 1154 ++++++++++++++++++
.../llap/materialized_view_rewrite_part_1.q.out | 113 +-
.../llap/materialized_view_rewrite_part_2.q.out | 66 +-
ql/src/test/results/clientpositive/macro.q.out | 6 +-
.../spark/list_bucket_dml_2.q.out | 2 +-
48 files changed, 4108 insertions(+), 552 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java
----------------------------------------------------------------------
diff --git a/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java b/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java
index 3e38fe6..3fb8e76 100644
--- a/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java
+++ b/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java
@@ -2226,6 +2226,9 @@ public class HiveConf extends Configuration {
"When hive.optimize.limittranspose is true, this variable specifies the minimal reduction in the\n" +
"number of tuples of the outer input of the join or the input of the union that you should get in order to apply the rule."),
+ HIVE_OPTIMIZE_CONSTRAINTS_JOIN("hive.optimize.constraints.join", true, "Whether to use referential constraints\n" +
+ "to optimize (remove or transform) join operators"),
+
HIVE_OPTIMIZE_REDUCE_WITH_STATS("hive.optimize.filter.stats.reduction", false, "Whether to simplify comparison\n" +
"expressions in filter operators using column stats"),
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/itests/src/test/resources/testconfiguration.properties
----------------------------------------------------------------------
diff --git a/itests/src/test/resources/testconfiguration.properties b/itests/src/test/resources/testconfiguration.properties
index 64e8183..3672c7a 100644
--- a/itests/src/test/resources/testconfiguration.properties
+++ b/itests/src/test/resources/testconfiguration.properties
@@ -530,6 +530,7 @@ minillaplocal.query.files=\
jdbc_handler.q,\
join1.q,\
join_acid_non_acid.q,\
+ join_constraints_optimization.q,\
join_filters.q,\
join_max_hashtable.q,\
join_nulls.q,\
@@ -583,6 +584,8 @@ minillaplocal.query.files=\
materialized_view_rewrite_8.q,\
materialized_view_rewrite_9.q,\
materialized_view_rewrite_10.q,\
+ materialized_view_rewrite_no_join_opt.q,\
+ materialized_view_rewrite_no_join_opt_2.q,\
materialized_view_rewrite_part_1.q,\
materialized_view_rewrite_part_2.q,\
materialized_view_rewrite_ssb.q,\
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/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 268284a..dc0a84b 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,17 +17,26 @@
*/
package org.apache.hadoop.hive.ql.optimizer.calcite;
+import com.google.common.collect.Multimap;
import java.util.AbstractList;
import java.util.ArrayList;
+import java.util.Collection;
import java.util.List;
import com.google.common.collect.ImmutableList;
+import java.util.Map.Entry;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Aggregate;
+import org.apache.calcite.rel.core.Aggregate.Group;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.core.Filter;
+import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.rel.core.Sort;
+import org.apache.calcite.rel.core.TableScan;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rex.RexBuilder;
@@ -35,6 +44,7 @@ import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexFieldAccess;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexOver;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlOperator;
@@ -460,4 +470,58 @@ public class HiveRelOptUtil extends RelOptUtil {
return aggregateFactory.createAggregate(rel, false, ImmutableBitSet.of(), null, aggCalls);
}
+
+ /**
+ * Given a RelNode, it checks whether there is any filtering condition
+ * below. Basically we check whether the operators
+ * below altered the PK cardinality in any way
+ */
+ public static boolean isRowFilteringPlan(final RelMetadataQuery mq, RelNode operator) {
+ final Multimap<Class<? extends RelNode>, RelNode> nodesBelowNonFkInput =
+ mq.getNodeTypes(operator);
+ for (Entry<Class<? extends RelNode>, Collection<RelNode>> e :
+ nodesBelowNonFkInput.asMap().entrySet()) {
+ if (e.getKey() == TableScan.class) {
+ if (e.getValue().size() > 1) {
+ // Bail out as we may not have more than one TS on non-FK side
+ return true;
+ }
+ } else if (e.getKey() == Project.class) {
+ // We check there is no windowing expression
+ for (RelNode node : e.getValue()) {
+ Project p = (Project) node;
+ for (RexNode expr : p.getChildExps()) {
+ if (expr instanceof RexOver) {
+ // Bail out as it may change cardinality
+ return true;
+ }
+ }
+ }
+ } else if (e.getKey() == Aggregate.class) {
+ // We check there is are not grouping sets
+ for (RelNode node : e.getValue()) {
+ Aggregate a = (Aggregate) node;
+ if (a.getGroupType() != Group.SIMPLE) {
+ // Bail out as it may change cardinality
+ return true;
+ }
+ }
+ } else if (e.getKey() == Sort.class) {
+ // We check whether there is a limit clause
+ for (RelNode node : e.getValue()) {
+ Sort s = (Sort) node;
+ if (s.fetch != null || s.offset != null) {
+ // Bail out as it may change cardinality
+ return true;
+ }
+ }
+ } else {
+ // Bail out, we cannot rewrite the expression if non-fk side cardinality
+ // is being altered
+ return true;
+ }
+ }
+ // It passed all the tests
+ return false;
+ }
}
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/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
new file mode 100644
index 0000000..0a307f2
--- /dev/null
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveJoinConstraintsRule.java
@@ -0,0 +1,497 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.hadoop.hive.ql.optimizer.calcite.rules;
+
+import com.google.common.collect.Multimap;
+import com.google.common.collect.Sets;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.HashMap;
+import java.util.LinkedHashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Map.Entry;
+import java.util.Set;
+import java.util.stream.Collectors;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.RelReferentialConstraint;
+import org.apache.calcite.rel.core.Aggregate;
+import org.apache.calcite.rel.core.Aggregate.Group;
+import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rel.core.Project;
+import org.apache.calcite.rel.core.TableScan;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexInputRef;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexOver;
+import org.apache.calcite.rex.RexPermuteInputsShuttle;
+import org.apache.calcite.rex.RexTableInputRef;
+import org.apache.calcite.rex.RexTableInputRef.RelTableRef;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.tools.RelBuilderFactory;
+import org.apache.calcite.util.ImmutableBitSet;
+import org.apache.calcite.util.mapping.Mapping;
+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.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+/**
+ * The rule can perform two different optimizations.
+ * 1) Removes a join if is does not alter the cardinality of the one of its inputs.
+ * In particular, this rule is triggered if:
+ * - it is a join on PK-FK/UK-FK,
+ * - project on top only references columns from the FK side, and
+ * - PK/UK side is not filtered
+ * It optionally adds an IS NOT NULL filter if any FK column can be nullable
+ * 2) Transforms a left/right outer join into an inner join if:
+ * - it is a join on PK-FK/UK-FK,
+ * - FK is not nullable
+ * - PK/UK side is not filtered
+ */
+public class HiveJoinConstraintsRule extends RelOptRule {
+
+ protected static final Logger LOG = LoggerFactory.getLogger(HiveJoinConstraintsRule.class);
+
+ public static final HiveJoinConstraintsRule INSTANCE =
+ new HiveJoinConstraintsRule(HiveRelFactories.HIVE_BUILDER);
+
+
+ protected HiveJoinConstraintsRule(RelBuilderFactory relBuilder) {
+ super(
+ operand(Project.class,
+ some(operand(Join.class, any()))),
+ relBuilder, "HiveJoinConstraintsRule");
+ }
+
+ @Override
+ public void onMatch(RelOptRuleCall call) {
+ final Project project = call.rel(0);
+ final RexBuilder rexBuilder = project.getCluster().getRexBuilder();
+ List<RexNode> topProjExprs = project.getChildExps();
+ Join join = call.rel(1);
+ final JoinRelType joinType = join.getJoinType();
+ final RelNode leftInput = join.getLeft();
+ final RelNode rightInput = join.getRight();
+ final RexNode cond = join.getCondition();
+
+ // 1) If it is an inner, check whether project only uses columns from one side.
+ // That side will need to be the FK side.
+ // If it is a left outer, left will be the FK side.
+ // If it is a right outer, right will be the FK side.
+ final RelNode fkInput;
+ final RelNode nonFkInput;
+ final ImmutableBitSet topRefs =
+ RelOptUtil.InputFinder.bits(topProjExprs, null);
+ final ImmutableBitSet leftBits =
+ ImmutableBitSet.range(leftInput.getRowType().getFieldCount());
+ final ImmutableBitSet rightBits =
+ ImmutableBitSet.range(leftInput.getRowType().getFieldCount(),
+ join.getRowType().getFieldCount());
+ // These boolean values represent corresponding left, right input which is potential FK
+ boolean leftInputPotentialFK = topRefs.intersects(leftBits);
+ boolean rightInputPotentialFK = topRefs.intersects(rightBits);
+ if (leftInputPotentialFK && rightInputPotentialFK && joinType == JoinRelType.INNER) {
+ // Both inputs are referenced. Before making a decision, try to swap
+ // references in join condition if it is an inner join, i.e. if a join
+ // condition column is referenced above the join, then we can just
+ // reference the column from the other side.
+ // For example, given two relations R(a1,a2), S(b1) :
+ // SELECT a2, b1 FROM R, S ON R.a1=R.b1 =>
+ // SELECT a2, a1 FROM R, S ON R.a1=R.b1
+ int joinFieldCount = join.getRowType().getFieldCount();
+ Mapping mappingLR = Mappings.create(MappingType.PARTIAL_FUNCTION, joinFieldCount, joinFieldCount);
+ Mapping mappingRL = Mappings.create(MappingType.PARTIAL_FUNCTION, joinFieldCount, joinFieldCount);
+ for (RexNode conj : RelOptUtil.conjunctions(cond)) {
+ if (!conj.isA(SqlKind.EQUALS)) {
+ continue;
+ }
+ RexCall eq = (RexCall) conj;
+ RexNode op1 = eq.getOperands().get(0);
+ RexNode op2 = eq.getOperands().get(1);
+ if (op1 instanceof RexInputRef && op2 instanceof RexInputRef) {
+ // Check references
+ int ref1 = ((RexInputRef) op1).getIndex();
+ int ref2 = ((RexInputRef) op2).getIndex();
+ int leftRef = -1;
+ int rightRef = -1;
+ if (leftBits.get(ref1) && rightBits.get(ref2)) {
+ leftRef = ref1;
+ rightRef = ref2;
+ } else if (rightBits.get(ref1) && leftBits.get(ref2)) {
+ leftRef = ref2;
+ rightRef = ref1;
+ }
+ if (leftRef != -1 && rightRef != -1) {
+ // We do not add more than one mapping per source
+ // as it is useless
+ if (mappingLR.getTargetOpt(leftRef) == -1) {
+ mappingLR.set(leftRef, rightRef);
+ }
+ if (mappingRL.getTargetOpt(rightRef) == -1) {
+ mappingRL.set(rightRef, leftRef);
+ }
+ }
+ }
+ }
+ if (mappingLR.size() != 0) {
+ // First insert missing elements into the mapping as identity mappings
+ for (int i = 0; i < joinFieldCount; i++) {
+ if (mappingLR.getTargetOpt(i) == -1) {
+ mappingLR.set(i, i);
+ }
+ if (mappingRL.getTargetOpt(i) == -1) {
+ mappingRL.set(i, i);
+ }
+ }
+ // Then, we start by trying to reference only left side in top projections
+ List<RexNode> swappedTopProjExprs = topProjExprs.stream()
+ .map(projExpr -> projExpr.accept(new RexPermuteInputsShuttle(mappingRL, call.rel(1))))
+ .collect(Collectors.toList());
+ rightInputPotentialFK = RelOptUtil.InputFinder.bits(swappedTopProjExprs, null).intersects(rightBits);
+ if (!rightInputPotentialFK) {
+ topProjExprs = swappedTopProjExprs;
+ } else {
+ // If it did not work, we try to reference only right side in top projections
+ swappedTopProjExprs = topProjExprs.stream()
+ .map(projExpr -> projExpr.accept(new RexPermuteInputsShuttle(mappingLR, call.rel(1))))
+ .collect(Collectors.toList());
+ leftInputPotentialFK = RelOptUtil.InputFinder.bits(swappedTopProjExprs, null).intersects(leftBits);
+ if (!leftInputPotentialFK) {
+ topProjExprs = swappedTopProjExprs;
+ }
+ }
+ }
+ } else if (!leftInputPotentialFK && !rightInputPotentialFK) {
+ // TODO: There are no references in the project operator above.
+ // In this case, we should probably do two passes, one for
+ // left as FK and one for right as FK, although it may be expensive.
+ // Currently we only assume left as FK
+ leftInputPotentialFK = true;
+ }
+
+ final Mode mode;
+ switch (joinType) {
+ case INNER:
+ if (leftInputPotentialFK && rightInputPotentialFK) {
+ // Bails out as it references columns from both sides (or no columns)
+ // and there is nothing to transform
+ return;
+ }
+ fkInput = leftInputPotentialFK ? leftInput : rightInput;
+ nonFkInput = leftInputPotentialFK ? rightInput : leftInput;
+ mode = Mode.REMOVE;
+ break;
+ case LEFT:
+ fkInput = leftInput;
+ nonFkInput = rightInput;
+ mode = leftInputPotentialFK && !rightInputPotentialFK ? Mode.REMOVE : Mode.TRANSFORM;
+ break;
+ case RIGHT:
+ fkInput = rightInput;
+ nonFkInput = leftInput;
+ mode = !leftInputPotentialFK && rightInputPotentialFK ? Mode.REMOVE : Mode.TRANSFORM;
+ break;
+ default:
+ // Other type, bail out
+ return;
+ }
+
+ // 2) Check whether there is any filtering condition on the
+ // non-FK side. Basically we check whether the operators
+ // below altered the PK cardinality in any way
+ final RelMetadataQuery mq = call.getMetadataQuery();
+ if (HiveRelOptUtil.isRowFilteringPlan(mq, nonFkInput)) {
+ return;
+ }
+
+ // 3) Check whether there is an FK relationship
+ if (join.getJoinType() != 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) call.builder()
+ .push(leftInput).push(rightInput)
+ .join(JoinRelType.INNER, cond)
+ .build();
+ }
+ final Map<RexTableInputRef, RexNode> refToRex = new HashMap<>();
+ final EquivalenceClasses ec = new EquivalenceClasses();
+ for (RexNode conj : RelOptUtil.conjunctions(cond)) {
+ if (!conj.isA(SqlKind.EQUALS)) {
+ // Not an equality, we bail out
+ return;
+ }
+ RexCall equiCond = (RexCall) conj;
+ RexNode eqOp1 = equiCond.getOperands().get(0);
+ Set<RexNode> eqOp1ExprsLineage = mq.getExpressionLineage(join, eqOp1);
+ if (eqOp1ExprsLineage == null) {
+ // Cannot be mapped, bail out
+ return;
+ }
+ RexNode eqOp2 = equiCond.getOperands().get(1);
+ Set<RexNode> eqOp2ExprsLineage = mq.getExpressionLineage(join, eqOp2);
+ if (eqOp2ExprsLineage == null) {
+ // Cannot be mapped, bail out
+ return;
+ }
+ List<RexTableInputRef> eqOp2ExprsFiltered = null;
+ for (RexNode eqOpExprLineage1 : eqOp1ExprsLineage) {
+ RexTableInputRef inputRef1 = extractTableInputRef(eqOpExprLineage1);
+ if (inputRef1 == null) {
+ // Bail out as this condition could not be map into an input reference
+ return;
+ }
+ refToRex.put(inputRef1, eqOp1);
+ 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
+ return;
+ }
+ // Add to list of expressions for follow-up iterations
+ eqOp2ExprsFiltered.add(inputRef2);
+ // Add to equivalence classes and backwards mapping
+ ec.addEquivalenceClass(inputRef1, inputRef2);
+ refToRex.put(inputRef2, eqOp2);
+ }
+ } else {
+ // Rest of iterations, only adding, no checking
+ for (RexTableInputRef inputRef2 : eqOp2ExprsFiltered) {
+ ec.addEquivalenceClass(inputRef1, inputRef2);
+ }
+ }
+ }
+ }
+ if (ec.getEquivalenceClassesMap().isEmpty()) {
+ // This may be a cartesian product, we bail out
+ return;
+ }
+
+ // 4) Gather all tables from the FK side and the table from the
+ // non-FK side
+ final Set<RelTableRef> leftTables = mq.getTableReferences(leftInput);
+ final Set<RelTableRef> rightTables =
+ Sets.difference(mq.getTableReferences(join), mq.getTableReferences(leftInput));
+ final Set<RelTableRef> fkTables = leftInputPotentialFK ? leftTables : rightTables;
+ final Set<RelTableRef> nonFkTables = leftInputPotentialFK ? rightTables : leftTables;
+ assert nonFkTables.size() == 1;
+ final RelTableRef nonFkTable = nonFkTables.iterator().next();
+ final List<String> nonFkTableQName = nonFkTable.getQualifiedName();
+
+ // 5) 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
+ boolean canBeRewritten = false;
+ List<RexNode> nullableNodes = new ArrayList<>();
+ for (RelTableRef tRef : fkTables) {
+ List<RelReferentialConstraint> constraints = tRef.getTable().getReferentialConstraints();
+ for (RelReferentialConstraint constraint : constraints) {
+ if (constraint.getTargetQualifiedName().equals(nonFkTableQName)) {
+ EquivalenceClasses ecT = EquivalenceClasses.copy(ec);
+ 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);
+ if (foreignKeyColumnType.isNullable()) {
+ if (joinType == JoinRelType.INNER) {
+ // If it is nullable and it is an INNER, we just need a IS NOT NULL filter
+ RexNode originalCondOp = refToRex.get(foreignKeyColumnRef);
+ assert originalCondOp != null;
+ nullableNodes.add(originalCondOp);
+ } else {
+ // If it is nullable and this is not an INNER, we cannot execute any transformation
+ allContained = false;
+ break;
+ }
+ }
+ 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
+ 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);
+ }
+ } else {
+ // No relationship, we cannot do anything
+ allContained = false;
+ break;
+ }
+ }
+ if (allContained && ecT.getEquivalenceClassesMap().isEmpty()) {
+ // We made it
+ canBeRewritten = true;
+ break;
+ }
+ }
+ }
+ }
+
+ // 6) If it is the only condition, we can trigger the rewriting
+ if (canBeRewritten) {
+ // If we reach here, we trigger the transform
+ if (mode == Mode.REMOVE) {
+ if (rightInputPotentialFK) {
+ // First, if FK is the right input, we need to shift
+ nullableNodes = nullableNodes.stream()
+ .map(node -> RexUtil.shift(node, 0, -leftInput.getRowType().getFieldCount()))
+ .collect(Collectors.toList());
+ topProjExprs = topProjExprs.stream()
+ .map(node -> RexUtil.shift(node, 0, -leftInput.getRowType().getFieldCount()))
+ .collect(Collectors.toList());
+ }
+ // Fix nullability in references to the input node
+ topProjExprs = RexUtil.fixUp(rexBuilder, topProjExprs, RelOptUtil.getFieldTypeList(fkInput.getRowType()));
+ // Trigger transformation
+ if (nullableNodes.isEmpty()) {
+ call.transformTo(call.builder()
+ .push(fkInput)
+ .project(topProjExprs)
+ .convert(project.getRowType(), false)
+ .build());
+ } else {
+ RexNode newFilterCond;
+ if (nullableNodes.size() == 1) {
+ newFilterCond = rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, nullableNodes.get(0));
+ } else {
+ List<RexNode> isNotNullConds = new ArrayList<>();
+ for (RexNode nullableNode : nullableNodes) {
+ isNotNullConds.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, nullableNode));
+ }
+ newFilterCond = rexBuilder.makeCall(SqlStdOperatorTable.AND, isNotNullConds);
+ }
+ call.transformTo(call.builder()
+ .push(fkInput)
+ .filter(newFilterCond)
+ .project(topProjExprs)
+ .convert(project.getRowType(), false)
+ .build());
+ }
+ } else { // Mode.TRANSFORM
+ // Trigger transformation
+ call.transformTo(call.builder()
+ .push(leftInput).push(rightInput)
+ .join(JoinRelType.INNER, join.getCondition())
+ .project(project.getChildExps())
+ .build());
+ }
+ }
+ }
+
+ private static RexTableInputRef extractTableInputRef(RexNode node) {
+ RexTableInputRef ref = null;
+ if (node instanceof RexTableInputRef) {
+ ref = (RexTableInputRef) node;
+ } else if (RexUtil.isLosslessCast(node) &&
+ ((RexCall) node).getOperands().get(0) instanceof RexTableInputRef) {
+ ref = (RexTableInputRef) ((RexCall) node).getOperands().get(0);
+ }
+ return ref;
+ }
+
+ /**
+ * Class representing an equivalence class, i.e., a set of equivalent columns
+ *
+ * TODO: This is a subset of a private class in materialized view rewriting
+ * in Calcite. It should be moved to its own class in Calcite so it can be
+ * accessible here.
+ */
+ private static class EquivalenceClasses {
+
+ private final Map<RexTableInputRef, Set<RexTableInputRef>> nodeToEquivalenceClass;
+
+ protected EquivalenceClasses() {
+ nodeToEquivalenceClass = new HashMap<>();
+ }
+
+ protected void addEquivalenceClass(RexTableInputRef p1, RexTableInputRef p2) {
+ Set<RexTableInputRef> c1 = nodeToEquivalenceClass.get(p1);
+ Set<RexTableInputRef> c2 = nodeToEquivalenceClass.get(p2);
+ if (c1 != null && c2 != null) {
+ // Both present, we need to merge
+ if (c1.size() < c2.size()) {
+ // We swap them to merge
+ Set<RexTableInputRef> c2Temp = c2;
+ c2 = c1;
+ c1 = c2Temp;
+ }
+ for (RexTableInputRef newRef : c2) {
+ c1.add(newRef);
+ nodeToEquivalenceClass.put(newRef, c1);
+ }
+ } else if (c1 != null) {
+ // p1 present, we need to merge into it
+ c1.add(p2);
+ nodeToEquivalenceClass.put(p2, c1);
+ } else if (c2 != null) {
+ // p2 present, we need to merge into it
+ c2.add(p1);
+ nodeToEquivalenceClass.put(p1, c2);
+ } else {
+ // None are present, add to same equivalence class
+ Set<RexTableInputRef> equivalenceClass = new LinkedHashSet<>();
+ equivalenceClass.add(p1);
+ equivalenceClass.add(p2);
+ nodeToEquivalenceClass.put(p1, equivalenceClass);
+ nodeToEquivalenceClass.put(p2, equivalenceClass);
+ }
+ }
+
+ protected Map<RexTableInputRef, Set<RexTableInputRef>> getEquivalenceClassesMap() {
+ return nodeToEquivalenceClass;
+ }
+
+ 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()));
+ }
+ return newEc;
+ }
+ }
+
+ private enum Mode {
+ // Removes join operator from the plan
+ REMOVE,
+ // Transforms LEFT/RIGHT outer join into INNER join
+ TRANSFORM
+ }
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectJoinTransposeRule.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectJoinTransposeRule.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectJoinTransposeRule.java
new file mode 100644
index 0000000..43c7896
--- /dev/null
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectJoinTransposeRule.java
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.hadoop.hive.ql.optimizer.calcite.rules;
+
+import org.apache.calcite.rel.rules.ProjectJoinTransposeRule;
+import org.apache.calcite.tools.RelBuilderFactory;
+import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
+
+public class HiveProjectJoinTransposeRule extends ProjectJoinTransposeRule {
+
+ public static final HiveProjectJoinTransposeRule INSTANCE =
+ new HiveProjectJoinTransposeRule(HiveRelFactories.HIVE_BUILDER);
+
+ private HiveProjectJoinTransposeRule(RelBuilderFactory relBuilderFactory) {
+ super(expr -> true, relBuilderFactory);
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectMergeRule.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectMergeRule.java b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectMergeRule.java
index 07518df..67ecdec 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectMergeRule.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveProjectMergeRule.java
@@ -20,11 +20,14 @@ package org.apache.hadoop.hive.ql.optimizer.calcite.rules;
import java.util.Set;
import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.rules.ProjectMergeRule;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexOver;
+import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.tools.RelBuilderFactory;
+import org.apache.calcite.util.ImmutableBitSet;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveCalciteUtil;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
@@ -66,4 +69,22 @@ public class HiveProjectMergeRule extends ProjectMergeRule {
return super.matches(call);
}
+ public void onMatch(RelOptRuleCall call) {
+ final Project topProject = call.rel(0);
+ final Project bottomProject = call.rel(1);
+
+ // If top project does not reference any column at the bottom project,
+ // we can just remove botton project
+ final ImmutableBitSet topRefs =
+ RelOptUtil.InputFinder.bits(topProject.getChildExps(), null);
+ if (topRefs.isEmpty()) {
+ RelBuilder relBuilder = call.builder();
+ relBuilder.push(bottomProject.getInput());
+ relBuilder.project(topProject.getChildExps());
+ call.transformTo(relBuilder.build());
+ return;
+ }
+ super.onMatch(call);
+ }
+
}
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
----------------------------------------------------------------------
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
index 9e6de05..ab5848a 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
@@ -100,7 +100,6 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperator;
-import org.apache.calcite.sql.SqlSampleSpec;
import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.dialect.HiveSqlDialect;
import org.apache.calcite.sql.parser.SqlParserPos;
@@ -114,8 +113,6 @@ import org.apache.calcite.util.ImmutableIntList;
import org.apache.calcite.util.Pair;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.hive.common.ObjectPair;
-import org.apache.hadoop.hive.common.ValidTxnList;
-import org.apache.hadoop.hive.common.ValidTxnWriteIdList;
import org.apache.hadoop.hive.conf.Constants;
import org.apache.hadoop.hive.conf.HiveConf;
import org.apache.hadoop.hive.conf.HiveConf.ConfVars;
@@ -191,6 +188,7 @@ import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveIntersectMergeRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveIntersectRewriteRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveJoinAddNotNullRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveJoinCommuteRule;
+import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveJoinConstraintsRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveJoinProjectTransposeRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveJoinPushTransitivePredicatesRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveJoinToMultiJoinRule;
@@ -198,6 +196,7 @@ import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HivePartitionPruneRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HivePointLookupOptimizerRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HivePreFilteringRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveProjectFilterPullUpConstantsRule;
+import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveProjectJoinTransposeRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveProjectMergeRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveProjectOverIntersectRemoveRule;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveProjectSortTransposeRule;
@@ -1660,7 +1659,8 @@ public class CalcitePlanner extends SemanticAnalyzer {
private enum ExtendedCBOProfile {
JOIN_REORDERING,
- WINDOWING_POSTPROCESSING;
+ WINDOWING_POSTPROCESSING,
+ REFERENTIAL_CONSTRAINTS;
}
/**
@@ -1789,9 +1789,9 @@ public class CalcitePlanner extends SemanticAnalyzer {
}
// 4.1 Remove Projects between Joins so that JoinToMultiJoinRule can merge them to MultiJoin.
// Don't run this rule if hive is to remove sq_count_check since that rule expects to have project b/w join.
- calcitePreCboPlan = hepPlan(calcitePreCboPlan, true, mdProvider.getMetadataProvider(), executorProvider,
- HepMatchOrder.BOTTOM_UP, HiveJoinProjectTransposeRule.LEFF_PROJECT_BTW_JOIN,
- HiveJoinProjectTransposeRule.RIGHT_PROJECT_BTW_JOIN);
+ calcitePreCboPlan = hepPlan(calcitePreCboPlan, true, mdProvider.getMetadataProvider(), executorProvider,
+ HepMatchOrder.BOTTOM_UP, HiveJoinProjectTransposeRule.LEFF_PROJECT_BTW_JOIN,
+ HiveJoinProjectTransposeRule.RIGHT_PROJECT_BTW_JOIN);
// 4.2 Apply join order optimizations: reordering MST algorithm
// If join optimizations failed because of missing stats, we continue with
@@ -2057,6 +2057,11 @@ public class CalcitePlanner extends SemanticAnalyzer {
rules.add(new HivePointLookupOptimizerRule.FilterCondition(minNumORClauses));
rules.add(new HivePointLookupOptimizerRule.JoinCondition(minNumORClauses));
}
+ if (conf.getBoolVar(HiveConf.ConfVars.HIVE_OPTIMIZE_CONSTRAINTS_JOIN) &&
+ profilesCBO.contains(ExtendedCBOProfile.REFERENTIAL_CONSTRAINTS)) {
+ rules.add(HiveProjectJoinTransposeRule.INSTANCE);
+ rules.add(HiveJoinConstraintsRule.INSTANCE);
+ }
rules.add(HiveJoinAddNotNullRule.INSTANCE_JOIN);
rules.add(HiveJoinAddNotNullRule.INSTANCE_SEMIJOIN);
rules.add(HiveJoinPushTransitivePredicatesRule.INSTANCE_JOIN);
@@ -2116,25 +2121,19 @@ public class CalcitePlanner extends SemanticAnalyzer {
perfLogger.PerfLogEnd(this.getClass().getName(), PerfLogger.OPTIMIZER,
"Calcite: Prejoin ordering transformation, Projection Pruning");
- // 8. Merge, remove and reduce Project if possible
- perfLogger.PerfLogBegin(this.getClass().getName(), PerfLogger.OPTIMIZER);
- basePlan = hepPlan(basePlan, false, mdProvider, executorProvider,
- HiveProjectMergeRule.INSTANCE, ProjectRemoveRule.INSTANCE, HiveSortMergeRule.INSTANCE);
- perfLogger.PerfLogEnd(this.getClass().getName(), PerfLogger.OPTIMIZER,
- "Calcite: Prejoin ordering transformation, Merge Project-Project, Merge Sort-Sort");
-
- // 9. Rerun PPD through Project as column pruning would have introduced
+ // 8. Rerun PPD through Project as column pruning would have introduced
// DT above scans; By pushing filter just above TS, Hive can push it into
// storage (incase there are filters on non partition cols). This only
// matches FIL-PROJ-TS
+ // Also merge, remove and reduce Project if possible
perfLogger.PerfLogBegin(this.getClass().getName(), PerfLogger.OPTIMIZER);
basePlan = hepPlan(basePlan, true, mdProvider, executorProvider,
HiveFilterProjectTSTransposeRule.INSTANCE, HiveFilterProjectTSTransposeRule.INSTANCE_DRUID,
- HiveProjectFilterPullUpConstantsRule.INSTANCE);
+ HiveProjectFilterPullUpConstantsRule.INSTANCE, HiveProjectMergeRule.INSTANCE,
+ ProjectRemoveRule.INSTANCE, HiveSortMergeRule.INSTANCE);
perfLogger.PerfLogEnd(this.getClass().getName(), PerfLogger.OPTIMIZER,
"Calcite: Prejoin ordering transformation, Rerun PPD");
-
return basePlan;
}
@@ -2820,6 +2819,7 @@ public class CalcitePlanner extends SemanticAnalyzer {
}
// 4. Build operator
+ RelOptHiveTable optTable;
if (tableType == TableType.DRUID ||
(tableType == TableType.JDBC && tabMetaData.getProperty("hive.sql.table") != null)) {
// Create case sensitive columns list
@@ -2875,7 +2875,7 @@ public class CalcitePlanner extends SemanticAnalyzer {
DruidTable druidTable = new DruidTable(new DruidSchema(address, address, false),
dataSource, RelDataTypeImpl.proto(rowType), metrics, DruidTable.DEFAULT_TIMESTAMP_COLUMN,
intervals, null, null);
- RelOptHiveTable optTable = new RelOptHiveTable(relOptSchema, relOptSchema.getTypeFactory(), fullyQualifiedTabName,
+ optTable = new RelOptHiveTable(relOptSchema, relOptSchema.getTypeFactory(), fullyQualifiedTabName,
rowType, tabMetaData, nonPartitionColumns, partitionColumns, virtualCols, conf,
partitionCache, colStatsCache, noColsMissingStats);
final TableScan scan = new HiveTableScan(cluster, cluster.traitSetOf(HiveRelNode.CONVENTION),
@@ -2885,11 +2885,10 @@ public class CalcitePlanner extends SemanticAnalyzer {
|| qb.getAliasInsideView().contains(tableAlias.toLowerCase()));
tableRel = DruidQuery.create(cluster, cluster.traitSetOf(BindableConvention.INSTANCE),
optTable, druidTable, ImmutableList.of(scan), DruidSqlOperatorConverter.getDefaultMap());
- } else if (tableType == TableType.JDBC) {
- RelOptHiveTable optTable = new RelOptHiveTable(relOptSchema, relOptSchema.getTypeFactory(), fullyQualifiedTabName,
+ } else {
+ optTable = new RelOptHiveTable(relOptSchema, relOptSchema.getTypeFactory(), fullyQualifiedTabName,
rowType, tabMetaData, nonPartitionColumns, partitionColumns, virtualCols, conf,
partitionCache, colStatsCache, noColsMissingStats);
-
final HiveTableScan hts = new HiveTableScan(cluster,
cluster.traitSetOf(HiveRelNode.CONVENTION), optTable,
null == tableAlias ? tabMetaData.getTableName() : tableAlias,
@@ -2927,7 +2926,7 @@ public class CalcitePlanner extends SemanticAnalyzer {
fullyQualifiedTabName.add(tabMetaData.getDbName());
}
fullyQualifiedTabName.add(tabMetaData.getTableName());
- RelOptHiveTable optTable = new RelOptHiveTable(relOptSchema, relOptSchema.getTypeFactory(), fullyQualifiedTabName,
+ optTable = new RelOptHiveTable(relOptSchema, relOptSchema.getTypeFactory(), fullyQualifiedTabName,
rowType, tabMetaData, nonPartitionColumns, partitionColumns, virtualCols, conf,
partitionCache, colStatsCache, noColsMissingStats);
// Build Hive Table Scan Rel
@@ -2938,6 +2937,10 @@ public class CalcitePlanner extends SemanticAnalyzer {
|| qb.getAliasInsideView().contains(tableAlias.toLowerCase()));
}
+ if (!optTable.getReferentialConstraints().isEmpty()) {
+ profilesCBO.add(ExtendedCBOProfile.REFERENTIAL_CONSTRAINTS);
+ }
+
// 6. Add Schema(RR) to RelNode-Schema map
ImmutableMap<String, Integer> hiveToCalciteColMap = buildHiveToCalciteColumnMap(rr,
tableRel);
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/join_constraints_optimization.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/join_constraints_optimization.q b/ql/src/test/queries/clientpositive/join_constraints_optimization.q
new file mode 100644
index 0000000..5981b04
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/join_constraints_optimization.q
@@ -0,0 +1,136 @@
+set hive.strict.checks.cartesian.product=false;
+
+CREATE TABLE `customer_removal_n0`(
+ `c_custkey` bigint,
+ `c_name` string,
+ `c_address` string,
+ `c_city` string,
+ `c_nation` string,
+ `c_region` string,
+ `c_phone` string,
+ `c_mktsegment` string,
+ primary key (`c_custkey`) disable rely)
+ROW FORMAT DELIMITED
+FIELDS TERMINATED BY '|'
+STORED AS TEXTFILE;
+
+CREATE TABLE `dates_removal_n0`(
+ `d_datekey` bigint,
+ `d_date` string,
+ `d_dayofweek` string,
+ `d_month` string,
+ `d_year` int,
+ `d_yearmonthnum` int,
+ `d_yearmonth` string,
+ `d_daynuminweek` int,
+ `d_daynuminmonth` int,
+ `d_daynuminyear` int,
+ `d_monthnuminyear` int,
+ `d_weeknuminyear` int,
+ `d_sellingseason` string,
+ `d_lastdayinweekfl` int,
+ `d_lastdayinmonthfl` int,
+ `d_holidayfl` int ,
+ `d_weekdayfl`int,
+ primary key (`d_datekey`) disable rely)
+ROW FORMAT DELIMITED
+FIELDS TERMINATED BY '|'
+STORED AS TEXTFILE;
+
+CREATE TABLE `lineorder_removal_n0`(
+ `lo_orderkey` bigint,
+ `lo_linenumber` int,
+ `lo_custkey` bigint not null disable rely,
+ `lo_partkey` bigint not null disable rely,
+ `lo_suppkey` bigint not null disable rely,
+ `lo_orderdate` bigint,
+ `lo_ordpriority` string,
+ `lo_shippriority` string,
+ `lo_quantity` double,
+ `lo_extendedprice` double,
+ `lo_ordtotalprice` double,
+ `lo_discount` double,
+ `lo_revenue` double,
+ `lo_supplycost` double,
+ `lo_tax` double,
+ `lo_commitdate` bigint,
+ `lo_shipmode` string,
+ primary key (`lo_orderkey`) disable rely,
+ constraint fk1 foreign key (`lo_custkey`) references `customer_removal_n0`(`c_custkey`) disable rely,
+ constraint fk2 foreign key (`lo_orderdate`) references `dates_removal_n0`(`d_datekey`) disable rely)
+ROW FORMAT DELIMITED
+FIELDS TERMINATED BY '|'
+STORED AS TEXTFILE;
+
+-- CAN BE REMOVED AND DOES NOT NEED FILTER ON JOIN COLUMN
+-- AS COLUMN IS ALREADY NOT NULLABLE
+EXPLAIN
+SELECT `lo_linenumber`
+FROM `lineorder_removal_n0`
+JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`;
+
+-- CAN BE REMOVED AND INTRODUCES A FILTER ON JOIN COLUMN
+EXPLAIN
+SELECT `lo_linenumber`
+FROM `lineorder_removal_n0`
+JOIN `dates_removal_n0` ON `lo_orderdate` = `d_datekey`;
+
+-- REMOVES THE JOIN
+EXPLAIN
+SELECT `lo_linenumber`
+FROM `lineorder_removal_n0`
+LEFT OUTER JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`;
+
+-- TRANSFORMS THE JOIN
+EXPLAIN
+SELECT `lo_linenumber`, `c_region`
+FROM `lineorder_removal_n0`
+LEFT OUTER JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`;
+
+-- NOT TRANSFORMED INTO INNER JOIN SINCE JOIN COLUMN IS NULLABLE
+EXPLAIN
+SELECT `lo_linenumber`
+FROM `lineorder_removal_n0`
+LEFT OUTER JOIN `dates_removal_n0` ON `lo_orderdate` = `d_datekey`;
+
+-- REMOVES BOTH JOINS
+EXPLAIN
+SELECT `lo_linenumber`
+FROM `lineorder_removal_n0`
+JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`
+JOIN `dates_removal_n0` ON `lo_orderdate` = `d_datekey`;
+
+-- REMOVES BOTH JOINS
+EXPLAIN
+SELECT `lo_linenumber`
+FROM `lineorder_removal_n0`
+LEFT OUTER JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`
+JOIN `dates_removal_n0` ON `lo_orderdate` = `d_datekey`;
+
+-- REMOVE INNER AND NOT TRANFORM OUTER
+EXPLAIN
+SELECT `lo_linenumber` FROM
+(SELECT *
+FROM `lineorder_removal_n0`
+JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`) subq
+LEFT OUTER JOIN `dates_removal_n0` ON `lo_orderdate` = `d_datekey`;
+
+-- REMOVE FIRST OUTER AND NOT TRANFORM SECOND OUTER
+EXPLAIN
+SELECT `lo_linenumber`
+FROM `lineorder_removal_n0`
+LEFT OUTER JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`
+LEFT OUTER JOIN `dates_removal_n0` ON `lo_orderdate` = `d_datekey`;
+
+-- NOT TRANFORM EITHER
+EXPLAIN
+SELECT `lo_linenumber`
+FROM `lineorder_removal_n0`
+LEFT OUTER JOIN `dates_removal_n0` ON `lo_orderdate` = `d_datekey`
+LEFT OUTER JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`;
+
+-- SWAP AND REMOVE
+EXPLAIN
+SELECT `lo_linenumber`, `c_custkey`
+FROM `lineorder_removal_n0`
+JOIN `customer_removal_n0` ON `lo_custkey` = `c_custkey`;
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q
index ee48442..18b9f7d 100644
--- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q
@@ -14,7 +14,7 @@ create table emps_n3 (
commission int)
stored as orc TBLPROPERTIES ('transactional'='true');
insert into emps_n3 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
- (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250);
+ (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250);
analyze table emps_n3 compute statistics for columns;
create table depts_n2 (
@@ -29,14 +29,14 @@ create table dependents_n2 (
empid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into dependents_n2 values (10, 'Michael'), (10, 'Jane');
+insert into dependents_n2 values (10, 'Michael'), (20, 'Jane');
analyze table dependents_n2 compute statistics for columns;
create table locations_n2 (
locationid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into locations_n2 values (10, 'San Francisco'), (10, 'San Diego');
+insert into locations_n2 values (10, 'San Francisco'), (20, 'San Diego');
analyze table locations_n2 compute statistics for columns;
alter table emps_n3 add constraint pk1 primary key (empid) disable novalidate rely;
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q
index 4aadd5f..0823f59 100644
--- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q
@@ -14,7 +14,7 @@ create table emps_n9 (
commission int)
stored as orc TBLPROPERTIES ('transactional'='true');
insert into emps_n9 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
- (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250);
+ (150, 10, 'Sebastian', 7000, null), (120, 10, 'Theodore', 10000, 250);
analyze table emps_n9 compute statistics for columns;
create table depts_n7 (
@@ -29,14 +29,14 @@ create table dependents_n5 (
empid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into dependents_n5 values (10, 'Michael'), (10, 'Jane');
+insert into dependents_n5 values (10, 'Michael'), (20, 'Jane');
analyze table dependents_n5 compute statistics for columns;
create table locations_n5 (
locationid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into locations_n5 values (10, 'San Francisco'), (10, 'San Diego');
+insert into locations_n5 values (10, 'San Francisco'), (20, 'San Diego');
analyze table locations_n5 compute statistics for columns;
alter table emps_n9 add constraint pk1 primary key (empid) disable novalidate rely;
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q
index dc20b68..6724cec 100644
--- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q
@@ -14,7 +14,7 @@ create table emps_n5 (
commission int)
stored as orc TBLPROPERTIES ('transactional'='true');
insert into emps_n5 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
- (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250);
+ (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250);
analyze table emps_n5 compute statistics for columns;
create table depts_n4 (
@@ -29,14 +29,14 @@ create table dependents_n3 (
empid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into dependents_n3 values (10, 'Michael'), (10, 'Jane');
+insert into dependents_n3 values (10, 'Michael'), (20, 'Jane');
analyze table dependents_n3 compute statistics for columns;
create table locations_n3 (
locationid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into locations_n3 values (10, 'San Francisco'), (10, 'San Diego');
+insert into locations_n3 values (10, 'San Francisco'), (20, 'San Diego');
analyze table locations_n3 compute statistics for columns;
alter table emps_n5 add constraint pk1 primary key (empid) disable novalidate rely;
@@ -78,7 +78,8 @@ from emps_n5 group by name, salary;
drop materialized view mv1_n3;
--- EXAMPLE 25
+-- EXAMPLE 25: REWRITING NOT TRIGGERED WHEN JOIN CONSTRAINTS
+-- OPTIMIZATION IS ENABLED
create materialized view mv1_n3 as
select empid, emps_n5.deptno, count(*) as c, sum(empid) as s
from emps_n5 join depts_n4 using (deptno)
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q
index 0e4fdf4..d87928c 100644
--- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q
@@ -14,7 +14,7 @@ create table emps_n2 (
commission int)
stored as orc TBLPROPERTIES ('transactional'='true');
insert into emps_n2 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
- (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250);
+ (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250);
analyze table emps_n2 compute statistics for columns;
create table depts_n1 (
@@ -29,14 +29,14 @@ create table dependents_n1 (
empid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into dependents_n1 values (10, 'Michael'), (10, 'Jane');
+insert into dependents_n1 values (10, 'Michael'), (20, 'Jane');
analyze table dependents_n1 compute statistics for columns;
create table locations_n1 (
locationid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into locations_n1 values (10, 'San Francisco'), (10, 'San Diego');
+insert into locations_n1 values (10, 'San Francisco'), (20, 'San Diego');
analyze table locations_n1 compute statistics for columns;
alter table emps_n2 add constraint pk1 primary key (empid) disable novalidate rely;
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_6.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_6.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_6.q
index 4f05f76..23fc3c1 100644
--- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_6.q
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_6.q
@@ -14,7 +14,7 @@ create table emps (
commission int)
stored as orc TBLPROPERTIES ('transactional'='true');
insert into emps values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
- (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250);
+ (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250);
analyze table emps compute statistics for columns;
create table depts (
@@ -29,14 +29,14 @@ create table dependents (
empid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into dependents values (10, 'Michael'), (10, 'Jane');
+insert into dependents values (10, 'Michael'), (20, 'Jane');
analyze table dependents compute statistics for columns;
create table locations (
locationid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into locations values (10, 'San Francisco'), (10, 'San Diego');
+insert into locations values (10, 'San Francisco'), (20, 'San Diego');
analyze table locations compute statistics for columns;
alter table emps add constraint pk1 primary key (empid) disable novalidate rely;
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q
index 59ed575..3d1cedc 100644
--- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q
@@ -29,14 +29,14 @@ create table dependents_n4 (
empid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into dependents_n4 values (10, 'Michael'), (10, 'Jane');
+insert into dependents_n4 values (10, 'Michael'), (20, 'Jane');
analyze table dependents_n4 compute statistics for columns;
create table locations_n4 (
locationid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into locations_n4 values (10, 'San Francisco'), (10, 'San Diego');
+insert into locations_n4 values (10, 'San Francisco'), (20, 'San Diego');
analyze table locations_n4 compute statistics for columns;
alter table emps_n8 add constraint pk1 primary key (empid) disable novalidate rely;
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_no_join_opt.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_no_join_opt.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_no_join_opt.q
new file mode 100644
index 0000000..8de9c70
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_no_join_opt.q
@@ -0,0 +1,132 @@
+-- SORT_QUERY_RESULTS
+
+set hive.support.concurrency=true;
+set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
+set hive.strict.checks.cartesian.product=false;
+set hive.stats.fetch.column.stats=true;
+set hive.optimize.constraints.join=false;
+
+create table emps_n30 (
+ empid int,
+ deptno int,
+ name varchar(256),
+ salary float,
+ commission int)
+stored as orc TBLPROPERTIES ('transactional'='true');
+insert into emps_n30 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
+ (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250);
+analyze table emps_n30 compute statistics for columns;
+
+create table depts_n20 (
+ deptno int,
+ name varchar(256),
+ locationid int)
+stored as orc TBLPROPERTIES ('transactional'='true');
+insert into depts_n20 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20);
+analyze table depts_n20 compute statistics for columns;
+
+create table dependents_n20 (
+ empid int,
+ name varchar(256))
+stored as orc TBLPROPERTIES ('transactional'='true');
+insert into dependents_n20 values (10, 'Michael'), (20, 'Jane');
+analyze table dependents_n20 compute statistics for columns;
+
+create table locations_n20 (
+ locationid int,
+ name varchar(256))
+stored as orc TBLPROPERTIES ('transactional'='true');
+insert into locations_n20 values (10, 'San Francisco'), (20, 'San Diego');
+analyze table locations_n20 compute statistics for columns;
+
+alter table emps_n30 add constraint pk1 primary key (empid) disable novalidate rely;
+alter table depts_n20 add constraint pk2 primary key (deptno) disable novalidate rely;
+alter table dependents_n20 add constraint pk3 primary key (empid) disable novalidate rely;
+alter table locations_n20 add constraint pk4 primary key (locationid) disable novalidate rely;
+
+alter table emps_n30 add constraint fk1 foreign key (deptno) references depts_n20(deptno) disable novalidate rely;
+alter table depts_n20 add constraint fk2 foreign key (locationid) references locations_n20(locationid) disable novalidate rely;
+
+-- EXAMPLE 1
+create materialized view mv1_n20 as
+select deptno, name, salary, commission
+from emps_n30;
+analyze table mv1_n20 compute statistics for columns;
+
+explain
+select emps_n30.name, emps_n30.salary, emps_n30.commission
+from emps_n30
+join depts_n20 using (deptno);
+
+select emps_n30.name, emps_n30.salary, emps_n30.commission
+from emps_n30
+join depts_n20 using (deptno);
+
+drop materialized view mv1_n20;
+
+-- EXAMPLE 2
+create materialized view mv1_n20 as
+select empid, emps_n30.deptno, count(*) as c, sum(empid) as s
+from emps_n30 join depts_n20 using (deptno)
+group by empid, emps_n30.deptno;
+analyze table mv1_n20 compute statistics for columns;
+
+explain
+select depts_n20.deptno, count(*) as c, sum(empid) as s
+from emps_n30 join depts_n20 using (deptno)
+group by depts_n20.deptno;
+
+select depts_n20.deptno, count(*) as c, sum(empid) as s
+from emps_n30 join depts_n20 using (deptno)
+group by depts_n20.deptno;
+
+drop materialized view mv1_n20;
+
+-- EXAMPLE 3
+create materialized view mv1_n20 as
+select dependents_n20.empid, emps_n30.deptno, sum(salary) as s
+from emps_n30
+join dependents_n20 on (emps_n30.empid = dependents_n20.empid)
+group by dependents_n20.empid, emps_n30.deptno;
+analyze table mv1_n20 compute statistics for columns;
+
+explain
+select dependents_n20.empid, sum(salary) as s
+from emps_n30
+join depts_n20 on (emps_n30.deptno = depts_n20.deptno)
+join dependents_n20 on (emps_n30.empid = dependents_n20.empid)
+group by dependents_n20.empid;
+
+select dependents_n20.empid, sum(salary) as s
+from emps_n30
+join depts_n20 on (emps_n30.deptno = depts_n20.deptno)
+join dependents_n20 on (emps_n30.empid = dependents_n20.empid)
+group by dependents_n20.empid;
+
+drop materialized view mv1_n20;
+
+-- EXAMPLE 4
+create materialized view mv1_n20 as
+select emps_n30.empid, emps_n30.deptno, emps_n30.name as name1, emps_n30.salary, emps_n30.commission, dependents_n20.name as name2
+from emps_n30 join dependents_n20 on (emps_n30.empid = dependents_n20.empid);
+analyze table mv1_n20 compute statistics for columns;
+
+explain
+select emps_n30.empid, dependents_n20.empid, emps_n30.deptno
+from emps_n30
+join dependents_n20 on (emps_n30.empid = dependents_n20.empid)
+join depts_n20 a on (emps_n30.deptno=a.deptno)
+where emps_n30.name = 'Bill';
+
+select emps_n30.empid, dependents_n20.empid, emps_n30.deptno
+from emps_n30
+join dependents_n20 on (emps_n30.empid = dependents_n20.empid)
+join depts_n20 a on (emps_n30.deptno=a.deptno)
+where emps_n30.name = 'Bill';
+
+drop materialized view mv1_n20;
+
+
+
+
+
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_no_join_opt_2.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_no_join_opt_2.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_no_join_opt_2.q
new file mode 100644
index 0000000..a137230
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_no_join_opt_2.q
@@ -0,0 +1,144 @@
+-- SORT_QUERY_RESULTS
+
+set hive.support.concurrency=true;
+set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
+set hive.strict.checks.cartesian.product=false;
+set hive.stats.fetch.column.stats=true;
+set hive.optimize.constraints.join=false;
+
+create table emps_n30 (
+ empid int,
+ deptno int,
+ name varchar(256),
+ salary float,
+ commission int)
+stored as orc TBLPROPERTIES ('transactional'='true');
+insert into emps_n30 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
+ (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250);
+analyze table emps_n30 compute statistics for columns;
+
+create table depts_n20 (
+ deptno int,
+ name varchar(256),
+ locationid int)
+stored as orc TBLPROPERTIES ('transactional'='true');
+insert into depts_n20 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20);
+analyze table depts_n20 compute statistics for columns;
+
+create table dependents_n20 (
+ empid int,
+ name varchar(256))
+stored as orc TBLPROPERTIES ('transactional'='true');
+insert into dependents_n20 values (10, 'Michael'), (20, 'Jane');
+analyze table dependents_n20 compute statistics for columns;
+
+create table locations_n20 (
+ locationid int,
+ name varchar(256))
+stored as orc TBLPROPERTIES ('transactional'='true');
+insert into locations_n20 values (10, 'San Francisco'), (20, 'San Diego');
+analyze table locations_n20 compute statistics for columns;
+
+alter table emps_n30 add constraint pk1 primary key (empid) disable novalidate rely;
+alter table depts_n20 add constraint pk2 primary key (deptno) disable novalidate rely;
+alter table dependents_n20 add constraint pk3 primary key (empid) disable novalidate rely;
+alter table locations_n20 add constraint pk4 primary key (locationid) disable novalidate rely;
+
+alter table emps_n30 add constraint fk1 foreign key (deptno) references depts_n20(deptno) disable novalidate rely;
+alter table depts_n20 add constraint fk2 foreign key (locationid) references locations_n20(locationid) disable novalidate rely;
+
+-- EXAMPLE 1
+create materialized view mv1_part_n2 partitioned on (deptno) as
+select * from emps_n30 where empid < 150;
+analyze table mv1_part_n2 compute statistics for columns;
+
+explain
+select *
+from (select * from emps_n30 where empid < 120) t
+join depts_n20 using (deptno);
+
+select *
+from (select * from emps_n30 where empid < 120) t
+join depts_n20 using (deptno);
+
+drop materialized view mv1_part_n2;
+
+-- EXAMPLE 2
+create materialized view mv1_part_n2 partitioned on (deptno) as
+select deptno, name, salary, commission
+from emps_n30;
+analyze table mv1_part_n2 compute statistics for columns;
+
+explain
+select emps_n30.name, emps_n30.salary, emps_n30.commission
+from emps_n30
+join depts_n20 using (deptno);
+
+select emps_n30.name, emps_n30.salary, emps_n30.commission
+from emps_n30
+join depts_n20 using (deptno);
+
+drop materialized view mv1_part_n2;
+
+-- EXAMPLE 4
+create materialized view mv1_part_n2 partitioned on (deptno) as
+select * from emps_n30 where empid < 200;
+analyze table mv1_part_n2 compute statistics for columns;
+
+explain
+select * from emps_n30 where empid > 120
+union all select * from emps_n30 where empid < 150;
+
+select * from emps_n30 where empid > 120
+union all select * from emps_n30 where empid < 150;
+
+drop materialized view mv1_part_n2;
+
+-- EXAMPLE 5
+create materialized view mv1_part_n2 partitioned on (name) as
+select name, salary from emps_n30 group by name, salary;
+analyze table mv1_part_n2 compute statistics for columns;
+
+explain
+select name, salary from emps_n30 group by name, salary;
+
+select name, salary from emps_n30 group by name, salary;
+
+drop materialized view mv1_part_n2;
+
+-- EXAMPLE 6
+create materialized view mv1_part_n2 partitioned on (name) as
+select name, salary from emps_n30 group by name, salary;
+analyze table mv1_part_n2 compute statistics for columns;
+
+explain
+select name from emps_n30 group by name;
+
+select name from emps_n30 group by name;
+
+drop materialized view mv1_part_n2;
+
+-- EXAMPLE 7
+create materialized view mv1_part_n2 partitioned on (name) as
+select name, salary from emps_n30 where deptno = 10 group by name, salary;
+analyze table mv1_part_n2 compute statistics for columns;
+
+explain
+select name from emps_n30 where deptno = 10 group by name;
+
+select name from emps_n30 where deptno = 10 group by name;
+
+drop materialized view mv1_part_n2;
+
+-- EXAMPLE 9
+create materialized view mv1_part_n2 partitioned on (name) as
+select name, salary, count(*) as c, sum(empid) as s
+from emps_n30 group by name, salary;
+analyze table mv1_part_n2 compute statistics for columns;
+
+explain
+select name from emps_n30 group by name;
+
+select name from emps_n30 group by name;
+
+drop materialized view mv1_part_n2;
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/queries/clientpositive/materialized_view_rewrite_part_1.q
----------------------------------------------------------------------
diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_part_1.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_part_1.q
index 5a2e74c..e6980c0 100644
--- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_part_1.q
+++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_part_1.q
@@ -15,7 +15,7 @@ create table emps_n30 (
commission int)
stored as orc TBLPROPERTIES ('transactional'='true');
insert into emps_n30 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
- (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250);
+ (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (120, 10, 'Bill', 10000, 250);
analyze table emps_n30 compute statistics for columns;
create table depts_n20 (
@@ -30,14 +30,14 @@ create table dependents_n20 (
empid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into dependents_n20 values (10, 'Michael'), (10, 'Jane');
+insert into dependents_n20 values (10, 'Michael'), (20, 'Jane');
analyze table dependents_n20 compute statistics for columns;
create table locations_n20 (
locationid int,
name varchar(256))
stored as orc TBLPROPERTIES ('transactional'='true');
-insert into locations_n20 values (10, 'San Francisco'), (10, 'San Diego');
+insert into locations_n20 values (10, 'San Francisco'), (20, 'San Diego');
analyze table locations_n20 compute statistics for columns;
alter table emps_n30 add constraint pk1 primary key (empid) disable novalidate rely;
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/ambiguitycheck.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/ambiguitycheck.q.out b/ql/src/test/results/clientpositive/ambiguitycheck.q.out
index 80c9582..e19f4b6 100644
--- a/ql/src/test/results/clientpositive/ambiguitycheck.q.out
+++ b/ql/src/test/results/clientpositive/ambiguitycheck.q.out
@@ -705,7 +705,7 @@ PREHOOK: query: explain extended select int(1.2) from src limit 1
PREHOOK: type: QUERY
POSTHOOK: query: explain extended select int(1.2) from src limit 1
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST(1.2 AS INTEGER) AS `_o__c0`
+OPTIMIZED SQL: SELECT CAST(1.2 AS INTEGER) AS `$f0`
FROM `default`.`src`
LIMIT 1
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/beeline/materialized_view_create_rewrite.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/beeline/materialized_view_create_rewrite.q.out b/ql/src/test/results/clientpositive/beeline/materialized_view_create_rewrite.q.out
index 7813aac..22305f5 100644
--- a/ql/src/test/results/clientpositive/beeline/materialized_view_create_rewrite.q.out
+++ b/ql/src/test/results/clientpositive/beeline/materialized_view_create_rewrite.q.out
@@ -124,7 +124,7 @@ STAGE PLANS:
alias: default.cmv_mat_view2_n4
Statistics: Num rows: 2 Data size: 232 Basic stats: COMPLETE Column stats: NONE
Select Operator
- expressions: 3 (type: int), c (type: decimal(10,2))
+ expressions: a (type: int), c (type: decimal(10,2))
outputColumnNames: _col0, _col1
Statistics: Num rows: 2 Data size: 232 Basic stats: COMPLETE Column stats: NONE
ListSink
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/druid/druidmini_mv.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/druid/druidmini_mv.q.out b/ql/src/test/results/clientpositive/druid/druidmini_mv.q.out
index 806262d..85ccd44 100644
--- a/ql/src/test/results/clientpositive/druid/druidmini_mv.q.out
+++ b/ql/src/test/results/clientpositive/druid/druidmini_mv.q.out
@@ -164,12 +164,12 @@ STAGE PLANS:
TableScan
alias: cmv_mat_view2_n0
properties:
- druid.fieldNames vc,c
+ druid.fieldNames a,c
druid.fieldTypes int,double
- druid.query.json {"queryType":"scan","dataSource":"default.cmv_mat_view2_n0","intervals":["1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z"],"virtualColumns":[{"type":"expression","name":"vc","expression":"3","outputType":"LONG"}],"columns":["vc","c"],"resultFormat":"compactedList"}
+ druid.query.json {"queryType":"scan","dataSource":"default.cmv_mat_view2_n0","intervals":["1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z"],"columns":["a","c"],"resultFormat":"compactedList"}
druid.query.type scan
Select Operator
- expressions: vc (type: int), c (type: double)
+ expressions: a (type: int), c (type: double)
outputColumnNames: _col0, _col1
ListSink
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/list_bucket_dml_2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/list_bucket_dml_2.q.out b/ql/src/test/results/clientpositive/list_bucket_dml_2.q.out
index bd8e215..5289fd8 100644
--- a/ql/src/test/results/clientpositive/list_bucket_dml_2.q.out
+++ b/ql/src/test/results/clientpositive/list_bucket_dml_2.q.out
@@ -361,7 +361,7 @@ PREHOOK: type: QUERY
POSTHOOK: query: explain extended
select * from list_bucketing_static_part_n4 where ds = '2008-04-08' and hr = '11' and key = '484' and value = 'val_484'
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `key`, CAST('val_484' AS STRING) AS `value`, CAST('2008-04-08' AS STRING) AS `ds`, CAST('11' AS STRING) AS `hr`
+OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `$f0`, CAST('val_484' AS STRING) AS `$f1`, CAST('2008-04-08' AS STRING) AS `$f2`, CAST('11' AS STRING) AS `$f3`
FROM `default`.`list_bucketing_static_part_n4`
WHERE `ds` = '2008-04-08' AND `hr` = '11' AND `key` = '484' AND `value` = 'val_484'
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/list_bucket_dml_4.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/list_bucket_dml_4.q.out b/ql/src/test/results/clientpositive/list_bucket_dml_4.q.out
index 520d48e..7881bba 100644
--- a/ql/src/test/results/clientpositive/list_bucket_dml_4.q.out
+++ b/ql/src/test/results/clientpositive/list_bucket_dml_4.q.out
@@ -804,7 +804,7 @@ PREHOOK: type: QUERY
POSTHOOK: query: explain extended
select * from list_bucketing_static_part_n2 where ds = '2008-04-08' and hr = '11' and key = '484' and value = 'val_484'
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `key`, CAST('val_484' AS STRING) AS `value`, CAST('2008-04-08' AS STRING) AS `ds`, CAST('11' AS STRING) AS `hr`
+OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `$f0`, CAST('val_484' AS STRING) AS `$f1`, CAST('2008-04-08' AS STRING) AS `$f2`, CAST('11' AS STRING) AS `$f3`
FROM `default`.`list_bucketing_static_part_n2`
WHERE `ds` = '2008-04-08' AND `hr` = '11' AND `key` = '484' AND `value` = 'val_484'
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/list_bucket_dml_9.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/list_bucket_dml_9.q.out b/ql/src/test/results/clientpositive/list_bucket_dml_9.q.out
index fbd4fde..acebfe8 100644
--- a/ql/src/test/results/clientpositive/list_bucket_dml_9.q.out
+++ b/ql/src/test/results/clientpositive/list_bucket_dml_9.q.out
@@ -804,7 +804,7 @@ PREHOOK: type: QUERY
POSTHOOK: query: explain extended
select * from list_bucketing_static_part_n0 where ds = '2008-04-08' and hr = '11' and key = '484' and value = 'val_484'
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `key`, CAST('val_484' AS STRING) AS `value`, CAST('2008-04-08' AS STRING) AS `ds`, CAST('11' AS STRING) AS `hr`
+OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `$f0`, CAST('val_484' AS STRING) AS `$f1`, CAST('2008-04-08' AS STRING) AS `$f2`, CAST('11' AS STRING) AS `$f3`
FROM `default`.`list_bucketing_static_part_n0`
WHERE `ds` = '2008-04-08' AND `hr` = '11' AND `key` = '484' AND `value` = 'val_484'
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/list_bucket_query_multiskew_1.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/list_bucket_query_multiskew_1.q.out b/ql/src/test/results/clientpositive/list_bucket_query_multiskew_1.q.out
index e324cab..e8cc54c 100644
--- a/ql/src/test/results/clientpositive/list_bucket_query_multiskew_1.q.out
+++ b/ql/src/test/results/clientpositive/list_bucket_query_multiskew_1.q.out
@@ -80,7 +80,7 @@ PREHOOK: query: explain extended SELECT key FROM fact_daily WHERE ( ds='1' and h
PREHOOK: type: QUERY
POSTHOOK: query: explain extended SELECT key FROM fact_daily WHERE ( ds='1' and hr='4') and (key='484' and value= 'val_484')
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `key`
+OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `$f0`
FROM `default`.`fact_daily`
WHERE `ds` = '1' AND `hr` = '4' AND `key` = '484' AND `value` = 'val_484'
STAGE DEPENDENCIES:
@@ -203,7 +203,7 @@ PREHOOK: query: explain extended SELECT key,value FROM fact_daily WHERE ( ds='1'
PREHOOK: type: QUERY
POSTHOOK: query: explain extended SELECT key,value FROM fact_daily WHERE ( ds='1' and hr='4') and (key='238' and value= 'val_238')
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST('238' AS STRING) AS `key`, CAST('val_238' AS STRING) AS `value`
+OPTIMIZED SQL: SELECT CAST('238' AS STRING) AS `$f0`, CAST('val_238' AS STRING) AS `$f1`
FROM `default`.`fact_daily`
WHERE `ds` = '1' AND `hr` = '4' AND `key` = '238' AND `value` = 'val_238'
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/list_bucket_query_multiskew_2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/list_bucket_query_multiskew_2.q.out b/ql/src/test/results/clientpositive/list_bucket_query_multiskew_2.q.out
index ec1e540..534c924 100644
--- a/ql/src/test/results/clientpositive/list_bucket_query_multiskew_2.q.out
+++ b/ql/src/test/results/clientpositive/list_bucket_query_multiskew_2.q.out
@@ -253,7 +253,7 @@ PREHOOK: query: explain extended SELECT key FROM fact_daily_n2 WHERE ds='1' and
PREHOOK: type: QUERY
POSTHOOK: query: explain extended SELECT key FROM fact_daily_n2 WHERE ds='1' and hr='4' and key= '406'
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST('406' AS STRING) AS `key`
+OPTIMIZED SQL: SELECT CAST('406' AS STRING) AS `$f0`
FROM `default`.`fact_daily_n2`
WHERE `ds` = '1' AND `hr` = '4' AND `key` = '406'
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/list_bucket_query_multiskew_3.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/list_bucket_query_multiskew_3.q.out b/ql/src/test/results/clientpositive/list_bucket_query_multiskew_3.q.out
index 889f23c..32ffe07 100644
--- a/ql/src/test/results/clientpositive/list_bucket_query_multiskew_3.q.out
+++ b/ql/src/test/results/clientpositive/list_bucket_query_multiskew_3.q.out
@@ -343,7 +343,7 @@ PREHOOK: type: QUERY
POSTHOOK: query: explain extended
SELECT * FROM fact_daily_n3 WHERE ds='1' and hr='2' and (key='484' and value='val_484')
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `key`, CAST('val_484' AS STRING) AS `value`, CAST('1' AS STRING) AS `ds`, CAST('2' AS STRING) AS `hr`
+OPTIMIZED SQL: SELECT CAST('484' AS STRING) AS `$f0`, CAST('val_484' AS STRING) AS `$f1`, CAST('1' AS STRING) AS `$f2`, CAST('2' AS STRING) AS `$f3`
FROM `default`.`fact_daily_n3`
WHERE `ds` = '1' AND `hr` = '2' AND `key` = '484' AND `value` = 'val_484'
STAGE DEPENDENCIES:
@@ -468,7 +468,7 @@ PREHOOK: type: QUERY
POSTHOOK: query: explain extended
SELECT * FROM fact_daily_n3 WHERE ds='1' and hr='3' and (key='327' and value='val_327')
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST('327' AS STRING) AS `key`, CAST('val_327' AS STRING) AS `value`, CAST('1' AS STRING) AS `ds`, CAST('3' AS STRING) AS `hr`
+OPTIMIZED SQL: SELECT CAST('327' AS STRING) AS `$f0`, CAST('val_327' AS STRING) AS `$f1`, CAST('1' AS STRING) AS `$f2`, CAST('3' AS STRING) AS `$f3`
FROM `default`.`fact_daily_n3`
WHERE `ds` = '1' AND `hr` = '3' AND `key` = '327' AND `value` = 'val_327'
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/list_bucket_query_oneskew_1.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/list_bucket_query_oneskew_1.q.out b/ql/src/test/results/clientpositive/list_bucket_query_oneskew_1.q.out
index dcff8a5..77b56d3 100644
--- a/ql/src/test/results/clientpositive/list_bucket_query_oneskew_1.q.out
+++ b/ql/src/test/results/clientpositive/list_bucket_query_oneskew_1.q.out
@@ -131,7 +131,7 @@ PREHOOK: query: explain extended SELECT x FROM fact_daily_n4 WHERE ds='1' and x=
PREHOOK: type: QUERY
POSTHOOK: query: explain extended SELECT x FROM fact_daily_n4 WHERE ds='1' and x=484
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST(484 AS INTEGER) AS `x`
+OPTIMIZED SQL: SELECT CAST(484 AS INTEGER) AS `$f0`
FROM `default`.`fact_daily_n4`
WHERE `ds` = '1' AND `x` = 484
STAGE DEPENDENCIES:
@@ -251,7 +251,7 @@ PREHOOK: query: explain extended SELECT x FROM fact_daily_n4 WHERE ds='1' and x=
PREHOOK: type: QUERY
POSTHOOK: query: explain extended SELECT x FROM fact_daily_n4 WHERE ds='1' and x=495
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST(495 AS INTEGER) AS `x`
+OPTIMIZED SQL: SELECT CAST(495 AS INTEGER) AS `$f0`
FROM `default`.`fact_daily_n4`
WHERE `ds` = '1' AND `x` = 495
STAGE DEPENDENCIES:
@@ -371,7 +371,7 @@ PREHOOK: query: explain extended SELECT x FROM fact_daily_n4 WHERE ds='1' and x=
PREHOOK: type: QUERY
POSTHOOK: query: explain extended SELECT x FROM fact_daily_n4 WHERE ds='1' and x=1
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST(1 AS INTEGER) AS `x`
+OPTIMIZED SQL: SELECT CAST(1 AS INTEGER) AS `$f0`
FROM `default`.`fact_daily_n4`
WHERE `ds` = '1' AND `x` = 1
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/list_bucket_query_oneskew_2.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/list_bucket_query_oneskew_2.q.out b/ql/src/test/results/clientpositive/list_bucket_query_oneskew_2.q.out
index 268051e..022af85 100644
--- a/ql/src/test/results/clientpositive/list_bucket_query_oneskew_2.q.out
+++ b/ql/src/test/results/clientpositive/list_bucket_query_oneskew_2.q.out
@@ -134,7 +134,7 @@ PREHOOK: query: explain extended select x from (select x from fact_daily_n5 wher
PREHOOK: type: QUERY
POSTHOOK: query: explain extended select x from (select x from fact_daily_n5 where ds = '1') subq where x = 484
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST(484 AS INTEGER) AS `x`
+OPTIMIZED SQL: SELECT CAST(484 AS INTEGER) AS `$f0`
FROM `default`.`fact_daily_n5`
WHERE `ds` = '1' AND `x` = 484
STAGE DEPENDENCIES:
http://git-wip-us.apache.org/repos/asf/hive/blob/befee74d/ql/src/test/results/clientpositive/llap/acid_bucket_pruning.q.out
----------------------------------------------------------------------
diff --git a/ql/src/test/results/clientpositive/llap/acid_bucket_pruning.q.out b/ql/src/test/results/clientpositive/llap/acid_bucket_pruning.q.out
index 3951b71..a12ac3a 100644
--- a/ql/src/test/results/clientpositive/llap/acid_bucket_pruning.q.out
+++ b/ql/src/test/results/clientpositive/llap/acid_bucket_pruning.q.out
@@ -30,7 +30,7 @@ PREHOOK: type: QUERY
POSTHOOK: query: EXPLAIN EXTENDED
SELECT * FROM acidTblDefault WHERE a = 1
POSTHOOK: type: QUERY
-OPTIMIZED SQL: SELECT CAST(1 AS INTEGER) AS `a`
+OPTIMIZED SQL: SELECT CAST(1 AS INTEGER) AS `$f0`
FROM `default`.`acidtbldefault`
WHERE `a` = 1
STAGE DEPENDENCIES: