PHOENIX-3745 SortMergeJoin might incorrectly override the OrderBy of LHS or RHS


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/2c53fc98
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/2c53fc98
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/2c53fc98

Branch: refs/heads/calcite
Commit: 2c53fc9856ba3770e742c0729cdef9b2c0181873
Parents: 2074d1f
Author: chenglei <cheng...@apache.org>
Authored: Fri Mar 31 09:58:35 2017 +0800
Committer: chenglei <cheng...@apache.org>
Committed: Fri Mar 31 09:58:35 2017 +0800

----------------------------------------------------------------------
 .../phoenix/end2end/SortMergeJoinMoreIT.java    | 135 +++++++++++++
 .../apache/phoenix/compile/JoinCompiler.java    |   6 +-
 .../phoenix/compile/SubselectRewriter.java      | 137 ++++++++++++--
 .../phoenix/exception/SQLExceptionCode.java     |   1 +
 .../phoenix/execute/DelegateQueryPlan.java      |   4 +
 .../phoenix/execute/SortMergeJoinPlan.java      |   8 +
 .../phoenix/compile/QueryCompilerTest.java      | 187 +++++++++++++++++++
 7 files changed, 466 insertions(+), 12 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/2c53fc98/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java
----------------------------------------------------------------------
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 992e55f..e61332b 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
@@ -499,4 +499,139 @@ public class SortMergeJoinMoreIT extends 
ParallelStatsDisabledIT {
             conn.close();
         }
     }
+
+    @Test
+    public void testSubQueryOrderByOverrideBug3745() throws Exception {
+        Connection conn = null;
+        try {
+            Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+            conn = DriverManager.getConnection(getUrl(), props);
+
+            String tableName1=generateUniqueName();
+            String tableName2=generateUniqueName();
+
+            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("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();
+
+            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);
+
+            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 1) b 
on a.aid=b.bid ";
+
+            ResultSet rs=conn.prepareStatement(sql).executeQuery();
+            assertTrue(rs.next());
+            assertTrue(rs.getInt(1) == 3);
+            assertTrue(rs.getInt(2) == 44);
+            assertTrue(!rs.next());
+
+            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 ";
+            rs=conn.prepareStatement(sql).executeQuery();
+            assertTrue(rs.next());
+            assertTrue(rs.getInt(1) == 2);
+            assertTrue(rs.getInt(2) == 55);
+            assertTrue(rs.next());
+            assertTrue(rs.getInt(1) == 3);
+            assertTrue(rs.getInt(2) == 44);
+            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 ";
+            rs=conn.prepareStatement(sql).executeQuery();
+            assertTrue(rs.next());
+            assertTrue(rs.getInt(1) == 2);
+            assertTrue(rs.getInt(2) == 55);
+            assertTrue(rs.next());
+            assertTrue(rs.getInt(1) == 3);
+            assertTrue(rs.getInt(2) == 44);
+            assertTrue(!rs.next());
+
+            String tableName3=generateUniqueName();;
+            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);
+
+            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";
+
+            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 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";
+
+            rs=conn.prepareStatement(sql).executeQuery();
+
+            assertTrue(rs.next());
+            assertTrue(rs.getInt(1) == 2);
+            assertTrue(rs.getInt(2) == 55);
+            assertTrue(rs.getInt(3) == 88);
+
+            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.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 
"+
+                "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();
+            }
+        }
+    }
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2c53fc98/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java 
b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
index eef604b..b1da739 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
@@ -691,7 +691,11 @@ public class JoinCompiler {
 
         public SelectStatement getAsSubquery(List<OrderByNode> orderBy) throws 
SQLException {
             if (isSubselect())
-                return 
SubselectRewriter.applyOrderBy(SubselectRewriter.applyPostFilters(subselect, 
preFilters, tableNode.getAlias()), orderBy, tableNode.getAlias());
+                return SubselectRewriter.applyOrderBy(
+                        SubselectRewriter.applyPostFilters(subselect, 
preFilters, tableNode.getAlias()),
+                        orderBy,
+                        tableNode.getAlias(),
+                        tableNode);
 
             return NODE_FACTORY.select(tableNode, select.getHint(), false, 
selectNodes, getPreFiltersCombined(), null,
                     null, orderBy, null, null, 0, false, select.hasSequence(),

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2c53fc98/phoenix-core/src/main/java/org/apache/phoenix/compile/SubselectRewriter.java
----------------------------------------------------------------------
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 db809c8..a926e06 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
@@ -19,11 +19,16 @@
 package org.apache.phoenix.compile;
 
 import java.sql.SQLException;
+import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Collections;
 import java.util.HashMap;
+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;
@@ -60,11 +65,11 @@ public class SubselectRewriter extends ParseNodeRewriter {
         return statement.getLimit() == null && (!statement.isAggregate() || 
!statement.getGroupBy().isEmpty());        
     }
     
-    public static SelectStatement applyOrderBy(SelectStatement statement, 
List<OrderByNode> orderBy, String subqueryAlias) throws SQLException {
+    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);
+        return new SubselectRewriter(null, statement.getSelect(), 
subqueryAlias).applyOrderBy(statement, orderBy, tableNode);
     }
     
     public static SelectStatement flatten(SelectStatement select, 
PhoenixConnection connection) throws SQLException {
@@ -248,17 +253,127 @@ public class SubselectRewriter extends ParseNodeRewriter 
{
         }
         return NODE_FACTORY.select(statement, statement.getWhere(), 
combine(postFiltersRewrite));
     }
-    
-    private SelectStatement applyOrderBy(SelectStatement statement, 
List<OrderByNode> orderBy) throws SQLException {
-        List<OrderByNode> orderByRewrite = Lists.<OrderByNode> 
newArrayListWithExpectedSize(orderBy.size());
-        for (OrderByNode orderByNode : orderBy) {
-            ParseNode node = orderByNode.getNode();
-            orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), 
orderByNode.isNullsLast(), orderByNode.isAscending()));
+
+    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()));
         }
-        
-        return NODE_FACTORY.select(statement, orderByRewrite);
+
+        // 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));
+        }
+
+        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;
+    }
+
     @Override
     public ParseNode visit(ColumnParseNode node) throws SQLException {
         if (node.getTableName() == null)

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2c53fc98/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java 
b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
index 1e48640..2836c45 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
@@ -87,6 +87,7 @@ public enum SQLExceptionCode {
     SUBQUERY_RETURNS_DIFFERENT_NUMBER_OF_FIELDS(216, "22016", "Sub-query must 
return the same number of fields as the left-hand-side expression of 'IN'."),
     AMBIGUOUS_JOIN_CONDITION(217, "22017", "Ambiguous or non-equi join 
condition specified. Consider using table list with where clause."),
     CONSTRAINT_VIOLATION(218, "23018", "Constraint violation."),
+    SUBQUERY_SELECT_LIST_COLUMN_MUST_HAS_ALIAS(219,"23019","Every column in 
subquery select lists must has alias when used for join."),
 
     CONCURRENT_TABLE_MUTATION(301, "23000", "Concurrent modification to 
table.", new Factory() {
         @Override

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2c53fc98/phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java 
b/phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java
index 46eec91..015b8f9 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java
@@ -137,4 +137,8 @@ public abstract class DelegateQueryPlan implements 
QueryPlan {
      public ResultIterator iterator(ParallelScanGrouper scanGrouper) throws 
SQLException {
          return iterator(scanGrouper, null);
      }
+
+    public QueryPlan getDelegate() {
+        return delegate;
+    }
 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2c53fc98/phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java 
b/phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
index 8913f3b..75bd11c 100644
--- 
a/phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
@@ -671,4 +671,12 @@ public class SortMergeJoinPlan implements QueryPlan {
     public Set<TableRef> getSourceRefs() {
         return tableRefs;
     }
+
+    public QueryPlan getLhsPlan() {
+        return lhsPlan;
+    }
+
+    public QueryPlan getRhsPlan() {
+        return rhsPlan;
+    }
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/2c53fc98/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
----------------------------------------------------------------------
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 fa270af..4bc7d2b 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
@@ -51,7 +51,12 @@ import org.apache.hadoop.hbase.util.Bytes;
 import org.apache.phoenix.compile.OrderByCompiler.OrderBy;
 import org.apache.phoenix.coprocessor.BaseScannerRegionObserver;
 import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.execute.AggregatePlan;
+import org.apache.phoenix.execute.ClientScanPlan;
 import org.apache.phoenix.execute.HashJoinPlan;
+import org.apache.phoenix.execute.ScanPlan;
+import org.apache.phoenix.execute.SortMergeJoinPlan;
+import org.apache.phoenix.execute.TupleProjectionPlan;
 import org.apache.phoenix.expression.Expression;
 import org.apache.phoenix.expression.LiteralExpression;
 import org.apache.phoenix.expression.aggregator.Aggregator;
@@ -3947,4 +3952,186 @@ public class QueryCompilerTest extends 
BaseConnectionlessQueryTest {
         queryPlan.iterator();
         return queryPlan;
     }
+
+    @Test
+    public void testSortMergeJoinSubQueryOrderByOverrideBug3745() 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 ";
+
+            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();
+            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 ";
+
+
+            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();
+            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";
+
+            PhoenixPreparedStatement phoenixPreparedStatement = 
conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
+            queryPlan = phoenixPreparedStatement.optimizeQuery(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"));
+            
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";
+
+            phoenixPreparedStatement = 
conn.prepareStatement(sql).unwrap(PhoenixPreparedStatement.class);
+            queryPlan = phoenixPreparedStatement.optimizeQuery(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"));
+            
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();
+            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 
"+
+                 "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();
+
+            
innerAggregatePlan=(AggregatePlan)((HashJoinPlan)(((TupleProjectionPlan)sortMergeJoinPlan.getLhsPlan()).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();
+            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();
+            }
+        }
+    }
 }

Reply via email to