You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ma...@apache.org on 2014/10/06 19:29:39 UTC
[1/3] PHOENIX-167 Support semi/anti-joins
Repository: phoenix
Updated Branches:
refs/heads/master 719eaf07a -> 909d97596
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
new file mode 100644
index 0000000..42d060f
--- /dev/null
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
@@ -0,0 +1,401 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.compile;
+
+import java.sql.SQLException;
+import java.sql.SQLFeatureNotSupportedException;
+import java.util.Collections;
+import java.util.List;
+
+import org.apache.hadoop.hbase.filter.CompareFilter;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.parse.AliasedNode;
+import org.apache.phoenix.parse.AndParseNode;
+import org.apache.phoenix.parse.BooleanParseNodeVisitor;
+import org.apache.phoenix.parse.ColumnParseNode;
+import org.apache.phoenix.parse.ComparisonParseNode;
+import org.apache.phoenix.parse.CompoundParseNode;
+import org.apache.phoenix.parse.ExistsParseNode;
+import org.apache.phoenix.parse.InParseNode;
+import org.apache.phoenix.parse.JoinTableNode.JoinType;
+import org.apache.phoenix.parse.LiteralParseNode;
+import org.apache.phoenix.parse.ParseNode;
+import org.apache.phoenix.parse.ParseNodeFactory;
+import org.apache.phoenix.parse.ParseNodeRewriter;
+import org.apache.phoenix.parse.RowValueConstructorParseNode;
+import org.apache.phoenix.parse.SelectStatement;
+import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor;
+import org.apache.phoenix.parse.SubqueryParseNode;
+import org.apache.phoenix.parse.TableName;
+import org.apache.phoenix.parse.TableNode;
+import org.apache.phoenix.schema.ColumnFamilyNotFoundException;
+import org.apache.phoenix.schema.ColumnNotFoundException;
+
+import com.google.common.collect.Lists;
+
+/*
+ * Class for rewriting where-clause sub-queries into join queries.
+ *
+ * If the where-clause sub-query is one of those top-node conditions (being
+ * the only condition node or direct descendant of AND nodes), we convert the
+ * sub-query directly into semi-joins, anti-joins or inner-joins, and meanwhile
+ * remove the original condition node from the where clause.
+ * Otherwise, we convert the sub-query into left-joins and change the original
+ * condition node into a null test of a join table field (ONE if matched, NULL
+ * if not matched).
+ */
+public class SubqueryRewriter extends ParseNodeRewriter {
+ private static final ParseNodeFactory NODE_FACTORY = new ParseNodeFactory();
+
+ private final ColumnResolver resolver;
+ private final PhoenixConnection connection;
+ private TableNode tableNode;
+ private ParseNode topNode;
+
+ public static SelectStatement transform(SelectStatement select, ColumnResolver resolver, PhoenixConnection connection) throws SQLException {
+ ParseNode where = select.getWhere();
+ if (where == null)
+ return select;
+
+ SubqueryRewriter rewriter = new SubqueryRewriter(select, resolver, connection);
+ ParseNode normWhere = rewrite(where, rewriter);
+ if (normWhere == where)
+ return select;
+
+ return NODE_FACTORY.select(Collections.singletonList(rewriter.tableNode), select.getHint(), select.isDistinct(), select.getSelect(), normWhere, select.getGroupBy(), select.getHaving(), select.getOrderBy(), select.getLimit(), select.getBindCount(), select.isAggregate(), select.hasSequence());
+ }
+
+ protected SubqueryRewriter(SelectStatement select, ColumnResolver resolver, PhoenixConnection connection) {
+ this.resolver = resolver;
+ this.connection = connection;
+ this.tableNode = select.getFrom().get(0);
+ this.topNode = null;
+ }
+
+ @Override
+ protected void enterParseNode(ParseNode node) {
+ if (topNode == null) {
+ topNode = node;
+ }
+ super.enterParseNode(node);
+ }
+
+ @Override
+ protected ParseNode leaveCompoundNode(CompoundParseNode node, List<ParseNode> children, ParseNodeRewriter.CompoundNodeFactory factory) {
+ if (topNode == node) {
+ topNode = null;
+ }
+
+ return super.leaveCompoundNode(node, children, factory);
+ }
+
+ @Override
+ public boolean visitEnter(AndParseNode node) throws SQLException {
+ return true;
+ }
+
+ @Override
+ public ParseNode visitLeave(AndParseNode node, List<ParseNode> l) throws SQLException {
+ return leaveCompoundNode(node, l, new CompoundNodeFactory() {
+ @Override
+ public ParseNode createNode(List<ParseNode> children) {
+ if (children.isEmpty()) {
+ return null;
+ }
+ if (children.size() == 1) {
+ return children.get(0);
+ }
+ return NODE_FACTORY.and(children);
+ }
+ });
+ }
+
+ @Override
+ public ParseNode visitLeave(InParseNode node, List<ParseNode> l) throws SQLException {
+ SubqueryParseNode subqueryNode = (SubqueryParseNode) l.get(1);
+ SelectStatement subquery = subqueryNode.getSelectNode();
+ String rhsTableAlias = ParseNodeFactory.createTempAlias();
+ List<AliasedNode> selectNodes = fixAliasedNodes(subquery.getSelect());
+ subquery = NODE_FACTORY.select(subquery.getFrom(), subquery.getHint(), true,
+ selectNodes, subquery.getWhere(), subquery.getGroupBy(), subquery.getHaving(), subquery.getOrderBy(),
+ subquery.getLimit(), subquery.getBindCount(), subquery.isAggregate(), subquery.hasSequence());
+ ParseNode onNode = getJoinConditionNode(l.get(0), selectNodes, rhsTableAlias);
+ TableNode rhsTable = NODE_FACTORY.derivedTable(rhsTableAlias, subquery);
+ JoinType joinType = topNode == node ? (node.isNegate() ? JoinType.Anti : JoinType.Semi) : JoinType.Left;
+ ParseNode ret = topNode == node ? null : NODE_FACTORY.isNull(NODE_FACTORY.column(NODE_FACTORY.table(null, rhsTableAlias), selectNodes.get(0).getAlias(), null), !node.isNegate());
+ tableNode = NODE_FACTORY.join(joinType, tableNode, rhsTable, onNode);
+
+ if (topNode == node) {
+ topNode = null;
+ }
+
+ return ret;
+ }
+
+ @Override
+ public ParseNode visitLeave(ExistsParseNode node, List<ParseNode> l) throws SQLException {
+ SubqueryParseNode subqueryNode = (SubqueryParseNode) l.get(0);
+ SelectStatement subquery = subqueryNode.getSelectNode();
+ String rhsTableAlias = ParseNodeFactory.createTempAlias();
+ JoinConditionExtractor conditionExtractor = new JoinConditionExtractor(subquery, resolver, connection, rhsTableAlias);
+ ParseNode where = subquery.getWhere() == null ? null : subquery.getWhere().accept(conditionExtractor);
+ if (where == subquery.getWhere()) { // non-correlated EXISTS subquery, add LIMIT 1
+ subquery = NODE_FACTORY.select(subquery, NODE_FACTORY.limit(NODE_FACTORY.literal(1)));
+ subqueryNode = NODE_FACTORY.subquery(subquery, false);
+ node = NODE_FACTORY.exists(subqueryNode, node.isNegate());
+ return super.visitLeave(node, Collections.<ParseNode> singletonList(subqueryNode));
+ }
+
+ List<AliasedNode> additionalSelectNodes = conditionExtractor.getAdditionalSelectNodes();
+ List<AliasedNode> selectNodes = Lists.newArrayListWithExpectedSize(additionalSelectNodes.size() + 1);
+ selectNodes.add(NODE_FACTORY.aliasedNode(ParseNodeFactory.createTempAlias(), LiteralParseNode.ONE));
+ selectNodes.addAll(additionalSelectNodes);
+
+ subquery = NODE_FACTORY.select(subquery.getFrom(), subquery.getHint(), true,
+ selectNodes, where, subquery.getGroupBy(), subquery.getHaving(), subquery.getOrderBy(),
+ subquery.getLimit(), subquery.getBindCount(), subquery.isAggregate(), subquery.hasSequence());
+ ParseNode onNode = conditionExtractor.getJoinCondition();
+ TableNode rhsTable = NODE_FACTORY.derivedTable(rhsTableAlias, subquery);
+ JoinType joinType = topNode == node ? (node.isNegate() ? JoinType.Anti : JoinType.Semi) : JoinType.Left;
+ ParseNode ret = topNode == node ? null : NODE_FACTORY.isNull(NODE_FACTORY.column(NODE_FACTORY.table(null, rhsTableAlias), selectNodes.get(0).getAlias(), null), !node.isNegate());
+ tableNode = NODE_FACTORY.join(joinType, tableNode, rhsTable, onNode);
+
+ if (topNode == node) {
+ topNode = null;
+ }
+
+ return ret;
+ }
+
+ private List<AliasedNode> fixAliasedNodes(List<AliasedNode> nodes) {
+ List<AliasedNode> normNodes = Lists.<AliasedNode> newArrayListWithExpectedSize(nodes.size() + 1);
+ normNodes.add(NODE_FACTORY.aliasedNode(ParseNodeFactory.createTempAlias(), LiteralParseNode.ONE));
+ for (int i = 0; i < nodes.size(); i++) {
+ AliasedNode aliasedNode = nodes.get(i);
+ normNodes.add(NODE_FACTORY.aliasedNode(
+ ParseNodeFactory.createTempAlias(), aliasedNode.getNode()));
+ }
+
+ return normNodes;
+ }
+
+ private ParseNode getJoinConditionNode(ParseNode lhs, List<AliasedNode> rhs, String rhsTableAlias) throws SQLException {
+ List<ParseNode> lhsNodes;
+ if (lhs instanceof RowValueConstructorParseNode) {
+ lhsNodes = ((RowValueConstructorParseNode) lhs).getChildren();
+ } else {
+ lhsNodes = Collections.singletonList(lhs);
+ }
+ if (lhsNodes.size() != (rhs.size() - 1))
+ throw new SQLExceptionInfo.Builder(SQLExceptionCode.SUBQUERY_RETURNS_DIFFERENT_NUMBER_OF_FIELDS).build().buildException();
+
+ int count = lhsNodes.size();
+ TableName rhsTableName = NODE_FACTORY.table(null, rhsTableAlias);
+ List<ParseNode> equalNodes = Lists.newArrayListWithExpectedSize(count);
+ for (int i = 0; i < count; i++) {
+ ParseNode rhsNode = NODE_FACTORY.column(rhsTableName, rhs.get(i + 1).getAlias(), null);
+ equalNodes.add(NODE_FACTORY.equal(lhsNodes.get(i), rhsNode));
+ }
+
+ return count == 1 ? equalNodes.get(0) : NODE_FACTORY.and(equalNodes);
+ }
+
+ private static class JoinConditionExtractor extends BooleanParseNodeVisitor<ParseNode> {
+ private final TableName tableName;
+ private ColumnResolveVisitor columnResolveVisitor;
+ private List<AliasedNode> additionalSelectNodes;
+ private List<ParseNode> joinConditions;
+
+ public JoinConditionExtractor(SelectStatement subquery, ColumnResolver outerResolver,
+ PhoenixConnection connection, String tableAlias) throws SQLException {
+ this.tableName = NODE_FACTORY.table(null, tableAlias);
+ ColumnResolver localResolver = FromCompiler.getResolverForQuery(subquery, connection);
+ this.columnResolveVisitor = new ColumnResolveVisitor(localResolver, outerResolver);
+ this.additionalSelectNodes = Lists.<AliasedNode> newArrayList();
+ this.joinConditions = Lists.<ParseNode> newArrayList();
+ }
+
+ public List<AliasedNode> getAdditionalSelectNodes() {
+ return this.additionalSelectNodes;
+ }
+
+ public ParseNode getJoinCondition() {
+ if (this.joinConditions.isEmpty())
+ return null;
+
+ if (this.joinConditions.size() == 1)
+ return this.joinConditions.get(0);
+
+ return NODE_FACTORY.and(this.joinConditions);
+ }
+
+ @Override
+ public List<ParseNode> newElementList(int size) {
+ return Lists.<ParseNode> newArrayListWithExpectedSize(size);
+ }
+
+ @Override
+ public void addElement(List<ParseNode> l, ParseNode element) {
+ if (element != null) {
+ l.add(element);
+ }
+ }
+
+ @Override
+ public boolean visitEnter(AndParseNode node) throws SQLException {
+ return true;
+ }
+
+ @Override
+ public ParseNode visitLeave(AndParseNode node, List<ParseNode> l)
+ throws SQLException {
+ if (l.equals(node.getChildren()))
+ return node;
+
+ if (l.isEmpty())
+ return null;
+
+ if (l.size() == 1)
+ return l.get(0);
+
+ return NODE_FACTORY.and(l);
+ }
+
+ @Override
+ protected boolean enterBooleanNode(ParseNode node) throws SQLException {
+ return false;
+ }
+
+ @Override
+ protected ParseNode leaveBooleanNode(ParseNode node, List<ParseNode> l)
+ throws SQLException {
+ columnResolveVisitor.reset();
+ node.accept(columnResolveVisitor);
+ ColumnResolveVisitor.ColumnResolveType type = columnResolveVisitor.getColumnResolveType();
+ if (type != ColumnResolveVisitor.ColumnResolveType.NONE
+ && type != ColumnResolveVisitor.ColumnResolveType.LOCAL)
+ throw new SQLFeatureNotSupportedException("Does not support non-standard or non-equi correlated-subquery conditions.");
+
+ return node;
+ }
+
+ @Override
+ protected boolean enterNonBooleanNode(ParseNode node)
+ throws SQLException {
+ return false;
+ }
+
+ @Override
+ protected ParseNode leaveNonBooleanNode(ParseNode node,
+ List<ParseNode> l) throws SQLException {
+ return node;
+ }
+
+ @Override
+ public ParseNode visitLeave(ComparisonParseNode node, List<ParseNode> l) throws SQLException {
+ if (node.getFilterOp() != CompareFilter.CompareOp.EQUAL)
+ return leaveBooleanNode(node, l);
+
+ columnResolveVisitor.reset();
+ node.getLHS().accept(columnResolveVisitor);
+ ColumnResolveVisitor.ColumnResolveType lhsType = columnResolveVisitor.getColumnResolveType();
+ columnResolveVisitor.reset();
+ node.getRHS().accept(columnResolveVisitor);
+ ColumnResolveVisitor.ColumnResolveType rhsType = columnResolveVisitor.getColumnResolveType();
+ if ((lhsType == ColumnResolveVisitor.ColumnResolveType.NONE || lhsType == ColumnResolveVisitor.ColumnResolveType.LOCAL)
+ && (rhsType == ColumnResolveVisitor.ColumnResolveType.NONE || rhsType == ColumnResolveVisitor.ColumnResolveType.LOCAL)) {
+ return node;
+ }
+ if (lhsType == ColumnResolveVisitor.ColumnResolveType.LOCAL && rhsType == ColumnResolveVisitor.ColumnResolveType.OUTER) {
+ String alias = ParseNodeFactory.createTempAlias();
+ this.additionalSelectNodes.add(NODE_FACTORY.aliasedNode(alias, node.getLHS()));
+ ParseNode lhsNode = NODE_FACTORY.column(tableName, alias, null);
+ this.joinConditions.add(NODE_FACTORY.equal(lhsNode, node.getRHS()));
+ return null;
+ }
+ if (lhsType == ColumnResolveVisitor.ColumnResolveType.OUTER && rhsType == ColumnResolveVisitor.ColumnResolveType.LOCAL) {
+ String alias = ParseNodeFactory.createTempAlias();
+ this.additionalSelectNodes.add(NODE_FACTORY.aliasedNode(alias, node.getRHS()));
+ ParseNode rhsNode = NODE_FACTORY.column(tableName, alias, null);
+ this.joinConditions.add(NODE_FACTORY.equal(node.getLHS(), rhsNode));
+ return null;
+ }
+
+ throw new SQLFeatureNotSupportedException("Does not support non-standard or non-equi correlated-subquery conditions.");
+ }
+ }
+
+ /*
+ * Class for resolving inner query column references
+ */
+ private static class ColumnResolveVisitor extends StatelessTraverseAllParseNodeVisitor {
+ public enum ColumnResolveType {NONE, LOCAL, OUTER, MIXED};
+
+ private final ColumnResolver localResolver;
+ private final ColumnResolver outerResolver;
+ private ColumnResolveType type;
+
+ public ColumnResolveVisitor(ColumnResolver localResolver, ColumnResolver outerResolver) {
+ this.localResolver = localResolver;
+ this.outerResolver = outerResolver;
+ this.type = ColumnResolveType.NONE;
+ }
+
+ public void reset() {
+ this.type = ColumnResolveType.NONE;
+ }
+
+ public ColumnResolveType getColumnResolveType() {
+ return this.type;
+ }
+
+ @Override
+ public Void visit(ColumnParseNode node) throws SQLException {
+ // Inner query column definitions should shade those of outer query.
+ try {
+ localResolver.resolveColumn(node.getSchemaName(), node.getTableName(), node.getName());
+ addType(true);
+ return null;
+ } catch (ColumnNotFoundException e) {
+ } catch (ColumnFamilyNotFoundException e) {
+ }
+
+ outerResolver.resolveColumn(node.getSchemaName(), node.getTableName(), node.getName());
+ addType(false);
+ return null;
+ }
+
+ private void addType(boolean isLocal) {
+ switch (this.type) {
+ case NONE:
+ this.type = isLocal ? ColumnResolveType.LOCAL : ColumnResolveType.OUTER;
+ break;
+ case LOCAL:
+ this.type = isLocal ? ColumnResolveType.LOCAL : ColumnResolveType.MIXED;
+ break;
+ case OUTER:
+ this.type = isLocal ? ColumnResolveType.MIXED : ColumnResolveType.OUTER;
+ break;
+ default: // MIXED do nothing
+ break;
+ }
+ }
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
index f363bdc..3381aa8 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
@@ -372,6 +372,11 @@ public class UpsertCompiler {
ColumnResolver selectResolver = FromCompiler.getResolverForQuery(select, connection);
select = StatementNormalizer.normalize(select, selectResolver);
select = prependTenantAndViewConstants(table, select, tenantId, addViewColumnsToBe);
+ SelectStatement transformedSelect = SubqueryRewriter.transform(select, selectResolver, connection);
+ if (transformedSelect != select) {
+ selectResolver = FromCompiler.getResolverForQuery(transformedSelect, connection);
+ select = StatementNormalizer.normalize(transformedSelect, selectResolver);
+ }
sameTable = select.getFrom().size() == 1
&& tableRefToBe.equals(selectResolver.getTables().get(0));
tableRefToBe = adjustTimestampToMinOfSameTable(tableRefToBe, selectResolver.getTables());
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
index 5f652f1..64a49c8 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
@@ -311,12 +311,14 @@ public class WhereOptimizer {
/**
* Get an optimal combination of key expressions for hash join key range optimization.
+ * @return returns true if the entire combined expression is covered by key range optimization
+ * @param result the optimal combination of key expressions
* @param context the temporary context to get scan ranges set by pushKeyExpressionsToScan()
* @param statement the statement being compiled
* @param expressions the join key expressions
* @return the optimal list of key expressions
*/
- public static List<Expression> getKeyExpressionCombination(StatementContext context, FilterableStatement statement, List<Expression> expressions) throws SQLException {
+ public static boolean getKeyExpressionCombination(List<Expression> result, StatementContext context, FilterableStatement statement, List<Expression> expressions) throws SQLException {
List<Integer> candidateIndexes = Lists.newArrayList();
final List<Integer> pkPositions = Lists.newArrayList();
for (int i = 0; i < expressions.size(); i++) {
@@ -339,7 +341,7 @@ public class WhereOptimizer {
}
if (candidateIndexes.isEmpty())
- return Collections.<Expression> emptyList();
+ return false;
Collections.sort(candidateIndexes, new Comparator<Integer>() {
@Override
@@ -364,12 +366,13 @@ public class WhereOptimizer {
int count = 0;
int maxPkSpan = 0;
+ Expression remaining = null;
while (count < candidates.size()) {
Expression lhs = count == 0 ? candidates.get(0) : new RowValueConstructorExpression(candidates.subList(0, count + 1), false);
Expression firstRhs = count == 0 ? sampleValues.get(0).get(0) : new RowValueConstructorExpression(sampleValues.get(0).subList(0, count + 1), true);
Expression secondRhs = count == 0 ? sampleValues.get(1).get(0) : new RowValueConstructorExpression(sampleValues.get(1).subList(0, count + 1), true);
Expression testExpression = InListExpression.create(Lists.newArrayList(lhs, firstRhs, secondRhs), false, context.getTempPtr());
- pushKeyExpressionsToScan(context, statement, testExpression);
+ remaining = pushKeyExpressionsToScan(context, statement, testExpression);
int pkSpan = context.getScanRanges().getPkColumnSpan();
if (pkSpan <= maxPkSpan) {
break;
@@ -378,7 +381,11 @@ public class WhereOptimizer {
count++;
}
- return candidates.subList(0, count);
+ result.addAll(candidates.subList(0, count));
+
+ return count == candidates.size()
+ && (context.getScanRanges().isPointLookup() || context.getScanRanges().useSkipScanFilter())
+ && (remaining == null || remaining.equals(LiteralExpression.newConstant(true, Determinism.ALWAYS)));
}
private static class RemoveExtractedNodesVisitor extends TraverseNoExpressionVisitor<Expression> {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java
index 47ffce7..02fc6e3 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java
@@ -72,7 +72,7 @@ public class HashJoinRegionScanner implements RegionScanner {
this.limit = Long.MAX_VALUE;
if (joinInfo != null) {
for (JoinType type : joinInfo.getJoinTypes()) {
- if (type != JoinType.Inner && type != JoinType.Left)
+ if (type != JoinType.Inner && type != JoinType.Left && type != JoinType.Semi && type != JoinType.Anti)
throw new DoNotRetryIOException("Got join type '" + type + "'. Expect only INNER or LEFT with hash-joins.");
}
if (joinInfo.getLimit() != null) {
@@ -85,6 +85,12 @@ public class HashJoinRegionScanner implements RegionScanner {
TenantCache cache = GlobalCache.getTenantCache(env, tenantId);
for (int i = 0; i < count; i++) {
ImmutableBytesPtr joinId = joinInfo.getJoinIds()[i];
+ if (joinId.getLength() == 0) { // semi-join optimized into skip-scan
+ hashCaches[i] = null;
+ tempSrcBitSet[i] = null;
+ tempTuples[i] = null;
+ continue;
+ }
HashCache hashCache = (HashCache)cache.getServerCache(joinId);
if (hashCache == null)
throw new DoNotRetryIOException("Could not find hash cache for joinId: "
@@ -119,12 +125,13 @@ public class HashJoinRegionScanner implements RegionScanner {
int count = joinInfo.getJoinIds().length;
boolean cont = true;
for (int i = 0; i < count; i++) {
- if (!(joinInfo.earlyEvaluation()[i]))
+ if (!(joinInfo.earlyEvaluation()[i]) || hashCaches[i] == null)
continue;
ImmutableBytesPtr key = TupleUtil.getConcatenatedValue(tuple, joinInfo.getJoinExpressions()[i]);
tempTuples[i] = hashCaches[i].get(key);
JoinType type = joinInfo.getJoinTypes()[i];
- if (type == JoinType.Inner && tempTuples[i] == null) {
+ if (((type == JoinType.Inner || type == JoinType.Semi) && tempTuples[i] == null)
+ || (type == JoinType.Anti && tempTuples[i] != null)) {
cont = false;
break;
}
@@ -146,7 +153,8 @@ public class HashJoinRegionScanner implements RegionScanner {
resultQueue.offer(tuple);
for (int i = 0; i < count; i++) {
boolean earlyEvaluation = joinInfo.earlyEvaluation()[i];
- if (earlyEvaluation && tempTuples[i] == null)
+ JoinType type = joinInfo.getJoinTypes()[i];
+ if (earlyEvaluation && (tempTuples[i] == null || type == JoinType.Semi))
continue;
int j = resultQueue.size();
while (j-- > 0) {
@@ -155,7 +163,7 @@ public class HashJoinRegionScanner implements RegionScanner {
ImmutableBytesPtr key = TupleUtil.getConcatenatedValue(lhs, joinInfo.getJoinExpressions()[i]);
tempTuples[i] = hashCaches[i].get(key);
if (tempTuples[i] == null) {
- if (joinInfo.getJoinTypes()[i] != JoinType.Inner) {
+ if (type != JoinType.Inner && type != JoinType.Semi) {
resultQueue.offer(lhs);
}
continue;
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
index bbb09dc..c99d14c 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
@@ -78,7 +78,8 @@ public enum SQLExceptionCode {
SERVER_ARITHMETIC_ERROR(212, "22012", "Arithmetic error on server."),
VALUE_OUTSIDE_RANGE(213,"22003","Value outside range."),
VALUE_IN_LIST_NOT_CONSTANT(214, "22008", "Values in IN must evaluate to a constant."),
- SINGLE_ROW_SUBQUERY_RETURNS_MULTIPLE_ROWS(215, "22015", "Single-row subquery returns more than one row."),
+ SINGLE_ROW_SUBQUERY_RETURNS_MULTIPLE_ROWS(215, "22015", "Single-row sub-query returns more than one row."),
+ SUBQUERY_RETURNS_DIFFERENT_NUMBER_OF_FIELDS(216, "22016", "Sub-query must return the same number of fields as the left-hand-side expression of 'IN'."),
/**
* Constraint Violation (errorcode 03, sqlstate 23)
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java b/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
index dcf162f..1ac9d68 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
@@ -267,7 +267,6 @@ public class HashJoinPlan implements QueryPlan {
public ExplainPlan getExplainPlan() throws SQLException {
List<String> planSteps = Lists.newArrayList(plan.getExplainPlan().getPlanSteps());
int count = subPlans.length;
- planSteps.add(" PARALLEL EQUI/SEMI/ANTI-JOIN " + count + " TABLES:");
for (int i = 0; i < count; i++) {
planSteps.addAll(subPlans[i].getPreSteps(this));
}
@@ -422,12 +421,29 @@ public class HashJoinPlan implements QueryPlan {
if (keyRangeRhsExpression != null) {
keyRangeRhsValues = Lists.<ImmutableBytesWritable>newArrayList();
}
- ServerCache cache = parent.hashClient.addHashCache(ranges, plan.iterator(),
- clientProjector, plan.getEstimatedSize(), hashExpressions, parent.plan.getTableRef(), keyRangeRhsExpression, keyRangeRhsValues);
- long endTime = System.currentTimeMillis();
- boolean isSet = parent.firstJobEndTime.compareAndSet(0, endTime);
- if (!isSet && (endTime - parent.firstJobEndTime.get()) > parent.maxServerCacheTimeToLive) {
- LOG.warn(addCustomAnnotations("Hash plan [" + index + "] execution seems too slow. Earlier hash cache(s) might have expired on servers.", parent.plan.context.getConnection()));
+ ServerCache cache = null;
+ if (hashExpressions != null) {
+ cache = parent.hashClient.addHashCache(ranges, plan.iterator(),
+ clientProjector, plan.getEstimatedSize(), hashExpressions, parent.plan.getTableRef(), keyRangeRhsExpression, keyRangeRhsValues);
+ long endTime = System.currentTimeMillis();
+ boolean isSet = parent.firstJobEndTime.compareAndSet(0, endTime);
+ if (!isSet && (endTime - parent.firstJobEndTime.get()) > parent.maxServerCacheTimeToLive) {
+ LOG.warn(addCustomAnnotations("Hash plan [" + index + "] execution seems too slow. Earlier hash cache(s) might have expired on servers.", parent.plan.context.getConnection()));
+ }
+ } else {
+ assert(keyRangeRhsExpression != null);
+ ResultIterator iterator = plan.iterator();
+ for (Tuple result = iterator.next(); result != null; result = iterator.next()) {
+ if (clientProjector != null) {
+ result = clientProjector.projectResults(result);
+ }
+ // Evaluate key expressions for hash join key range optimization.
+ ImmutableBytesWritable value = new ImmutableBytesWritable();
+ keyRangeRhsExpression.reset();
+ if (keyRangeRhsExpression.evaluate(result, value)) {
+ keyRangeRhsValues.add(value);
+ }
+ }
}
if (keyRangeRhsValues != null) {
parent.keyRangeExpressions.add(parent.createKeyRangeExpression(keyRangeLhsExpression, keyRangeRhsExpression, keyRangeRhsValues, plan.getContext().getTempPtr(), hasFilters));
@@ -439,8 +455,10 @@ public class HashJoinPlan implements QueryPlan {
public void postProcess(Object result, HashJoinPlan parent)
throws SQLException {
ServerCache cache = (ServerCache) result;
- parent.joinInfo.getJoinIds()[index].set(cache.getId());
- parent.dependencies.add(cache);
+ if (cache != null) {
+ parent.joinInfo.getJoinIds()[index].set(cache.getId());
+ parent.dependencies.add(cache);
+ }
}
@Override
@@ -448,7 +466,13 @@ public class HashJoinPlan implements QueryPlan {
List<String> steps = Lists.newArrayList();
boolean earlyEvaluation = parent.joinInfo.earlyEvaluation()[index];
boolean skipMerge = parent.joinInfo.getSchemas()[index].getFieldCount() == 0;
- steps.add(" BUILD HASH TABLE " + index + (earlyEvaluation ? "" : "(DELAYED EVALUATION)") + (skipMerge ? " (SKIP MERGE)" : ""));
+ if (hashExpressions != null) {
+ steps.add(" PARALLEL " + parent.joinInfo.getJoinTypes()[index].toString().toUpperCase()
+ + "-JOIN TABLE " + index + (earlyEvaluation ? "" : "(DELAYED EVALUATION)") + (skipMerge ? " (SKIP MERGE)" : ""));
+ }
+ else {
+ steps.add(" SKIP-SCAN-JOIN TABLE " + index);
+ }
for (String step : plan.getExplainPlan().getPlanSteps()) {
steps.add(" " + step);
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
index bdde415..30376e7 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
@@ -45,6 +45,7 @@ import org.apache.phoenix.compile.DropSequenceCompiler;
import org.apache.phoenix.compile.ExplainPlan;
import org.apache.phoenix.compile.ExpressionProjector;
import org.apache.phoenix.compile.FromCompiler;
+import org.apache.phoenix.compile.SubqueryRewriter;
import org.apache.phoenix.compile.GroupByCompiler.GroupBy;
import org.apache.phoenix.compile.MutationPlan;
import org.apache.phoenix.compile.OrderByCompiler.OrderBy;
@@ -301,6 +302,11 @@ public class PhoenixStatement implements Statement, SQLCloseable, org.apache.pho
SelectStatement select = SubselectRewriter.flatten(this, stmt.getConnection());
ColumnResolver resolver = FromCompiler.getResolverForQuery(select, stmt.getConnection());
select = StatementNormalizer.normalize(select, resolver);
+ SelectStatement transformedSelect = SubqueryRewriter.transform(select, resolver, stmt.getConnection());
+ if (transformedSelect != select) {
+ resolver = FromCompiler.getResolverForQuery(transformedSelect, stmt.getConnection());
+ select = StatementNormalizer.normalize(transformedSelect, resolver);
+ }
QueryPlan plan = new QueryCompiler(stmt, select, resolver).compile();
plan.getContext().getSequenceManager().validateSequences(seqAction);
return plan;
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java
index eb68211..0d6feda 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java
@@ -139,6 +139,16 @@ public abstract class BooleanParseNodeVisitor<T> extends BaseParseNodeVisitor<T>
}
@Override
+ public boolean visitEnter(ExistsParseNode node) throws SQLException {
+ return enterBooleanNode(node);
+ }
+
+ @Override
+ public T visitLeave(ExistsParseNode node, List<T> l) throws SQLException {
+ return leaveBooleanNode(node, l);
+ }
+
+ @Override
public boolean visitEnter(InListParseNode node) throws SQLException {
return enterBooleanNode(node);
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java
index 2c252e3..45ccdfe 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java
@@ -30,11 +30,11 @@ import java.util.List;
*
* @since 0.1
*/
-public class ExistsParseNode extends BinaryParseNode {
+public class ExistsParseNode extends UnaryParseNode {
private final boolean negate;
- ExistsParseNode(ParseNode l, ParseNode r, boolean negate) {
- super(l, r);
+ ExistsParseNode(ParseNode child, boolean negate) {
+ super(child);
this.negate = negate;
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java
index cbd6bce..a51ca5c 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java
@@ -29,7 +29,15 @@ import java.sql.SQLException;
* @since 0.1
*/
public class JoinTableNode extends TableNode {
- public enum JoinType {Inner, Left, Right, Full};
+ public enum JoinType {
+ Inner,
+ Left,
+ Right,
+ Full,
+ // the following two types derive from sub-query rewriting
+ Semi,
+ Anti,
+ };
private final JoinType type;
private final TableNode lhs;
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
index eb1fda5..2b9f914 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
@@ -24,6 +24,7 @@ import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;
+import java.util.concurrent.atomic.AtomicInteger;
import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
import org.apache.hadoop.hbase.util.Pair;
@@ -180,6 +181,12 @@ public class ParseNodeFactory {
public ParseNodeFactory() {
}
+
+ private static AtomicInteger tempAliasCounter = new AtomicInteger(0);
+
+ public static String createTempAlias() {
+ return "$" + tempAliasCounter.incrementAndGet();
+ }
public ExplainStatement explain(BindableStatement statement) {
return new ExplainStatement(statement);
@@ -395,8 +402,8 @@ public class ParseNodeFactory {
return new InListParseNode(children, negate);
}
- public ExistsParseNode exists(ParseNode l, ParseNode r, boolean negate) {
- return new ExistsParseNode(l, r, negate);
+ public ExistsParseNode exists(ParseNode child, boolean negate) {
+ return new ExistsParseNode(child, negate);
}
public InParseNode in(ParseNode l, ParseNode r, boolean negate) {
@@ -555,7 +562,11 @@ public class ParseNodeFactory {
return new NotEqualParseNode(lhs, rhs);
}
- public NotParseNode not(ParseNode child) {
+ public ParseNode not(ParseNode child) {
+ if (child instanceof ExistsParseNode) {
+ return exists(child.getChildren().get(0), !((ExistsParseNode) child).isNegate());
+ }
+
return new NotParseNode(child);
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
index be34efa..06ac1c6 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
@@ -204,17 +204,21 @@ public class ParseNodeRewriter extends TraverseAllParseNodeVisitor<ParseNode> {
this.nodeCount = 0;
}
- private static interface CompoundNodeFactory {
+ protected static interface CompoundNodeFactory {
ParseNode createNode(List<ParseNode> children);
}
- private ParseNode leaveCompoundNode(CompoundParseNode node, List<ParseNode> children, CompoundNodeFactory factory) {
+ protected ParseNode leaveCompoundNode(CompoundParseNode node, List<ParseNode> children, CompoundNodeFactory factory) {
if (children.equals(node.getChildren())) {
return node;
} else { // Child nodes have been inverted (because a literal was found on LHS)
return factory.createNode(children);
}
}
+
+ @Override
+ protected void enterParseNode(ParseNode node) {
+ }
@Override
public ParseNode visitLeave(AndParseNode node, List<ParseNode> nodes) throws SQLException {
@@ -327,6 +331,16 @@ public class ParseNodeRewriter extends TraverseAllParseNodeVisitor<ParseNode> {
}
@Override
+ public ParseNode visitLeave(final ExistsParseNode node, List<ParseNode> nodes) throws SQLException {
+ return leaveCompoundNode(node, nodes, new CompoundNodeFactory() {
+ @Override
+ public ParseNode createNode(List<ParseNode> children) {
+ return NODE_FACTORY.exists(children.get(0), node.isNegate());
+ }
+ });
+ }
+
+ @Override
public ParseNode visitLeave(final CastParseNode node, List<ParseNode> nodes) throws SQLException {
return leaveCompoundNode(node, nodes, new CompoundNodeFactory() {
@Override
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java
index 01925ff..50edf91 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java
@@ -78,6 +78,9 @@ public interface ParseNodeVisitor<E> {
public boolean visitEnter(NotParseNode node) throws SQLException;
public E visitLeave(NotParseNode node, List<E> l) throws SQLException;
+ public boolean visitEnter(ExistsParseNode node) throws SQLException;
+ public E visitLeave(ExistsParseNode node, List<E> l) throws SQLException;
+
public boolean visitEnter(InListParseNode node) throws SQLException;
public E visitLeave(InListParseNode node, List<E> l) throws SQLException;
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java
index 228e1be..e95b480 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java
@@ -23,6 +23,10 @@ import java.util.List;
public class StatelessTraverseAllParseNodeVisitor extends TraverseAllParseNodeVisitor<Void> {
@Override
+ protected void enterParseNode(ParseNode node) {
+ }
+
+ @Override
public Void visitLeave(LikeParseNode node, List<Void> l) throws SQLException {
return null;
}
@@ -81,6 +85,11 @@ public class StatelessTraverseAllParseNodeVisitor extends TraverseAllParseNodeVi
public Void visitLeave(NotParseNode node, List<Void> l) throws SQLException {
return null;
}
+
+ @Override
+ public Void visitLeave(ExistsParseNode node, List<Void> l) throws SQLException {
+ return null;
+ }
@Override
public Void visitLeave(CastParseNode node, List<Void> l) throws SQLException {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java
index ae24824..bbe58d0 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java
@@ -18,7 +18,6 @@
package org.apache.phoenix.parse;
import java.sql.SQLException;
-import java.util.List;
/**
@@ -29,158 +28,197 @@ import java.util.List;
* @since 0.1
*/
public abstract class TraverseAllParseNodeVisitor<T> extends BaseParseNodeVisitor<T> {
+ protected abstract void enterParseNode(ParseNode node) throws SQLException;
+
@Override
public boolean visitEnter(AndParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(OrParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(FunctionParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(CaseParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(ComparisonParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(LikeParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(NotParseNode node) throws SQLException {
+ enterParseNode(node);
+ return true;
+ }
+
+ @Override
+ public boolean visitEnter(ExistsParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(CastParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(InListParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(InParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(IsNullParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(MultiplyParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(SubtractParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(AddParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(DivideParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(ModulusParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(BetweenParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public T visit(ColumnParseNode node) throws SQLException {
+ enterParseNode(node);
return null;
}
@Override
public T visit(LiteralParseNode node) throws SQLException {
+ enterParseNode(node);
return null;
}
@Override
public T visit(BindParseNode node) throws SQLException {
+ enterParseNode(node);
return null;
}
@Override
public T visit(WildcardParseNode node) throws SQLException {
+ enterParseNode(node);
return null;
}
@Override
public T visit(TableWildcardParseNode node) throws SQLException {
+ enterParseNode(node);
return null;
}
@Override
public T visit(FamilyWildcardParseNode node) throws SQLException {
+ enterParseNode(node);
return null;
}
@Override
public T visit(SubqueryParseNode node) throws SQLException {
+ enterParseNode(node);
return null;
}
@Override
public boolean visitEnter(StringConcatParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(RowValueConstructorParseNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public T visit(SequenceValueParseNode node) throws SQLException {
+ enterParseNode(node);
return null;
}
@Override
public boolean visitEnter(ArrayConstructorNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(ArrayAllComparisonNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(ArrayAnyComparisonNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
@Override
public boolean visitEnter(ArrayElemRefNode node) throws SQLException {
+ enterParseNode(node);
return true;
}
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java
index 37be462..7a8732a 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java
@@ -75,6 +75,11 @@ public abstract class TraverseNoParseNodeVisitor<T> extends BaseParseNodeVisitor
}
@Override
+ public boolean visitEnter(ExistsParseNode node) throws SQLException {
+ return false;
+ }
+
+ @Override
public boolean visitEnter(CastParseNode node) throws SQLException {
return false;
}
@@ -85,6 +90,11 @@ public abstract class TraverseNoParseNodeVisitor<T> extends BaseParseNodeVisitor
}
@Override
+ public T visitLeave(ExistsParseNode node, List<T> l) throws SQLException {
+ return null;
+ }
+
+ @Override
public T visitLeave(CastParseNode node, List<T> l) throws SQLException {
return null;
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java
index 43cb0c3..8e6a84e 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java
@@ -76,6 +76,11 @@ abstract public class UnsupportedAllParseNodeVisitor<E> extends BaseParseNodeVis
}
@Override
+ public E visit(SubqueryParseNode node) throws SQLException {
+ throw new SQLFeatureNotSupportedException(node.toString());
+ }
+
+ @Override
public boolean visitEnter(AndParseNode node) throws SQLException {
throw new SQLFeatureNotSupportedException(node.toString());
}
@@ -139,6 +144,16 @@ abstract public class UnsupportedAllParseNodeVisitor<E> extends BaseParseNodeVis
public boolean visitEnter(NotParseNode node) throws SQLException {
throw new SQLFeatureNotSupportedException(node.toString());
}
+
+ @Override
+ public E visitLeave(ExistsParseNode node, List<E> l) throws SQLException {
+ throw new SQLFeatureNotSupportedException(node.toString());
+ }
+
+ @Override
+ public boolean visitEnter(ExistsParseNode node) throws SQLException {
+ throw new SQLFeatureNotSupportedException(node.toString());
+ }
@Override
public E visitLeave(CastParseNode node, List<E> l) throws SQLException {
@@ -154,6 +169,11 @@ abstract public class UnsupportedAllParseNodeVisitor<E> extends BaseParseNodeVis
public E visitLeave(InListParseNode node, List<E> l) throws SQLException {
throw new SQLFeatureNotSupportedException(node.toString());
}
+
+ @Override
+ public E visitLeave(InParseNode node, List<E> l) throws SQLException {
+ throw new SQLFeatureNotSupportedException(node.toString());
+ }
@Override
public E visitLeave(BetweenParseNode node, List<E> l) throws SQLException {
@@ -164,6 +184,11 @@ abstract public class UnsupportedAllParseNodeVisitor<E> extends BaseParseNodeVis
public boolean visitEnter(InListParseNode node) throws SQLException {
throw new SQLFeatureNotSupportedException(node.toString());
}
+
+ @Override
+ public boolean visitEnter(InParseNode node) throws SQLException {
+ throw new SQLFeatureNotSupportedException(node.toString());
+ }
@Override
public boolean visitEnter(IsNullParseNode node) throws SQLException {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
index a08b0e3..ffaafd8 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
@@ -59,14 +59,12 @@ public class JoinQueryCompilerTest extends BaseConnectionlessQueryTest {
assertEquals(
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY NAME LIKE 'C%'\n" +
- " BUILD HASH TABLE 1\n" +
+ " PARALLEL LEFT-JOIN TABLE 1\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" AFTER-JOIN SERVER FILTER BY I.NAME LIKE 'T%'", QueryUtil.getExplainPlan(rs));
}
@@ -133,6 +131,11 @@ public class JoinQueryCompilerTest extends BaseConnectionlessQueryTest {
SelectStatement select = SubselectRewriter.flatten(parser.parseQuery(), connection);
ColumnResolver resolver = FromCompiler.getResolverForQuery(select, connection);
select = StatementNormalizer.normalize(select, resolver);
+ SelectStatement transformedSelect = SubqueryRewriter.transform(select, resolver, connection);
+ if (transformedSelect != select) {
+ resolver = FromCompiler.getResolverForQuery(transformedSelect, connection);
+ select = StatementNormalizer.normalize(transformedSelect, resolver);
+ }
PhoenixStatement stmt = connection.createStatement().unwrap(PhoenixStatement.class);
return JoinCompiler.compile(stmt, select, resolver);
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
index 11f394f..1844edb 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
@@ -45,6 +45,7 @@ import static org.apache.phoenix.util.TestUtil.HBASE_DYNAMIC_COLUMNS;
import static org.apache.phoenix.util.TestUtil.HBASE_NATIVE;
import static org.apache.phoenix.util.TestUtil.INDEX_DATA_SCHEMA;
import static org.apache.phoenix.util.TestUtil.INDEX_DATA_TABLE;
+import static org.apache.phoenix.util.TestUtil.JOIN_COITEM_TABLE_FULL_NAME;
import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME;
import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME;
import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME;
@@ -433,6 +434,13 @@ public abstract class BaseTest {
" phone varchar(12), " +
" address varchar, " +
" loc_id varchar(5))");
+ builder.put(JOIN_COITEM_TABLE_FULL_NAME, "create table " + JOIN_COITEM_TABLE_FULL_NAME +
+ " (item_id varchar(10) NOT NULL, " +
+ " item_name varchar NOT NULL, " +
+ " co_item_id varchar(10), " +
+ " co_item_name varchar " +
+ " CONSTRAINT pk PRIMARY KEY (item_id, item_name)) " +
+ " SALT_BUCKETS=4");
tableDDLMap = builder.build();
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java b/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
index 1aac3c5..4b25992 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
@@ -182,14 +182,17 @@ public class TestUtil {
public static final String JOIN_CUSTOMER_TABLE = "CustomerTable";
public static final String JOIN_ITEM_TABLE = "ItemTable";
public static final String JOIN_SUPPLIER_TABLE = "SupplierTable";
+ public static final String JOIN_COITEM_TABLE = "CoitemTable";
public static final String JOIN_ORDER_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_ORDER_TABLE + '"';
public static final String JOIN_CUSTOMER_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_CUSTOMER_TABLE + '"';
public static final String JOIN_ITEM_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_ITEM_TABLE + '"';
public static final String JOIN_SUPPLIER_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_SUPPLIER_TABLE + '"';
+ public static final String JOIN_COITEM_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_COITEM_TABLE + '"';
public static final String JOIN_ORDER_TABLE_DISPLAY_NAME = JOIN_SCHEMA + "." + JOIN_ORDER_TABLE;
public static final String JOIN_CUSTOMER_TABLE_DISPLAY_NAME = JOIN_SCHEMA + "." + JOIN_CUSTOMER_TABLE;
public static final String JOIN_ITEM_TABLE_DISPLAY_NAME = JOIN_SCHEMA + "." + JOIN_ITEM_TABLE;
public static final String JOIN_SUPPLIER_TABLE_DISPLAY_NAME = JOIN_SCHEMA + "." + JOIN_SUPPLIER_TABLE;
+ public static final String JOIN_COITEM_TABLE_DISPLAY_NAME = JOIN_SCHEMA + "." + JOIN_COITEM_TABLE;
/**
* Read-only properties used by all tests
[3/3] git commit: PHOENIX-167 Support semi/anti-joins
Posted by ma...@apache.org.
PHOENIX-167 Support semi/anti-joins
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/909d9759
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/909d9759
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/909d9759
Branch: refs/heads/master
Commit: 909d975960f4592e75fb3fdb6c2d0cecd2d51f1d
Parents: 719eaf0
Author: maryannxue <ma...@apache.org>
Authored: Mon Oct 6 13:29:07 2014 -0400
Committer: maryannxue <ma...@apache.org>
Committed: Mon Oct 6 13:29:07 2014 -0400
----------------------------------------------------------------------
.../org/apache/phoenix/end2end/HashJoinIT.java | 433 +++-------
.../org/apache/phoenix/end2end/SubqueryIT.java | 810 +++++++++++++++++++
phoenix-core/src/main/antlr3/PhoenixSQL.g | 2 +-
.../phoenix/compile/ExpressionCompiler.java | 25 +-
.../apache/phoenix/compile/JoinCompiler.java | 135 ++--
.../apache/phoenix/compile/QueryCompiler.java | 37 +-
.../phoenix/compile/StatementNormalizer.java | 3 +-
.../phoenix/compile/SubqueryRewriter.java | 401 +++++++++
.../apache/phoenix/compile/UpsertCompiler.java | 5 +
.../apache/phoenix/compile/WhereOptimizer.java | 15 +-
.../coprocessor/HashJoinRegionScanner.java | 18 +-
.../phoenix/exception/SQLExceptionCode.java | 3 +-
.../apache/phoenix/execute/HashJoinPlan.java | 44 +-
.../apache/phoenix/jdbc/PhoenixStatement.java | 6 +
.../phoenix/parse/BooleanParseNodeVisitor.java | 10 +
.../apache/phoenix/parse/ExistsParseNode.java | 6 +-
.../org/apache/phoenix/parse/JoinTableNode.java | 10 +-
.../apache/phoenix/parse/ParseNodeFactory.java | 17 +-
.../apache/phoenix/parse/ParseNodeRewriter.java | 18 +-
.../apache/phoenix/parse/ParseNodeVisitor.java | 3 +
.../StatelessTraverseAllParseNodeVisitor.java | 9 +
.../parse/TraverseAllParseNodeVisitor.java | 40 +-
.../parse/TraverseNoParseNodeVisitor.java | 10 +
.../parse/UnsupportedAllParseNodeVisitor.java | 25 +
.../phoenix/compile/JoinQueryCompilerTest.java | 13 +-
.../java/org/apache/phoenix/query/BaseTest.java | 8 +
.../java/org/apache/phoenix/util/TestUtil.java | 3 +
27 files changed, 1644 insertions(+), 465 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
index ceba009..8e35216 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
@@ -119,8 +119,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.NAME]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
/*
* testLeftJoinWithAggregation()
@@ -132,8 +131,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY FIRST KEY ONLY",
/*
@@ -147,8 +145,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testRightJoinWithAggregation()
@@ -160,8 +157,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.NAME]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testRightJoinWithAggregation()
@@ -174,8 +170,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testJoinWithWildcard()
@@ -184,8 +179,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY item_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
/*
* testJoinPlanWithIndex()
@@ -197,8 +191,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY (NAME >= 'T1' AND NAME <= 'T5')\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')",
/*
@@ -211,8 +204,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY (NAME = 'T1' OR NAME = 'T5')\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY (NAME = 'S1' OR NAME = 'S5')",
/*
@@ -222,11 +214,10 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY QUANTITY < 5000\n" +
- " BUILD HASH TABLE 1\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY item_id IN (O.item_id)",
/*
@@ -236,8 +227,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY i1.item_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (I2.item_id)",
@@ -250,8 +240,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER SORTED BY [I1.NAME, I2.NAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (I2.supplier_id)",
/*
@@ -263,10 +252,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY order_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
- " BUILD HASH TABLE 1\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
/*
* testStarJoin()
@@ -279,11 +267,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER SORTED BY [O.order_id]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (O.item_id)",
/*
@@ -302,16 +288,13 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
" SERVER SORTED BY [C.customer_id, I.NAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY order_id != '000000000000003'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY NAME != 'T3'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY customer_id IN (O.customer_id)",
/*
@@ -325,8 +308,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.NAME]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
/*
* testJoinWithSubqueryAndAggregation()
@@ -340,8 +322,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY FIRST KEY ONLY",
/*
@@ -356,8 +337,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
" CLIENT MERGE SORT",
@@ -373,8 +353,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [O.Q DESC, I.IID]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
" CLIENT MERGE SORT",
@@ -400,16 +379,13 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
" SERVER SORTED BY [C.CID, QO.INAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY order_id != '000000000000003'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY NAME != 'T3'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
/*
* testJoinWithLimit()
@@ -422,10 +398,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY PageFilter 4\n" +
" SERVER 4 ROW LIMIT\n" +
"CLIENT 4 ROW LIMIT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+ " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" JOIN-SCANNER 4 ROW LIMIT",
/*
@@ -437,10 +412,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
"CLIENT 4 ROW LIMIT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+ " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY supplier_id BETWEEN MIN/MAX OF (I.supplier_id)\n" +
" JOIN-SCANNER 4 ROW LIMIT",
@@ -452,8 +426,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT",
/*
@@ -464,8 +437,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY COL0 IN (RHS.COL2)",
@@ -477,8 +449,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY (COL0, COL1) IN ((RHS.COL1, RHS.COL2))",
@@ -499,8 +470,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.0:NAME]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.0:NAME]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY",
/*
@@ -513,8 +483,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.:item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY",
/*
@@ -528,8 +497,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testRightJoinWithAggregation()
@@ -541,8 +509,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.0:NAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testRightJoinWithAggregation()
@@ -555,8 +522,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testJoinWithWildcard()
@@ -565,8 +531,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY item_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
/*
* testJoinPlanWithIndex()
@@ -578,8 +543,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']",
/*
* testJoinPlanWithIndex()
@@ -590,8 +554,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* AND (supp.name = 'S1' OR supp.name = 'S5')
*/
"CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']",
/*
* testJoinWithSkipMergeOptimization()
@@ -600,11 +563,10 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY QUANTITY < 5000\n" +
- " BUILD HASH TABLE 1\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier",
/*
* testSelfJoin()
@@ -613,8 +575,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY i1.item_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (I2.:item_id)",
@@ -628,8 +589,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [I1.0:NAME, I2.0:NAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item",
/*
* testStarJoin()
@@ -640,10 +600,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY order_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
- " BUILD HASH TABLE 1\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY",
/*
@@ -658,11 +617,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [O.order_id]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer",
/*
* testSubJoin()
@@ -680,16 +637,13 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
" SERVER SORTED BY [C.customer_id, I.0:NAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY order_id != '000000000000003'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY NAME != 'T3'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY customer_id IN (O.customer_id)",
/*
@@ -703,8 +657,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.NAME]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY",
/*
@@ -719,8 +672,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY",
/*
@@ -735,8 +687,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
" CLIENT MERGE SORT",
@@ -752,8 +703,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [O.Q DESC, I.IID]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
" CLIENT MERGE SORT",
@@ -779,16 +729,13 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
" SERVER SORTED BY [C.CID, QO.INAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY order_id != '000000000000003'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY NAME != 'T3'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
/*
* testJoinWithLimit()
@@ -801,10 +748,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY PageFilter 4\n" +
" SERVER 4 ROW LIMIT\n" +
"CLIENT 4 ROW LIMIT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
- " BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+ " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" JOIN-SCANNER 4 ROW LIMIT",
/*
@@ -816,10 +762,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
"CLIENT 4 ROW LIMIT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
- " BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+ " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY supplier_id BETWEEN MIN/MAX OF (I.0:supplier_id)\n" +
" JOIN-SCANNER 4 ROW LIMIT",
@@ -831,8 +776,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT",
/*
@@ -843,8 +787,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY COL0 IN (RHS.COL2)",
@@ -856,8 +799,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY (COL0, COL1) IN ((RHS.COL1, RHS.COL2))",
@@ -878,8 +820,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.0:NAME]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.0:NAME]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" CLIENT MERGE SORT",
@@ -893,8 +834,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.:item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" CLIENT MERGE SORT",
@@ -909,8 +849,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testRightJoinWithAggregation()
@@ -923,8 +862,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.0:NAME]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.0:NAME]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testRightJoinWithAggregation()
@@ -937,8 +875,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
/*
* testJoinWithWildcard()
@@ -947,8 +884,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY item_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
/*
* testJoinPlanWithIndex()
@@ -961,8 +897,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']\n" +
" CLIENT MERGE SORT",
/*
@@ -975,8 +910,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']\n" +
" CLIENT MERGE SORT",
/*
@@ -987,11 +921,10 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY QUANTITY < 5000\n" +
- " BUILD HASH TABLE 1\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + " [-32768]\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY item_id IN (O.item_id)",
@@ -1002,8 +935,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY i1.item_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" CLIENT MERGE SORT\n" +
@@ -1018,8 +950,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [I1.0:NAME, I2.0:NAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (I2.0:supplier_id)",
@@ -1032,11 +963,10 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
* ORDER BY order_id
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [-32768]\n" +
" CLIENT MERGE SORT\n" +
- " BUILD HASH TABLE 1\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" CLIENT MERGE SORT",
@@ -1052,11 +982,9 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [O.order_id]\n"+
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME+" [-32768]\n"+
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF (O.item_id)",
@@ -1076,17 +1004,14 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
" SERVER SORTED BY [C.customer_id, I.0:NAME]\n"+
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY order_id != '000000000000003'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
" SERVER FILTER BY NAME != 'T3'\n" +
" CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY customer_id IN (O.customer_id)",
/*
@@ -1100,8 +1025,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.NAME]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME+" [-32768]\n"+
" SERVER FILTER BY FIRST KEY ONLY\n" +
" CLIENT MERGE SORT",
@@ -1117,8 +1041,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
"CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" CLIENT MERGE SORT",
@@ -1134,8 +1057,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n"+
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
" CLIENT MERGE SORT",
@@ -1151,8 +1073,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [O.Q DESC, I.IID]\n"+
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
" CLIENT MERGE SORT",
@@ -1178,17 +1099,14 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
" SERVER SORTED BY [C.CID, QO.INAME]\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" SERVER FILTER BY order_id != '000000000000003'\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
" SERVER FILTER BY NAME != 'T3'\n" +
" CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
/*
* testJoinWithLimit()
@@ -1201,11 +1119,10 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
" SERVER FILTER BY PageFilter 4\n" +
" SERVER 4 ROW LIMIT\n" +
"CLIENT 4 ROW LIMIT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
" CLIENT MERGE SORT\n" +
- " BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+ " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" JOIN-SCANNER 4 ROW LIMIT",
/*
@@ -1217,11 +1134,10 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
"CLIENT 4 ROW LIMIT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
" CLIENT MERGE SORT\n" +
- " BUILD HASH TABLE 1(DELAYED EVALUATION)\n" +
+ " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
" DYNAMIC SERVER FILTER BY supplier_id BETWEEN MIN/MAX OF (I.0:supplier_id)\n" +
" JOIN-SCANNER 4 ROW LIMIT",
@@ -1233,8 +1149,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT",
/*
@@ -1245,8 +1160,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY COL0 IN (RHS.COL2)",
@@ -1258,8 +1172,7 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
*/
"CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
"CLIENT MERGE SORT\n" +
- " PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
- " BUILD HASH TABLE 0\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY (COL0, COL1) IN ((RHS.COL1, RHS.COL2))",
@@ -3883,153 +3796,5 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT {
}
}
- @Test
- public void testNonCorrelatedSubquery() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- String query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY \"item_id\"";
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000005");
- assertEquals(rs.getString(2), "T5");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "invalid001");
- assertEquals(rs.getString(2), "INVALID-1");
-
- assertFalse(rs.next());
-
- query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" >= ALL (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY \"item_id\"";
- statement = conn.prepareStatement(query);
- rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000006");
- assertEquals(rs.getString(2), "T6");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "invalid001");
- assertEquals(rs.getString(2), "INVALID-1");
-
- assertFalse(rs.next());
-
- query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" < ANY (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")";
- statement = conn.prepareStatement(query);
- rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000001");
- assertEquals(rs.getString(2), "T1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000002");
- assertEquals(rs.getString(2), "T2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000003");
- assertEquals(rs.getString(2), "T3");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000005");
- assertEquals(rs.getString(2), "T5");
-
- assertFalse(rs.next());
-
- query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" < (SELECT max(\"item_id\") FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")";
- statement = conn.prepareStatement(query);
- rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000001");
- assertEquals(rs.getString(2), "T1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000002");
- assertEquals(rs.getString(2), "T2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000003");
- assertEquals(rs.getString(2), "T3");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000005");
- assertEquals(rs.getString(2), "T5");
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testNonCorrelatedSubqueryWithRowConstructor() throws Exception {
- String tempItemJoinTable = "TEMP_ITEM_JOIN_TABLE";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- conn.createStatement().execute("CREATE TABLE " + tempItemJoinTable
- + " (item_id varchar(10) NOT NULL, "
- + " item_name varchar NOT NULL, "
- + " co_item_id varchar(10), "
- + " co_item_name varchar "
- + " CONSTRAINT pk PRIMARY KEY (item_id, item_name)) "
- + " SALT_BUCKETS=4");
-
- PreparedStatement upsertStmt = conn.prepareStatement(
- "upsert into " + tempItemJoinTable + "(item_id, item_name, co_item_id, co_item_name) " + "values (?, ?, ?, ?)");
- upsertStmt.setString(1, "0000000001");
- upsertStmt.setString(2, "T1");
- upsertStmt.setString(3, "0000000002");
- upsertStmt.setString(4, "T3");
- upsertStmt.execute();
- upsertStmt.setString(1, "0000000004");
- upsertStmt.setString(2, "T4");
- upsertStmt.setString(3, "0000000003");
- upsertStmt.setString(4, "T3");
- upsertStmt.execute();
- upsertStmt.setString(1, "0000000003");
- upsertStmt.setString(2, "T4");
- upsertStmt.setString(3, "0000000005");
- upsertStmt.setString(4, "T5");
- upsertStmt.execute();
- upsertStmt.setString(1, "0000000006");
- upsertStmt.setString(2, "T6");
- upsertStmt.setString(3, "0000000001");
- upsertStmt.setString(4, "T1");
- upsertStmt.execute();
- conn.commit();
-
- String query = "SELECT * FROM " + tempItemJoinTable + " WHERE (item_id, item_name) != ALL (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + ")";
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000003");
- assertEquals(rs.getString(2), "T4");
- assertEquals(rs.getString(3), "0000000005");
- assertEquals(rs.getString(4), "T5");
-
- assertFalse(rs.next());
-
- query = "SELECT * FROM " + tempItemJoinTable + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + "))"
- + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + "))";
- statement = conn.prepareStatement(query);
- rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertEquals(rs.getString(3), "0000000003");
- assertEquals(rs.getString(4), "T3");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000006");
- assertEquals(rs.getString(2), "T6");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "T1");
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
}
[2/3] PHOENIX-167 Support semi/anti-joins
Posted by ma...@apache.org.
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java
new file mode 100644
index 0000000..3aecd29
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SubqueryIT.java
@@ -0,0 +1,810 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.JOIN_COITEM_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_COITEM_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SCHEMA;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Timestamp;
+import java.text.SimpleDateFormat;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Properties;
+import java.util.regex.Pattern;
+
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.schema.TableAlreadyExistsException;
+import org.apache.phoenix.util.MetaDataUtil;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.Before;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+@Category(HBaseManagedTimeTest.class)
+@RunWith(Parameterized.class)
+public class SubqueryIT extends BaseHBaseManagedTimeIT {
+
+ private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ private String[] indexDDL;
+ private String[] plans;
+
+ public SubqueryIT(String[] indexDDL, String[] plans) {
+ this.indexDDL = indexDDL;
+ this.plans = plans;
+ }
+
+ @BeforeClass
+ @Shadower(classBeingShadowed = BaseHBaseManagedTimeIT.class)
+ public static void doSetup() throws Exception {
+ Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
+ // Forces server cache to be used
+ props.put(QueryServices.INDEX_MUTATE_BATCH_SIZE_THRESHOLD_ATTRIB, Integer.toString(2));
+ // Must update config before starting server
+ setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
+ }
+
+ @Before
+ public void initTable() throws Exception {
+ initTableValues();
+ if (indexDDL != null && indexDDL.length > 0) {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ for (String ddl : indexDDL) {
+ try {
+ conn.createStatement().execute(ddl);
+ } catch (TableAlreadyExistsException e) {
+ }
+ }
+ conn.close();
+ }
+ }
+
+ @Parameters
+ public static Collection<Object> data() {
+ List<Object> testCases = Lists.newArrayList();
+ testCases.add(new String[][] {
+ {}, {
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER SORTED BY \\[I.NAME\\]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SKIP-SCAN-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + " \\['000000000000001'\\] - \\[\\*\\]\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY item_id IN \\(\\$\\d+.\\$\\d+\\)",
+
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER SORTED BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL SEMI-JOIN TABLE 1(DELAYED EVALUATION) (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+ " CLIENT MERGE SORT",
+
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_DISPLAY_NAME + "\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id, NAME\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id, NAME\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL SEMI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF \\(\\$\\d+.\\$\\d+\\)\n" +
+ " AFTER-JOIN SERVER FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
+
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER SORTED BY [NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL ANTI-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+ " CLIENT MERGE SORT",
+ }});
+ testCases.add(new String[][] {
+ {
+ "CREATE INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
+ "CREATE INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
+ "CREATE INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
+ }, {
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
+ " PARALLEL SEMI-JOIN TABLE 1 \\(SKIP MERGE\\)\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + " \\['000000000000001'\\] - \\[\\*\\]\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT",
+
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
+ " SERVER SORTED BY [I.0:NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " PARALLEL SEMI-JOIN TABLE 1(DELAYED EVALUATION) (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+ " CLIENT MERGE SORT",
+
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_DISPLAY_NAME + "\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[NAME, item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[NAME, item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL SEMI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " AFTER-JOIN SERVER FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
+
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " PARALLEL ANTI-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+ " CLIENT MERGE SORT",
+ }});
+ testCases.add(new String[][] {
+ {
+ "CREATE LOCAL INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
+ "CREATE LOCAL INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
+ "CREATE LOCAL INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
+ }, {
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_ITEM_TABLE_DISPLAY_NAME + " \\[-32768\\]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + " \\[-32768\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL SEMI-JOIN TABLE 1 \\(SKIP MERGE\\)\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + " \\['000000000000001'\\] - \\[\\*\\]\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY item_id IN \\(\\$\\d+.\\$\\d+\\)",
+
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER SORTED BY [I.0:NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL SEMI-JOIN TABLE 1(DELAYED EVALUATION) (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+ " CLIENT MERGE SORT",
+
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_DISPLAY_NAME + "\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_ITEM_TABLE_DISPLAY_NAME + " \\[-32768\\]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[NAME, item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_ITEM_TABLE_DISPLAY_NAME + " \\[-32768\\]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[NAME, item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL SEMI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY \\[item_id\\]\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY item_id BETWEEN MIN/MAX OF \\(\\$\\d+.\\$\\d+\\)\n" +
+ " AFTER-JOIN SERVER FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
+
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL ANTI-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [item_id]\n" +
+ " CLIENT MERGE SORT",
+ }});
+ return testCases;
+ }
+
+
+ protected void initTableValues() throws Exception {
+ ensureTableCreated(getUrl(), JOIN_CUSTOMER_TABLE_FULL_NAME);
+ ensureTableCreated(getUrl(), JOIN_ITEM_TABLE_FULL_NAME);
+ ensureTableCreated(getUrl(), JOIN_SUPPLIER_TABLE_FULL_NAME);
+ ensureTableCreated(getUrl(), JOIN_ORDER_TABLE_FULL_NAME);
+ ensureTableCreated(getUrl(), JOIN_COITEM_TABLE_FULL_NAME);
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.createStatement().execute("CREATE SEQUENCE my.seq");
+ // Insert into customer table
+ PreparedStatement stmt = conn.prepareStatement(
+ "upsert into " + JOIN_CUSTOMER_TABLE_FULL_NAME +
+ " (\"customer_id\", " +
+ " NAME, " +
+ " PHONE, " +
+ " ADDRESS, " +
+ " LOC_ID, " +
+ " DATE) " +
+ "values (?, ?, ?, ?, ?, ?)");
+ stmt.setString(1, "0000000001");
+ stmt.setString(2, "C1");
+ stmt.setString(3, "999-999-1111");
+ stmt.setString(4, "101 XXX Street");
+ stmt.setString(5, "10001");
+ stmt.setDate(6, new Date(format.parse("2013-11-01 10:20:36").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000002");
+ stmt.setString(2, "C2");
+ stmt.setString(3, "999-999-2222");
+ stmt.setString(4, "202 XXX Street");
+ stmt.setString(5, null);
+ stmt.setDate(6, new Date(format.parse("2013-11-25 16:45:07").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000003");
+ stmt.setString(2, "C3");
+ stmt.setString(3, "999-999-3333");
+ stmt.setString(4, "303 XXX Street");
+ stmt.setString(5, null);
+ stmt.setDate(6, new Date(format.parse("2013-11-25 10:06:29").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000004");
+ stmt.setString(2, "C4");
+ stmt.setString(3, "999-999-4444");
+ stmt.setString(4, "404 XXX Street");
+ stmt.setString(5, "10004");
+ stmt.setDate(6, new Date(format.parse("2013-11-22 14:22:56").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000005");
+ stmt.setString(2, "C5");
+ stmt.setString(3, "999-999-5555");
+ stmt.setString(4, "505 XXX Street");
+ stmt.setString(5, "10005");
+ stmt.setDate(6, new Date(format.parse("2013-11-27 09:37:50").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000006");
+ stmt.setString(2, "C6");
+ stmt.setString(3, "999-999-6666");
+ stmt.setString(4, "606 XXX Street");
+ stmt.setString(5, "10001");
+ stmt.setDate(6, new Date(format.parse("2013-11-01 10:20:36").getTime()));
+ stmt.execute();
+
+ // Insert into item table
+ stmt = conn.prepareStatement(
+ "upsert into " + JOIN_ITEM_TABLE_FULL_NAME +
+ " (\"item_id\", " +
+ " NAME, " +
+ " PRICE, " +
+ " DISCOUNT1, " +
+ " DISCOUNT2, " +
+ " \"supplier_id\", " +
+ " DESCRIPTION) " +
+ "values (?, ?, ?, ?, ?, ?, ?)");
+ stmt.setString(1, "0000000001");
+ stmt.setString(2, "T1");
+ stmt.setInt(3, 100);
+ stmt.setInt(4, 5);
+ stmt.setInt(5, 10);
+ stmt.setString(6, "0000000001");
+ stmt.setString(7, "Item T1");
+ stmt.execute();
+
+ stmt.setString(1, "0000000002");
+ stmt.setString(2, "T2");
+ stmt.setInt(3, 200);
+ stmt.setInt(4, 5);
+ stmt.setInt(5, 8);
+ stmt.setString(6, "0000000001");
+ stmt.setString(7, "Item T2");
+ stmt.execute();
+
+ stmt.setString(1, "0000000003");
+ stmt.setString(2, "T3");
+ stmt.setInt(3, 300);
+ stmt.setInt(4, 8);
+ stmt.setInt(5, 12);
+ stmt.setString(6, "0000000002");
+ stmt.setString(7, "Item T3");
+ stmt.execute();
+
+ stmt.setString(1, "0000000004");
+ stmt.setString(2, "T4");
+ stmt.setInt(3, 400);
+ stmt.setInt(4, 6);
+ stmt.setInt(5, 10);
+ stmt.setString(6, "0000000002");
+ stmt.setString(7, "Item T4");
+ stmt.execute();
+
+ stmt.setString(1, "0000000005");
+ stmt.setString(2, "T5");
+ stmt.setInt(3, 500);
+ stmt.setInt(4, 8);
+ stmt.setInt(5, 15);
+ stmt.setString(6, "0000000005");
+ stmt.setString(7, "Item T5");
+ stmt.execute();
+
+ stmt.setString(1, "0000000006");
+ stmt.setString(2, "T6");
+ stmt.setInt(3, 600);
+ stmt.setInt(4, 8);
+ stmt.setInt(5, 15);
+ stmt.setString(6, "0000000006");
+ stmt.setString(7, "Item T6");
+ stmt.execute();
+
+ stmt.setString(1, "invalid001");
+ stmt.setString(2, "INVALID-1");
+ stmt.setInt(3, 0);
+ stmt.setInt(4, 0);
+ stmt.setInt(5, 0);
+ stmt.setString(6, "0000000000");
+ stmt.setString(7, "Invalid item for join test");
+ stmt.execute();
+
+ // Insert into supplier table
+ stmt = conn.prepareStatement(
+ "upsert into " + JOIN_SUPPLIER_TABLE_FULL_NAME +
+ " (\"supplier_id\", " +
+ " NAME, " +
+ " PHONE, " +
+ " ADDRESS, " +
+ " LOC_ID) " +
+ "values (?, ?, ?, ?, ?)");
+ stmt.setString(1, "0000000001");
+ stmt.setString(2, "S1");
+ stmt.setString(3, "888-888-1111");
+ stmt.setString(4, "101 YYY Street");
+ stmt.setString(5, "10001");
+ stmt.execute();
+
+ stmt.setString(1, "0000000002");
+ stmt.setString(2, "S2");
+ stmt.setString(3, "888-888-2222");
+ stmt.setString(4, "202 YYY Street");
+ stmt.setString(5, "10002");
+ stmt.execute();
+
+ stmt.setString(1, "0000000003");
+ stmt.setString(2, "S3");
+ stmt.setString(3, "888-888-3333");
+ stmt.setString(4, "303 YYY Street");
+ stmt.setString(5, null);
+ stmt.execute();
+
+ stmt.setString(1, "0000000004");
+ stmt.setString(2, "S4");
+ stmt.setString(3, "888-888-4444");
+ stmt.setString(4, "404 YYY Street");
+ stmt.setString(5, null);
+ stmt.execute();
+
+ stmt.setString(1, "0000000005");
+ stmt.setString(2, "S5");
+ stmt.setString(3, "888-888-5555");
+ stmt.setString(4, "505 YYY Street");
+ stmt.setString(5, "10005");
+ stmt.execute();
+
+ stmt.setString(1, "0000000006");
+ stmt.setString(2, "S6");
+ stmt.setString(3, "888-888-6666");
+ stmt.setString(4, "606 YYY Street");
+ stmt.setString(5, "10006");
+ stmt.execute();
+
+ // Insert into order table
+ stmt = conn.prepareStatement(
+ "upsert into " + JOIN_ORDER_TABLE_FULL_NAME +
+ " (\"order_id\", " +
+ " \"customer_id\", " +
+ " \"item_id\", " +
+ " PRICE, " +
+ " QUANTITY," +
+ " DATE) " +
+ "values (?, ?, ?, ?, ?, ?)");
+ stmt.setString(1, "000000000000001");
+ stmt.setString(2, "0000000004");
+ stmt.setString(3, "0000000001");
+ stmt.setInt(4, 100);
+ stmt.setInt(5, 1000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-22 14:22:56").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "000000000000002");
+ stmt.setString(2, "0000000003");
+ stmt.setString(3, "0000000006");
+ stmt.setInt(4, 552);
+ stmt.setInt(5, 2000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-25 10:06:29").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "000000000000003");
+ stmt.setString(2, "0000000002");
+ stmt.setString(3, "0000000002");
+ stmt.setInt(4, 190);
+ stmt.setInt(5, 3000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-25 16:45:07").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "000000000000004");
+ stmt.setString(2, "0000000004");
+ stmt.setString(3, "0000000006");
+ stmt.setInt(4, 510);
+ stmt.setInt(5, 4000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-26 13:26:04").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "000000000000005");
+ stmt.setString(2, "0000000005");
+ stmt.setString(3, "0000000003");
+ stmt.setInt(4, 264);
+ stmt.setInt(5, 5000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-27 09:37:50").getTime()));
+ stmt.execute();
+
+ conn.commit();
+
+ // Insert into coitem table
+ stmt = conn.prepareStatement(
+ "upsert into " + JOIN_COITEM_TABLE_FULL_NAME +
+ " (item_id, " +
+ " item_name, " +
+ " co_item_id, " +
+ " co_item_name) " +
+ "values (?, ?, ?, ?)");
+ stmt.setString(1, "0000000001");
+ stmt.setString(2, "T1");
+ stmt.setString(3, "0000000002");
+ stmt.setString(4, "T3");
+ stmt.execute();
+
+ stmt.setString(1, "0000000004");
+ stmt.setString(2, "T4");
+ stmt.setString(3, "0000000003");
+ stmt.setString(4, "T3");
+ stmt.execute();
+
+ stmt.setString(1, "0000000003");
+ stmt.setString(2, "T4");
+ stmt.setString(3, "0000000005");
+ stmt.setString(4, "T5");
+ stmt.execute();
+
+ stmt.setString(1, "0000000006");
+ stmt.setString(2, "T6");
+ stmt.setString(3, "0000000001");
+ stmt.setString(4, "T1");
+ stmt.execute();
+
+ conn.commit();
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testNonCorrelatedSubquery() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" >= ALL (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY name";
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "invalid001");
+ assertEquals(rs.getString(2), "INVALID-1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+
+ assertFalse(rs.next());
+
+ query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" < ANY (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+
+ assertFalse(rs.next());
+
+ query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" < (SELECT max(\"item_id\") FROM " + JOIN_ORDER_TABLE_FULL_NAME + ")";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+
+ assertFalse(rs.next());
+
+ query = "SELECT * FROM " + JOIN_COITEM_TABLE_FULL_NAME + " WHERE (item_id, item_name) != ALL (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + ")";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "T5");
+
+ assertFalse(rs.next());
+
+ query = "SELECT * FROM " + JOIN_COITEM_TABLE_FULL_NAME + " WHERE EXISTS (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + ")";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "T3");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "T5");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000003");
+ assertEquals(rs.getString(4), "T3");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "T1");
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testInSubquery() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY name";
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+
+ assertFalse(rs.next());
+
+ query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY name";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "invalid001");
+ assertEquals(rs.getString(2), "INVALID-1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+
+ assertFalse(rs.next());
+
+ query = "SELECT i.\"item_id\", s.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " i JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + " WHERE \"order_id\" > '000000000000001') ORDER BY i.name";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "S6");
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ String plan = QueryUtil.getExplainPlan(rs);
+ assertTrue("\"" + plan + "\" does not match \"" + plans[0] + "\"", Pattern.matches(plans[0], plan));
+
+ query = "SELECT i.\"item_id\", s.name FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME + " s LEFT JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " i ON i.\"supplier_id\" = s.\"supplier_id\" WHERE i.\"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") ORDER BY i.name";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "S6");
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ assertEquals(plans[1], QueryUtil.getExplainPlan(rs));
+
+ query = "SELECT * FROM " + JOIN_COITEM_TABLE_FULL_NAME + " WHERE (item_id, item_name) IN (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + "))"
+ + " OR (co_item_id, co_item_name) IN (SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + "))";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000003");
+ assertEquals(rs.getString(4), "T3");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "T1");
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ plan = QueryUtil.getExplainPlan(rs);
+ assertTrue("\"" + plan + "\" does not match \"" + plans[2] + "\"", Pattern.matches(plans[2], plan));
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testExistsSubquery() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ String query = "SELECT \"item_id\", name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " i WHERE NOT EXISTS (SELECT 1 FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o WHERE o.\"item_id\" = i.\"item_id\") ORDER BY name";
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "invalid001");
+ assertEquals(rs.getString(2), "INVALID-1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ assertEquals(plans[3], QueryUtil.getExplainPlan(rs));
+
+ query = "SELECT * FROM " + JOIN_COITEM_TABLE_FULL_NAME + " co WHERE EXISTS (SELECT 1 FROM " + JOIN_ITEM_TABLE_FULL_NAME + " i WHERE NOT EXISTS (SELECT 1 FROM " + JOIN_ORDER_TABLE_FULL_NAME + " WHERE \"item_id\" = i.\"item_id\") AND co.item_id = \"item_id\" AND name = co.item_name)"
+ + " OR EXISTS (SELECT 1 FROM " + JOIN_ITEM_TABLE_FULL_NAME + " WHERE \"item_id\" IN (SELECT \"item_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + ") AND co.co_item_id = \"item_id\" AND name = co.co_item_name)";
+ statement = conn.prepareStatement(query);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000003");
+ assertEquals(rs.getString(4), "T3");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "T1");
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ String plan = QueryUtil.getExplainPlan(rs);
+ assertTrue("\"" + plan + "\" does not match \"" + plans[2] + "\"", Pattern.matches(plans[2], plan));
+ } finally {
+ conn.close();
+ }
+ }
+
+}
+
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/antlr3/PhoenixSQL.g
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/antlr3/PhoenixSQL.g b/phoenix-core/src/main/antlr3/PhoenixSQL.g
index 893c3de..980ec8b 100644
--- a/phoenix-core/src/main/antlr3/PhoenixSQL.g
+++ b/phoenix-core/src/main/antlr3/PhoenixSQL.g
@@ -717,7 +717,6 @@ boolean_expression returns [ParseNode ret]
| (IS n=NOT? NULL {$ret = factory.isNull(l,n!=null); } )
| ( n=NOT? ((LIKE r=value_expression {$ret = factory.like(l,r,n!=null,LikeType.CASE_SENSITIVE); } )
| (ILIKE r=value_expression {$ret = factory.like(l,r,n!=null,LikeType.CASE_INSENSITIVE); } )
- | (EXISTS LPAREN r=subquery_expression RPAREN {$ret = factory.exists(l,r,n!=null);} )
| (BETWEEN r1=value_expression AND r2=value_expression {$ret = factory.between(l,r1,r2,n!=null); } )
| ((IN ((r=bind_expression {$ret = factory.inList(Arrays.asList(l,r),n!=null);} )
| (LPAREN r=subquery_expression RPAREN {$ret = factory.in(l,r,n!=null);} )
@@ -725,6 +724,7 @@ boolean_expression returns [ParseNode ret]
)))
))
| { $ret = l; } )
+ | EXISTS LPAREN s=subquery_expression RPAREN {$ret = factory.exists(s,false);}
;
bind_expression returns [BindParseNode ret]
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
index 573cb55..a01c147 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java
@@ -79,6 +79,7 @@ import org.apache.phoenix.parse.CastParseNode;
import org.apache.phoenix.parse.ColumnParseNode;
import org.apache.phoenix.parse.ComparisonParseNode;
import org.apache.phoenix.parse.DivideParseNode;
+import org.apache.phoenix.parse.ExistsParseNode;
import org.apache.phoenix.parse.FunctionParseNode;
import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunctionInfo;
import org.apache.phoenix.parse.LikeParseNode.LikeType;
@@ -1246,29 +1247,15 @@ public class ExpressionCompiler extends UnsupportedAllParseNodeVisitor<Expressio
}
@Override
- public boolean visitEnter(InParseNode node) throws SQLException {
+ public boolean visitEnter(ExistsParseNode node) throws SQLException {
return true;
}
@Override
- public Expression visitLeave(InParseNode node, List<Expression> l)
- throws SQLException {
- Expression firstChild = l.get(0);
- LiteralExpression secondChild = (LiteralExpression) l.get(1);
- ImmutableBytesWritable ptr = context.getTempPtr();
- ParseNode firstChildNode = node.getChildren().get(0);
-
- if (firstChildNode instanceof BindParseNode) {
- context.getBindManager().addParamMetaData((BindParseNode)firstChildNode, firstChild);
- }
-
- List<Expression> children = Lists.<Expression> newArrayList(firstChild);
- PhoenixArray array = (PhoenixArray) secondChild.getValue();
- PDataType type = PDataType.fromTypeId(array.getBaseType());
- for (Object obj : (Object[]) array.getArray()) {
- children.add(LiteralExpression.newConstant(obj, type));
- }
- return wrapGroupByExpression(InListExpression.create(children, node.isNegate(), ptr));
+ public Expression visitLeave(ExistsParseNode node, List<Expression> l) throws SQLException {
+ LiteralExpression child = (LiteralExpression) l.get(0);
+ PhoenixArray array = (PhoenixArray) child.getValue();
+ return LiteralExpression.newConstant(array.getDimensions() > 0 ^ node.isNegate(), PDataType.BOOLEAN);
}
@Override
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
index 0a97292..d473c97 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
@@ -51,6 +51,7 @@ import org.apache.phoenix.parse.ComparisonParseNode;
import org.apache.phoenix.parse.DerivedTableNode;
import org.apache.phoenix.parse.EqualParseNode;
import org.apache.phoenix.parse.HintNode;
+import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor;
import org.apache.phoenix.parse.HintNode.Hint;
import org.apache.phoenix.parse.JoinTableNode;
import org.apache.phoenix.parse.JoinTableNode.JoinType;
@@ -59,7 +60,6 @@ import org.apache.phoenix.parse.OrderByNode;
import org.apache.phoenix.parse.ParseNode;
import org.apache.phoenix.parse.ParseNodeFactory;
import org.apache.phoenix.parse.SelectStatement;
-import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor;
import org.apache.phoenix.parse.TableName;
import org.apache.phoenix.parse.TableNode;
import org.apache.phoenix.parse.TableNodeVisitor;
@@ -82,6 +82,7 @@ import org.apache.phoenix.util.SchemaUtil;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.ListMultimap;
import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
public class JoinCompiler {
@@ -122,9 +123,9 @@ public class JoinCompiler {
joinTable.addFilter(select.getWhere());
}
- ColumnParseNodeVisitor generalRefVisitor = new ColumnParseNodeVisitor(resolver);
- ColumnParseNodeVisitor joinLocalRefVisitor = new ColumnParseNodeVisitor(resolver);
- ColumnParseNodeVisitor prefilterRefVisitor = new ColumnParseNodeVisitor(resolver);
+ ColumnRefParseNodeVisitor generalRefVisitor = new ColumnRefParseNodeVisitor(resolver);
+ ColumnRefParseNodeVisitor joinLocalRefVisitor = new ColumnRefParseNodeVisitor(resolver);
+ ColumnRefParseNodeVisitor prefilterRefVisitor = new ColumnRefParseNodeVisitor(resolver);
joinTable.pushDownColumnRefVisitors(generalRefVisitor, joinLocalRefVisitor, prefilterRefVisitor);
@@ -256,7 +257,7 @@ public class JoinCompiler {
this.prefilterAcceptedTables = new ArrayList<JoinTable>();
for (int i = lastRightJoinIndex == -1 ? 0 : lastRightJoinIndex; i < joinSpecs.size(); i++) {
JoinSpec joinSpec = joinSpecs.get(i);
- if (joinSpec.getType() != JoinType.Left) {
+ if (joinSpec.getType() != JoinType.Left && joinSpec.getType() != JoinType.Anti) {
prefilterAcceptedTables.add(joinSpec.getJoinTable());
}
}
@@ -306,9 +307,9 @@ public class JoinCompiler {
filter.accept(visitor);
}
- public void pushDownColumnRefVisitors(ColumnParseNodeVisitor generalRefVisitor,
- ColumnParseNodeVisitor joinLocalRefVisitor,
- ColumnParseNodeVisitor prefilterRefVisitor) throws SQLException {
+ public void pushDownColumnRefVisitors(ColumnRefParseNodeVisitor generalRefVisitor,
+ ColumnRefParseNodeVisitor joinLocalRefVisitor,
+ ColumnRefParseNodeVisitor prefilterRefVisitor) throws SQLException {
for (ParseNode node : table.getPreFilters()) {
node.accept(prefilterRefVisitor);
}
@@ -359,14 +360,18 @@ public class JoinCompiler {
if (!table.isFlat() ||
(!useStarJoin
&& count > 1
- && joinSpecs.get(count - 1).getType() != JoinType.Left))
+ && joinSpecs.get(count - 1).getType() != JoinType.Left
+ && joinSpecs.get(count - 1).getType() != JoinType.Semi
+ && joinSpecs.get(count - 1).getType() != JoinType.Anti))
return null;
boolean[] vector = new boolean[count];
for (int i = 0; i < count; i++) {
JoinSpec joinSpec = joinSpecs.get(i);
if (joinSpec.getType() != JoinType.Left
- && joinSpec.getType() != JoinType.Inner)
+ && joinSpec.getType() != JoinType.Inner
+ && joinSpec.getType() != JoinType.Semi
+ && joinSpec.getType() != JoinType.Anti)
return null;
vector[i] = true;
Iterator<TableRef> iter = joinSpec.getDependencies().iterator();
@@ -787,22 +792,22 @@ public class JoinCompiler {
}
private static class WhereNodeVisitor extends BooleanParseNodeVisitor<Void> {
- private ColumnResolver resolver;
private Table table;
private List<ParseNode> postFilters;
private List<TableRef> selfTableRefs;
private boolean hasRightJoin;
private List<JoinTable> prefilterAcceptedTables;
+ ColumnRefParseNodeVisitor columnRefVisitor;
public WhereNodeVisitor(ColumnResolver resolver, Table table,
List<ParseNode> postFilters, List<TableRef> selfTableRefs, boolean hasRightJoin,
List<JoinTable> prefilterAcceptedTables) {
- this.resolver = resolver;
this.table = table;
this.postFilters = postFilters;
this.selfTableRefs = selfTableRefs;
this.hasRightJoin = hasRightJoin;
this.prefilterAcceptedTables = prefilterAcceptedTables;
+ this.columnRefVisitor = new ColumnRefParseNodeVisitor(resolver);
}
@Override
@@ -813,9 +818,9 @@ public class JoinCompiler {
@Override
protected Void leaveBooleanNode(ParseNode node,
List<Void> l) throws SQLException {
- ColumnParseNodeVisitor visitor = new ColumnParseNodeVisitor(resolver);
- node.accept(visitor);
- ColumnParseNodeVisitor.ContentType type = visitor.getContentType(selfTableRefs);
+ columnRefVisitor.reset();
+ node.accept(columnRefVisitor);
+ ColumnRefParseNodeVisitor.ColumnRefType type = columnRefVisitor.getContentType(selfTableRefs);
switch (type) {
case NONE:
case SELF_ONLY:
@@ -828,7 +833,7 @@ public class JoinCompiler {
case FOREIGN_ONLY:
JoinTable matched = null;
for (JoinTable joinTable : prefilterAcceptedTables) {
- if (visitor.getContentType(joinTable.getTableRefs()) == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+ if (columnRefVisitor.getContentType(joinTable.getTableRefs()) == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
matched = joinTable;
break;
}
@@ -868,17 +873,17 @@ public class JoinCompiler {
}
private static class OnNodeVisitor extends BooleanParseNodeVisitor<Void> {
- private ColumnResolver resolver;
private List<ComparisonParseNode> onConditions;
private Set<TableRef> dependencies;
private JoinTable joinTable;
+ private ColumnRefParseNodeVisitor columnRefVisitor;
public OnNodeVisitor(ColumnResolver resolver, List<ComparisonParseNode> onConditions,
Set<TableRef> dependencies, JoinTable joinTable) {
- this.resolver = resolver;
this.onConditions = onConditions;
this.dependencies = dependencies;
this.joinTable = joinTable;
+ this.columnRefVisitor = new ColumnRefParseNodeVisitor(resolver);
}
@Override
@@ -889,11 +894,11 @@ public class JoinCompiler {
@Override
protected Void leaveBooleanNode(ParseNode node,
List<Void> l) throws SQLException {
- ColumnParseNodeVisitor visitor = new ColumnParseNodeVisitor(resolver);
- node.accept(visitor);
- ColumnParseNodeVisitor.ContentType type = visitor.getContentType(joinTable.getTableRefs());
- if (type == ColumnParseNodeVisitor.ContentType.NONE
- || type == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+ columnRefVisitor.reset();
+ node.accept(columnRefVisitor);
+ ColumnRefParseNodeVisitor.ColumnRefType type = columnRefVisitor.getContentType(joinTable.getTableRefs());
+ if (type == ColumnRefParseNodeVisitor.ColumnRefType.NONE
+ || type == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
joinTable.addFilter(node);
} else {
throwUnsupportedJoinConditionException();
@@ -926,23 +931,25 @@ public class JoinCompiler {
throws SQLException {
if (!(node instanceof EqualParseNode))
return leaveBooleanNode(node, l);
- ColumnParseNodeVisitor lhsVisitor = new ColumnParseNodeVisitor(resolver);
- ColumnParseNodeVisitor rhsVisitor = new ColumnParseNodeVisitor(resolver);
- node.getLHS().accept(lhsVisitor);
- node.getRHS().accept(rhsVisitor);
- ColumnParseNodeVisitor.ContentType lhsType = lhsVisitor.getContentType(joinTable.getTableRefs());
- ColumnParseNodeVisitor.ContentType rhsType = rhsVisitor.getContentType(joinTable.getTableRefs());
- if ((lhsType == ColumnParseNodeVisitor.ContentType.SELF_ONLY || lhsType == ColumnParseNodeVisitor.ContentType.NONE)
- && (rhsType == ColumnParseNodeVisitor.ContentType.SELF_ONLY || rhsType == ColumnParseNodeVisitor.ContentType.NONE)) {
+ columnRefVisitor.reset();
+ node.getLHS().accept(columnRefVisitor);
+ ColumnRefParseNodeVisitor.ColumnRefType lhsType = columnRefVisitor.getContentType(joinTable.getTableRefs());
+ Set<TableRef> lhsTableRefSet = Sets.newHashSet(columnRefVisitor.getTableRefSet());
+ columnRefVisitor.reset();
+ node.getRHS().accept(columnRefVisitor);
+ ColumnRefParseNodeVisitor.ColumnRefType rhsType = columnRefVisitor.getContentType(joinTable.getTableRefs());
+ Set<TableRef> rhsTableRefSet = Sets.newHashSet(columnRefVisitor.getTableRefSet());
+ if ((lhsType == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY || lhsType == ColumnRefParseNodeVisitor.ColumnRefType.NONE)
+ && (rhsType == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY || rhsType == ColumnRefParseNodeVisitor.ColumnRefType.NONE)) {
joinTable.addFilter(node);
- } else if (lhsType == ColumnParseNodeVisitor.ContentType.FOREIGN_ONLY
- && rhsType == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+ } else if (lhsType == ColumnRefParseNodeVisitor.ColumnRefType.FOREIGN_ONLY
+ && rhsType == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
onConditions.add(node);
- dependencies.addAll(lhsVisitor.getTableRefSet());
- } else if (rhsType == ColumnParseNodeVisitor.ContentType.FOREIGN_ONLY
- && lhsType == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+ dependencies.addAll(lhsTableRefSet);
+ } else if (rhsType == ColumnRefParseNodeVisitor.ColumnRefType.FOREIGN_ONLY
+ && lhsType == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
onConditions.add(NODE_FACTORY.equal(node.getRHS(), node.getLHS()));
- dependencies.addAll(rhsVisitor.getTableRefSet());
+ dependencies.addAll(rhsTableRefSet);
} else {
throwUnsupportedJoinConditionException();
}
@@ -961,25 +968,25 @@ public class JoinCompiler {
throw new SQLFeatureNotSupportedException("Does not support non-standard or non-equi join conditions.");
}
}
-
- private static class ColumnParseNodeVisitor extends StatelessTraverseAllParseNodeVisitor {
- public enum ContentType {NONE, SELF_ONLY, FOREIGN_ONLY, COMPLEX};
-
+
+ private static class ColumnRefParseNodeVisitor extends StatelessTraverseAllParseNodeVisitor {
+ public enum ColumnRefType {NONE, SELF_ONLY, FOREIGN_ONLY, COMPLEX};
+
private ColumnResolver resolver;
private final Set<TableRef> tableRefSet;
private final Map<ColumnRef, ColumnParseNode> columnRefMap;
-
- public ColumnParseNodeVisitor(ColumnResolver resolver) {
+
+ public ColumnRefParseNodeVisitor(ColumnResolver resolver) {
this.resolver = resolver;
this.tableRefSet = new HashSet<TableRef>();
this.columnRefMap = new HashMap<ColumnRef, ColumnParseNode>();
}
-
+
public void reset() {
this.tableRefSet.clear();
this.columnRefMap.clear();
}
-
+
@Override
public Void visit(ColumnParseNode node) throws SQLException {
ColumnRef columnRef = resolver.resolveColumn(node.getSchemaName(), node.getTableName(), node.getName());
@@ -987,41 +994,41 @@ public class JoinCompiler {
tableRefSet.add(columnRef.getTableRef());
return null;
}
-
+
public Set<TableRef> getTableRefSet() {
return tableRefSet;
}
-
+
public Map<ColumnRef, ColumnParseNode> getColumnRefMap() {
return columnRefMap;
}
-
- public ContentType getContentType(List<TableRef> selfTableRefs) {
+
+ public ColumnRefType getContentType(List<TableRef> selfTableRefs) {
if (tableRefSet.isEmpty())
- return ContentType.NONE;
-
- ContentType ret = ContentType.NONE;
+ return ColumnRefType.NONE;
+
+ ColumnRefType ret = ColumnRefType.NONE;
for (TableRef tRef : tableRefSet) {
boolean isSelf = selfTableRefs.contains(tRef);
switch (ret) {
case NONE:
- ret = isSelf ? ContentType.SELF_ONLY : ContentType.FOREIGN_ONLY;
+ ret = isSelf ? ColumnRefType.SELF_ONLY : ColumnRefType.FOREIGN_ONLY;
break;
case SELF_ONLY:
- ret = isSelf ? ContentType.SELF_ONLY : ContentType.COMPLEX;
+ ret = isSelf ? ColumnRefType.SELF_ONLY : ColumnRefType.COMPLEX;
break;
case FOREIGN_ONLY:
- ret = isSelf ? ContentType.COMPLEX : ContentType.FOREIGN_ONLY;
+ ret = isSelf ? ColumnRefType.COMPLEX : ColumnRefType.FOREIGN_ONLY;
break;
default: // COMPLEX do nothing
break;
}
-
- if (ret == ContentType.COMPLEX) {
+
+ if (ret == ColumnRefType.COMPLEX) {
break;
}
}
-
+
return ret;
}
}
@@ -1050,7 +1057,7 @@ public class JoinCompiler {
private static List<AliasedNode> extractFromSelect(List<AliasedNode> select, TableRef tableRef, ColumnResolver resolver) throws SQLException {
List<AliasedNode> ret = new ArrayList<AliasedNode>();
- ColumnParseNodeVisitor visitor = new ColumnParseNodeVisitor(resolver);
+ ColumnRefParseNodeVisitor visitor = new ColumnRefParseNodeVisitor(resolver);
for (AliasedNode aliasedNode : select) {
ParseNode node = aliasedNode.getNode();
if (node instanceof TableWildcardParseNode) {
@@ -1064,10 +1071,10 @@ public class JoinCompiler {
}
node.accept(visitor);
- ColumnParseNodeVisitor.ContentType type = visitor.getContentType(Collections.singletonList(tableRef));
- if (type == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+ ColumnRefParseNodeVisitor.ColumnRefType type = visitor.getContentType(Collections.singletonList(tableRef));
+ if (type == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
ret.add(aliasedNode);
- } else if (type == ColumnParseNodeVisitor.ContentType.COMPLEX) {
+ } else if (type == ColumnRefParseNodeVisitor.ColumnRefType.COMPLEX) {
for (Map.Entry<ColumnRef, ColumnParseNode> entry : visitor.getColumnRefMap().entrySet()) {
if (entry.getKey().getTableRef().equals(tableRef)) {
ret.add(NODE_FACTORY.aliasedNode(null, entry.getValue()));
@@ -1083,7 +1090,7 @@ public class JoinCompiler {
TableRef groupByTableRef = null;
TableRef orderByTableRef = null;
if (select.getGroupBy() != null && !select.getGroupBy().isEmpty()) {
- ColumnParseNodeVisitor groupByVisitor = new ColumnParseNodeVisitor(resolver);
+ ColumnRefParseNodeVisitor groupByVisitor = new ColumnRefParseNodeVisitor(resolver);
for (ParseNode node : select.getGroupBy()) {
node.accept(groupByVisitor);
}
@@ -1092,7 +1099,7 @@ public class JoinCompiler {
groupByTableRef = set.iterator().next();
}
} else if (select.getOrderBy() != null && !select.getOrderBy().isEmpty()) {
- ColumnParseNodeVisitor orderByVisitor = new ColumnParseNodeVisitor(resolver);
+ ColumnRefParseNodeVisitor orderByVisitor = new ColumnRefParseNodeVisitor(resolver);
for (OrderByNode node : select.getOrderBy()) {
node.getNode().accept(orderByVisitor);
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
index 1512d82..52abb9e 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
@@ -215,15 +215,17 @@ public class QueryCompiler {
Pair<List<Expression>, List<Expression>> joinConditions = joinSpec.compileJoinConditions(context, leftResolver, resolver);
joinExpressions[i] = joinConditions.getFirst();
List<Expression> hashExpressions = joinConditions.getSecond();
- Pair<Expression, Expression> keyRangeExpressions = getKeyExpressionCombinations(context, tableRef, joinSpec.getType(), joinExpressions[i], hashExpressions);
+ Pair<Expression, Expression> keyRangeExpressions = new Pair<Expression, Expression>(null, null);
+ boolean complete = getKeyExpressionCombinations(keyRangeExpressions, context, tableRef, joinSpec.getType(), joinExpressions[i], hashExpressions);
Expression keyRangeLhsExpression = keyRangeExpressions.getFirst();
Expression keyRangeRhsExpression = keyRangeExpressions.getSecond();
boolean hasFilters = joinSpec.getJoinTable().hasFilters();
+ boolean optimized = complete && hasFilters;
joinTypes[i] = joinSpec.getType();
if (i < count - 1) {
fieldPositions[i + 1] = fieldPositions[i] + (tables[i] == null ? 0 : (tables[i].getColumns().size() - tables[i].getPKColumns().size()));
}
- subPlans[i] = new HashSubPlan(i, joinPlan, hashExpressions, keyRangeLhsExpression, keyRangeRhsExpression, clientProjector, hasFilters);
+ subPlans[i] = new HashSubPlan(i, joinPlan, optimized ? null : hashExpressions, keyRangeLhsExpression, keyRangeRhsExpression, clientProjector, hasFilters);
}
if (needsProject) {
TupleProjector.serializeProjectorIntoScan(context.getScan(), initialProjectedTable.createTupleProjector());
@@ -292,7 +294,8 @@ public class QueryCompiler {
limit = LimitCompiler.compile(context, rhs);
}
HashJoinInfo joinInfo = new HashJoinInfo(projectedTable.getTable(), joinIds, new List[] {joinExpressions}, new JoinType[] {type == JoinType.Inner ? type : JoinType.Left}, new boolean[] {true}, new PTable[] {lhsProjTable.getTable()}, new int[] {fieldPosition}, postJoinFilterExpression, limit, forceProjection);
- Pair<Expression, Expression> keyRangeExpressions = getKeyExpressionCombinations(context, rhsTableRef, type, joinExpressions, hashExpressions);
+ Pair<Expression, Expression> keyRangeExpressions = new Pair<Expression, Expression>(null, null);
+ getKeyExpressionCombinations(keyRangeExpressions, context, rhsTableRef, type, joinExpressions, hashExpressions);
return HashJoinPlan.create(joinTable.getStatement(), rhsPlan, joinInfo, new HashSubPlan[] {new HashSubPlan(0, lhsPlan, hashExpressions, keyRangeExpressions.getFirst(), keyRangeExpressions.getSecond(), clientProjector, lhsJoin.hasFilters())});
}
@@ -300,16 +303,17 @@ public class QueryCompiler {
throw new SQLFeatureNotSupportedException("Joins with pattern 'A right join B left join C' not supported.");
}
- private Pair<Expression, Expression> getKeyExpressionCombinations(StatementContext context, TableRef table, JoinType type, final List<Expression> joinExpressions, final List<Expression> hashExpressions) throws SQLException {
- if (type != JoinType.Inner)
- return new Pair<Expression, Expression>(null, null);
+ private boolean getKeyExpressionCombinations(Pair<Expression, Expression> combination, StatementContext context, TableRef table, JoinType type, final List<Expression> joinExpressions, final List<Expression> hashExpressions) throws SQLException {
+ if (type != JoinType.Inner && type != JoinType.Semi)
+ return false;
Scan scanCopy = ScanUtil.newScan(context.getScan());
StatementContext contextCopy = new StatementContext(statement, context.getResolver(), scanCopy, new SequenceManager(statement));
contextCopy.setCurrentTable(table);
- List<Expression> lhsCombination = WhereOptimizer.getKeyExpressionCombination(contextCopy, this.select, joinExpressions);
+ List<Expression> lhsCombination = Lists.<Expression> newArrayList();
+ boolean complete = WhereOptimizer.getKeyExpressionCombination(lhsCombination, contextCopy, this.select, joinExpressions);
if (lhsCombination.isEmpty())
- return new Pair<Expression, Expression>(null, null);
+ return false;
List<Expression> rhsCombination = Lists.newArrayListWithExpectedSize(lhsCombination.size());
for (int i = 0; i < lhsCombination.size(); i++) {
@@ -322,15 +326,26 @@ public class QueryCompiler {
}
}
- if (lhsCombination.size() == 1)
- return new Pair<Expression, Expression>(lhsCombination.get(0), rhsCombination.get(0));
+ if (lhsCombination.size() == 1) {
+ combination.setFirst(lhsCombination.get(0));
+ combination.setSecond(rhsCombination.get(0));
+ } else {
+ combination.setFirst(new RowValueConstructorExpression(lhsCombination, false));
+ combination.setSecond(new RowValueConstructorExpression(rhsCombination, false));
+ }
- return new Pair<Expression, Expression>(new RowValueConstructorExpression(lhsCombination, false), new RowValueConstructorExpression(rhsCombination, false));
+ return type == JoinType.Semi && complete;
}
protected QueryPlan compileSubquery(SelectStatement subquery) throws SQLException {
+ subquery = SubselectRewriter.flatten(subquery, this.statement.getConnection());
ColumnResolver resolver = FromCompiler.getResolverForQuery(subquery, this.statement.getConnection());
subquery = StatementNormalizer.normalize(subquery, resolver);
+ SelectStatement transformedSubquery = SubqueryRewriter.transform(subquery, resolver, this.statement.getConnection());
+ if (transformedSubquery != subquery) {
+ resolver = FromCompiler.getResolverForQuery(transformedSubquery, this.statement.getConnection());
+ subquery = StatementNormalizer.normalize(transformedSubquery, resolver);
+ }
QueryPlan plan = new QueryCompiler(this.statement, subquery, resolver).compile();
return statement.getConnection().getQueryServices().getOptimizer().optimize(statement, plan);
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/909d9759/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
index 698756b..acab605 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
@@ -41,6 +41,7 @@ import org.apache.phoenix.parse.TableNode;
import org.apache.phoenix.parse.TableNodeVisitor;
import org.apache.phoenix.parse.TableWildcardParseNode;
import org.apache.phoenix.parse.WildcardParseNode;
+import org.apache.phoenix.parse.JoinTableNode.JoinType;
import org.apache.phoenix.util.SchemaUtil;
import com.google.common.collect.Lists;
@@ -121,7 +122,7 @@ public class StatementNormalizer extends ParseNodeRewriter {
@Override
public List<TableName> visit(JoinTableNode joinNode) throws SQLException {
List<TableName> lhs = joinNode.getLHS().accept(this);
- List<TableName> rhs = joinNode.getRHS().accept(this);
+ List<TableName> rhs = joinNode.getType() == JoinType.Semi || joinNode.getType() == JoinType.Anti ? Collections.<TableName> emptyList() : joinNode.getRHS().accept(this);
List<TableName> ret = Lists.<TableName>newArrayListWithExpectedSize(lhs.size() + rhs.size());
ret.addAll(lhs);
ret.addAll(rhs);