[
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15932836#comment-15932836
]
chenglei commented on PHOENIX-3745:
-----------------------------------
I uploaded my first patch,the patch fix 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)
