This is an automated email from the ASF dual-hosted git repository.
krisztiankasa pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new 5916236ac62 HIVE-26737: Subquery returning wrong results when database
has materialized views (Steve Carlin, reviewed by Krisztian Kasa)
5916236ac62 is described below
commit 5916236ac6205fd5add66593ee09bd3cf6e1b19f
Author: scarlin-cloudera <55709772+scarlin-cloud...@users.noreply.github.com>
AuthorDate: Mon Dec 5 00:49:51 2022 -0800
HIVE-26737: Subquery returning wrong results when database has materialized
views (Steve Carlin, reviewed by Krisztian Kasa)
* HIVE-26737: Subquery returning wrong results when database has
materialized views
When there is a materialized view in the materialized view registry,
the HiveMaterializedViewASTSubQueryRewriteShuttle runs and rewrites some
RelNodes and RexNodes.
On creation time, the HivePlannerContext is given the RexSubquery nodes
which
are used to see if it is a correlated subquery with an agg. In the case
where
the RexSubQuery was rewritten, the Context contains references to stale
nodes.
It loses the correlated subquery information and creates an incorrect query
plan.
The boolean check for subqueries with agg is now done within the Calcite
nodes
rather than when checking the ASTNodes. It was only used at rule time, so it
made more sense for the calculation of the boolean value to be done there,
and
it's safer in the long run (as opposed to just update the global context
when
a new RexSubQuery is created).
The HiveFilter and HiveProject will contain the structure holding the
calculated correlation information. The information is done with a lazy
fetch
and only calculated when needed.
The HiveCorrelationInfo structure only contains information for the current
subquery level, similar to the old code. A correlated variable cannot go
down to a subquery within a subquery at this point.
---
.../ql/optimizer/calcite/HivePlannerContext.java | 14 +-
.../correlation/CorrelationInfoVisitor.java | 167 +++++++++++++
.../calcite/correlation/HiveCorrelationInfo.java | 108 +++++++++
.../optimizer/calcite/reloperators/HiveFilter.java | 103 +++-----
.../calcite/reloperators/HiveProject.java | 34 +++
.../calcite/rules/HiveSubQueryRemoveRule.java | 46 ++--
.../hadoop/hive/ql/parse/CalcitePlanner.java | 22 +-
.../apache/hadoop/hive/ql/parse/QBSubQuery.java | 52 +---
.../apache/hadoop/hive/ql/parse/SubQueryUtils.java | 11 +-
.../calcite/TestCBORuleFiredOnlyOnce.java | 3 +-
.../clientpositive/subquery_with_corr_and_mv.q | 57 +++++
.../clientpositive/llap/subquery_scalar.q.out | 14 +-
.../llap/subquery_with_corr_and_mv.q.out | 264 +++++++++++++++++++++
.../perf/tpcds30tb/tez/cbo_query32.q.out | 6 +-
.../perf/tpcds30tb/tez/cbo_query92.q.out | 6 +-
.../perf/tpcds30tb/tez/query32.q.out | 10 +-
.../perf/tpcds30tb/tez/query92.q.out | 10 +-
17 files changed, 721 insertions(+), 206 deletions(-)
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HivePlannerContext.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HivePlannerContext.java
index 3a86140fa73..08e82a91cde 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HivePlannerContext.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/HivePlannerContext.java
@@ -19,33 +19,24 @@ package org.apache.hadoop.hive.ql.optimizer.calcite;
import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.plan.Context;
-import org.apache.calcite.rel.RelNode;
import org.apache.hadoop.hive.ql.optimizer.calcite.cost.HiveAlgorithmsConf;
import org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveRulesRegistry;
import org.apache.hadoop.hive.ql.plan.mapper.StatsSource;
-import java.util.Set;
-
-
public class HivePlannerContext implements Context {
private HiveAlgorithmsConf algoConfig;
private HiveRulesRegistry registry;
private CalciteConnectionConfig calciteConfig;
- private SubqueryConf subqueryConfig;
private HiveConfPlannerContext isCorrelatedColumns;
private StatsSource statsSource;
public HivePlannerContext(HiveAlgorithmsConf algoConfig, HiveRulesRegistry
registry,
- CalciteConnectionConfig calciteConfig, Set<RelNode>
corrScalarRexSQWithAgg,
+ CalciteConnectionConfig calciteConfig,
HiveConfPlannerContext isCorrelatedColumns, StatsSource statsSource) {
this.algoConfig = algoConfig;
this.registry = registry;
this.calciteConfig = calciteConfig;
this.statsSource = statsSource;
- // this is to keep track if a subquery is correlated and contains aggregate
- // this is computed in CalcitePlanner while planning and is later required
by subuery remove rule
- // hence this is passed using HivePlannerContext
- this.subqueryConfig = new SubqueryConf(corrScalarRexSQWithAgg);
this.isCorrelatedColumns = isCorrelatedColumns;
}
@@ -60,9 +51,6 @@ public class HivePlannerContext implements Context {
if (clazz.isInstance(calciteConfig)) {
return clazz.cast(calciteConfig);
}
- if(clazz.isInstance(subqueryConfig)) {
- return clazz.cast(subqueryConfig);
- }
if(clazz.isInstance(isCorrelatedColumns)) {
return clazz.cast(isCorrelatedColumns);
}
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/correlation/CorrelationInfoVisitor.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/correlation/CorrelationInfoVisitor.java
new file mode 100644
index 00000000000..0f85ffbb1d2
--- /dev/null
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/correlation/CorrelationInfoVisitor.java
@@ -0,0 +1,167 @@
+/*
+ * 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.correlation;
+
+
+import com.google.common.collect.ImmutableList;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.CorrelationId;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexSubQuery;
+import org.apache.calcite.rex.RexShuttle;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelShuttleImpl;
+import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveAggregate;
+import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveFilter;
+import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveJoin;
+import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveProject;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Set;
+import java.util.LinkedHashSet;
+
+/**
+ * CorrelationIdVisitor digs out information from a given RexNode about all
the RexSubQuery
+ * RexNodes in the top layer. It does not go into RexSubQuery nodes within a
RexSubQuery node.
+ */
+public class CorrelationInfoVisitor extends RexShuttle {
+ // List of CorrelationInfo for all the found RexSubQuery, one
HiveCorrelationInfo for each
+ // RexSubQuery
+ private List<HiveCorrelationInfo> correlationInfos = new ArrayList<>();
+
+ // True if there is a "NOT" outside of the subquery
+ private boolean notFlag;
+
+ // Prevent direct instantiation. Information should be retrieved through
the static
+ // getCorrelationInfos method at the bottom of the file.
+ private CorrelationInfoVisitor() {
+ }
+
+ // Private method called by static methoc to retrieve information after
RexNode was visited
+ private List<HiveCorrelationInfo> getCorrelationInfos() {
+ return correlationInfos;
+ }
+
+ @Override
+ public RexNode visitCall(RexCall call) {
+ // Record if we see not flag before seeing the subquery.
+ // Can handle NOT(NOT(...)) if it exists
+ if (call.getOperator().getKind() == SqlKind.NOT) {
+ notFlag = !notFlag;
+ }
+ RexNode returnNode = super.visitCall(call);
+ // reset flag after visit.
+ if (call.getOperator().getKind() == SqlKind.NOT) {
+ notFlag = !notFlag;
+ }
+ return returnNode;
+ }
+
+ @Override
+ public RexNode visitSubQuery(RexSubQuery subQuery) {
+ // Call the RelNode Shuttle to gather all the information within the
RelNodes of the
+ // RexSubQUery
+ SubQueryRelNodeShuttle relShuttle = new SubQueryRelNodeShuttle(subQuery,
notFlag);
+ subQuery.rel.accept(relShuttle);
+ correlationInfos.add(relShuttle.getCorrelationInfo());
+ return subQuery;
+ }
+
+ /**
+ * InnerRexSubQueryVisitor is a shuttle that only gets called during the
RexSubQuery RexNode
+ * visit. The RexSubQuery has RelNodes inside. These RelNodes contain the
correlationIds used
+ * for the subquery within their RexNodes.
+ */
+ private static class InsideRexSubQueryVisitor extends RexShuttle {
+ private Set<CorrelationId> correlationIds = new LinkedHashSet<>();
+
+ @Override
+ public RexNode visitCorrelVariable(RexCorrelVariable variable) {
+ correlationIds.add(variable.id);
+ return super.visitCorrelVariable(variable);
+ }
+
+ public Set<CorrelationId> getCorrelationIds() {
+ return correlationIds;
+ }
+ }
+
+ /**
+ * The SubQueryRelNodeShuttle walks through the RelNodes within the
RexSubQuery.
+ * The CorrelationIds can be found within the RexNodes inside the RelNodes,
so
+ * we call the InsideRexSubQueryVisitor to fetch them. This visitor will
hold the
+ * array of correlationIds and keep the composite list of Ids across RelNodes
+ */
+ private static class SubQueryRelNodeShuttle extends HiveRelShuttleImpl {
+ public final RexSubQuery rexSubQuery;
+ public final boolean notFlag;
+ private HiveAggregate aggregateRel;
+ private InsideRexSubQueryVisitor visitor = new InsideRexSubQueryVisitor();
+
+ public SubQueryRelNodeShuttle(RexSubQuery rexSubQuery, boolean notFlag) {
+ this.rexSubQuery = rexSubQuery;
+ this.notFlag = notFlag;
+ }
+
+ @Override
+ public RelNode visit(HiveFilter filter) {
+ // call the shuttle to fetch the CorrelationIds
+ filter.getCondition().accept(visitor);
+ return super.visit(filter);
+ }
+
+ @Override
+ public RelNode visit(HiveProject project) {
+ for (RexNode r : project.getProjects()) {
+ // call the shuttle for each project to fetch the CorrelationIds
+ r.accept(visitor);
+ }
+ return super.visit(project);
+ }
+
+ @Override
+ public RelNode visit(HiveJoin join) {
+ // call the shuttle to fetch the CorrelationIds
+ join.getCondition().accept(visitor);
+ return super.visit(join);
+ }
+
+ @Override
+ public RelNode visit(HiveAggregate aggregate) {
+ // capture the aggregate RelNode to grab information off of it.
+ if (this.aggregateRel == null) {
+ this.aggregateRel = aggregate;
+ }
+ return super.visit(aggregate);
+ }
+
+ public HiveCorrelationInfo getCorrelationInfo() {
+ return new HiveCorrelationInfo(visitor.getCorrelationIds(), rexSubQuery,
+ aggregateRel, notFlag);
+ }
+ }
+
+ public static List<HiveCorrelationInfo> getCorrelationInfos(RexNode rexNode)
{
+ CorrelationInfoVisitor visitor = new CorrelationInfoVisitor();
+ rexNode.accept(visitor);
+ return ImmutableList.copyOf(visitor.getCorrelationInfos());
+ }
+}
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/correlation/HiveCorrelationInfo.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/correlation/HiveCorrelationInfo.java
new file mode 100644
index 00000000000..2cbec32fe6c
--- /dev/null
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/correlation/HiveCorrelationInfo.java
@@ -0,0 +1,108 @@
+/*
+ * 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.correlation;
+
+import com.google.common.collect.ImmutableSet;
+import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.CorrelationId;
+import org.apache.calcite.rex.RexSubQuery;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveAggregate;
+import java.util.Set;
+
+/**
+ * HiveCorrelationInfo contains information gathered by visiting the
RexSubQuery node
+ */
+public class HiveCorrelationInfo {
+ // The RexSubQuery relevant to the correlation info.
+ public final RexSubQuery rexSubQuery;
+
+ // List of all correlationIds within the SubQuery. This does not contain
correlationIds
+ // of a RexSubQuery nested within a RexSubQuery
+ public final Set<CorrelationId> correlationIds;
+
+ // the Aggreate RelNode if it exists within the RexNode
+ public final HiveAggregate aggregateRel;
+
+ // True if there is a not flag before the RexSubQuery (e.g. NOT EXISTS
(select ...)
+ public final boolean notFlag;
+
+ public HiveCorrelationInfo(Set<CorrelationId> correlationIds, RexSubQuery
rexSubQuery,
+ HiveAggregate aggregateRel, boolean notFlag) {
+ this.correlationIds = ImmutableSet.copyOf(correlationIds);
+ this.rexSubQuery = rexSubQuery;
+ this.aggregateRel = aggregateRel;
+ this.notFlag = notFlag;
+ }
+
+
+ /**
+ * isCorrScalarQuery returns true for special cases as specified in the
+ * HiveSubQueryRemoveRule rewrite methods for steps that need to be taken
+ * before writing the join operator. It will add an sq_count_check to ensure
+ * that there is a row.
+ *
+ * This logic was copied from QBSubQuery.java for HIVE-26736. The following
+ * comment was copied from there:
+ *
+ * Restriction.13.m :: In the case of an implied Group By on a
+ * correlated SubQuery, the SubQuery always returns 1 row.
+ * Following is special cases for different type of subqueries which have
aggregate and implicit group by
+ * and are correlatd
+ * * SCALAR - This should return true since later in subquery remove
+ * rule we need to know about this case.
+ * * IN - always allowed, BUT returns true for cases with aggregate
other than COUNT since later in subquery remove
+ * rule we need to know about this case.
+ * * NOT IN - always allow, but always return true because later subq
remove rule will generate diff plan for this case
+ */
+ public boolean isCorrScalarQuery() {
+ if (aggregateRel == null) {
+ return false;
+ }
+
+ if (hasExplicitGroupBy()) {
+ return false;
+ }
+
+ switch (rexSubQuery.getKind()) {
+ case SCALAR_QUERY:
+ return hasCorrelation();
+ case IN:
+ return notFlag ? hasCorrelation() : hasCorrelation() && hasCount();
+ default:
+ return false;
+ }
+ }
+
+ private boolean hasExplicitGroupBy() {
+ return aggregateRel.getGroupCount() > 0;
+ }
+
+ private boolean hasCorrelation() {
+ return !correlationIds.isEmpty();
+ }
+
+ private boolean hasCount() {
+ for (AggregateCall aggCall : aggregateRel.getAggCallList()) {
+ if (aggCall.getAggregation().getKind() == SqlKind.COUNT) {
+ return true;
+ }
+ }
+ return false;
+ }
+}
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/reloperators/HiveFilter.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/reloperators/HiveFilter.java
index 7f11a2ec341..241d2b09153 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/reloperators/HiveFilter.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/reloperators/HiveFilter.java
@@ -25,19 +25,21 @@ import org.apache.calcite.rel.core.Filter;
import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
-import org.apache.calcite.rex.RexCall;
-import org.apache.calcite.rex.RexCorrelVariable;
-import org.apache.calcite.rex.RexFieldAccess;
-import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexNode;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.correlation.CorrelationInfoVisitor;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.correlation.HiveCorrelationInfo;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelShuttle;
import org.apache.hadoop.hive.ql.optimizer.calcite.TraitsUtil;
import org.apache.calcite.rel.core.CorrelationId;
+import java.util.List;
import java.util.Set;
-import java.util.HashSet;
+import java.util.LinkedHashSet;
public class HiveFilter extends Filter implements HiveRelNode {
+ // Information about correlations within a subquery.
+ private final CorrelationInfoSupplier correlationInfos;
+
public static class StatEnhancedHiveFilter extends HiveFilter {
private long rowCount;
@@ -68,6 +70,7 @@ public class HiveFilter extends Filter implements HiveRelNode
{
public HiveFilter(RelOptCluster cluster, RelTraitSet traits, RelNode child,
RexNode condition) {
super(cluster, TraitsUtil.getDefaultTraitSet(cluster), child, condition);
+ this.correlationInfos = new CorrelationInfoSupplier(getCondition());
}
@Override
@@ -76,70 +79,13 @@ public class HiveFilter extends Filter implements
HiveRelNode {
return new HiveFilter(getCluster(), traitSet, input, condition);
}
- private static void findCorrelatedVar(RexNode node, Set<CorrelationId>
allVars) {
- if(node instanceof RexCall) {
- RexCall nd = (RexCall)node;
- for (RexNode rn : nd.getOperands()) {
- if (rn instanceof RexFieldAccess) {
- final RexNode ref = ((RexFieldAccess) rn).getReferenceExpr();
- if (ref instanceof RexCorrelVariable) {
- allVars.add(((RexCorrelVariable) ref).id);
- }
- } else {
- findCorrelatedVar(rn, allVars);
- }
- }
- }
- }
-
- //traverse the given node to find all correlated variables
- // Note that correlated variables are supported in Filter only i.e. Where &
Having
- private static void traverseFilter(RexNode node, Set<CorrelationId> allVars)
{
- if(node instanceof RexSubQuery) {
- RexSubQuery rexSubQuery = (RexSubQuery) node;
- //we expect correlated variables in HiveFilter only for now.
- // Also check for case where operator has 0 inputs .e.g TableScan
- if (rexSubQuery.rel.getInputs().isEmpty()) {
- return;
- }
- RelNode input = rexSubQuery.rel.getInput(0);
- while(input != null && !(input instanceof HiveFilter)
- && input.getInputs().size() >=1) {
- //we don't expect corr vars within UNION for now
- if(input.getInputs().size() > 1) {
- if (input instanceof HiveJoin) {
- findCorrelatedVar(((HiveJoin) input).getJoinFilter(),
allVars);
- }
- return;
- }
- input = input.getInput(0);
- }
- if(input != null && input instanceof HiveFilter) {
- findCorrelatedVar(((HiveFilter)input).getCondition(), allVars);
- }
- return;
- }
- //AND, NOT etc
- if(node instanceof RexCall) {
- int numOperands = ((RexCall)node).getOperands().size();
- for(int i=0; i<numOperands; i++) {
- RexNode op = ((RexCall)node).getOperands().get(i);
- traverseFilter(op, allVars);
- }
- }
- }
-
@Override
public Set<CorrelationId> getVariablesSet() {
- Set<CorrelationId> allCorrVars = new HashSet<>();
- traverseFilter(condition, allCorrVars);
- return allCorrVars;
- }
-
- public static Set<CorrelationId> getVariablesSet(RexSubQuery e) {
- Set<CorrelationId> allCorrVars = new HashSet<>();
- traverseFilter(e, allCorrVars);
- return allCorrVars;
+ Set<CorrelationId> correlationIds = new LinkedHashSet<>();
+ for (HiveCorrelationInfo h : correlationInfos.get()) {
+ correlationIds.addAll(h.correlationIds);
+ }
+ return correlationIds;
}
@Override
@@ -150,4 +96,27 @@ public class HiveFilter extends Filter implements
HiveRelNode {
return shuttle.visit(this);
}
+ public List<HiveCorrelationInfo> getCorrelationInfos() {
+ return correlationInfos.get();
+ }
+
+ /**
+ * CorrelationInfoSupplier allows for a lazy fetch so that the
HiveCorrelationInfo
+ * only gets retrieved on demand.
+ */
+ private static class CorrelationInfoSupplier {
+ private final RexNode condition;
+ public CorrelationInfoSupplier(RexNode condition) {
+ this.condition = condition;
+ }
+
+ List<HiveCorrelationInfo> correlationInfos;
+
+ public List<HiveCorrelationInfo> get() {
+ if (correlationInfos == null) {
+ correlationInfos =
CorrelationInfoVisitor.getCorrelationInfos(condition);
+ }
+ return correlationInfos;
+ }
+ }
}
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/reloperators/HiveProject.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/reloperators/HiveProject.java
index b0abeaac120..46edad00f7a 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/reloperators/HiveProject.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/reloperators/HiveProject.java
@@ -18,6 +18,7 @@
package org.apache.hadoop.hive.ql.optimizer.calcite.reloperators;
import java.util.Collections;
+import java.util.ArrayList;
import java.util.List;
import org.apache.calcite.plan.RelOptCluster;
@@ -37,9 +38,14 @@ import
org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSemanticException;
import
org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSemanticException.UnsupportedFeature;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelShuttle;
import org.apache.hadoop.hive.ql.optimizer.calcite.TraitsUtil;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.correlation.CorrelationInfoVisitor;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.correlation.HiveCorrelationInfo;
public class HiveProject extends Project implements HiveRelNode {
+ // Information about correlations within a subquery.
+ private final CorrelationInfoSupplier correlationInfos;
+
private boolean isSysnthetic;
/**
@@ -56,6 +62,7 @@ public class HiveProject extends Project implements
HiveRelNode {
public HiveProject(RelOptCluster cluster, RelTraitSet traitSet, RelNode
child, List<? extends RexNode> exps,
RelDataType rowType) {
super(cluster, traitSet, child, exps, rowType);
+ this.correlationInfos = new CorrelationInfoSupplier(getProjects());
assert traitSet.containsIfApplicable(HiveRelNode.CONVENTION);
}
@@ -134,4 +141,31 @@ public class HiveProject extends Project implements
HiveRelNode {
return super.explainTerms(pw)
.itemIf("synthetic", this.isSysnthetic, pw.getDetailLevel() ==
SqlExplainLevel.DIGEST_ATTRIBUTES);
}
+
+ public List<HiveCorrelationInfo> getCorrelationInfos() {
+ return correlationInfos.get();
+ }
+
+ /**
+ * CorrelationInfoSupplier allows for a lazy fetch so that the
HiveCorrelationInfo
+ * only gets retrieved on demand.
+ */
+ private static class CorrelationInfoSupplier {
+ public final List<RexNode> projects;
+ private List<HiveCorrelationInfo> correlationInfos;
+
+ public CorrelationInfoSupplier(List<RexNode> projects) {
+ this.projects = projects;
+ }
+
+ public List<HiveCorrelationInfo> get() {
+ if (correlationInfos == null) {
+ correlationInfos = new ArrayList<>();
+ for (RexNode r : projects) {
+
correlationInfos.addAll(CorrelationInfoVisitor.getCorrelationInfos(r));
+ }
+ }
+ return correlationInfos;
+ }
+ }
}
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
index 364c0bcfc0b..705b04728c4 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveSubQueryRemoveRule.java
@@ -17,6 +17,7 @@
*/
package org.apache.hadoop.hive.ql.optimizer.calcite.rules;
+import com.google.common.base.Preconditions;
import org.apache.calcite.plan.RelOptRule;
import org.apache.calcite.plan.RelOptRuleCall;
import org.apache.calcite.plan.RelOptRuleOperand;
@@ -56,7 +57,7 @@ import org.apache.hadoop.hive.conf.HiveConf;
import
org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSubqueryRuntimeException;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelShuttleImpl;
-import org.apache.hadoop.hive.ql.optimizer.calcite.SubqueryConf;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.correlation.HiveCorrelationInfo;
import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveAggregate;
import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveFilter;
import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveProject;
@@ -89,7 +90,7 @@ public class HiveSubQueryRemoveRule extends RelOptRule {
RelOptRule.operandJ(HiveFilter.class, null,
RexUtil.SubQueryFinder::containsSubQuery, any()),
"SubQueryRemoveRule:Filter", conf);
}
-
+
private final HiveConf conf;
private HiveSubQueryRemoveRule(RelOptRuleOperand operand, String
description, HiveConf conf) {
@@ -104,44 +105,43 @@ public class HiveSubQueryRemoveRule extends RelOptRule {
// if subquery is in FILTER
if (relNode instanceof HiveFilter) {
final HiveFilter filter = call.rel(0);
- final RexSubQuery e = RexUtil.SubQueryFinder.find(filter.getCondition());
- assert e != null;
-
- final RelOptUtil.Logic logic =
- LogicVisitor.find(RelOptUtil.Logic.TRUE,
ImmutableList.of(filter.getCondition()), e);
+ // Since there is a RexSubQuery, there should be a HiveCorrelationInfo
+ // in the RelNode
+ Preconditions.checkState(!filter.getCorrelationInfos().isEmpty());
+ HiveCorrelationInfo correlationInfo =
filter.getCorrelationInfos().get(0);
+ final RelOptUtil.Logic logic = LogicVisitor.find(RelOptUtil.Logic.TRUE,
+ ImmutableList.of(filter.getCondition()),
correlationInfo.rexSubQuery);
builder.push(filter.getInput());
final int fieldCount = builder.peek().getRowType().getFieldCount();
- SubqueryConf subqueryConfig =
filter.getCluster().getPlanner().getContext().unwrap(SubqueryConf.class);
- boolean isCorrScalarQuery =
subqueryConfig.getCorrScalarRexSQWithAgg().contains(e.rel);
+ boolean isCorrScalarQuery = correlationInfo.isCorrScalarQuery();
final RexNode target =
- apply(call.getMetadataQuery(), e, HiveFilter.getVariablesSet(e),
logic, builder, 1,
- fieldCount, isCorrScalarQuery);
- final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
+ apply(call.getMetadataQuery(), correlationInfo.rexSubQuery,
+ correlationInfo.correlationIds, logic, builder, 1, fieldCount,
isCorrScalarQuery);
+ final RexShuttle shuttle = new
ReplaceSubQueryShuttle(correlationInfo.rexSubQuery, target);
builder.filter(shuttle.apply(filter.getCondition()));
builder.project(fields(builder, filter.getRowType().getFieldCount()));
RelNode newRel = builder.build();
call.transformTo(newRel);
} else if (relNode instanceof HiveProject) {
- // if subquery is in PROJECT
final HiveProject project = call.rel(0);
- final RexSubQuery e = RexUtil.SubQueryFinder.find(project.getProjects());
- assert e != null;
+ // Since there is a RexSubQuery, there should be a HiveCorrelationInfo
+ // in the RelNode
+ Preconditions.checkState(!project.getCorrelationInfos().isEmpty());
+ HiveCorrelationInfo correlationInfo =
project.getCorrelationInfos().get(0);
- final RelOptUtil.Logic logic =
- LogicVisitor.find(RelOptUtil.Logic.TRUE_FALSE_UNKNOWN,
project.getProjects(), e);
+ final RelOptUtil.Logic logic =
LogicVisitor.find(RelOptUtil.Logic.TRUE_FALSE_UNKNOWN,
+ project.getProjects(), correlationInfo.rexSubQuery);
builder.push(project.getInput());
final int fieldCount = builder.peek().getRowType().getFieldCount();
- SubqueryConf subqueryConfig =
-
project.getCluster().getPlanner().getContext().unwrap(SubqueryConf.class);
- boolean isCorrScalarQuery =
subqueryConfig.getCorrScalarRexSQWithAgg().contains(e.rel);
+ boolean isCorrScalarQuery = correlationInfo.isCorrScalarQuery();
final RexNode target =
- apply(call.getMetadataQuery(), e, HiveFilter.getVariablesSet(e),
logic, builder, 1,
- fieldCount, isCorrScalarQuery);
- final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
+ apply(call.getMetadataQuery(), correlationInfo.rexSubQuery,
+ correlationInfo.correlationIds, logic, builder, 1, fieldCount,
isCorrScalarQuery);
+ final RexShuttle shuttle = new
ReplaceSubQueryShuttle(correlationInfo.rexSubQuery, target);
builder.project(shuttle.apply(project.getProjects()),
project.getRowType().getFieldNames());
call.transformTo(builder.build());
}
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 7bb42e89e9f..5caa11d6745 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
@@ -499,12 +499,11 @@ public class CalcitePlanner extends SemanticAnalyzer {
}
public static RelOptPlanner createPlanner(HiveConf conf) {
- return createPlanner(conf, new HashSet<>(), EmptyStatsSource.INSTANCE,
false);
+ return createPlanner(conf, EmptyStatsSource.INSTANCE, false);
}
private static RelOptPlanner createPlanner(
- HiveConf conf, Set<RelNode> corrScalarRexSQWithAgg,
- StatsSource statsSource, boolean isExplainPlan) {
+ HiveConf conf, StatsSource statsSource, boolean isExplainPlan) {
final Double maxSplitSize = (double) HiveConf.getLongVar(
conf, HiveConf.ConfVars.MAPREDMAXSPLITSIZE);
final Double maxMemory = (double) HiveConf.getLongVar(
@@ -523,7 +522,6 @@ public class CalcitePlanner extends SemanticAnalyzer {
boolean heuristicMaterializationStrategy = HiveConf.getVar(conf,
HiveConf.ConfVars.HIVE_MATERIALIZED_VIEW_REWRITING_SELECTION_STRATEGY).equals("heuristic");
HivePlannerContext confContext = new HivePlannerContext(algorithmsConf,
registry, calciteConfig,
- corrScalarRexSQWithAgg,
new HiveConfPlannerContext(isCorrelatedColumns,
heuristicMaterializationStrategy, isExplainPlan),
statsSource);
RelOptPlanner planner = HiveVolcanoPlanner.createPlanner(confContext);
@@ -1602,10 +1600,6 @@ public class CalcitePlanner extends SemanticAnalyzer {
// correlated vars across subqueries within same query needs to have
different ID
private int subqueryId;
- // this is to keep track if a subquery is correlated and contains aggregate
- // since this is special cased when it is rewritten in SubqueryRemoveRule
- Set<RelNode> corrScalarRexSQWithAgg = new HashSet<RelNode>();
-
// TODO: Do we need to keep track of RR, ColNameToPosMap for every op or
// just last one.
LinkedHashMap<RelNode, RowResolver> relToHiveRR
= new LinkedHashMap<RelNode, RowResolver>();
@@ -1637,7 +1631,7 @@ public class CalcitePlanner extends SemanticAnalyzer {
/*
* recreate cluster, so that it picks up the additional traitDef
*/
- RelOptPlanner planner = createPlanner(conf, corrScalarRexSQWithAgg,
statsSource, ctx.isExplainPlan());
+ RelOptPlanner planner = createPlanner(conf, statsSource,
ctx.isExplainPlan());
final RexBuilder rexBuilder = cluster.getRexBuilder();
final RelOptCluster optCluster = RelOptCluster.create(planner,
rexBuilder);
@@ -3395,7 +3389,6 @@ public class CalcitePlanner extends SemanticAnalyzer {
Map<ASTNode, QBSubQueryParseInfo>
subQueryToRelNode)
throws CalciteSubquerySemanticException {
- Set<ASTNode> corrScalarQueriesWithAgg = new HashSet<ASTNode>();
boolean isSubQuery = false;
boolean enableJoinReordering = false;
try {
@@ -3419,7 +3412,7 @@ public class CalcitePlanner extends SemanticAnalyzer {
//disallow subqueries which HIVE doesn't currently support
SubQueryUtils.subqueryRestrictionCheck(qb, next, srcRel,
forHavingClause,
- corrScalarQueriesWithAgg, ctx, this.relToHiveRR);
+ ctx, this.relToHiveRR);
String sbQueryAlias = "sq_" + qb.incrNumSubQueryPredicates();
QB qbSQ = new QB(qb.getId(), sbQueryAlias, true);
@@ -3437,13 +3430,6 @@ public class CalcitePlanner extends SemanticAnalyzer {
}
subQueryToRelNode.put(next,
parseInfo.setSubQueryRelNode(subQueryRelNode));
- //keep track of subqueries which are scalar, correlated and
contains aggregate
- // subquery expression. This will later be special cased in
Subquery remove rule
- // for correlated scalar queries with aggregate we have take care
of the case where
- // inner aggregate happens on empty result
- if (corrScalarQueriesWithAgg.contains(next)) {
- corrScalarRexSQWithAgg.add(subQueryRelNode);
- }
isSubQuery = true;
enableJoinReordering = true;
break;
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java
b/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java
index 4307dceed96..affd608c38c 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/QBSubQuery.java
@@ -529,7 +529,7 @@ public class QBSubQuery implements ISubQueryJoinInfo {
*/
void subqueryRestrictionsCheck(RowResolver parentQueryRR,
boolean forHavingClause,
- String outerQueryAlias, boolean []
subqueryConfig)
+ String outerQueryAlias)
throws SemanticException {
ASTNode insertClause = getChildFromSubqueryAST("Insert",
HiveParser.TOK_INSERT);
@@ -554,25 +554,20 @@ public class QBSubQuery implements ISubQueryJoinInfo {
subQueryAST, "SubQuery can contain only 1 item in Select List."));
}
- boolean hasAggregateExprs = false;
boolean hasWindowing = false;
// we need to know if aggregate is COUNT since IN corr subq with count
aggregate
// is not special cased later in subquery remove rule
- boolean hasCount = false;
for(int i= selectExprStart; i < selectClause.getChildCount(); i++ ) {
ASTNode selectItem = (ASTNode) selectClause.getChild(i);
int r = SubQueryUtils.checkAggOrWindowing(selectItem);
hasWindowing = hasWindowing | ( r == 3);
- hasAggregateExprs = hasAggregateExprs | ( r == 1 | r== 2 );
- hasCount = hasCount | ( r == 2 );
}
// figure out correlation and presence of non-equi join predicate
boolean hasCorrelation = false;
- boolean hasNonEquiJoinPred = false;
ASTNode whereClause = SubQueryUtils.subQueryWhere(insertClause);
if ( whereClause != null ) {
@@ -588,18 +583,6 @@ public class QBSubQuery implements ISubQueryJoinInfo {
if (conjunct.isCorrelated()) {
hasCorrelation = true;
}
- if (conjunct.eitherSideRefersBoth() && conjunctAST.getType() !=
HiveParser.EQUAL) {
- hasNonEquiJoinPred = true;
- }
- }
- }
-
- // figure out if there is group by
- boolean hasExplicitGby = false;
- for(int i=0; i<insertClause.getChildCount(); i++) {
- if(insertClause.getChild(i).getType() == HiveParser.TOK_GROUPBY) {
- hasExplicitGby = true;
- break;
}
}
@@ -613,39 +596,6 @@ public class QBSubQuery implements ISubQueryJoinInfo {
subQueryAST, "Only Correlated Exists/Not exists Sub Queries can
contain Windowing clauses."));
}
- /*
- * Restriction.13.m :: In the case of an implied Group By on a
- * correlated SubQuery, the SubQuery always returns 1 row.
- */
- // Following is special cases for different type of subqueries which
have aggregate and implicit group by
- // and are correlatd
- // * SCALAR - This should return true since later in subquery remove
- // rule we need to know about this case.
- // * IN - always allowed, BUT returns true for cases with aggregate
other than COUNT since later in subquery remove
- // rule we need to know about this case.
- // * NOT IN - always allow, but always return true because later subq
remove rule will generate diff plan for this case
- if (hasAggregateExprs &&
- !hasExplicitGby) {
-
- if(operator.getType() == SubQueryType.SCALAR) {
- if(!hasWindowing) {
- subqueryConfig[1] = true;
- }
- if(hasCorrelation) {
- subqueryConfig[0] = true;
- }
- }
- else if(operator.getType() == SubQueryType.IN) {
- if(hasCount && hasCorrelation) {
- subqueryConfig[0] = true;
- }
- }
- else if (operator.getType() == SubQueryType.NOT_IN) {
- if(hasCorrelation) {
- subqueryConfig[0] = true;
- }
- }
- }
}
void validateAndRewriteAST(RowResolver outerQueryRR,
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java
b/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java
index b6dc51e4956..7d5964ded01 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/SubQueryUtils.java
@@ -26,7 +26,6 @@ import java.util.Deque;
import java.util.List;
import java.util.LinkedHashMap;
import java.util.Map;
-import java.util.Set;
import org.antlr.runtime.CommonToken;
import org.antlr.runtime.tree.CommonTreeAdaptor;
@@ -107,7 +106,7 @@ public class SubQueryUtils {
}
static public void subqueryRestrictionCheck(QB qb, ASTNode subqueryExprNode,
RelNode srcRel,
- boolean forHavingClause, Set<ASTNode> corrScalarQueries, Context ctx,
+ boolean forHavingClause, Context ctx,
LinkedHashMap<RelNode, RowResolver> relToHiveRR)
throws SemanticException {
@@ -157,14 +156,8 @@ public class SubQueryUtils {
String havingInputAlias = null;
- boolean [] subqueryConfig = {false, false};
subQuery.subqueryRestrictionsCheck(inputRR, forHavingClause,
- havingInputAlias, subqueryConfig);
-
- if(subqueryConfig[0]) {
- corrScalarQueries.add(subqueryExprNode);
- }
- //}
+ havingInputAlias);
}
diff --git
a/ql/src/test/org/apache/hadoop/hive/ql/optimizer/calcite/TestCBORuleFiredOnlyOnce.java
b/ql/src/test/org/apache/hadoop/hive/ql/optimizer/calcite/TestCBORuleFiredOnlyOnce.java
index 081d5f8c350..25e20b6167f 100644
---
a/ql/src/test/org/apache/hadoop/hive/ql/optimizer/calcite/TestCBORuleFiredOnlyOnce.java
+++
b/ql/src/test/org/apache/hadoop/hive/ql/optimizer/calcite/TestCBORuleFiredOnlyOnce.java
@@ -61,8 +61,7 @@ public class TestCBORuleFiredOnlyOnce {
// Create rules registry to not trigger a rule more than once
HiveRulesRegistry registry = new HiveRulesRegistry();
- HivePlannerContext context = new HivePlannerContext(null, registry, null,
- null, null, null);
+ HivePlannerContext context = new HivePlannerContext(null, registry, null,
null, null);
HepPlanner planner = new HepPlanner(programBuilder.build(), context);
// Cluster
diff --git a/ql/src/test/queries/clientpositive/subquery_with_corr_and_mv.q
b/ql/src/test/queries/clientpositive/subquery_with_corr_and_mv.q
new file mode 100644
index 00000000000..274ad204b6a
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/subquery_with_corr_and_mv.q
@@ -0,0 +1,57 @@
+set hive.support.concurrency=true;
+set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
+
+CREATE TABLE t_test1(
+ id int,
+ int_col int,
+ year int,
+ month int
+);
+
+CREATE TABLE t_test2(
+ id int,
+ int_col int,
+ year int,
+ month int
+);
+
+CREATE TABLE t_test3(
+ id int,
+ int_col int,
+ year int,
+ month int
+);
+
+CREATE TABLE t_test4(
+ id int,
+ int_col int,
+ year int,
+ month int
+);
+
+
+CREATE TABLE dummy (
+ id int
+) stored as orc TBLPROPERTIES ('transactional'='true');
+
+CREATE MATERIALIZED VIEW need_a_mat_view_in_registry AS
+SELECT * FROM dummy where id > 5;
+
+INSERT INTO t_test1 VALUES (1, 1, 2009, 1), (10,0, 2009, 1);
+INSERT INTO t_test2 VALUES (1, 1, 2009, 1);
+INSERT INTO t_test3 VALUES (1, 1, 2009, 1);
+INSERT INTO t_test4 VALUES (1, 1, 2009, 1);
+
+select id, int_col, year, month from t_test1 s where s.int_col = (select
count(*) from t_test2 t where s.id = t.id) order by id;
+explain cbo select id, int_col, year, month from t_test1 s where s.int_col =
(select count(*) from t_test2 t where s.id = t.id) order by id;
+
+explain cbo
+select id, int_col, year, month from t_test2 s where not (
+ s.int_col in (select count(*) from t_test3 t2 where s.id = t2.id) and
+ s.int_col in (select count(*) from t_test4 t3 where s.id = t3.id)
+);
+select id, int_col, year, month from t_test2 s where not (
+ s.int_col in (select count(*) from t_test3 t2 where s.id = t2.id) and
+ s.int_col in (select count(*) from t_test4 t3 where s.id = t3.id)
+);
+
diff --git a/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
b/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
index 47437fe202d..9df6393010a 100644
--- a/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
+++ b/ql/src/test/results/clientpositive/llap/subquery_scalar.q.out
@@ -4094,9 +4094,9 @@ STAGE PLANS:
Inner Join 0 to 1
keys:
0 _col3 (type: int)
- 1 _col0 (type: int)
- outputColumnNames: _col1, _col2, _col5
- residual filter predicates: {(_col1 > _col5)}
+ 1 _col1 (type: int)
+ outputColumnNames: _col1, _col2, _col4
+ residual filter predicates: {(_col1 > _col4)}
Statistics: Num rows: 8 Data size: 192 Basic stats: COMPLETE
Column stats: COMPLETE
Select Operator
expressions: _col2 (type: double)
@@ -4141,16 +4141,16 @@ STAGE PLANS:
predicate: (_col1 is not null and _col2 is not null) (type:
boolean)
Statistics: Num rows: 100 Data size: 2000 Basic stats:
COMPLETE Column stats: COMPLETE
Select Operator
- expressions: _col0 (type: int), (_col1 / _col2) (type:
double)
+ expressions: (_col1 / _col2) (type: double), _col0 (type:
int)
outputColumnNames: _col0, _col1
Statistics: Num rows: 100 Data size: 1200 Basic stats:
COMPLETE Column stats: COMPLETE
Reduce Output Operator
- key expressions: _col0 (type: int)
+ key expressions: _col1 (type: int)
null sort order: z
sort order: +
- Map-reduce partition columns: _col0 (type: int)
+ Map-reduce partition columns: _col1 (type: int)
Statistics: Num rows: 100 Data size: 1200 Basic stats:
COMPLETE Column stats: COMPLETE
- value expressions: _col1 (type: double)
+ value expressions: _col0 (type: double)
Stage: Stage-0
Fetch Operator
diff --git
a/ql/src/test/results/clientpositive/llap/subquery_with_corr_and_mv.q.out
b/ql/src/test/results/clientpositive/llap/subquery_with_corr_and_mv.q.out
new file mode 100644
index 00000000000..88dd78a0d8d
--- /dev/null
+++ b/ql/src/test/results/clientpositive/llap/subquery_with_corr_and_mv.q.out
@@ -0,0 +1,264 @@
+PREHOOK: query: CREATE TABLE t_test1(
+ id int,
+ int_col int,
+ year int,
+ month int
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@t_test1
+POSTHOOK: query: CREATE TABLE t_test1(
+ id int,
+ int_col int,
+ year int,
+ month int
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@t_test1
+PREHOOK: query: CREATE TABLE t_test2(
+ id int,
+ int_col int,
+ year int,
+ month int
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@t_test2
+POSTHOOK: query: CREATE TABLE t_test2(
+ id int,
+ int_col int,
+ year int,
+ month int
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@t_test2
+PREHOOK: query: CREATE TABLE t_test3(
+ id int,
+ int_col int,
+ year int,
+ month int
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@t_test3
+POSTHOOK: query: CREATE TABLE t_test3(
+ id int,
+ int_col int,
+ year int,
+ month int
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@t_test3
+PREHOOK: query: CREATE TABLE t_test4(
+ id int,
+ int_col int,
+ year int,
+ month int
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@t_test4
+POSTHOOK: query: CREATE TABLE t_test4(
+ id int,
+ int_col int,
+ year int,
+ month int
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@t_test4
+PREHOOK: query: CREATE TABLE dummy (
+ id int
+) stored as orc TBLPROPERTIES ('transactional'='true')
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@dummy
+POSTHOOK: query: CREATE TABLE dummy (
+ id int
+) stored as orc TBLPROPERTIES ('transactional'='true')
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@dummy
+PREHOOK: query: CREATE MATERIALIZED VIEW need_a_mat_view_in_registry AS
+SELECT * FROM dummy where id > 5
+PREHOOK: type: CREATE_MATERIALIZED_VIEW
+PREHOOK: Input: default@dummy
+PREHOOK: Output: database:default
+PREHOOK: Output: default@need_a_mat_view_in_registry
+POSTHOOK: query: CREATE MATERIALIZED VIEW need_a_mat_view_in_registry AS
+SELECT * FROM dummy where id > 5
+POSTHOOK: type: CREATE_MATERIALIZED_VIEW
+POSTHOOK: Input: default@dummy
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@need_a_mat_view_in_registry
+POSTHOOK: Lineage: need_a_mat_view_in_registry.id SIMPLE
[(dummy)dummy.FieldSchema(name:id, type:int, comment:null), ]
+PREHOOK: query: INSERT INTO t_test1 VALUES (1, 1, 2009, 1), (10,0, 2009, 1)
+PREHOOK: type: QUERY
+PREHOOK: Input: _dummy_database@_dummy_table
+PREHOOK: Output: default@t_test1
+POSTHOOK: query: INSERT INTO t_test1 VALUES (1, 1, 2009, 1), (10,0, 2009, 1)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: _dummy_database@_dummy_table
+POSTHOOK: Output: default@t_test1
+POSTHOOK: Lineage: t_test1.id SCRIPT []
+POSTHOOK: Lineage: t_test1.int_col SCRIPT []
+POSTHOOK: Lineage: t_test1.month SCRIPT []
+POSTHOOK: Lineage: t_test1.year SCRIPT []
+PREHOOK: query: INSERT INTO t_test2 VALUES (1, 1, 2009, 1)
+PREHOOK: type: QUERY
+PREHOOK: Input: _dummy_database@_dummy_table
+PREHOOK: Output: default@t_test2
+POSTHOOK: query: INSERT INTO t_test2 VALUES (1, 1, 2009, 1)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: _dummy_database@_dummy_table
+POSTHOOK: Output: default@t_test2
+POSTHOOK: Lineage: t_test2.id SCRIPT []
+POSTHOOK: Lineage: t_test2.int_col SCRIPT []
+POSTHOOK: Lineage: t_test2.month SCRIPT []
+POSTHOOK: Lineage: t_test2.year SCRIPT []
+PREHOOK: query: INSERT INTO t_test3 VALUES (1, 1, 2009, 1)
+PREHOOK: type: QUERY
+PREHOOK: Input: _dummy_database@_dummy_table
+PREHOOK: Output: default@t_test3
+POSTHOOK: query: INSERT INTO t_test3 VALUES (1, 1, 2009, 1)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: _dummy_database@_dummy_table
+POSTHOOK: Output: default@t_test3
+POSTHOOK: Lineage: t_test3.id SCRIPT []
+POSTHOOK: Lineage: t_test3.int_col SCRIPT []
+POSTHOOK: Lineage: t_test3.month SCRIPT []
+POSTHOOK: Lineage: t_test3.year SCRIPT []
+PREHOOK: query: INSERT INTO t_test4 VALUES (1, 1, 2009, 1)
+PREHOOK: type: QUERY
+PREHOOK: Input: _dummy_database@_dummy_table
+PREHOOK: Output: default@t_test4
+POSTHOOK: query: INSERT INTO t_test4 VALUES (1, 1, 2009, 1)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: _dummy_database@_dummy_table
+POSTHOOK: Output: default@t_test4
+POSTHOOK: Lineage: t_test4.id SCRIPT []
+POSTHOOK: Lineage: t_test4.int_col SCRIPT []
+POSTHOOK: Lineage: t_test4.month SCRIPT []
+POSTHOOK: Lineage: t_test4.year SCRIPT []
+PREHOOK: query: select id, int_col, year, month from t_test1 s where s.int_col
= (select count(*) from t_test2 t where s.id = t.id) order by id
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t_test1
+PREHOOK: Input: default@t_test2
+#### A masked pattern was here ####
+POSTHOOK: query: select id, int_col, year, month from t_test1 s where
s.int_col = (select count(*) from t_test2 t where s.id = t.id) order by id
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t_test1
+POSTHOOK: Input: default@t_test2
+#### A masked pattern was here ####
+1 1 2009 1
+10 0 2009 1
+PREHOOK: query: explain cbo select id, int_col, year, month from t_test1 s
where s.int_col = (select count(*) from t_test2 t where s.id = t.id) order by id
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t_test1
+PREHOOK: Input: default@t_test2
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select id, int_col, year, month from t_test1 s
where s.int_col = (select count(*) from t_test2 t where s.id = t.id) order by id
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t_test1
+POSTHOOK: Input: default@t_test2
+#### A masked pattern was here ####
+CBO PLAN:
+HiveSortLimit(sort0=[$0], dir0=[ASC])
+ HiveProject(id=[$0], int_col=[$1], year=[$2], month=[$3])
+ HiveFilter(condition=[=(CAST($1):BIGINT, CASE(IS NULL($5), 0:BIGINT, $4))])
+ HiveJoin(condition=[=($0, $6)], joinType=[left], algorithm=[none],
cost=[not available])
+ HiveProject(id=[$0], int_col=[$1], year=[$2], month=[$3])
+ HiveTableScan(table=[[default, t_test1]], table:alias=[s])
+ HiveProject(_o__c0=[$1], trueLiteral=[true], id=[$0])
+ HiveAggregate(group=[{0}], agg#0=[count()])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, t_test2]], table:alias=[t])
+
+PREHOOK: query: explain cbo
+select id, int_col, year, month from t_test2 s where not (
+ s.int_col in (select count(*) from t_test3 t2 where s.id = t2.id) and
+ s.int_col in (select count(*) from t_test4 t3 where s.id = t3.id)
+)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t_test2
+PREHOOK: Input: default@t_test3
+PREHOOK: Input: default@t_test4
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo
+select id, int_col, year, month from t_test2 s where not (
+ s.int_col in (select count(*) from t_test3 t2 where s.id = t2.id) and
+ s.int_col in (select count(*) from t_test4 t3 where s.id = t3.id)
+)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t_test2
+POSTHOOK: Input: default@t_test3
+POSTHOOK: Input: default@t_test4
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(id=[$0], int_col=[$1], year=[$2], month=[$3])
+ HiveFilter(condition=[OR(IS NULL($4), IS NULL($8), =($4, 0), =($8, 0), IS
NOT TRUE(OR(IS NOT NULL($6), IS NULL($1), <($5, $4))), IS NOT TRUE(OR(IS NOT
NULL($11), IS NULL($1), <($9, $8))))])
+ HiveProject(id=[$0], int_col=[$1], year=[$2], month=[$3], c=[$5], ck=[$6],
literalTrue=[$8], id0=[$11], c0=[$12], ck0=[$13], _o__c0=[$14],
literalTrue0=[$15], id1=[$16])
+ HiveJoin(condition=[AND(=($0, $16), =($1, $14))], joinType=[left],
algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $11)], joinType=[left], algorithm=[none],
cost=[not available])
+ HiveJoin(condition=[=($0, $10)], joinType=[left], algorithm=[none],
cost=[not available])
+ HiveJoin(condition=[AND(=($0, $9), =($1, $7))], joinType=[left],
algorithm=[none], cost=[not available])
+ HiveJoin(condition=[=($0, $4)], joinType=[left],
algorithm=[none], cost=[not available])
+ HiveProject(id=[$0], int_col=[$1], year=[$2], month=[$3])
+ HiveFilter(condition=[CASE(IS NULL($5),
sq_count_check(0:BIGINT, true), sq_count_check($5, true))])
+ HiveJoin(condition=[=($0, $4)], joinType=[left],
algorithm=[none], cost=[not available])
+ HiveProject(id=[$0], int_col=[$1], year=[$2], month=[$3])
+ HiveTableScan(table=[[default, t_test2]],
table:alias=[s])
+ HiveProject(id=[$0], cnt_in=[$1])
+ HiveAggregate(group=[{0}], cnt_in=[COUNT()])
+ HiveProject(id=[$0])
+ HiveAggregate(group=[{0}])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, t_test3]],
table:alias=[t2])
+ HiveProject(id=[$0], c=[$1], ck=[$2])
+ HiveAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)])
+ HiveProject(id=[$0], $f1=[$1])
+ HiveAggregate(group=[{0}], agg#0=[count()])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, t_test3]],
table:alias=[t2])
+ HiveProject(_o__c0=[$1], literalTrue=[true], id=[$0])
+ HiveFilter(condition=[IS NOT NULL($1)])
+ HiveAggregate(group=[{0}], agg#0=[count()])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, t_test3]],
table:alias=[t2])
+ HiveProject(id=[$0])
+ HiveAggregate(group=[{0}])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, t_test4]], table:alias=[t3])
+ HiveProject(id=[$0], c=[$1], ck=[$2])
+ HiveAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)])
+ HiveProject(id=[$0], $f1=[$1])
+ HiveAggregate(group=[{0}], agg#0=[count()])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, t_test4]], table:alias=[t3])
+ HiveProject(_o__c0=[$1], literalTrue=[true], id=[$0])
+ HiveFilter(condition=[IS NOT NULL($1)])
+ HiveAggregate(group=[{0}], agg#0=[count()])
+ HiveFilter(condition=[IS NOT NULL($0)])
+ HiveTableScan(table=[[default, t_test4]], table:alias=[t3])
+
+PREHOOK: query: select id, int_col, year, month from t_test2 s where not (
+ s.int_col in (select count(*) from t_test3 t2 where s.id = t2.id) and
+ s.int_col in (select count(*) from t_test4 t3 where s.id = t3.id)
+)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@t_test2
+PREHOOK: Input: default@t_test3
+PREHOOK: Input: default@t_test4
+#### A masked pattern was here ####
+POSTHOOK: query: select id, int_col, year, month from t_test2 s where not (
+ s.int_col in (select count(*) from t_test3 t2 where s.id = t2.id) and
+ s.int_col in (select count(*) from t_test4 t3 where s.id = t3.id)
+)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@t_test2
+POSTHOOK: Input: default@t_test3
+POSTHOOK: Input: default@t_test4
+#### A masked pattern was here ####
diff --git
a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query32.q.out
b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query32.q.out
index 294489c8d1d..d4afed4dfc3 100644
--- a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query32.q.out
+++ b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query32.q.out
@@ -1,6 +1,6 @@
CBO PLAN:
HiveAggregate(group=[{}], agg#0=[sum($1)])
- HiveJoin(condition=[AND(=($5, $3), >($1, $6))], joinType=[inner],
algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($6, $3), >($1, $5))], joinType=[inner],
algorithm=[none], cost=[not available])
HiveJoin(condition=[=($4, $2)], joinType=[inner], algorithm=[none],
cost=[not available])
HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none],
cost=[not available])
HiveProject(cs_item_sk=[$14], cs_ext_discount_amt=[$21],
cs_sold_date_sk=[$33])
@@ -12,8 +12,8 @@ HiveAggregate(group=[{}], agg#0=[sum($1)])
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-03-18
00:00:00:TIMESTAMP(9), 1998-06-16 00:00:00:TIMESTAMP(9))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveProject(cs_item_sk=[$0], CAST3=[CAST(*(1.3:DECIMAL(2, 1), CAST(/($1,
$2)):DECIMAL(11, 6))):DECIMAL(14, 7)])
- HiveFilter(condition=[IS NOT NULL(CAST(*(1.3:DECIMAL(2, 1), CAST(/($1,
$2)):DECIMAL(11, 6))):DECIMAL(14, 7))])
+ HiveProject(_o__c0=[*(1.3:DECIMAL(2, 1), CAST(/($1, $2)):DECIMAL(11, 6))],
cs_item_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL(CAST(/($1, $2)):DECIMAL(11, 6))])
HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[count($1)])
HiveJoin(condition=[=($3, $2)], joinType=[inner], algorithm=[none],
cost=[not available])
HiveProject(cs_item_sk=[$14], cs_ext_discount_amt=[$21],
cs_sold_date_sk=[$33])
diff --git
a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query92.q.out
b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query92.q.out
index 5cc69adcb9f..b98fec5e068 100644
--- a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query92.q.out
+++ b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query92.q.out
@@ -4,7 +4,7 @@ HiveAggregate(group=[{}], agg#0=[sum($2)])
HiveProject(d_date_sk=[$0])
HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9), 1998-03-18
00:00:00:TIMESTAMP(9), 1998-06-16 00:00:00:TIMESTAMP(9))])
HiveTableScan(table=[[default, date_dim]], table:alias=[date_dim])
- HiveJoin(condition=[AND(=($4, $3), >($1, $5))], joinType=[inner],
algorithm=[none], cost=[not available])
+ HiveJoin(condition=[AND(=($5, $3), >($1, $4))], joinType=[inner],
algorithm=[none], cost=[not available])
HiveJoin(condition=[=($3, $0)], joinType=[inner], algorithm=[none],
cost=[not available])
HiveProject(ws_item_sk=[$2], ws_ext_discount_amt=[$21],
ws_sold_date_sk=[$33])
HiveFilter(condition=[AND(IS NOT NULL($21), IS NOT NULL($33))])
@@ -12,8 +12,8 @@ HiveAggregate(group=[{}], agg#0=[sum($2)])
HiveProject(i_item_sk=[$0])
HiveFilter(condition=[=($13, 269)])
HiveTableScan(table=[[default, item]], table:alias=[item])
- HiveProject(ws_item_sk=[$0], CAST3=[CAST(*(1.3:DECIMAL(2, 1), CAST(/($1,
$2)):DECIMAL(11, 6))):DECIMAL(14, 7)])
- HiveFilter(condition=[IS NOT NULL(CAST(*(1.3:DECIMAL(2, 1), CAST(/($1,
$2)):DECIMAL(11, 6))):DECIMAL(14, 7))])
+ HiveProject(_o__c0=[*(1.3:DECIMAL(2, 1), CAST(/($1, $2)):DECIMAL(11,
6))], ws_item_sk=[$0])
+ HiveFilter(condition=[IS NOT NULL(CAST(/($1, $2)):DECIMAL(11, 6))])
HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[count($1)])
HiveJoin(condition=[=($3, $2)], joinType=[inner],
algorithm=[none], cost=[not available])
HiveProject(ws_item_sk=[$2], ws_ext_discount_amt=[$21],
ws_sold_date_sk=[$33])
diff --git
a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/query32.q.out
b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/query32.q.out
index abbdfb6ea9a..a712ab95b28 100644
--- a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/query32.q.out
+++ b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/query32.q.out
@@ -198,10 +198,10 @@ STAGE PLANS:
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 51643 Data size: 6610304 Basic stats:
COMPLETE Column stats: COMPLETE
Filter Operator
- predicate: CAST( (1.3 * CAST( (_col1 / _col2) AS
decimal(11,6))) AS decimal(14,7)) is not null (type: boolean)
+ predicate: CAST( (_col1 / _col2) AS decimal(11,6)) is not
null (type: boolean)
Statistics: Num rows: 51643 Data size: 6610304 Basic stats:
COMPLETE Column stats: COMPLETE
Select Operator
- expressions: _col0 (type: bigint), CAST( (1.3 * CAST(
(_col1 / _col2) AS decimal(11,6))) AS decimal(14,7)) (type: decimal(14,7))
+ expressions: (1.3 * CAST( (_col1 / _col2) AS
decimal(11,6))) (type: decimal(14,7)), _col0 (type: bigint)
outputColumnNames: _col0, _col1
Statistics: Num rows: 51643 Data size: 6197160 Basic
stats: COMPLETE Column stats: COMPLETE
Map Join Operator
@@ -209,13 +209,13 @@ STAGE PLANS:
Inner Join 0 to 1
keys:
0 _col3 (type: bigint)
- 1 _col0 (type: bigint)
- outputColumnNames: _col1, _col6
+ 1 _col1 (type: bigint)
+ outputColumnNames: _col1, _col5
input vertices:
0 Map 1
Statistics: Num rows: 51643 Data size: 5784128 Basic
stats: COMPLETE Column stats: COMPLETE
Filter Operator
- predicate: (_col1 > _col6) (type: boolean)
+ predicate: (_col1 > _col5) (type: boolean)
Statistics: Num rows: 17214 Data size: 1928080 Basic
stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: _col1 (type: decimal(7,2))
diff --git
a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/query92.q.out
b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/query92.q.out
index 7fd2dafd68b..7e1a427778d 100644
--- a/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/query92.q.out
+++ b/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/query92.q.out
@@ -179,10 +179,10 @@ STAGE PLANS:
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 51643 Data size: 6610304 Basic stats:
COMPLETE Column stats: COMPLETE
Filter Operator
- predicate: CAST( (1.3 * CAST( (_col1 / _col2) AS
decimal(11,6))) AS decimal(14,7)) is not null (type: boolean)
+ predicate: CAST( (_col1 / _col2) AS decimal(11,6)) is not
null (type: boolean)
Statistics: Num rows: 51643 Data size: 6610304 Basic stats:
COMPLETE Column stats: COMPLETE
Select Operator
- expressions: _col0 (type: bigint), CAST( (1.3 * CAST(
(_col1 / _col2) AS decimal(11,6))) AS decimal(14,7)) (type: decimal(14,7))
+ expressions: (1.3 * CAST( (_col1 / _col2) AS
decimal(11,6))) (type: decimal(14,7)), _col0 (type: bigint)
outputColumnNames: _col0, _col1
Statistics: Num rows: 51643 Data size: 6197160 Basic
stats: COMPLETE Column stats: COMPLETE
Map Join Operator
@@ -190,13 +190,13 @@ STAGE PLANS:
Inner Join 0 to 1
keys:
0 _col3 (type: bigint)
- 1 _col0 (type: bigint)
- outputColumnNames: _col1, _col2, _col5
+ 1 _col1 (type: bigint)
+ outputColumnNames: _col1, _col2, _col4
input vertices:
0 Reducer 2
Statistics: Num rows: 51643 Data size: 6197272 Basic
stats: COMPLETE Column stats: COMPLETE
Filter Operator
- predicate: (_col1 > _col5) (type: boolean)
+ predicate: (_col1 > _col4) (type: boolean)
Statistics: Num rows: 17214 Data size: 2065792 Basic
stats: COMPLETE Column stats: COMPLETE
Map Join Operator
condition map:
