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 15:36:41 GMT

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

chenglei edited comment on PHOENIX-3745 at 3/20/17 3:35 PM:
------------------------------------------------------------

I uploaded my first patch,the patch fixs this issue in mainly two aspects:
(1) convert the sql "(select bid,code from merge2 order by code limit 1)"  to   
 "(select bid,code from (select bid,code from merge2 order by code limit 1) order by bid)"
when there is limit statement in subquery.
(2) a simple optimization: if the sql is "(select bid,code from merge2 order by bid, code
limit 1)" , because "order by bid" is the prefix of "order by bid, code",there is no need
to modify the sql.


was (Author: comnetwork):
I uploaded my first patch,the patch fixs this issue in mainly two aspects:
(1) convert the sql "(select bid,code from merge2 order by code limit 1)"  to 
 "select bid,code (select bid,code from merge2 order by code limit 1) order by bid" when there
is limit statement in subquery.
(2) a simple optimization: if the sql is "(select bid,code from merge2 order by bid, code
limit 1)" , because "order by bid" is the prefix of "order by bid, code",there is no need
to modify the sql.

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

Mime
View raw message