[jira] [Commented] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

["chenglei (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15932724#comment-15932724 ] 

chenglei commented on PHOENIX-3745:
-----------------------------------

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). Obviously ,because there exits limit statement in subquery,we can not simply override OrderBy, and the subquery should be :

select bid,code  (select bid,code from merge2  order by code limit 1) order by bid

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    }


> 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 at 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)