[ https://issues.apache.org/jira/browse/PHOENIX-5105?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
chenglei updated PHOENIX-5105: ------------------------------ Description: Given two tables: {code} CREATE TABLE Merge1 ( aid INTEGER PRIMARY KEY, age INTEGER) CREATE TABLE Merge2 ( bid INTEGER PRIMARY KEY, code INTEGER) {code} for following sql : {code} select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from (select aid,age from merge1 where age >=11 and age<=33 order by age limit 3) a inner join (select bid,code from merge2 order by code limit 1) b on a.aid=b.bid where b.code > 50 {code} As the RHS of SortMergeJoin, {{(select bid,code from merge2 order by code limit 1)}} would append {{order by b.bid}} and be rewritten to {{select bid,code from (select bid,code from merge2 order by code limit 1) order by bid}} was: Given two tables: {code} CREATE TABLE Merge1 ( aid INTEGER PRIMARY KEY, age INTEGER) CREATE TABLE Merge2 ( bid INTEGER PRIMARY KEY, code INTEGER) {code} for following sql : {code} select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from (select aid,age from merge1 where age >=11 and age<=33 order by age limit 3) a inner join (select bid,code from merge2 order by code limit 1) b on a.aid=b.bid where b.code > 50 {code} As the RHS of SortMergeJoin, {{(select bid,code from merge2 order by code limit 1)}} would append {{order by b.bid}} and be rewritten to {{select bid,code from (select bid,code from merge2 order by code limit 1) order by bid }} > Push Filter through Sort for SortMergeJoin > ------------------------------------------ > > Key: PHOENIX-5105 > URL: https://issues.apache.org/jira/browse/PHOENIX-5105 > Project: Phoenix > Issue Type: Improvement > Affects Versions: 4.14.1 > Reporter: chenglei > Priority: Major > > Given two tables: > {code} > CREATE TABLE Merge1 ( > aid INTEGER PRIMARY KEY, > age INTEGER) > > CREATE TABLE Merge2 ( > bid INTEGER PRIMARY KEY, > code INTEGER) > {code} > for following sql : > {code} > select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from > (select aid,age from merge1 where age >=11 and age<=33 order by age limit 3) > a inner join > (select bid,code from merge2 order by code limit 1) b on a.aid=b.bid where > b.code > 50 > {code} > As the RHS of SortMergeJoin, {{(select bid,code from merge2 order by code > limit 1)}} would append {{order by b.bid}} and be rewritten to {{select > bid,code from (select bid,code from merge2 order by code limit 1) order by > bid}} -- This message was sent by Atlassian JIRA (v7.6.3#76005)