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-5105) Push Filter through Sort for SortMergeJoin
Date Sun, 20 Jan 2019 12:32:00 GMT

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

Mime
View raw message