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 2019/02/05 02:25:48 UTC
[phoenix] branch 4.x-HBase-1.3 updated: PHOENIX-5105 Push Filter
through Sort for SortMergeJoin
This is an automated email from the ASF dual-hosted git repository.
chenglei pushed a commit to branch 4.x-HBase-1.3
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/4.x-HBase-1.3 by this push:
new ee3880b PHOENIX-5105 Push Filter through Sort for SortMergeJoin
ee3880b is described below
commit ee3880bc8a3c8d4772ca013f769190729c2e8d6f
Author: chenglei <ch...@apache.org>
AuthorDate: Tue Feb 5 10:25:00 2019 +0800
PHOENIX-5105 Push Filter through Sort for SortMergeJoin
---
.../phoenix/end2end/SortMergeJoinMoreIT.java | 109 +++++++
.../org/apache/phoenix/compile/JoinCompiler.java | 21 +-
.../org/apache/phoenix/compile/QueryCompiler.java | 6 +-
.../apache/phoenix/compile/SubselectRewriter.java | 357 ++++++++++++---------
.../org/apache/phoenix/parse/DerivedTableNode.java | 1 +
.../apache/phoenix/compile/QueryCompilerTest.java | 218 +++++++++++++
6 files changed, 549 insertions(+), 163 deletions(-)
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
index dea349a..2db8e85 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
@@ -742,4 +742,113 @@ public class SortMergeJoinMoreIT extends ParallelStatsDisabledIT {
assertFalse(rs.next());
}
}
+
+ @Test
+ public void testSortMergeJoinPushFilterThroughSortBug5105() 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,55)");
+ conn.createStatement().execute("UPSERT INTO "+tableName2+"(BID,CODE) VALUES (3,44)");
+ conn.commit();
+
+ //test for simple scan
+
+ sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from (select aid,age from "+tableName1+" where age >=11 and age<=33) a inner join "+
+ "(select bid,code from "+tableName2+" order by code limit 2) b on a.aid=b.bid where b.code > 50";
+ ResultSet rs=conn.prepareStatement(sql).executeQuery();
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 2);
+ assertTrue(rs.getInt(2) == 55);
+ assertTrue(!rs.next());
+
+ //test for aggregate
+ sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.codesum from (select aid,sum(age) agesum from "+tableName1+" where age >=11 and age<=33 group by aid order by agesum limit 3) a inner join "+
+ "(select bid,sum(code) codesum from "+tableName2+" group by bid order by codesum limit 2) b on a.aid=b.bid where b.codesum > 50";
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 2);
+ assertTrue(rs.getInt(2) == 55);
+ assertTrue(!rs.next());
+
+ String tableName3=generateUniqueName();
+ sql="CREATE TABLE IF NOT EXISTS "+tableName3+" ( "+
+ "CID INTEGER PRIMARY KEY,"+
+ "REGION INTEGER"+
+ ")";
+ conn.createStatement().execute(sql);
+
+ conn.createStatement().execute("UPSERT INTO "+tableName3+"(CID,REGION) VALUES (1,77)");
+ conn.createStatement().execute("UPSERT INTO "+tableName3+"(CID,REGION) VALUES (2,88)");
+ conn.createStatement().execute("UPSERT INTO "+tableName3+"(CID,REGION) VALUES (3,99)");
+ conn.commit();
+
+ //test for join
+ sql="select t1.aid,t1.code,t2.region from "+
+ "(select a.aid,b.code from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 order by b.code limit 3) t1 inner join "+
+ "(select a.aid,c.region from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 order by c.region desc limit 1) t2 on t1.aid=t2.aid "+
+ "where t1.code > 50";
+
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertTrue(!rs.next());
+
+ //test for join and aggregate
+ sql="select t1.aid,t1.codesum,t2.regionsum from "+
+ "(select a.aid,sum(b.code) codesum from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 group by a.aid order by codesum limit 3) t1 inner join "+
+ "(select a.aid,sum(c.region) regionsum from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 group by a.aid order by regionsum desc limit 2) t2 on t1.aid=t2.aid "+
+ "where t1.codesum >=40 and t2.regionsum >= 90";
+
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 3);
+ assertTrue(rs.getInt(2) == 44);
+ assertTrue(rs.getInt(3) == 99);
+ assertTrue(!rs.next());
+
+ //test for if SubselectRewriter.isOuterOrderByNodesPrefixOfInner had take effect
+ sql="select t1.aid,t1.codesum,t2.regionsum from "+
+ "(select a.aid,sum(b.code) codesum from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 group by a.aid order by a.aid,codesum limit 3) t1 inner join "+
+ "(select a.aid,sum(c.region) regionsum from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 group by a.aid order by a.aid desc,regionsum desc limit 2) t2 on t1.aid=t2.aid "+
+ "where t1.codesum >=40 and t2.regionsum >= 80 order by t1.aid desc";
+
+ rs=conn.prepareStatement(sql).executeQuery();
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 3);
+ assertTrue(rs.getInt(2) == 44);
+ assertTrue(rs.getInt(3) == 99);
+
+ assertTrue(rs.next());
+ assertTrue(rs.getInt(1) == 2);
+ assertTrue(rs.getInt(2) == 55);
+ assertTrue(rs.getInt(3) == 88);
+ assertTrue(!rs.next());
+ } 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 b2b660e..3803201 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
@@ -359,11 +359,8 @@ public class JoinCompiler {
}
}
- public Expression compilePostFilterExpression(StatementContext context, Table table) throws SQLException {
+ public Expression compilePostFilterExpression(StatementContext context) throws SQLException {
List<ParseNode> filtersCombined = Lists.<ParseNode> newArrayList(postFilters);
- if (table != null) {
- filtersCombined.addAll(table.getPostFilters());
- }
return JoinCompiler.compilePostFilterExpression(context, filtersCombined);
}
@@ -756,18 +753,16 @@ public class JoinCompiler {
return combine(preFilters);
}
- public Expression compilePostFilterExpression(StatementContext context) throws SQLException {
- return JoinCompiler.compilePostFilterExpression(context, postFilters);
- }
-
public SelectStatement getAsSubquery(List<OrderByNode> orderBy) throws SQLException {
- if (isSubselect())
- return SubselectRewriter.applyOrderBy(
- SubselectRewriter.applyPostFilters(subselect, preFilters, tableNode.getAlias()),
+ if (isSubselect()) {
+ return SubselectRewriter.applyOrderByAndPostFilters(
+ SubselectRewriter.applyPreFiltersForSubselect(subselect, preFilters, tableNode.getAlias()),
orderBy,
tableNode.getAlias(),
- tableNode);
-
+ postFilters);
+ }
+ //for 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());
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 6e36158..15cdc10 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
@@ -221,7 +221,7 @@ public class QueryCompiler {
QueryPlan plan = compileSubquery(subquery, false);
PTable projectedTable = table.createProjectedTable(plan.getProjector());
context.setResolver(FromCompiler.getResolverForProjectedTable(projectedTable, context.getConnection(), subquery.getUdfParseNodes()));
- return new TupleProjectionPlan(plan, new TupleProjector(plan.getProjector()), table.compilePostFilterExpression(context));
+ return new TupleProjectionPlan(plan, new TupleProjector(plan.getProjector()), null);
}
List<JoinCompiler.Strategy> strategies = joinTable.getApplicableJoinStrategies();
@@ -320,7 +320,7 @@ public class QueryCompiler {
}
TupleProjector.serializeProjectorIntoScan(context.getScan(), tupleProjector);
QueryPlan plan = compileSingleFlatQuery(context, query, binds, asSubquery, !asSubquery && joinTable.isAllLeftJoin(), null, !table.isSubselect() && projectPKColumns ? tupleProjector : null, true);
- Expression postJoinFilterExpression = joinTable.compilePostFilterExpression(context, table);
+ Expression postJoinFilterExpression = joinTable.compilePostFilterExpression(context);
Integer limit = null;
Integer offset = null;
if (!query.isAggregate() && !query.isDistinct() && query.getOrderBy().isEmpty()) {
@@ -373,7 +373,7 @@ public class QueryCompiler {
TupleProjector.serializeProjectorIntoScan(context.getScan(), tupleProjector);
context.setResolver(FromCompiler.getResolverForProjectedTable(projectedTable, context.getConnection(), rhs.getUdfParseNodes()));
QueryPlan rhsPlan = compileSingleFlatQuery(context, rhs, binds, asSubquery, !asSubquery && type == JoinType.Right, null, !rhsTable.isSubselect() && projectPKColumns ? tupleProjector : null, true);
- Expression postJoinFilterExpression = joinTable.compilePostFilterExpression(context, rhsTable);
+ Expression postJoinFilterExpression = joinTable.compilePostFilterExpression(context);
Integer limit = null;
Integer offset = null;
if (!rhs.isAggregate() && !rhs.isDistinct() && rhs.getOrderBy().isEmpty()) {
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 af19ed1..468bd1f 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
@@ -27,8 +27,6 @@ import java.util.Iterator;
import java.util.List;
import java.util.Map;
-import org.apache.phoenix.exception.SQLExceptionCode;
-import org.apache.phoenix.exception.SQLExceptionInfo;
import org.apache.phoenix.jdbc.PhoenixConnection;
import org.apache.phoenix.parse.AliasedNode;
import org.apache.phoenix.parse.ColumnParseNode;
@@ -38,6 +36,7 @@ import org.apache.phoenix.parse.LimitNode;
import org.apache.phoenix.parse.OffsetNode;
import org.apache.phoenix.parse.OrderByNode;
import org.apache.phoenix.parse.ParseNode;
+import org.apache.phoenix.parse.ParseNodeFactory;
import org.apache.phoenix.parse.ParseNodeRewriter;
import org.apache.phoenix.parse.SelectStatement;
import org.apache.phoenix.parse.TableNode;
@@ -57,25 +56,142 @@ public class SubselectRewriter extends ParseNodeRewriter {
private final Map<String, ParseNode> aliasMap;
private boolean removeAlias = false;
- public static SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters, String subqueryAlias) throws SQLException {
- if (postFilters.isEmpty())
+ public static SelectStatement applyPreFiltersForSubselect(SelectStatement statement, List<ParseNode> preFilterParseNodes, String subqueryAlias) throws SQLException {
+ if (preFilterParseNodes.isEmpty())
return statement;
assert(isPostFilterConvertible(statement));
- return new SubselectRewriter(null, statement.getSelect(), subqueryAlias).applyPostFilters(statement, postFilters);
+ return new SubselectRewriter(null, statement.getSelect(), subqueryAlias).applyPreFilters(statement, preFilterParseNodes);
}
public static boolean isPostFilterConvertible(SelectStatement statement) throws SQLException {
return statement.getLimit() == null && (!statement.isAggregate() || !statement.getGroupBy().isEmpty());
}
- public static SelectStatement applyOrderBy(SelectStatement statement, List<OrderByNode> orderBy, String subqueryAlias,TableNode tableNode) throws SQLException {
- if (orderBy == null)
- return statement;
-
- return new SubselectRewriter(null, statement.getSelect(), subqueryAlias).applyOrderBy(statement, orderBy, tableNode);
- }
+ /**
+ * <pre>
+ * only append orderByNodes and postFilters, the optimization is left to {@link #flatten(SelectStatement, SelectStatement)}.
+ * an example :
+ * when the subselectStatment is : (SELECT reverse(loc_id), \"supplier_id\", name FROM " + JOIN_SUPPLIER_TABLE + " LIMIT 5) AS supp
+ * orderByNodes is : supp.\"supplier_id\"
+ * postFilterParseNodes is : supp.name != 'S1'
+ * we rewrite the subselectStatment as :
+ * (SELECT $2.$3,$2."supplier_id",$2.NAME FROM (SELECT REVERSE(LOC_ID) $3,"supplier_id",NAME FROM SUPPLIERTABLE LIMIT 5) $2 WHERE $2.NAME != 'S1' ORDER BY $2."supplier_id") AS supp
+ *
+ * </pre>
+ * @param subselectStatement
+ * @param orderByNodes
+ * @param subselectTableAliasName
+ * @param postFilterParseNodes
+ * @return
+ * @throws SQLException
+ */
+ public static SelectStatement applyOrderByAndPostFilters(
+ SelectStatement subselectStatement,
+ List<OrderByNode> orderByNodes,
+ String subselectTableAliasName,
+ List<ParseNode> postFilterParseNodes) throws SQLException {
+
+ if(orderByNodes == null) {
+ orderByNodes = Collections.emptyList();
+ }
+
+ if(postFilterParseNodes == null) {
+ postFilterParseNodes = Collections.emptyList();
+ }
+
+ if(orderByNodes.isEmpty() && postFilterParseNodes.isEmpty()) {
+ return subselectStatement;
+ }
+
+ List<AliasedNode> subselectAliasedNodes = subselectStatement.getSelect();
+ List<AliasedNode> newOuterSelectAliasedNodes = new ArrayList<AliasedNode>(subselectAliasedNodes.size());
+ Map<String,ParseNode> subselectAliasFullNameToNewColumnParseNode = new HashMap<String,ParseNode>();
+
+ String newSubselectTableAliasName = ParseNodeFactory.createTempAlias();
+ List<AliasedNode> newSubselectAliasedNodes = null;
+ int index = 0;
+ for (AliasedNode subselectAliasedNode : subselectAliasedNodes) {
+ String aliasName = subselectAliasedNode.getAlias();
+ ParseNode aliasParseNode = subselectAliasedNode.getNode();
+ if (aliasName == null) {
+ aliasName = aliasParseNode.getAlias();
+ }
+ if(aliasName == null) {
+ //if there is no alias,we generate a new alias,
+ //and added the new alias to the old subselectAliasedNodes
+ aliasName = ParseNodeFactory.createTempAlias();
+ if(newSubselectAliasedNodes == null) {
+ newSubselectAliasedNodes = new ArrayList<AliasedNode>(subselectAliasedNodes.size());
+ if(index > 0) {
+ newSubselectAliasedNodes.addAll(subselectAliasedNodes.subList(0, index));
+ }
+ }
+ newSubselectAliasedNodes.add(NODE_FACTORY.aliasedNode(aliasName, aliasParseNode));
+ } else {
+ if(newSubselectAliasedNodes != null) {
+ newSubselectAliasedNodes.add(subselectAliasedNode);
+ }
+ }
+
+ ColumnParseNode newColumnParseNode = NODE_FACTORY.column(
+ NODE_FACTORY.table(null, newSubselectTableAliasName),
+ aliasName,
+ aliasName);
+ subselectAliasFullNameToNewColumnParseNode.put(
+ SchemaUtil.getColumnName(subselectTableAliasName, SchemaUtil.normalizeIdentifier(aliasName)),
+ newColumnParseNode);
+ AliasedNode newOuterSelectAliasNode = NODE_FACTORY.aliasedNode(null, newColumnParseNode);
+ newOuterSelectAliasedNodes.add(newOuterSelectAliasNode);
+ index++;
+ }
+
+ SubselectRewriter rewriter = new SubselectRewriter(subselectAliasFullNameToNewColumnParseNode);
+ List<OrderByNode> rewrittenOrderByNodes = null;
+ if(orderByNodes.size() > 0) {
+ rewrittenOrderByNodes = new ArrayList<OrderByNode>(orderByNodes.size());
+ for (OrderByNode orderByNode : orderByNodes) {
+ ParseNode parseNode = orderByNode.getNode();
+ rewrittenOrderByNodes.add(NODE_FACTORY.orderBy(
+ parseNode.accept(rewriter),
+ orderByNode.isNullsLast(),
+ orderByNode.isAscending()));
+ }
+ }
+
+ ParseNode newWhereParseNode = null;
+ if(postFilterParseNodes.size() > 0) {
+ List<ParseNode> rewrittenPostFilterParseNodes =
+ new ArrayList<ParseNode>(postFilterParseNodes.size());
+ for(ParseNode postFilterParseNode : postFilterParseNodes) {
+ rewrittenPostFilterParseNodes.add(postFilterParseNode.accept(rewriter));
+ }
+ newWhereParseNode = combine(rewrittenPostFilterParseNodes);
+ }
+
+ SelectStatement subselectStatementToUse = subselectStatement;
+ if(newSubselectAliasedNodes != null) {
+ subselectStatementToUse = NODE_FACTORY.select(subselectStatement, subselectStatement.isDistinct(), newSubselectAliasedNodes);
+ }
+
+ return NODE_FACTORY.select(
+ NODE_FACTORY.derivedTable(newSubselectTableAliasName, subselectStatementToUse),
+ HintNode.EMPTY_HINT_NODE,
+ false,
+ newOuterSelectAliasedNodes,
+ newWhereParseNode,
+ null,
+ null,
+ rewrittenOrderByNodes,
+ null,
+ null,
+ 0,
+ false,
+ subselectStatementToUse.hasSequence(),
+ Collections.<SelectStatement> emptyList(),
+ subselectStatementToUse.getUdfParseNodes());
+ }
public static SelectStatement flatten(SelectStatement select, PhoenixConnection connection) throws SQLException {
TableNode from = select.getFrom();
@@ -113,6 +229,73 @@ public class SubselectRewriter extends ParseNodeRewriter {
}
}
+ private SubselectRewriter(Map<String, ParseNode> selectAliasFullNameToAliasParseNode) {
+ super(null, selectAliasFullNameToAliasParseNode.size());
+ this.tableAlias = null;
+ this.aliasMap = selectAliasFullNameToAliasParseNode;
+ }
+
+ /**
+ * if the OrderBy of outerSelectStatement is prefix of innerSelectStatement,
+ * we can remove the OrderBy of outerSelectStatement.
+ * @param outerSelectStatement
+ * @param innerSelectStatement
+ * @return
+ * @throws SQLException
+ */
+ private SelectStatement removeOuterSelectStatementOrderByIfNecessary(
+ SelectStatement outerSelectStatement, SelectStatement innerSelectStatement) throws SQLException {
+ if(outerSelectStatement.isDistinct() ||
+ outerSelectStatement.isAggregate() ||
+ (outerSelectStatement.getGroupBy() != null && !outerSelectStatement.getGroupBy().isEmpty()) ||
+ outerSelectStatement.isJoin() ||
+ outerSelectStatement.isUnion()) {
+ return outerSelectStatement;
+ }
+
+ List<OrderByNode> outerOrderByNodes = outerSelectStatement.getOrderBy();
+ if(outerOrderByNodes == null || outerOrderByNodes.isEmpty()) {
+ return outerSelectStatement;
+ }
+
+ if(this.isOuterOrderByNodesPrefixOfInner(innerSelectStatement.getOrderBy(), outerOrderByNodes)) {
+ return NODE_FACTORY.select(outerSelectStatement, (List<OrderByNode>)null);
+ }
+ return outerSelectStatement;
+ }
+
+ /**
+ * check if outerOrderByNodes is prefix of innerOrderByNodes.
+ * @param selectStatement
+ * @param outerOrderByNodes
+ * @return
+ */
+ private boolean isOuterOrderByNodesPrefixOfInner(
+ List<OrderByNode> innerOrderByNodes,
+ List<OrderByNode> outerOrderByNodes) throws SQLException {
+
+ assert outerOrderByNodes != null && outerOrderByNodes.size() > 0;
+
+ if(innerOrderByNodes == null || outerOrderByNodes.size() > innerOrderByNodes.size()) {
+ return false;
+ }
+
+ Iterator<OrderByNode> innerOrderByNodeIter = innerOrderByNodes.iterator();
+ for(OrderByNode outerOrderByNode : outerOrderByNodes) {
+ ParseNode outerOrderByParseNode = outerOrderByNode.getNode();
+ OrderByNode rewrittenOuterOrderByNode = NODE_FACTORY.orderBy(
+ outerOrderByParseNode.accept(this),
+ outerOrderByNode.isNullsLast(),
+ outerOrderByNode.isAscending());
+ assert innerOrderByNodeIter.hasNext();
+ OrderByNode innerOrderByNode = innerOrderByNodeIter.next();
+ if(!innerOrderByNode.equals(rewrittenOuterOrderByNode)) {
+ return false;
+ }
+ }
+ return true;
+ }
+
private SelectStatement flatten(SelectStatement select, SelectStatement subselect) throws SQLException {
// Replace aliases in sub-select first.
subselect = ParseNodeRewriter.rewrite(subselect, this);
@@ -130,7 +313,7 @@ public class SubselectRewriter extends ParseNodeRewriter {
ParseNode where = select.getWhere();
if (where != null) {
if (subselect.getLimit() != null || (subselect.isAggregate() && subselect.getGroupBy().isEmpty())) {
- return select;
+ return removeOuterSelectStatementOrderByIfNecessary(select,subselect);
}
ParseNode postFilter = where.accept(this);
if (subselect.getGroupBy().isEmpty()) {
@@ -142,7 +325,7 @@ public class SubselectRewriter extends ParseNodeRewriter {
if (select.isDistinct()) {
if (subselect.getLimit() != null || subselect.isAggregate() || subselect.isDistinct()) {
- return select;
+ return removeOuterSelectStatementOrderByIfNecessary(select,subselect);
}
isDistinctRewrite = true;
orderByRewrite = null;
@@ -150,7 +333,7 @@ public class SubselectRewriter extends ParseNodeRewriter {
if (select.isAggregate()) {
if (subselect.getLimit() != null || subselect.isAggregate() || subselect.isDistinct()) {
- return select;
+ return removeOuterSelectStatementOrderByIfNecessary(select,subselect);
}
isAggregateRewrite = true;
orderByRewrite = null;
@@ -159,7 +342,7 @@ public class SubselectRewriter extends ParseNodeRewriter {
List<ParseNode> groupBy = select.getGroupBy();
if (!groupBy.isEmpty()) {
if (subselect.getLimit() != null || subselect.isAggregate() || subselect.isDistinct()) {
- return select;
+ return removeOuterSelectStatementOrderByIfNecessary(select,subselect);
}
groupByRewrite = Lists.<ParseNode>newArrayListWithExpectedSize(groupBy.size());
for (ParseNode node : groupBy) {
@@ -191,7 +374,7 @@ public class SubselectRewriter extends ParseNodeRewriter {
List<OrderByNode> orderBy = select.getOrderBy();
if (!orderBy.isEmpty()) {
if (subselect.getLimit() != null) {
- return select;
+ return removeOuterSelectStatementOrderByIfNecessary(select,subselect);
}
orderByRewrite = Lists.newArrayListWithExpectedSize(orderBy.size());
for (OrderByNode orderByNode : orderBy) {
@@ -202,7 +385,7 @@ public class SubselectRewriter extends ParseNodeRewriter {
OffsetNode offset = select.getOffset();
if (offsetRewrite != null || (limitRewrite != null && offset != null)) {
- return select;
+ return removeOuterSelectStatementOrderByIfNecessary(select,subselect);
} else {
offsetRewrite = offset;
}
@@ -217,7 +400,7 @@ public class SubselectRewriter extends ParseNodeRewriter {
if (limitValue != null && limitValueSubselect != null) {
limitRewrite = limitValue < limitValueSubselect ? limit : limitRewrite;
} else {
- return select;
+ return removeOuterSelectStatementOrderByIfNecessary(select,subselect);
}
}
}
@@ -237,146 +420,26 @@ public class SubselectRewriter extends ParseNodeRewriter {
}
return stmt;
}
-
- private SelectStatement applyPostFilters(SelectStatement statement, List<ParseNode> postFilters) throws SQLException {
- List<ParseNode> postFiltersRewrite = Lists.<ParseNode>newArrayListWithExpectedSize(postFilters.size());
- for (ParseNode node : postFilters) {
- postFiltersRewrite.add(node.accept(this));
+
+ private SelectStatement applyPreFilters(SelectStatement statement, List<ParseNode> preFilterParseNodes) throws SQLException {
+ List<ParseNode> rewrittenPreFilterParseNodes = Lists.<ParseNode>newArrayListWithExpectedSize(preFilterParseNodes.size());
+ for (ParseNode preFilterParseNode : preFilterParseNodes) {
+ rewrittenPreFilterParseNodes.add(preFilterParseNode.accept(this));
}
if (statement.getGroupBy().isEmpty()) {
ParseNode where = statement.getWhere();
if (where != null) {
- postFiltersRewrite.add(where);
+ rewrittenPreFilterParseNodes.add(where);
}
- return NODE_FACTORY.select(statement, combine(postFiltersRewrite));
+ return NODE_FACTORY.select(statement, combine(rewrittenPreFilterParseNodes));
}
ParseNode having = statement.getHaving();
if (having != null) {
- postFiltersRewrite.add(having);
- }
- return NODE_FACTORY.select(statement, statement.getWhere(), combine(postFiltersRewrite));
- }
-
- private SelectStatement applyOrderBy(SelectStatement subselectStatement,List<OrderByNode> newOrderByNodes, TableNode subselectAsTableNode) throws SQLException {
- ArrayList<OrderByNode> rewrittenNewOrderByNodes = Lists.<OrderByNode> newArrayListWithExpectedSize(newOrderByNodes.size());
- for (OrderByNode newOrderByNode : newOrderByNodes) {
- ParseNode parseNode = newOrderByNode.getNode();
- rewrittenNewOrderByNodes.add(NODE_FACTORY.orderBy(
- parseNode.accept(this),
- newOrderByNode.isNullsLast(),
- newOrderByNode.isAscending()));
- }
-
- // in these case,we can safely override subselect's orderBy
- if(subselectStatement.getLimit()==null ||
- subselectStatement.getOrderBy() == null ||
- subselectStatement.getOrderBy().isEmpty()) {
- return NODE_FACTORY.select(subselectStatement, rewrittenNewOrderByNodes);
- }
-
- //if rewrittenNewOrderByNodes is prefix of subselectStatement's orderBy,
- //then subselectStatement no need to modify
- if(this.isOrderByPrefix(subselectStatement, rewrittenNewOrderByNodes)) {
- return subselectStatement;
- }
-
- //modify the subselect "(select id,code from tableName order by code limit 3) as a" to
- //"(select id,code from (select id,code from tableName order by code limit 3) order by id) as a"
- List<AliasedNode> newSelectAliasedNodes = createAliasedNodesFromSubselect(subselectStatement,rewrittenNewOrderByNodes);
- assert subselectAsTableNode instanceof DerivedTableNode;
- //set the subselect alias to null.
- subselectAsTableNode=NODE_FACTORY.derivedTable(null, ((DerivedTableNode)subselectAsTableNode).getSelect());
-
- return NODE_FACTORY.select(
- subselectAsTableNode,
- HintNode.EMPTY_HINT_NODE,
- false,
- newSelectAliasedNodes,
- null,
- null,
- null,
- rewrittenNewOrderByNodes,
- null,
- null,
- 0,
- false,
- subselectStatement.hasSequence(),
- Collections.<SelectStatement> emptyList(),
- subselectStatement.getUdfParseNodes());
- }
-
- /**
- * create new aliasedNodes from subSelectStatement's select alias.
- * @param subSelectStatement
- * @param rewrittenOrderByNodes
- * @return
- */
- private List<AliasedNode> createAliasedNodesFromSubselect(SelectStatement subSelectStatement,ArrayList<OrderByNode> rewrittenOrderByNodes) throws SQLException {
- List<AliasedNode> selectAliasedNodes=subSelectStatement.getSelect();
- List<AliasedNode> newSelectAliasedNodes = new ArrayList<AliasedNode>(selectAliasedNodes.size());
- Map<ParseNode,Integer> rewrittenOrderByParseNodeToIndex=new HashMap<ParseNode, Integer>(rewrittenOrderByNodes.size());
- for(int index=0;index < rewrittenOrderByNodes.size();index++) {
- OrderByNode rewrittenOrderByNode=rewrittenOrderByNodes.get(index);
- rewrittenOrderByParseNodeToIndex.put(rewrittenOrderByNode.getNode(), Integer.valueOf(index));
+ rewrittenPreFilterParseNodes.add(having);
}
-
- for (AliasedNode selectAliasedNode : selectAliasedNodes) {
- String selectAliasName = selectAliasedNode.getAlias();
- ParseNode oldSelectAliasParseNode = selectAliasedNode.getNode();
- if (selectAliasName == null) {
- selectAliasName = SchemaUtil.normalizeIdentifier(oldSelectAliasParseNode.getAlias());
- }
- //in order to convert the subselect "select id,sum(code) codesum from table group by id order by codesum limit 3"
- //to "select id,codesum from (select id,sum(code) codesum from table group by id order by codesum limit 3) order by id"
- //we must has alias for sum(code)
- if(selectAliasName== null) {
- throw new SQLExceptionInfo.Builder(SQLExceptionCode.SUBQUERY_SELECT_LIST_COLUMN_MUST_HAS_ALIAS)
- .setMessage("the subquery is:"+subSelectStatement)
- .build()
- .buildException();
- }
-
- ColumnParseNode newColumnParseNode=NODE_FACTORY.column(null, selectAliasName, selectAliasName);
- Integer index=rewrittenOrderByParseNodeToIndex.get(oldSelectAliasParseNode);
- if(index !=null) {
- //replace the rewrittenOrderByNode's child to newColumnParseNode
- OrderByNode oldOrderByNode=rewrittenOrderByNodes.get(index);
- rewrittenOrderByNodes.set(index,
- NODE_FACTORY.orderBy(
- newColumnParseNode,
- oldOrderByNode.isNullsLast(),
- oldOrderByNode.isAscending()));
- }
-
- AliasedNode newSelectAliasNode=NODE_FACTORY.aliasedNode(null,newColumnParseNode);
- newSelectAliasedNodes.add(newSelectAliasNode);
- }
- return newSelectAliasedNodes;
- }
-
- /**
- * check if rewrittenNewOrderByNodes is prefix of selectStatement's order by.
- * @param selectStatement
- * @param rewrittenNewOrderByNodes
- * @return
- */
- private boolean isOrderByPrefix(SelectStatement selectStatement,List<OrderByNode> rewrittenNewOrderByNodes) {
- List<OrderByNode> existingOrderByNodes=selectStatement.getOrderBy();
- if(rewrittenNewOrderByNodes.size() > existingOrderByNodes.size()) {
- return false;
- }
-
- Iterator<OrderByNode> existingOrderByNodeIter=existingOrderByNodes.iterator();
- for(OrderByNode rewrittenNewOrderByNode : rewrittenNewOrderByNodes) {
- assert existingOrderByNodeIter.hasNext();
- OrderByNode existingOrderByNode=existingOrderByNodeIter.next();
- if(!existingOrderByNode.equals(rewrittenNewOrderByNode)) {
- return false;
- }
- }
- return true;
+ return NODE_FACTORY.select(statement, statement.getWhere(), combine(rewrittenPreFilterParseNodes));
}
@Override
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/DerivedTableNode.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/DerivedTableNode.java
index d1ceb89..33b57bc 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/DerivedTableNode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/DerivedTableNode.java
@@ -54,6 +54,7 @@ public class DerivedTableNode extends TableNode {
buf.append('(');
select.toSQL(resolver, buf);
buf.append(')');
+ buf.append(" " + (this.getAlias() == null ? "" : this.getAlias()));
}
@Override
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 68954b8..0289e02 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
@@ -5284,4 +5284,222 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
}
}
}
+
+ @Test
+ public void testSortMergeJoinPushFilterThroughSortBug5105() throws Exception {
+ Connection conn = null;
+ try {
+ conn= DriverManager.getConnection(getUrl());
+
+ String tableName1="MERGE1";
+ String tableName2="MERGE2";
+
+ conn.createStatement().execute("DROP TABLE if exists "+tableName1);
+
+ String sql="CREATE TABLE IF NOT EXISTS "+tableName1+" ( "+
+ "AID INTEGER PRIMARY KEY,"+
+ "AGE INTEGER"+
+ ")";
+ conn.createStatement().execute(sql);
+
+ conn.createStatement().execute("DROP TABLE if exists "+tableName2);
+ sql="CREATE TABLE IF NOT EXISTS "+tableName2+" ( "+
+ "BID INTEGER PRIMARY KEY,"+
+ "CODE INTEGER"+
+ ")";
+ conn.createStatement().execute(sql);
+
+ //test for simple scan
+ sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from (select aid,age from "+tableName1+" where age >=11 and age<=33 order by age limit 3) a inner join "+
+ "(select bid,code from "+tableName2+" order by code limit 1) b on a.aid=b.bid where b.code > 50";
+
+ QueryPlan queryPlan=getQueryPlan(conn, sql);
+ SortMergeJoinPlan sortMergeJoinPlan=(SortMergeJoinPlan)((ClientScanPlan)queryPlan).getDelegate();
+
+ ClientScanPlan lhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getLhsPlan())).getDelegate();
+ OrderBy orderBy=lhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
+ ScanPlan innerScanPlan=(ScanPlan)((TupleProjectionPlan)lhsOuterPlan.getDelegate()).getDelegate();
+ orderBy=innerScanPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AGE"));
+ assertTrue(innerScanPlan.getLimit().intValue() == 3);
+
+ ClientScanPlan rhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getRhsPlan())).getDelegate();
+ String tableAlias = rhsOuterPlan.getTableRef().getTableAlias();
+ String rewrittenSql = "SELECT "+tableAlias+".BID,"+tableAlias+".CODE FROM (SELECT BID,CODE FROM MERGE2 ORDER BY CODE LIMIT 1) "+tableAlias+" WHERE "+tableAlias+".CODE > 50 ORDER BY "+tableAlias+".BID";
+ assertTrue(rhsOuterPlan.getStatement().toString().equals(rewrittenSql));
+
+ orderBy=rhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("BID"));
+ innerScanPlan=(ScanPlan)((TupleProjectionPlan)rhsOuterPlan.getDelegate()).getDelegate();
+ orderBy=innerScanPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("CODE"));
+ assertTrue(innerScanPlan.getLimit().intValue() == 1);
+
+ //test for aggregate
+ sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.codesum from (select aid,sum(age) agesum from "+tableName1+" where age >=11 and age<=33 group by aid order by agesum limit 3) a inner join "+
+ "(select bid,sum(code) codesum from "+tableName2+" group by bid order by codesum limit 1) b on a.aid=b.bid where b.codesum > 50";
+
+
+ queryPlan=getQueryPlan(conn, sql);
+ sortMergeJoinPlan=(SortMergeJoinPlan)((ClientScanPlan)queryPlan).getDelegate();
+
+ lhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getLhsPlan())).getDelegate();
+ orderBy=lhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
+ AggregatePlan innerAggregatePlan=(AggregatePlan)((TupleProjectionPlan)lhsOuterPlan.getDelegate()).getDelegate();
+ orderBy=innerAggregatePlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("SUM(AGE)"));
+ assertTrue(innerAggregatePlan.getLimit().intValue() == 3);
+
+ rhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getRhsPlan())).getDelegate();
+ tableAlias = rhsOuterPlan.getTableRef().getTableAlias();
+ rewrittenSql = "SELECT "+tableAlias+".BID,"+tableAlias+".CODESUM FROM (SELECT BID, SUM(CODE) CODESUM FROM MERGE2 GROUP BY BID ORDER BY CODESUM LIMIT 1) "+tableAlias+" WHERE "+tableAlias+".CODESUM > 50 ORDER BY "+tableAlias+".BID";
+ assertTrue(rhsOuterPlan.getStatement().toString().equals(rewrittenSql));
+
+ orderBy=rhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("BID"));
+ innerAggregatePlan=(AggregatePlan)((TupleProjectionPlan)rhsOuterPlan.getDelegate()).getDelegate();
+ orderBy=innerAggregatePlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("SUM(CODE)"));
+ assertTrue(innerAggregatePlan.getLimit().intValue() == 1);
+
+ String tableName3="merge3";
+ conn.createStatement().execute("DROP TABLE if exists "+tableName3);
+ sql="CREATE TABLE IF NOT EXISTS "+tableName3+" ( "+
+ "CID INTEGER PRIMARY KEY,"+
+ "REGION INTEGER"+
+ ")";
+ conn.createStatement().execute(sql);
+
+ //test for join
+ sql="select t1.aid,t1.code,t2.region from "+
+ "(select a.aid,b.code from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 order by b.code limit 3) t1 inner join "+
+ "(select a.aid,c.region from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 order by c.region desc limit 1) t2 on t1.aid=t2.aid "+
+ "where t1.code > 50";
+
+ PhoenixPreparedStatement phoenixPreparedStatement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
+ queryPlan = phoenixPreparedStatement.optimizeQuery(sql);
+ sortMergeJoinPlan=(SortMergeJoinPlan)((ClientScanPlan)queryPlan).getDelegate();
+
+ lhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getLhsPlan())).getDelegate();
+ tableAlias = lhsOuterPlan.getTableRef().getTableAlias();
+ rewrittenSql = "SELECT "+tableAlias+".AID,"+tableAlias+".CODE FROM (SELECT A.AID,B.CODE FROM MERGE1 A Inner JOIN MERGE2 B ON (A.AID = B.BID) WHERE (B.CODE >= 44 AND B.CODE <= 66) ORDER BY B.CODE LIMIT 3) "+
+ tableAlias+" WHERE "+tableAlias+".CODE > 50 ORDER BY "+tableAlias+".AID";
+ assertTrue(lhsOuterPlan.getStatement().toString().equals(rewrittenSql));
+
+ orderBy=lhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
+ innerScanPlan=(ScanPlan)((HashJoinPlan)((TupleProjectionPlan)lhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
+ orderBy=innerScanPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("B.CODE"));
+ assertTrue(innerScanPlan.getLimit().intValue() == 3);
+
+ rhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getRhsPlan())).getDelegate();
+ orderBy=rhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
+ innerScanPlan=(ScanPlan)((HashJoinPlan)((TupleProjectionPlan)rhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
+ orderBy=innerScanPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("C.REGION DESC"));
+ assertTrue(innerScanPlan.getLimit().intValue() == 1);
+
+ //test for join and aggregate
+ sql="select t1.aid,t1.codesum,t2.regionsum from "+
+ "(select a.aid,sum(b.code) codesum from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 group by a.aid order by codesum limit 3) t1 inner join "+
+ "(select a.aid,sum(c.region) regionsum from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 group by a.aid order by regionsum desc limit 2) t2 on t1.aid=t2.aid "+
+ "where t1.codesum >=40 and t2.regionsum >= 90";
+
+ phoenixPreparedStatement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
+ queryPlan = phoenixPreparedStatement.optimizeQuery(sql);
+ sortMergeJoinPlan=(SortMergeJoinPlan)((ClientScanPlan)queryPlan).getDelegate();
+
+ lhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getLhsPlan())).getDelegate();
+ tableAlias = lhsOuterPlan.getTableRef().getTableAlias();
+ rewrittenSql = "SELECT "+tableAlias+".AID,"+tableAlias+".CODESUM FROM (SELECT A.AID, SUM(B.CODE) CODESUM FROM MERGE1 A Inner JOIN MERGE2 B ON (A.AID = B.BID) WHERE (B.CODE >= 44 AND B.CODE <= 66) GROUP BY A.AID ORDER BY CODESUM LIMIT 3) "+tableAlias+
+ " WHERE "+tableAlias+".CODESUM >= 40 ORDER BY "+tableAlias+".AID";
+ assertTrue(lhsOuterPlan.getStatement().toString().equals(rewrittenSql));
+
+ orderBy=lhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
+ innerAggregatePlan=(AggregatePlan)((HashJoinPlan)((TupleProjectionPlan)lhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
+ orderBy=innerAggregatePlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("SUM(B.CODE)"));
+ assertTrue(innerAggregatePlan.getLimit().intValue() == 3);
+
+ rhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getRhsPlan())).getDelegate();
+ tableAlias = rhsOuterPlan.getTableRef().getTableAlias();
+ rewrittenSql = "SELECT "+tableAlias+".AID,"+tableAlias+".REGIONSUM FROM (SELECT A.AID, SUM(C.REGION) REGIONSUM FROM MERGE1 A Inner JOIN MERGE3 C ON (A.AID = C.CID) WHERE (C.REGION >= 77 AND C.REGION <= 99) GROUP BY A.AID ORDER BY REGIONSUM DESC LIMIT 2) "+tableAlias+
+ " WHERE "+tableAlias+".REGIONSUM >= 90 ORDER BY "+tableAlias+".AID";
+ assertTrue(rhsOuterPlan.getStatement().toString().equals(rewrittenSql));
+
+ orderBy=rhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
+ innerAggregatePlan=(AggregatePlan)((HashJoinPlan)((TupleProjectionPlan)rhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
+ orderBy=innerAggregatePlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("SUM(C.REGION) DESC"));
+ assertTrue(innerAggregatePlan.getLimit().intValue() == 2);
+
+ //test for if SubselectRewriter.isOrderByPrefix had take effect
+ sql="select t1.aid,t1.codesum,t2.regionsum from "+
+ "(select a.aid,sum(b.code) codesum from "+tableName1+" a inner join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 group by a.aid order by a.aid,codesum limit 3) t1 inner join "+
+ "(select a.aid,sum(c.region) regionsum from "+tableName1+" a inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 group by a.aid order by a.aid desc,regionsum desc limit 2) t2 on t1.aid=t2.aid "+
+ "where t1.codesum >=40 and t2.regionsum >= 90 order by t1.aid desc";
+
+ phoenixPreparedStatement = conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
+ queryPlan = phoenixPreparedStatement.optimizeQuery(sql);
+ orderBy=queryPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("T1.AID DESC"));
+ sortMergeJoinPlan=(SortMergeJoinPlan)((ClientScanPlan)queryPlan).getDelegate();
+
+ lhsOuterPlan = (ClientScanPlan)(((TupleProjectionPlan)sortMergeJoinPlan.getLhsPlan()).getDelegate());
+ tableAlias = lhsOuterPlan.getTableRef().getTableAlias();
+ rewrittenSql = "SELECT "+tableAlias+".AID,"+tableAlias+".CODESUM FROM (SELECT A.AID, SUM(B.CODE) CODESUM FROM MERGE1 A Inner JOIN MERGE2 B ON (A.AID = B.BID) WHERE (B.CODE >= 44 AND B.CODE <= 66) GROUP BY A.AID ORDER BY A.AID,CODESUM LIMIT 3) "+tableAlias+
+ " WHERE "+tableAlias+".CODESUM >= 40";
+ assertTrue(lhsOuterPlan.getStatement().toString().equals(rewrittenSql));
+
+ innerAggregatePlan=(AggregatePlan)((HashJoinPlan)((TupleProjectionPlan)lhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
+ orderBy=innerAggregatePlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 2);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("A.AID"));
+ assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("SUM(B.CODE)"));
+ assertTrue(innerAggregatePlan.getLimit().intValue() == 3);
+
+ rhsOuterPlan=(ClientScanPlan)((TupleProjectionPlan)(sortMergeJoinPlan.getRhsPlan())).getDelegate();
+ tableAlias = rhsOuterPlan.getTableRef().getTableAlias();
+ rewrittenSql = "SELECT "+tableAlias+".AID,"+tableAlias+".REGIONSUM FROM (SELECT A.AID, SUM(C.REGION) REGIONSUM FROM MERGE1 A Inner JOIN MERGE3 C ON (A.AID = C.CID) WHERE (C.REGION >= 77 AND C.REGION <= 99) GROUP BY A.AID ORDER BY A.AID DESC,REGIONSUM DESC LIMIT 2) "+tableAlias+
+ " WHERE "+tableAlias+".REGIONSUM >= 90 ORDER BY "+tableAlias+".AID";
+ assertTrue(rhsOuterPlan.getStatement().toString().equals(rewrittenSql));
+
+ orderBy=rhsOuterPlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 1);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("AID"));
+ innerAggregatePlan=(AggregatePlan)((HashJoinPlan)((TupleProjectionPlan)rhsOuterPlan.getDelegate()).getDelegate()).getDelegate();
+ orderBy=innerAggregatePlan.getOrderBy();
+ assertTrue(orderBy.getOrderByExpressions().size() == 2);
+ assertTrue(orderBy.getOrderByExpressions().get(0).toString().equals("A.AID DESC"));
+ assertTrue(orderBy.getOrderByExpressions().get(1).toString().equals("SUM(C.REGION) DESC"));
+ assertTrue(innerAggregatePlan.getLimit().intValue() == 2);
+ } finally {
+ if(conn!=null) {
+ conn.close();
+ }
+ }
+ }
}