phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From cheng...@apache.org
Subject phoenix git commit: PHOENIX-3745 SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
Date Fri, 31 Mar 2017 02:02:43 GMT
Repository: phoenix
Updated Branches:
  refs/heads/4.x-HBase-0.98 7ae02f095 -> 12fd0fa46


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/12fd0fa4
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/12fd0fa4
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/12fd0fa4

Branch: refs/heads/4.x-HBase-0.98
Commit: 12fd0fa4687e1d2a241dcbbe81c844437da01771
Parents: 7ae02f0
Author: chenglei <chenglei@apache.org>
Authored: Fri Mar 31 10:01:55 2017 +0800
Committer: chenglei <chenglei@apache.org>
Committed: Fri Mar 31 10:01:55 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/12fd0fa4/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/12fd0fa4/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/12fd0fa4/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 1def3a7..4f787d0 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;
@@ -59,11 +64,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 {
@@ -242,17 +247,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/12fd0fa4/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/12fd0fa4/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/12fd0fa4/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/12fd0fa4/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();
+            }
+        }
+    }
 }


Mime
View raw message