PHOENIX-5105 Push Filter through Sort for SortMergeJoin
authorchenglei <chenglei@apache.org>
Tue, 5 Feb 2019 02:32:39 +0000 (10:32 +0800)
committerchenglei <chenglei@apache.org>
Tue, 5 Feb 2019 02:32:39 +0000 (10:32 +0800)
phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java
phoenix-core/src/main/java/org/apache/phoenix/parse/DerivedTableNode.java
phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java

index dea349a..2db8e85 100644 (file)
@@ -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();
+            }
+        }
+    }
 }
index da58fcc..ab9eac4 100644 (file)
@@ -358,11 +358,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);
         }
 
@@ -755,18 +752,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());
index 6e36158..15cdc10 100644 (file)
@@ -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()) {
index af19ed1..468bd1f 100644 (file)
@@ -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
index d1ceb89..33b57bc 100644 (file)
@@ -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
index e307217..2c438a6 100644 (file)
@@ -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();
+            }
+        }
+    }
 }