You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ch...@apache.org on 2020/12/03 03:42:12 UTC
[phoenix] branch 4.x updated: PHOENIX-6232 Correlated subquery
should not push to RegionServer as the probe side of the Hash join
This is an automated email from the ASF dual-hosted git repository.
chenglei pushed a commit to branch 4.x
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/4.x by this push:
new 5e70f76 PHOENIX-6232 Correlated subquery should not push to RegionServer as the probe side of the Hash join
5e70f76 is described below
commit 5e70f76bb59ff08eff699f3cbfc532c4f8d86667
Author: chenglei <ch...@apache.org>
AuthorDate: Thu Dec 3 11:41:12 2020 +0800
PHOENIX-6232 Correlated subquery should not push to RegionServer as the probe side of the Hash join
---
.../phoenix/end2end/join/HashJoinMoreIT.java | 104 +++++++++
.../org/apache/phoenix/compile/JoinCompiler.java | 249 +++++++++++++++------
.../org/apache/phoenix/compile/QueryCompiler.java | 32 +--
.../apache/phoenix/compile/SubselectRewriter.java | 7 +-
.../org/apache/phoenix/jdbc/PhoenixStatement.java | 38 ++--
.../apache/phoenix/optimize/QueryOptimizer.java | 19 +-
.../org/apache/phoenix/util/ParseNodeUtil.java | 45 ++++
.../phoenix/compile/JoinQueryCompilerTest.java | 48 ++--
.../apache/phoenix/compile/QueryCompilerTest.java | 193 +++++++++++++++-
9 files changed, 588 insertions(+), 147 deletions(-)
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinMoreIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinMoreIT.java
index 3a1b015..f0f411f 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinMoreIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinMoreIT.java
@@ -21,18 +21,23 @@ 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 static org.junit.Assert.fail;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
+import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.util.Properties;
+import org.apache.phoenix.compile.QueryPlan;
import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.execute.HashJoinPlan;
import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.TestUtil;
import org.junit.Test;
public class HashJoinMoreIT extends ParallelStatsDisabledIT {
@@ -912,4 +917,103 @@ public class HashJoinMoreIT extends ParallelStatsDisabledIT {
conn.close();
}
}
+
+ @Test
+ public void testHashJoinBug6232() throws Exception {
+ Connection conn = null;
+ try {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ conn = DriverManager.getConnection(getUrl(), props);
+
+ String tableName1 = generateUniqueName();
+ String tableName2 = generateUniqueName();
+
+ String sql="CREATE TABLE IF NOT EXISTS "+tableName1+" ( "+
+ "AID INTEGER PRIMARY KEY,"+
+ "AGE INTEGER"+
+ ")";
+ conn.createStatement().execute(sql);
+
+ conn.createStatement().execute("UPSERT INTO "+tableName1+"(AID,AGE) VALUES (1,11)");
+ conn.createStatement().execute("UPSERT INTO "+tableName1+"(AID,AGE) VALUES (2,22)");
+ conn.createStatement().execute("UPSERT INTO "+tableName1+"(AID,AGE) VALUES (3,33)");
+ conn.commit();
+
+ sql="CREATE TABLE IF NOT EXISTS "+tableName2+" ( "+
+ "BID INTEGER PRIMARY KEY,"+
+ "CODE INTEGER"+
+ ")";
+ conn.createStatement().execute(sql);
+
+ conn.createStatement().execute("UPSERT INTO "+tableName2+"(BID,CODE) VALUES (1,66)");
+ conn.createStatement().execute("UPSERT INTO "+tableName2+"(BID,CODE) VALUES (2,11)");
+ conn.createStatement().execute("UPSERT INTO "+tableName2+"(BID,CODE) VALUES (3,22)");
+ conn.commit();
+
+ sql="select a.aid from " + tableName1 + " a inner join "+
+ "(select bid,code from " + tableName2 + " where code > 10 limit 3) b on a.aid = b.bid "+
+ "where a.age > (select code from " + tableName2 + " c where c.bid = 2) order by a.aid";
+ ResultSet rs=conn.prepareStatement(sql).executeQuery();
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 2);
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 3);
+ assertTrue(!rs.next());
+
+ sql = "select a.aid from (select aid,age from " + tableName1 + " where age >=11 and age<=33) a inner join "+
+ "(select bid,code from " + tableName2 + " where code > 10 limit 3) b on a.aid = b.bid "+
+ "where a.age > (select code from " + tableName2 + " c where c.bid = 2) order by a.aid";
+ rs = conn.prepareStatement(sql).executeQuery();
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 2);
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 3);
+ assertTrue(!rs.next());
+
+ sql = "select a.aid from (select aid,age from " + tableName1 + " where age >=11 and age<=33) a inner join "+
+ "(select bid,code from " + tableName2 + " where code > 10 limit 3) b on a.aid = b.bid "+
+ "where a.age > (select max(code) from " + tableName2 + " c where c.bid >= 1) order by a.aid";
+ rs = conn.prepareStatement(sql).executeQuery();
+ assertTrue(!rs.next());
+
+ sql = "select a.aid from (select aid,age from " + tableName1 + " where age >=11 and age<=33) a inner join "+
+ "(select bid,code from " + tableName2 + " where code > 10 limit 3) b on a.aid = b.bid "+
+ "where a.age > (select max(code) from " + tableName2 + " c where c.bid = a.aid) order by a.aid";
+ rs = conn.prepareStatement(sql).executeQuery();
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 2);
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 3);
+ assertTrue(!rs.next());
+
+ String tableName3 = generateUniqueName();
+ sql ="CREATE TABLE " + tableName3 + " (" +
+ " id INTEGER NOT NULL," +
+ " test_id INTEGER," +
+ " lastchanged VARCHAR," +
+ " CONSTRAINT my_pk PRIMARY KEY (id))";
+ conn.createStatement().execute(sql);
+ conn.createStatement().execute("UPSERT INTO " + tableName3 + "(id,test_id,lastchanged) VALUES (0,100,'2000-01-01 00:00:00')");
+ conn.createStatement().execute("UPSERT INTO " + tableName3 + "(id,test_id,lastchanged) VALUES (1,101,'2000-01-01 00:00:00')");
+ conn.createStatement().execute("UPSERT INTO " + tableName3 + "(id,test_id,lastchanged) VALUES (2,100,'2011-11-11 11:11:11')");
+ conn.commit();
+
+ sql= "SELECT AAA.* FROM " +
+ "(SELECT id, test_id, lastchanged FROM " + tableName3 + " T " +
+ " WHERE lastchanged = ( SELECT max(lastchanged) FROM " + tableName3 + " WHERE test_id = T.test_id )) AAA " +
+ "inner join " +
+ "(SELECT id FROM " + tableName3 + ") BBB " +
+ "on AAA.id = BBB.id order by AAA.id";
+ rs = conn.prepareStatement(sql).executeQuery();
+ TestUtil.assertResultSet(
+ rs,
+ new Object[][] {
+ {1,101,"2000-01-01 00:00:00"},
+ {2,100,"2011-11-11 11:11:11"}});
+ } finally {
+ if(conn!=null) {
+ conn.close();
+ }
+ }
+ }
}
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 17c99a8..46bf2a2 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
@@ -38,6 +38,7 @@ import com.google.common.collect.ImmutableList;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.util.Pair;
+import org.apache.phoenix.coprocessor.HashJoinRegionScanner;
import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.exception.SQLExceptionInfo;
import org.apache.phoenix.expression.AndExpression;
@@ -100,6 +101,7 @@ import org.apache.phoenix.schema.types.PVarchar;
import org.apache.phoenix.util.EncodedColumnsUtil;
import org.apache.phoenix.util.IndexUtil;
import org.apache.phoenix.util.ParseNodeUtil;
+import org.apache.phoenix.util.ParseNodeUtil.RewriteResult;
import org.apache.phoenix.util.SchemaUtil;
import com.google.common.base.Preconditions;
@@ -120,8 +122,8 @@ public class JoinCompiler {
GENERAL,
}
- private final PhoenixStatement statement;
- private final SelectStatement select;
+ private final PhoenixStatement phoenixStatement;
+ private final SelectStatement originalJoinSelectStatement;
private final ColumnResolver origResolver;
private final boolean useStarJoin;
private final Map<ColumnRef, ColumnRefType> columnRefs;
@@ -129,8 +131,8 @@ public class JoinCompiler {
private final boolean useSortMergeJoin;
private JoinCompiler(PhoenixStatement statement, SelectStatement select, ColumnResolver resolver) {
- this.statement = statement;
- this.select = select;
+ this.phoenixStatement = statement;
+ this.originalJoinSelectStatement = select;
this.origResolver = resolver;
this.useStarJoin = !select.getHint().hasHint(Hint.NO_STAR_JOIN);
this.columnRefs = new HashMap<ColumnRef, ColumnRefType>();
@@ -196,7 +198,7 @@ public class JoinCompiler {
@Override
public Pair<Table, List<JoinSpec>> visit(BindTableNode boundTableNode) throws SQLException {
TableRef tableRef = resolveTable(boundTableNode.getAlias(), boundTableNode.getName());
- boolean isWildCard = isWildCardSelectForTable(select.getSelect(), tableRef, origResolver);
+ boolean isWildCard = isWildCardSelectForTable(originalJoinSelectStatement.getSelect(), tableRef, origResolver);
Table table = new Table(boundTableNode, isWildCard, Collections.<ColumnDef>emptyList(), boundTableNode.getTableSamplingRate(), tableRef);
return new Pair<Table, List<JoinSpec>>(table, null);
}
@@ -219,7 +221,7 @@ public class JoinCompiler {
public Pair<Table, List<JoinSpec>> visit(NamedTableNode namedTableNode)
throws SQLException {
TableRef tableRef = resolveTable(namedTableNode.getAlias(), namedTableNode.getName());
- boolean isWildCard = isWildCardSelectForTable(select.getSelect(), tableRef, origResolver);
+ boolean isWildCard = isWildCardSelectForTable(originalJoinSelectStatement.getSelect(), tableRef, origResolver);
Table table = new Table(namedTableNode, isWildCard, namedTableNode.getDynamicColumns(), namedTableNode.getTableSamplingRate(), tableRef);
return new Pair<Table, List<JoinSpec>>(table, null);
}
@@ -228,7 +230,7 @@ public class JoinCompiler {
public Pair<Table, List<JoinSpec>> visit(DerivedTableNode subselectNode)
throws SQLException {
TableRef tableRef = resolveTable(subselectNode.getAlias(), null);
- boolean isWildCard = isWildCardSelectForTable(select.getSelect(), tableRef, origResolver);
+ boolean isWildCard = isWildCardSelectForTable(originalJoinSelectStatement.getSelect(), tableRef, origResolver);
Table table = new Table(subselectNode, isWildCard, tableRef);
return new Pair<Table, List<JoinSpec>>(table, null);
}
@@ -313,7 +315,7 @@ public class JoinCompiler {
}
public SelectStatement getStatement() {
- return select;
+ return originalJoinSelectStatement;
}
public ColumnResolver getOriginalResolver() {
@@ -361,14 +363,14 @@ public class JoinCompiler {
WhereNodeVisitor visitor = new WhereNodeVisitor(
origResolver,
this,
- statement.getConnection());
+ phoenixStatement.getConnection());
filter.accept(visitor);
}
public void pushDownColumnRefVisitors(
ColumnRefParseNodeVisitor generalRefVisitor,
ColumnRefParseNodeVisitor joinLocalRefVisitor) throws SQLException {
- for (ParseNode node : leftTable.getPostFilters()) {
+ for (ParseNode node : leftTable.getPostFilterParseNodes()) {
node.accept(generalRefVisitor);
}
for (ParseNode node : postFilters) {
@@ -421,8 +423,9 @@ public class JoinCompiler {
* or a flat sub-query,
* add BUILD_LEFT to the returned list.
* 4. add SORT_MERGE to the returned list.
+ * @throws SQLException
*/
- public List<Strategy> getApplicableJoinStrategies() {
+ public List<Strategy> getApplicableJoinStrategies() throws SQLException {
List<Strategy> strategies = Lists.newArrayList();
if (useSortMergeJoin) {
strategies.add(Strategy.SORT_MERGE);
@@ -434,7 +437,7 @@ public class JoinCompiler {
JoinType type = lastJoinSpec.getType();
if ((type == JoinType.Right || type == JoinType.Inner)
&& lastJoinSpec.getRhsJoinTable().getJoinSpecs().isEmpty()
- && lastJoinSpec.getRhsJoinTable().getLeftTable().isFlat()) {
+ && lastJoinSpec.getRhsJoinTable().getLeftTable().isCouldPushToServerAsHashJoinProbeSide()) {
strategies.add(Strategy.HASH_BUILD_LEFT);
}
strategies.add(Strategy.SORT_MERGE);
@@ -448,10 +451,11 @@ public class JoinCompiler {
* can be evaluated at an early stage if the input JoinSpec can be taken as a
* star join. Otherwise returns null.
* @return a boolean vector for a star join; or null for non star join.
+ * @throws SQLException
*/
- public boolean[] getStarJoinVector() {
+ public boolean[] getStarJoinVector() throws SQLException {
int count = joinSpecs.size();
- if (!leftTable.isFlat() ||
+ if (!leftTable.isCouldPushToServerAsHashJoinProbeSide() ||
(!useStarJoin
&& count > 1
&& joinSpecs.get(count - 1).getType() != JoinType.Left
@@ -530,12 +534,12 @@ public class JoinCompiler {
}
public SelectStatement getAsSingleSubquery(SelectStatement query, boolean asSubquery) throws SQLException {
- assert (isFlat(query));
+ assert (isCouldPushToServerAsHashJoinProbeSide(query));
if (asSubquery)
return query;
- return NODE_FACTORY.select(select, query.getFrom(), query.getWhere());
+ return NODE_FACTORY.select(originalJoinSelectStatement, query.getFrom(), query.getWhere());
}
public boolean hasPostReference() {
@@ -586,7 +590,7 @@ public class JoinCompiler {
this.rhsJoinTable = joinTable;
this.singleValueOnly = singleValueOnly;
this.dependentTableRefs = new HashSet<TableRef>();
- this.onNodeVisitor = new OnNodeVisitor(resolver, this, statement.getConnection());
+ this.onNodeVisitor = new OnNodeVisitor(resolver, this, phoenixStatement.getConnection());
if (onNode != null) {
this.pushDownOnCondition(onNode);
}
@@ -768,10 +772,27 @@ public class JoinCompiler {
private final boolean isWildcard;
private final List<ColumnDef> dynamicColumns;
private final Double tableSamplingRate;
- private SelectStatement subselect;
+ private SelectStatement subselectStatement;
private TableRef tableRef;
- private final List<ParseNode> preFilters;
- private final List<ParseNode> postFilters;
+ /**
+ * Which could as this {@link Table}'s where conditions.
+ * Note: for {@link #isSubselect()}, added preFilterParseNode
+ * is at first rewritten by
+ * {@link SubselectRewriter#rewritePreFilterForSubselect}.
+ */
+ private final List<ParseNode> preFilterParseNodes;
+ /**
+ * Only make sense for {@link #isSubselect()}.
+ * {@link #postFilterParseNodes} could not as this
+ * {@link Table}'s where conditions, but need to filter after
+ * {@link #getSelectStatementByApplyPreFiltersIfSubselect()}
+ * is executed.
+ */
+ private final List<ParseNode> postFilterParseNodes;
+ /**
+ * Determined by {@link SubselectRewriter#isFilterCanPushDownToSelect}.
+ * Only make sense for {@link #isSubselect()},
+ */
private final boolean filterCanPushDownToSubselect;
private Table(TableNode tableNode, boolean isWildcard, List<ColumnDef> dynamicColumns,
@@ -780,10 +801,10 @@ public class JoinCompiler {
this.isWildcard = isWildcard;
this.dynamicColumns = dynamicColumns;
this.tableSamplingRate=tableSamplingRate;
- this.subselect = null;
+ this.subselectStatement = null;
this.tableRef = tableRef;
- this.preFilters = new ArrayList<ParseNode>();
- this.postFilters = Collections.<ParseNode>emptyList();
+ this.preFilterParseNodes = new ArrayList<ParseNode>();
+ this.postFilterParseNodes = Collections.<ParseNode>emptyList();
this.filterCanPushDownToSubselect = false;
}
@@ -792,11 +813,11 @@ public class JoinCompiler {
this.isWildcard = isWildcard;
this.dynamicColumns = Collections.<ColumnDef>emptyList();
this.tableSamplingRate=ConcreteTableNode.DEFAULT_TABLE_SAMPLING_RATE;
- this.subselect = SubselectRewriter.flatten(tableNode.getSelect(), statement.getConnection());
+ this.subselectStatement = SubselectRewriter.flatten(tableNode.getSelect(), phoenixStatement.getConnection());
this.tableRef = tableRef;
- this.preFilters = new ArrayList<ParseNode>();
- this.postFilters = new ArrayList<ParseNode>();
- this.filterCanPushDownToSubselect = SubselectRewriter.isFilterCanPushDownToSelect(subselect);
+ this.preFilterParseNodes = new ArrayList<ParseNode>();
+ this.postFilterParseNodes = new ArrayList<ParseNode>();
+ this.filterCanPushDownToSubselect = SubselectRewriter.isFilterCanPushDownToSelect(subselectStatement);
}
public TableNode getTableNode() {
@@ -812,11 +833,11 @@ public class JoinCompiler {
}
public boolean isSubselect() {
- return subselect != null;
+ return subselectStatement != null;
}
- public SelectStatement getSubselect() {
- return this.subselect;
+ public SelectStatement getSubselectStatement() {
+ return this.subselectStatement;
}
/**
@@ -831,10 +852,10 @@ public class JoinCompiler {
Set<String> referencedColumnNames = this.getReferencedColumnNames();
SelectStatement newSubselectStatement =
SubselectRewriter.pruneSelectAliasedNodes(
- this.subselect,
+ this.subselectStatement,
referencedColumnNames,
- statement.getConnection());
- if(!newSubselectStatement.getSelect().equals(this.subselect.getSelect())) {
+ phoenixStatement.getConnection());
+ if(!newSubselectStatement.getSelect().equals(this.subselectStatement.getSelect())) {
/**
* The columns are pruned, so {@link ColumnResolver} should be refreshed.
*/
@@ -843,7 +864,7 @@ public class JoinCompiler {
TableRef newTableRef =
FromCompiler.refreshDerivedTableNode(origResolver, newDerivedTableNode);
assert newTableRef != null;
- this.subselect = newSubselectStatement;
+ this.subselectStatement = newSubselectStatement;
this.tableRef = newTableRef;
this.tableNode = newDerivedTableNode;
}
@@ -874,7 +895,7 @@ public class JoinCompiler {
/**
* Returns all the basic select nodes, no aggregation.
*/
- public List<AliasedNode> getSelectNodes() {
+ public List<AliasedNode> getSelectAliasedNodes() {
if (isWildCardSelect()) {
return Collections.singletonList(NODE_FACTORY.aliasedNode(null, NODE_FACTORY.wildcard()));
}
@@ -891,12 +912,12 @@ public class JoinCompiler {
return ret;
}
- public List<ParseNode> getPreFilters() {
- return preFilters;
+ public List<ParseNode> getPreFilterParseNodes() {
+ return preFilterParseNodes;
}
- public List<ParseNode> getPostFilters() {
- return postFilters;
+ public List<ParseNode> getPostFilterParseNodes() {
+ return postFilterParseNodes;
}
public TableRef getTableRef() {
@@ -907,7 +928,7 @@ public class JoinCompiler {
if (!isSubselect() || filterCanPushDownToSubselect) {
this.addPreFilter(filter);
} else {
- postFilters.add(filter);
+ postFilterParseNodes.add(filter);
}
}
@@ -922,29 +943,34 @@ public class JoinCompiler {
preFilterParseNode =
SubselectRewriter.rewritePreFilterForSubselect(
preFilterParseNode,
- this.subselect,
+ this.subselectStatement,
tableNode.getAlias());
}
- preFilters.add(preFilterParseNode);
+ preFilterParseNodes.add(preFilterParseNode);
}
- public ParseNode getPreFiltersCombined() {
- return combine(preFilters);
+ public ParseNode getCombinedPreFilterParseNodes() {
+ return combine(preFilterParseNodes);
}
- public SelectStatement getAsSubquery(List<OrderByNode> orderBy) throws SQLException {
+ /**
+ * Get this {@link Table}'s new {@link SelectStatement} by applying {@link #preFilterParseNodes},
+ * {@link #postFilterParseNodes} and additional newOrderByNodes.
+ * @param newOrderByNodes
+ * @return
+ * @throws SQLException
+ */
+ public SelectStatement getAsSubquery(List<OrderByNode> newOrderByNodes) throws SQLException {
if (isSubselect()) {
return SubselectRewriter.applyOrderByAndPostFilters(
- SubselectRewriter.applyPreFiltersForSubselect(subselect, preFilters, tableNode.getAlias()),
- orderBy,
+ this.getSelectStatementByApplyPreFiltersIfSubselect(),
+ newOrderByNodes,
tableNode.getAlias(),
- postFilters);
+ postFilterParseNodes);
}
//for flat table, postFilters is empty , because it can safely pushed down as preFilters.
- assert postFilters == null || postFilters.isEmpty();
- return NODE_FACTORY.select(tableNode, select.getHint(), false, getSelectNodes(), getPreFiltersCombined(), null,
- null, orderBy, null, null, 0, false, select.hasSequence(),
- Collections.<SelectStatement> emptyList(), select.getUdfParseNodes());
+ assert postFilterParseNodes == null || postFilterParseNodes.isEmpty();
+ return this.getSelectStatementByApplyPreFiltersIfNotSubselect(newOrderByNodes);
}
public SelectStatement getAsSubqueryForOptimization(boolean applyGroupByOrOrderBy) throws SQLException {
@@ -956,18 +982,18 @@ public class JoinCompiler {
boolean addGroupBy = false;
boolean addOrderBy = false;
- if (select.getGroupBy() != null && !select.getGroupBy().isEmpty()) {
- ColumnRefParseNodeVisitor groupByVisitor = new ColumnRefParseNodeVisitor(origResolver, statement.getConnection());
- for (ParseNode node : select.getGroupBy()) {
+ if (originalJoinSelectStatement.getGroupBy() != null && !originalJoinSelectStatement.getGroupBy().isEmpty()) {
+ ColumnRefParseNodeVisitor groupByVisitor = new ColumnRefParseNodeVisitor(origResolver, phoenixStatement.getConnection());
+ for (ParseNode node : originalJoinSelectStatement.getGroupBy()) {
node.accept(groupByVisitor);
}
Set<TableRef> set = groupByVisitor.getTableRefSet();
if (set.size() == 1 && tableRef.equals(set.iterator().next())) {
addGroupBy = true;
}
- } else if (select.getOrderBy() != null && !select.getOrderBy().isEmpty()) {
- ColumnRefParseNodeVisitor orderByVisitor = new ColumnRefParseNodeVisitor(origResolver, statement.getConnection());
- for (OrderByNode node : select.getOrderBy()) {
+ } else if (originalJoinSelectStatement.getOrderBy() != null && !originalJoinSelectStatement.getOrderBy().isEmpty()) {
+ ColumnRefParseNodeVisitor orderByVisitor = new ColumnRefParseNodeVisitor(origResolver, phoenixStatement.getConnection());
+ for (OrderByNode node : originalJoinSelectStatement.getOrderBy()) {
node.getNode().accept(orderByVisitor);
}
Set<TableRef> set = orderByVisitor.getTableRefSet();
@@ -991,18 +1017,86 @@ public class JoinCompiler {
}
return NODE_FACTORY.select(query.getFrom(), query.getHint(), query.isDistinct(), selectList,
- query.getWhere(), addGroupBy ? select.getGroupBy() : query.getGroupBy(),
- addGroupBy ? null : query.getHaving(), addOrderBy ? select.getOrderBy() : query.getOrderBy(),
+ query.getWhere(), addGroupBy ? originalJoinSelectStatement.getGroupBy() : query.getGroupBy(),
+ addGroupBy ? null : query.getHaving(), addOrderBy ? originalJoinSelectStatement.getOrderBy() : query.getOrderBy(),
query.getLimit(), query.getOffset(), query.getBindCount(), addGroupBy, query.hasSequence(),
query.getSelects(), query.getUdfParseNodes());
}
public boolean hasFilters() {
- return isSubselect() ? (!postFilters.isEmpty() || subselect.getWhere() != null || subselect.getHaving() != null) : !preFilters.isEmpty();
+ return isSubselect() ?
+ (!postFilterParseNodes.isEmpty() || subselectStatement.getWhere() != null || subselectStatement.getHaving() != null) :
+ !preFilterParseNodes.isEmpty();
+ }
+
+ /**
+ * Check if this {@link Table} could be pushed to RegionServer
+ * {@link HashJoinRegionScanner} as the probe side of Hash join.
+ * @return
+ * @throws SQLException
+ */
+ public boolean isCouldPushToServerAsHashJoinProbeSide() throws SQLException {
+ /**
+ * If {@link #postFilterParseNodes} is not empty, obviously this {@link Table}
+ * should execute {@link #postFilterParseNodes} before join.
+ */
+ if(this.postFilterParseNodes != null && !this.postFilterParseNodes.isEmpty()) {
+ return false;
+ }
+
+ SelectStatement selectStatementToUse =
+ this.getSelectStatementByApplyPreFilters();
+ RewriteResult rewriteResult =
+ ParseNodeUtil.rewrite(selectStatementToUse, phoenixStatement.getConnection());
+ return JoinCompiler.isCouldPushToServerAsHashJoinProbeSide(rewriteResult.getRewrittenSelectStatement());
}
- public boolean isFlat() {
- return subselect == null || JoinCompiler.isFlat(subselect);
+ /**
+ * Get this {@link Table}'s new {@link SelectStatement} only applying
+ * {@link #preFilterParseNodes}.
+ * @return
+ */
+ private SelectStatement getSelectStatementByApplyPreFilters() {
+ return this.isSubselect() ?
+ this.getSelectStatementByApplyPreFiltersIfSubselect() :
+ this.getSelectStatementByApplyPreFiltersIfNotSubselect(null);
+ }
+
+ /**
+ * Get this {@link Table}'s new {@link SelectStatement} only applying
+ * {@link #preFilterParseNodes} for {@link #isSubselect()}.
+ * @return
+ */
+ private SelectStatement getSelectStatementByApplyPreFiltersIfSubselect() {
+ return SubselectRewriter.applyPreFiltersForSubselect(
+ subselectStatement,
+ preFilterParseNodes,
+ tableNode.getAlias());
+
+ }
+
+ /**
+ * Get this {@link Table}'s new {@link SelectStatement} only applying
+ * {@link #preFilterParseNodes} if not {@link #isSubselect()}.
+ * @return
+ */
+ private SelectStatement getSelectStatementByApplyPreFiltersIfNotSubselect(List<OrderByNode> newOrderByNodes) {
+ return NODE_FACTORY.select(
+ tableNode,
+ originalJoinSelectStatement.getHint(),
+ false,
+ getSelectAliasedNodes(),
+ getCombinedPreFilterParseNodes(),
+ null,
+ null,
+ newOrderByNodes,
+ null,
+ null,
+ 0,
+ false,
+ originalJoinSelectStatement.hasSequence(),
+ Collections.<SelectStatement> emptyList(),
+ originalJoinSelectStatement.getUdfParseNodes());
}
protected boolean isWildCardSelect() {
@@ -1058,7 +1152,7 @@ public class JoinCompiler {
public PTable createProjectedTable(RowProjector rowProjector) throws SQLException {
assert(isSubselect());
- TableRef tableRef = FromCompiler.getResolverForCompiledDerivedTable(statement.getConnection(), this.tableRef, rowProjector).getTables().get(0);
+ TableRef tableRef = FromCompiler.getResolverForCompiledDerivedTable(phoenixStatement.getConnection(), this.tableRef, rowProjector).getTables().get(0);
List<ColumnRef> sourceColumns = new ArrayList<ColumnRef>();
PTable table = tableRef.getTable();
for (PColumn column : table.getColumns()) {
@@ -1373,13 +1467,24 @@ public class JoinCompiler {
// for creation of new statements
private static final ParseNodeFactory NODE_FACTORY = new ParseNodeFactory();
- private static boolean isFlat(SelectStatement select) {
- return !select.isJoin()
- && !select.isAggregate()
- && !select.isDistinct()
- && !(select.getFrom() instanceof DerivedTableNode)
- && select.getLimit() == null
- && select.getOffset() == null;
+ /**
+ * Check if this {@link Table} could be pushed to RegionServer
+ * {@link HashJoinRegionScanner} as the probe side of Hash join.
+ * Note: the {@link SelectStatement} parameter should be rewritten by
+ * {@link ParseNodeUtil#rewrite} before this method.
+ * {@link SelectStatement} parameter could has NonCorrelated subquery,
+ * but for Correlated subquery, {@link ParseNodeUtil#rewrite} rewrite
+ * it as join.
+ * @param selectStatement
+ * @return
+ */
+ private static boolean isCouldPushToServerAsHashJoinProbeSide(SelectStatement selectStatement) {
+ return !selectStatement.isJoin()
+ && !selectStatement.isAggregate()
+ && !selectStatement.isDistinct()
+ && !(selectStatement.getFrom() instanceof DerivedTableNode)
+ && selectStatement.getLimit() == null
+ && selectStatement.getOffset() == null;
}
private static ParseNode combine(List<ParseNode> nodes) {
@@ -1393,7 +1498,7 @@ public class JoinCompiler {
}
private boolean isWildCardSelectForTable(List<AliasedNode> select, TableRef tableRef, ColumnResolver resolver) throws SQLException {
- ColumnRefParseNodeVisitor visitor = new ColumnRefParseNodeVisitor(resolver, statement.getConnection());
+ ColumnRefParseNodeVisitor visitor = new ColumnRefParseNodeVisitor(resolver, phoenixStatement.getConnection());
for (AliasedNode aliasedNode : select) {
ParseNode node = aliasedNode.getNode();
if (node instanceof TableWildcardParseNode) {
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 58565de..ae0deb3 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
@@ -82,6 +82,8 @@ import org.apache.phoenix.schema.RowValueConstructorOffsetNotCoercibleException;
import org.apache.phoenix.schema.TableNotFoundException;
import org.apache.phoenix.schema.TableRef;
import org.apache.phoenix.util.EnvironmentEdgeManager;
+import org.apache.phoenix.util.ParseNodeUtil;
+import org.apache.phoenix.util.ParseNodeUtil.RewriteResult;
import org.apache.phoenix.util.QueryUtil;
import org.apache.phoenix.util.ScanUtil;
@@ -589,24 +591,28 @@ public class QueryCompiler {
return type == JoinType.Semi && complete;
}
- protected QueryPlan compileSubquery(SelectStatement subquery, boolean pushDownMaxRows) throws SQLException {
- PhoenixConnection connection = this.statement.getConnection();
- subquery = SubselectRewriter.flatten(subquery, connection);
- ColumnResolver resolver = FromCompiler.getResolverForQuery(subquery, connection);
- subquery = StatementNormalizer.normalize(subquery, resolver);
- SelectStatement transformedSubquery = SubqueryRewriter.transform(subquery, resolver, connection);
- if (transformedSubquery != subquery) {
- resolver = FromCompiler.getResolverForQuery(transformedSubquery, connection);
- subquery = StatementNormalizer.normalize(transformedSubquery, resolver);
- }
+ protected QueryPlan compileSubquery(
+ SelectStatement subquerySelectStatement,
+ boolean pushDownMaxRows) throws SQLException {
+ PhoenixConnection phoenixConnection = this.statement.getConnection();
+ RewriteResult rewriteResult =
+ ParseNodeUtil.rewrite(subquerySelectStatement, phoenixConnection);
int maxRows = this.statement.getMaxRows();
this.statement.setMaxRows(pushDownMaxRows ? maxRows : 0); // overwrite maxRows to avoid its impact on inner queries.
- QueryPlan plan = new QueryCompiler(this.statement, subquery, resolver, false, optimizeSubquery, null).compile();
+ QueryPlan queryPlan = new QueryCompiler(
+ this.statement,
+ rewriteResult.getRewrittenSelectStatement(),
+ rewriteResult.getColumnResolver(),
+ false,
+ optimizeSubquery,
+ null).compile();
if (optimizeSubquery) {
- plan = statement.getConnection().getQueryServices().getOptimizer().optimize(statement, plan);
+ queryPlan = statement.getConnection().getQueryServices().getOptimizer().optimize(
+ statement,
+ queryPlan);
}
this.statement.setMaxRows(maxRows); // restore maxRows.
- return plan;
+ return queryPlan;
}
protected QueryPlan compileSingleQuery(StatementContext context, SelectStatement select, List<Object> binds, boolean asSubquery, boolean allowPageFilter) throws SQLException{
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java
index 385df50..a5d501e 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java
@@ -73,7 +73,7 @@ public class SubselectRewriter extends ParseNodeRewriter {
public static SelectStatement applyPreFiltersForSubselect(
SelectStatement subselectStatement,
List<ParseNode> preFilterParseNodes,
- String subselectAlias) throws SQLException {
+ String subselectAlias) {
if (preFilterParseNodes.isEmpty()) {
return subselectStatement;
@@ -113,8 +113,9 @@ public class SubselectRewriter extends ParseNodeRewriter {
* @return
* @throws SQLException
*/
- public static boolean isFilterCanPushDownToSelect(SelectStatement statement) throws SQLException {
- return statement.getLimit() == null && (!statement.isAggregate() || !statement.getGroupBy().isEmpty());
+ public static boolean isFilterCanPushDownToSelect(SelectStatement statement) {
+ return statement.getLimit() == null &&
+ (!statement.isAggregate() || !statement.getGroupBy().isEmpty());
}
/**
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 a9d5eb0..3acff61 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
@@ -63,7 +63,6 @@ import org.apache.phoenix.compile.DeleteCompiler;
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.GroupByCompiler.GroupBy;
import org.apache.phoenix.compile.ListJarsQueryPlan;
import org.apache.phoenix.compile.MutationPlan;
@@ -74,10 +73,7 @@ import org.apache.phoenix.compile.QueryPlan;
import org.apache.phoenix.compile.RowProjector;
import org.apache.phoenix.compile.SequenceManager;
import org.apache.phoenix.compile.StatementContext;
-import org.apache.phoenix.compile.StatementNormalizer;
import org.apache.phoenix.compile.StatementPlan;
-import org.apache.phoenix.compile.SubqueryRewriter;
-import org.apache.phoenix.compile.SubselectRewriter;
import org.apache.phoenix.compile.TraceQueryPlan;
import org.apache.phoenix.compile.UpsertCompiler;
import org.apache.phoenix.coprocessor.MetaDataProtocol;
@@ -184,6 +180,8 @@ import org.apache.phoenix.util.CursorUtil;
import org.apache.phoenix.util.EnvironmentEdgeManager;
import org.apache.phoenix.util.KeyValueUtil;
import org.apache.phoenix.util.LogUtil;
+import org.apache.phoenix.util.ParseNodeUtil;
+import org.apache.phoenix.util.ParseNodeUtil.RewriteResult;
import org.apache.phoenix.util.PhoenixContextExecutor;
import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.QueryUtil;
@@ -482,25 +480,25 @@ public class PhoenixStatement implements Statement, SQLCloseable {
@SuppressWarnings("unchecked")
@Override
- public QueryPlan compilePlan(PhoenixStatement stmt, Sequence.ValueOp seqAction) throws SQLException {
+ public QueryPlan compilePlan(PhoenixStatement phoenixStatement, Sequence.ValueOp seqAction) throws SQLException {
if(!getUdfParseNodes().isEmpty()) {
- stmt.throwIfUnallowedUserDefinedFunctions(getUdfParseNodes());
- }
- 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);
+ phoenixStatement.throwIfUnallowedUserDefinedFunctions(getUdfParseNodes());
}
- QueryPlan plan = new QueryCompiler(stmt, select, resolver, Collections.<PDatum>emptyList(),
- stmt.getConnection().getIteratorFactory(), new SequenceManager(stmt),
- true, false, null)
- .compile();
- plan.getContext().getSequenceManager().validateSequences(seqAction);
- return plan;
+ RewriteResult rewriteResult =
+ ParseNodeUtil.rewrite(this, phoenixStatement.getConnection());
+ QueryPlan queryPlan = new QueryCompiler(
+ phoenixStatement,
+ rewriteResult.getRewrittenSelectStatement(),
+ rewriteResult.getColumnResolver(),
+ Collections.<PDatum>emptyList(),
+ phoenixStatement.getConnection().getIteratorFactory(),
+ new SequenceManager(phoenixStatement),
+ true,
+ false,
+ null).compile();
+ queryPlan.getContext().getSequenceManager().validateSequences(seqAction);
+ return queryPlan;
}
}
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
index 50c0deb..53fbf15 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
@@ -74,6 +74,8 @@ import org.apache.phoenix.schema.RowValueConstructorOffsetNotCoercibleException;
import org.apache.phoenix.schema.TableRef;
import org.apache.phoenix.schema.types.PDataType;
import org.apache.phoenix.util.IndexUtil;
+import org.apache.phoenix.util.ParseNodeUtil;
+import org.apache.phoenix.util.ParseNodeUtil.RewriteResult;
import com.google.common.collect.Lists;
@@ -400,11 +402,18 @@ public class QueryOptimizer {
}
HintNode hint = HintNode.combine(HintNode.subtract(indexSelect.getHint(), new Hint[] {Hint.INDEX, Hint.NO_CHILD_PARENT_JOIN_OPTIMIZATION}), FACTORY.hint("NO_INDEX"));
SelectStatement query = FACTORY.select(dataSelect, hint, outerWhere);
- ColumnResolver queryResolver = FromCompiler.getResolverForQuery(query, statement.getConnection());
- query = SubqueryRewriter.transform(query, queryResolver, statement.getConnection());
- queryResolver = FromCompiler.getResolverForQuery(query, statement.getConnection());
- query = StatementNormalizer.normalize(query, queryResolver);
- QueryPlan plan = new QueryCompiler(statement, query, queryResolver, targetColumns, parallelIteratorFactory, dataPlan.getContext().getSequenceManager(), isProjected, true, dataPlans).compile();
+ RewriteResult rewriteResult =
+ ParseNodeUtil.rewrite(query, statement.getConnection());
+ QueryPlan plan = new QueryCompiler(
+ statement,
+ rewriteResult.getRewrittenSelectStatement(),
+ rewriteResult.getColumnResolver(),
+ targetColumns,
+ parallelIteratorFactory,
+ dataPlan.getContext().getSequenceManager(),
+ isProjected,
+ true,
+ dataPlans).compile();
return plan;
}
}
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/ParseNodeUtil.java b/phoenix-core/src/main/java/org/apache/phoenix/util/ParseNodeUtil.java
index 1c6ca36..2e81af0 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/util/ParseNodeUtil.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/util/ParseNodeUtil.java
@@ -26,11 +26,18 @@ import org.apache.phoenix.parse.FamilyWildcardParseNode;
import org.apache.phoenix.parse.OrderByNode;
import org.apache.phoenix.parse.ParseNodeVisitor;
import org.apache.phoenix.parse.SelectStatement;
+import org.apache.phoenix.compile.ColumnResolver;
+import org.apache.phoenix.compile.FromCompiler;
+import org.apache.phoenix.compile.StatementNormalizer;
+import org.apache.phoenix.compile.SubqueryRewriter;
+import org.apache.phoenix.compile.SubselectRewriter;
+import org.apache.phoenix.jdbc.PhoenixConnection;
import org.apache.phoenix.parse.AliasedNode;
import org.apache.phoenix.parse.ParseNode;
import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor;
import org.apache.phoenix.parse.TableWildcardParseNode;
import org.apache.phoenix.parse.WildcardParseNode;
+import org.apache.phoenix.compile.QueryCompiler;
public class ParseNodeUtil {
@@ -139,4 +146,42 @@ public class ParseNodeUtil {
return null;
}
}
+
+ public static class RewriteResult {
+ private SelectStatement rewrittenSelectStatement;
+ private ColumnResolver columnResolver;
+ public RewriteResult(SelectStatement rewrittenSelectStatement, ColumnResolver columnResolver) {
+ this.rewrittenSelectStatement = rewrittenSelectStatement;
+ this.columnResolver = columnResolver;
+ }
+ public SelectStatement getRewrittenSelectStatement() {
+ return rewrittenSelectStatement;
+ }
+ public ColumnResolver getColumnResolver() {
+ return columnResolver;
+ }
+ }
+
+ /**
+ * Optimize rewriting {@link SelectStatement} by {@link SubselectRewriter} and {@link SubqueryRewriter} before
+ * {@link QueryCompiler#compile}.
+ * @param selectStatement
+ * @param phoenixConnection
+ * @return
+ * @throws SQLException
+ */
+ public static RewriteResult rewrite(SelectStatement selectStatement, PhoenixConnection phoenixConnection) throws SQLException {
+ SelectStatement selectStatementToUse =
+ SubselectRewriter.flatten(selectStatement, phoenixConnection);
+ ColumnResolver columnResolver =
+ FromCompiler.getResolverForQuery(selectStatementToUse, phoenixConnection);
+ selectStatementToUse = StatementNormalizer.normalize(selectStatementToUse, columnResolver);
+ SelectStatement transformedSubquery =
+ SubqueryRewriter.transform(selectStatementToUse, columnResolver, phoenixConnection);
+ if (transformedSubquery != selectStatementToUse) {
+ columnResolver = FromCompiler.getResolverForQuery(transformedSubquery, phoenixConnection);
+ transformedSubquery = StatementNormalizer.normalize(transformedSubquery, columnResolver);
+ }
+ return new RewriteResult(transformedSubquery, columnResolver);
+ }
}
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 1a70281..5027e54 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
@@ -115,51 +115,51 @@ public class JoinQueryCompilerTest extends BaseConnectionlessQueryTest {
String query = String.format(queryTemplate, "INNER", "INNER");
JoinTable joinTable = TestUtil.getJoinTable(query, pconn);
- assertEquals(1, joinTable.getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilters().size());
+ assertEquals(1, joinTable.getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
query = String.format(queryTemplate, "INNER", "LEFT");
joinTable = TestUtil.getJoinTable(query, pconn);
- assertEquals(1, joinTable.getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilters().size());
- assertEquals(0, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilters().size());
+ assertEquals(1, joinTable.getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(0, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
query = String.format(queryTemplate, "INNER", "RIGHT");
joinTable = TestUtil.getJoinTable(query, pconn);
- assertEquals(0, joinTable.getLeftTable().getPreFilters().size());
- assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilters().size());
+ assertEquals(0, joinTable.getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
query = String.format(queryTemplate, "LEFT", "INNER");
joinTable = TestUtil.getJoinTable(query, pconn);
- assertEquals(1, joinTable.getLeftTable().getPreFilters().size());
- assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilters().size());
+ assertEquals(1, joinTable.getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
query = String.format(queryTemplate, "LEFT", "LEFT");
joinTable = TestUtil.getJoinTable(query, pconn);
- assertEquals(1, joinTable.getLeftTable().getPreFilters().size());
- assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilters().size());
- assertEquals(0, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilters().size());
+ assertEquals(1, joinTable.getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(0, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
query = String.format(queryTemplate, "LEFT", "RIGHT");
joinTable = TestUtil.getJoinTable(query, pconn);
- assertEquals(0, joinTable.getLeftTable().getPreFilters().size());
- assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilters().size());
+ assertEquals(0, joinTable.getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
query = String.format(queryTemplate, "RIGHT", "INNER");
joinTable = TestUtil.getJoinTable(query, pconn);
- assertEquals(0, joinTable.getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilters().size());
+ assertEquals(0, joinTable.getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
query = String.format(queryTemplate, "RIGHT", "RIGHT");
joinTable = TestUtil.getJoinTable(query, pconn);
- assertEquals(0, joinTable.getLeftTable().getPreFilters().size());
- assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilters().size());
- assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilters().size());
+ assertEquals(0, joinTable.getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(0, joinTable.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
+ assertEquals(1, joinTable.getJoinSpecs().get(1).getRhsJoinTable().getLeftTable().getPreFilterParseNodes().size());
}
}
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
index e31849c..f530aed 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
@@ -63,6 +63,8 @@ import org.apache.phoenix.execute.CorrelatePlan;
import org.apache.phoenix.execute.CursorFetchPlan;
import org.apache.phoenix.execute.HashJoinPlan;
import org.apache.phoenix.execute.HashJoinPlan.HashSubPlan;
+import org.apache.phoenix.execute.HashJoinPlan.SubPlan;
+import org.apache.phoenix.execute.HashJoinPlan.WhereClauseSubPlan;
import org.apache.phoenix.execute.LiteralResultIterationPlan;
import org.apache.phoenix.execute.ScanPlan;
import org.apache.phoenix.execute.SortMergeJoinPlan;
@@ -6376,14 +6378,14 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
JoinTable joinTablesContext = TestUtil.getJoinTable(sql, conn);
Table leftmostTableContext = joinTablesContext.getLeftTable();
TestUtil.assertSelectStatement(
- leftmostTableContext.getSubselect(),
+ leftmostTableContext.getSubselectStatement(),
"SELECT ENTITY_ID ID,A_STRING A FROM TESTA WHERE A_BYTE >= 8");
- assertTrue(leftmostTableContext.getPreFilters().isEmpty());
+ assertTrue(leftmostTableContext.getPreFilterParseNodes().isEmpty());
Table rightTableContext = joinTablesContext.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable();
- TestUtil.assertSelectStatement(rightTableContext.getSubselect(), "SELECT ENTITY_ID ID,B_STRING B FROM TESTA");
- assertTrue(rightTableContext.getPreFilters().size() == 1);
- assertTrue(rightTableContext.getPreFilters().get(0).toString().equals("A_BYTE != 5"));
+ TestUtil.assertSelectStatement(rightTableContext.getSubselectStatement(), "SELECT ENTITY_ID ID,B_STRING B FROM TESTA");
+ assertTrue(rightTableContext.getPreFilterParseNodes().size() == 1);
+ assertTrue(rightTableContext.getPreFilterParseNodes().get(0).toString().equals("A_BYTE != 5"));
queryPlan = TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
HashJoinPlan hashJoinPlan = (HashJoinPlan)queryPlan;
@@ -6410,16 +6412,16 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
joinTablesContext = TestUtil.getJoinTable(sql, conn);
leftmostTableContext = joinTablesContext.getLeftTable();
TestUtil.assertSelectStatement(
- leftmostTableContext.getSubselect(),
+ leftmostTableContext.getSubselectStatement(),
"SELECT ENTITY_ID ID,A_STRING A,B_STRING B FROM TESTA WHERE A_BYTE >= 8");
- assertTrue(leftmostTableContext.getPreFilters().isEmpty());
+ assertTrue(leftmostTableContext.getPreFilterParseNodes().isEmpty());
rightTableContext = joinTablesContext.getJoinSpecs().get(0).getRhsJoinTable().getLeftTable();
TestUtil.assertSelectStatement(
- rightTableContext.getSubselect(),
+ rightTableContext.getSubselectStatement(),
"SELECT ENTITY_ID ID,A_STRING A,B_STRING B,A_BYTE X FROM TESTA");
- assertTrue(rightTableContext.getPreFilters().size() == 1);
- assertTrue(rightTableContext.getPreFilters().get(0).toString().equals("A_BYTE != 5"));
+ assertTrue(rightTableContext.getPreFilterParseNodes().size() == 1);
+ assertTrue(rightTableContext.getPreFilterParseNodes().get(0).toString().equals("A_BYTE != 5"));
queryPlan = TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
hashJoinPlan = (HashJoinPlan)queryPlan;
@@ -6597,4 +6599,175 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
conn.close();
}
}
+
+ @Test
+ public void testHashJoinBug6232() throws Exception {
+ Connection conn = null;
+ try {
+ conn = DriverManager.getConnection(getUrl());
+ String sql ="CREATE TABLE test (" +
+ " id INTEGER NOT NULL," +
+ " test_id INTEGER," +
+ " lastchanged TIMESTAMP," +
+ " CONSTRAINT my_pk PRIMARY KEY (id))";
+ conn.createStatement().execute(sql);
+
+ sql= "SELECT AAA.* FROM " +
+ "(SELECT id, test_id, lastchanged FROM test T " +
+ " WHERE lastchanged = ( SELECT max(lastchanged) FROM test WHERE test_id = T.test_id )) AAA " +
+ "inner join " +
+ "(SELECT id FROM test) BBB " +
+ "on AAA.id = BBB.id";
+ QueryPlan queryPlan= TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
+ assertTrue(queryPlan instanceof HashJoinPlan);
+ HashJoinPlan hashJoinPlan = (HashJoinPlan)queryPlan;
+ assertTrue(hashJoinPlan.getDelegate() instanceof ScanPlan);
+ TestUtil.assertSelectStatement(
+ hashJoinPlan.getDelegate().getStatement(), "SELECT AAA.* FROM TEST");
+ SubPlan[] subPlans = hashJoinPlan.getSubPlans();
+ assertTrue(subPlans.length == 1);
+ assertTrue(subPlans[0] instanceof HashSubPlan);
+ assertTrue(subPlans[0].getInnerPlan() instanceof TupleProjectionPlan);
+ assertTrue(
+ ((TupleProjectionPlan)(subPlans[0].getInnerPlan())).getDelegate() instanceof HashJoinPlan);
+
+ sql= "SELECT AAA.* FROM " +
+ "(SELECT id, test_id, lastchanged FROM test T " +
+ " WHERE lastchanged = ( SELECT max(lastchanged) FROM test WHERE test_id = T.test_id )) AAA " +
+ "inner join " +
+ "(SELECT id FROM test limit 10) BBB " +
+ "on AAA.id = BBB.id";
+ queryPlan= TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
+ assertTrue(queryPlan instanceof ClientScanPlan);
+ assertTrue(((ClientScanPlan)queryPlan).getDelegate() instanceof SortMergeJoinPlan);
+
+ String GRAMMAR_TABLE = "CREATE TABLE IF NOT EXISTS GRAMMAR_TABLE (ID INTEGER PRIMARY KEY, " +
+ "unsig_id UNSIGNED_INT, big_id BIGINT, unsig_long_id UNSIGNED_LONG, tiny_id TINYINT," +
+ "unsig_tiny_id UNSIGNED_TINYINT, small_id SMALLINT, unsig_small_id UNSIGNED_SMALLINT," +
+ "float_id FLOAT, unsig_float_id UNSIGNED_FLOAT, double_id DOUBLE, unsig_double_id UNSIGNED_DOUBLE," +
+ "decimal_id DECIMAL, boolean_id BOOLEAN, time_id TIME, date_id DATE, timestamp_id TIMESTAMP," +
+ "unsig_time_id TIME, unsig_date_id DATE, unsig_timestamp_id TIMESTAMP, varchar_id VARCHAR (30)," +
+ "char_id CHAR (30), binary_id BINARY (100), varbinary_id VARBINARY (100))";
+ conn.createStatement().execute(GRAMMAR_TABLE);
+
+ String LARGE_TABLE = "CREATE TABLE IF NOT EXISTS LARGE_TABLE (ID INTEGER PRIMARY KEY, " +
+ "unsig_id UNSIGNED_INT, big_id BIGINT, unsig_long_id UNSIGNED_LONG, tiny_id TINYINT," +
+ "unsig_tiny_id UNSIGNED_TINYINT, small_id SMALLINT, unsig_small_id UNSIGNED_SMALLINT," +
+ "float_id FLOAT, unsig_float_id UNSIGNED_FLOAT, double_id DOUBLE, unsig_double_id UNSIGNED_DOUBLE," +
+ "decimal_id DECIMAL, boolean_id BOOLEAN, time_id TIME, date_id DATE, timestamp_id TIMESTAMP," +
+ "unsig_time_id TIME, unsig_date_id DATE, unsig_timestamp_id TIMESTAMP, varchar_id VARCHAR (30)," +
+ "char_id CHAR (30), binary_id BINARY (100), varbinary_id VARBINARY (100))";
+ conn.createStatement().execute(LARGE_TABLE);
+
+ String SECONDARY_LARGE_TABLE = "CREATE TABLE IF NOT EXISTS SECONDARY_LARGE_TABLE (SEC_ID INTEGER PRIMARY KEY," +
+ "sec_unsig_id UNSIGNED_INT, sec_big_id BIGINT, sec_usnig_long_id UNSIGNED_LONG, sec_tiny_id TINYINT," +
+ "sec_unsig_tiny_id UNSIGNED_TINYINT, sec_small_id SMALLINT, sec_unsig_small_id UNSIGNED_SMALLINT," +
+ "sec_float_id FLOAT, sec_unsig_float_id UNSIGNED_FLOAT, sec_double_id DOUBLE, sec_unsig_double_id UNSIGNED_DOUBLE," +
+ "sec_decimal_id DECIMAL, sec_boolean_id BOOLEAN, sec_time_id TIME, sec_date_id DATE," +
+ "sec_timestamp_id TIMESTAMP, sec_unsig_time_id TIME, sec_unsig_date_id DATE, sec_unsig_timestamp_id TIMESTAMP," +
+ "sec_varchar_id VARCHAR (30), sec_char_id CHAR (30), sec_binary_id BINARY (100), sec_varbinary_id VARBINARY (100))";
+ conn.createStatement().execute(SECONDARY_LARGE_TABLE);
+
+ sql = "SELECT * FROM (SELECT ID, BIG_ID, DATE_ID FROM LARGE_TABLE AS A WHERE (A.ID % 5) = 0) AS A " +
+ "INNER JOIN (SELECT SEC_ID, SEC_TINY_ID, SEC_UNSIG_FLOAT_ID FROM SECONDARY_LARGE_TABLE AS B WHERE (B.SEC_ID % 5) = 0) AS B " +
+ "ON A.ID=B.SEC_ID WHERE A.DATE_ID > ALL (SELECT SEC_DATE_ID FROM SECONDARY_LARGE_TABLE LIMIT 100) " +
+ "AND B.SEC_UNSIG_FLOAT_ID = ANY (SELECT sec_unsig_float_id FROM SECONDARY_LARGE_TABLE " +
+ "WHERE SEC_ID > ALL (SELECT MIN (ID) FROM GRAMMAR_TABLE WHERE UNSIG_ID IS NULL) AND " +
+ "SEC_UNSIG_ID < ANY (SELECT DISTINCT(UNSIG_ID) FROM LARGE_TABLE WHERE UNSIG_ID<2500) LIMIT 1000) " +
+ "AND A.ID < 10000";
+ queryPlan= TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
+ assertTrue(queryPlan instanceof HashJoinPlan);
+ hashJoinPlan = (HashJoinPlan)queryPlan;
+ subPlans = hashJoinPlan.getSubPlans();
+ assertTrue(subPlans.length == 2);
+ assertTrue(subPlans[0] instanceof WhereClauseSubPlan);
+ assertTrue(subPlans[1] instanceof HashSubPlan);
+
+
+ String tableName1 = generateUniqueName();
+ String tableName2 = generateUniqueName();
+
+ sql="CREATE TABLE IF NOT EXISTS "+tableName1+" ( "+
+ "AID INTEGER PRIMARY KEY,"+
+ "AGE INTEGER"+
+ ")";
+ conn.createStatement().execute(sql);
+
+ sql="CREATE TABLE IF NOT EXISTS "+tableName2+" ( "+
+ "BID INTEGER PRIMARY KEY,"+
+ "CODE INTEGER"+
+ ")";
+ conn.createStatement().execute(sql);
+
+ sql="select a.aid from " + tableName1 + " a inner join "+
+ "(select bid,code from " + tableName2 + " where code > 10 limit 3) b on a.aid = b.bid "+
+ "where a.age > (select code from " + tableName2 + " c where c.bid = 2) order by a.aid";
+ queryPlan= TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
+ assertTrue(queryPlan instanceof HashJoinPlan);
+ hashJoinPlan = (HashJoinPlan)queryPlan;
+ ScanPlan scanPlan=(ScanPlan)(hashJoinPlan.getDelegate());
+ TestUtil.assertSelectStatement(
+ scanPlan.getStatement(),
+ "SELECT A.AID FROM " +tableName1+ " A WHERE A.AGE > (SELECT CODE FROM " + tableName2 + " C WHERE C.BID = 2 LIMIT 2) ORDER BY A.AID");
+ subPlans = hashJoinPlan.getSubPlans();
+ assertTrue(subPlans.length == 2);
+ assertTrue(subPlans[0] instanceof WhereClauseSubPlan);
+ WhereClauseSubPlan whereClauseSubPlan = (WhereClauseSubPlan)subPlans[0];
+ TestUtil.assertSelectStatement(
+ whereClauseSubPlan.getInnerPlan().getStatement(),
+ "SELECT CODE FROM " + tableName2 + " C WHERE C.BID = 2 LIMIT 2");
+ assertTrue(subPlans[1] instanceof HashSubPlan);
+
+ sql="select a.aid from (select aid,age from " + tableName1 + " where age >=11 and age<=33) a inner join "+
+ "(select bid,code from " + tableName2 + " where code > 10 limit 3) b on a.aid = b.bid "+
+ "where a.age > (select code from " + tableName2 + " c where c.bid = 2) order by a.aid";
+ queryPlan= TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
+ assertTrue(queryPlan instanceof HashJoinPlan);
+ hashJoinPlan = (HashJoinPlan)queryPlan;
+ scanPlan=(ScanPlan)(hashJoinPlan.getDelegate());
+ TestUtil.assertSelectStatement(
+ scanPlan.getStatement(),
+ "SELECT A.AID FROM " +tableName1+ " WHERE (AGE > (SELECT CODE FROM " + tableName2 + " C WHERE C.BID = 2 LIMIT 2) AND (AGE >= 11 AND AGE <= 33)) ORDER BY A.AID");
+ subPlans = hashJoinPlan.getSubPlans();
+ assertTrue(subPlans.length == 2);
+ assertTrue(subPlans[0] instanceof WhereClauseSubPlan);
+ whereClauseSubPlan = (WhereClauseSubPlan)subPlans[0];
+ TestUtil.assertSelectStatement(
+ whereClauseSubPlan.getInnerPlan().getStatement(),
+ "SELECT CODE FROM " + tableName2 + " C WHERE C.BID = 2 LIMIT 2");
+ assertTrue(subPlans[1] instanceof HashSubPlan);
+
+ sql = "select a.aid from (select aid,age from " + tableName1 + " where age >=11 and age<=33) a inner join "+
+ "(select bid,code from " + tableName2 + " where code > 10 limit 3) b on a.aid = b.bid "+
+ "where a.age > (select max(code) from " + tableName2 + " c where c.bid >= 1) order by a.aid";
+ queryPlan= TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
+ assertTrue(queryPlan instanceof HashJoinPlan);
+ hashJoinPlan = (HashJoinPlan)queryPlan;
+ scanPlan=(ScanPlan)(hashJoinPlan.getDelegate());
+ TestUtil.assertSelectStatement(
+ scanPlan.getStatement(),
+ "SELECT A.AID FROM " + tableName1 + " WHERE (AGE > (SELECT MAX(CODE) FROM " + tableName2 + " C WHERE C.BID >= 1 LIMIT 2) AND (AGE >= 11 AND AGE <= 33)) ORDER BY A.AID");
+ subPlans = hashJoinPlan.getSubPlans();
+ assertTrue(subPlans.length == 2);
+ assertTrue(subPlans[0] instanceof WhereClauseSubPlan);
+ whereClauseSubPlan = (WhereClauseSubPlan)subPlans[0];
+ TestUtil.assertSelectStatement(
+ whereClauseSubPlan.getInnerPlan().getStatement(),
+ "SELECT MAX(CODE) FROM " + tableName2 + " C WHERE C.BID >= 1 LIMIT 2");
+ assertTrue(subPlans[1] instanceof HashSubPlan);
+
+ sql = "select a.aid from (select aid,age from " + tableName1 + " where age >=11 and age<=33) a inner join "+
+ "(select bid,code from " + tableName2 + " where code > 10 limit 3) b on a.aid = b.bid "+
+ "where a.age > (select max(code) from " + tableName2 + " c where c.bid = a.aid) order by a.aid";
+ queryPlan= TestUtil.getOptimizeQueryPlanNoIterator(conn, sql);
+ assertTrue(queryPlan instanceof HashJoinPlan);
+ hashJoinPlan = (HashJoinPlan)queryPlan;
+ subPlans = hashJoinPlan.getSubPlans();
+ assertTrue(subPlans.length == 2);
+ assertTrue(subPlans[0] instanceof HashSubPlan);
+ assertTrue(subPlans[1] instanceof HashSubPlan);
+ } finally {
+ conn.close();
+ }
+ }
}