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 04:00:00 GMT

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

chenglei updated PHOENIX-5389:
------------------------------
    Description: 
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 join composited as: 
{{(supplier_table s inner join item_table i  ) inner join order_table}}.
The where condition {{s.address = 'hai' or i.supplier_id != 'medi'}} could be pushed to the
first join {{(supplier_table  inner join item_table)}}
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 Sub-JoinTable as expected and as the postFilter of the whole join
{{(supplier_table s inner join item_table i  ) inner join order_table}}.




  was:
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 Sub-JoinTable as expected and as the postFilter of the whole join.






> 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 join composited as: 
> {{(supplier_table s inner join item_table i  ) inner join order_table}}.
> The where condition {{s.address = 'hai' or i.supplier_id != 'medi'}} could be pushed
to the first join {{(supplier_table  inner join item_table)}}
> 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 Sub-JoinTable as expected and as the postFilter of the whole join
{{(supplier_table s inner join item_table i  ) inner join order_table}}.



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

Mime
View raw message