[jira] [Updated] (PHOENIX-5389) Push down PostFilter to sub-JoinTable for SortMergeJoin and NoStarJoin

[chenglei (JIRA)]

     [ 
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 i.supplier_id != 'medi' or s.address = 'hai'
{code}


  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 i.supplier_id != 'medi' or s.address = 'hai'
{code}



> 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 i.supplier_id != 'medi' or s.address = 'hai'
> {code}



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