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(); + } + } + } }
