[ 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 cases: 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 cases: > 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)