[ https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15932724#comment-15932724 ]
chenglei edited comment on PHOENIX-3745 at 3/20/17 2:35 PM: ------------------------------------------------------------ The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the RHS subquery's OrderBy. When we look into following line 421 in QueryCompiler.compileJoinQuery method,we can see QueryCompiler.compileJoinQuery method is invoked to compile the above test case's RHS subquery: (select bid,code from merge2 order by code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by b.bid asc. {code} 420 Scan rhsScan = ScanUtil.newScan(originalScan); 421 StatementContext rhsCtx = new StatementContext(statement, context.getResolver(), rhsScan, new SequenceManager(statement)); 422 QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, true, rhsOrderBy); {code} QueryCompiler.compileJoinQuery then invokes following SubselectRewriter.applyOrderBy to apply the OrderBy which is "order by b.bid asc" to RHS subquery statement which is (select bid,code from merge2 order by code limit 1), in line 259 we can see "order by code" in subquery is overrided by "order by b.bid", and the subquery becomes: (select bid,code from merge2 order by bid limit 1). {code} 252 private SelectStatement applyOrderBy(SelectStatement statement, List<OrderByNode> orderBy) throws SQLException { 253 List<OrderByNode> orderByRewrite = Lists.<OrderByNode> newArrayListWithExpectedSize(orderBy.size()); 254 for (OrderByNode orderByNode : orderBy) { 255 ParseNode node = orderByNode.getNode(); 256 orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), orderByNode.isNullsLast(), orderByNode.isAscending())); 257 } 258 259 return NODE_FACTORY.select(statement, orderByRewrite); 260 } {code} Obviously ,because there is limit statement in subquery,we can not simply override OrderBy in subquery, and the subquery should be : select bid,code (select bid,code from merge2 order by code limit 1) order by bid was (Author: comnetwork): The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the RHS subquery's OrderBy. When we look into following line 421 in QueryCompiler.compileJoinQuery method,we can see QueryCompiler.compileJoinQuery method is invoked to compile the above test case's RHS subquery: (select bid,code from merge2 order by code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by b.bid asc. {code} 420 Scan rhsScan = ScanUtil.newScan(originalScan); 421 StatementContext rhsCtx = new StatementContext(statement, context.getResolver(), rhsScan, new SequenceManager(statement)); 422 QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, true, rhsOrderBy); {code} QueryCompiler.compileJoinQuery then invokes following SubselectRewriter.applyOrderBy to apply the OrderBy which is order by b.bid asc to RHS subquery statement which is (select bid,code from merge2 order by code limit 1), in line 259 we can see "order by code" in subquery is overrided by "order by b.bid", and the subquery becomes: (select bid,code from merge2 order by bid limit 1). {code} 252 private SelectStatement applyOrderBy(SelectStatement statement, List<OrderByNode> orderBy) throws SQLException { 253 List<OrderByNode> orderByRewrite = Lists.<OrderByNode> newArrayListWithExpectedSize(orderBy.size()); 254 for (OrderByNode orderByNode : orderBy) { 255 ParseNode node = orderByNode.getNode(); 256 orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), orderByNode.isNullsLast(), orderByNode.isAscending())); 257 } 258 259 return NODE_FACTORY.select(statement, orderByRewrite); 260 } {code} Obviously ,because there is limit statement in subquery,we can not simply override OrderBy in subquery, and the subquery should be : select bid,code (select bid,code from merge2 order by code limit 1) order by bid > SortMergeJoin might incorrectly override the OrderBy of LHS or RHS > ------------------------------------------------------------------ > > Key: PHOENIX-3745 > URL: https://issues.apache.org/jira/browse/PHOENIX-3745 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.9.0 > Reporter: chenglei > > Let us look a simple test case: > h4. 1. Create two tables > {noformat} > CREATE TABLE IF NOT EXISTS MERGE1 ( > AID INTEGER PRIMARY KEY > AGE INTEGER > ); > CREATE TABLE IF NOT EXISTS MERGE2 ( > BID INTEGER PRIMARY KEY, > CODE INTEGER > ); > {noformat} > h4. 2. Upsert values > {noformat} > UPSERT INTO MERGE1(AID,AGE) VALUES (1,11); > UPSERT INTO MERGE1(AID,AGE) VALUES (2,22); > UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33); > UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66); > UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55); > UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44); > {noformat} > h4. 3. Execute query > {noformat} > select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from > (select aid,age from merge1 where age >=11 and age<=33) a inner > join > (select bid,code from merge2 order by code limit 1) b on > a.aid=b.bid > {noformat} > h4. (/) Expected result > {noformat} > 3,44 > {noformat} > h4. (!) Incorrect actual result > {noformat} > 1,66 > {noformat} -- This message was sent by Atlassian JIRA (v6.3.15#6346)