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-5389) Push down PostFilter to Sub-JoinTable for SortMergeJoin and NoStarJoin
Date Fri, 12 Jul 2019 03:56:00 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-5389?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

chenglei updated PHOENIX-5389:
------------------------------
    Summary: Push down PostFilter to Sub-JoinTable for SortMergeJoin and NoStarJoin  (was:
Push down PostFilter to sub-JoinTable for SortMergeJoin and NoStarJoin)

> Push down PostFilter to Sub-JoinTable for SortMergeJoin and NoStarJoin
> ----------------------------------------------------------------------
>
>                 Key: PHOENIX-5389
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-5389
>             Project: Phoenix
>          Issue Type: Improvement
>    Affects Versions: 4.14.2
>            Reporter: chenglei
>            Assignee: chenglei
>            Priority: Major
>             Fix For: 4.15.0, 5.1.0
>
>
> Given following tables:
> {code:java}
>    create table order_table
>    (order_id varchar(15) not null primary key, 
>     customer_id varchar(10), 
>     item_id varchar(10), 
>     price integer, 
>     quantity integer, 
>     date timestamp)
>    create table item_table
>    (item_id varchar(10) not null primary key, 
>      name varchar, 
>      price integer, 
>      discount1 integer, 
>      discount2 integer, 
>      supplier_id varchar(10), 
>      description varchar)
>    create table supplier_table
>     (supplier_id varchar(10) not null primary key, 
>       name varchar, 
>       phone varchar(12), 
>       address varchar, 
>       loc_id varchar(5))
> {code}
> for following sql :
> {code:java}
> select /*+ USE_SORT_MERGE_JOIN */  o.order_id,i.item_id, s.supplier_id, s.name from 
> supplier_table s inner join item_table i on  s.supplier_id = i.supplier_id 
> inner join order_table o on  i.item_id = o.item_id 
> where  s.address = 'hai' or i.supplier_id != 'medi'
> {code}
> for SortMergeJoin,  the above sql is decomposed to: 
> {{(supplier_table s inner join item_table i on  s.supplier_id = i.supplier_id ) inner
join order_table o on  i.item_id = o.item_id }}.
> The where condition {{s.address = 'hai' or i.supplier_id != 'medi'}} could be pushed
to the first join {{(supplier_table s inner join item_table i on  s.supplier_id = i.supplier_id
)}}
> because it only references the columns of {{supplier_table}} and {{item_table}}, 
> but unfortunately, this where condition  {{s.address = 'hai' or i.supplier_id != 'medi'}}
does not pushed down to SubJoinTable and as the postFilter  current version of 



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)

Mime
View raw message