phoenix-dev mailing list archives

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

     [ https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

chenglei updated PHOENIX-3745:
------------------------------
    Description: 
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}

  was:
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}


> 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