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