You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ma...@apache.org on 2017/04/06 17:32:10 UTC
[4/7] phoenix git commit: PHOENIX-3745 SortMergeJoin might
incorrectly override the OrderBy of LHS or RHS
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 <ch...@apache.org>
Authored: Fri Mar 31 09:58:35 2017 +0800
Committer: chenglei <ch...@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();
+ }
+ }
+ }
}