phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "chenglei (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
Date Mon, 20 Mar 2017 14:31:41 GMT

    [ 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:31 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). 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)

Mime
View raw message