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 <cheng...@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(); + } + } }