Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Tue, 5 May 2015 13:36:32 +0000 (UTC)
From: "Jacques Nadeau (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.12781945.1426284807000.1531.1430832992047@Atlassian.JIRA>
In-Reply-To: <JIRA.12781945.1426284807000@Atlassian.JIRA>
References: <JIRA.12781945.1426284807000@Atlassian.JIRA>
 <JIRA.12781945.1426284807195@arcas>
Subject: [jira] [Updated] (DRILL-2454) Support for local join filter in
 outer joins
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Jacques Nadeau updated DRILL-2454:
----------------------------------
    Fix Version/s:     (was: 1.0.0)
                   1.2.0

> Support for local join filter in outer joins
> --------------------------------------------
>
>                 Key: DRILL-2454
>                 URL: https://issues.apache.org/jira/browse/DRILL-2454
>             Project: Apache Drill
>          Issue Type: Improvement
>          Components: Query Planning & Optimization
>            Reporter: Victoria Markman
>            Assignee: Jinfeng Ni
>             Fix For: 1.2.0
>
>
> Currently in the case of outer join, we can't have local join filter on the preserving side of the outer join. "Can not plan" exception will be thrown.
> Below you will find examples of queries that are going to fail:
> -- Join filter on the left side of the left outer join 
> {code:sql}
> select
>         count(*)
> from
>         cp.`tpch/orders.parquet` o
>                 left outer join
>         cp.`tpch/lineitem.parquet` l
> on      (o.o_orderkey = l.l_orderkey and o.o_orderkey = 1);
> {code}
> -- Join filter on the right side of the right outer join
> {code:sql}
> select
>         count(*)
> from
>         cp.`tpch/lineitem.parquet` l
>                 right outer join
>         cp.`tpch/orders.parquet` o
> on      (o.o_orderkey = l.l_orderkey and o.o_orderkey >= 1)
> where
>         o.o_orderkey = 1;
> {code}
> Full outer join with the join filter on the left side:
> {code:sql}
> select
>         count(*)
> from
>         cp.`tpch/orders.parquet` o
>                 full outer join
>         cp.`tpch/lineitem.parquet` l
> on      (o.o_orderkey = l.l_orderkey and o.o_orderkey >= 1)
> ;
> {code}
> Full outer join with the join filter on the right side:
> {code:sql}
> select
>         count(*)
> from
>         cp.`tpch/orders.parquet` o
>                 full outer join
>         cp.`tpch/lineitem.parquet` l
> on      (o.o_orderkey = l.l_orderkey and l.l_orderkey >= 1)
> ;
> {code}
> The only case that will currently work is an inner join:
> {code:sql}
> select
>         count(*)
> from
>         cp.`tpch/orders.parquet` o
>                 inner join
>         cp.`tpch/lineitem.parquet` l
> on      (o.o_orderkey = l.l_orderkey and o.o_orderkey >= 1)
> ;
> {code}
> Or outer join with the local join filter on "non-preserving side" of the outer join:
> {code:sql}
> select
>         count(*)
> from
>         cp.`tpch/orders.parquet` o
>                 left outer join
>         cp.`tpch/lineitem.parquet` l
> on      (o.o_orderkey = l.l_orderkey and l.l_orderkey >= 1)
> ;
> {code}
> As a reminder, queries with filter in the ON clause of the join condition are semantically different from those with the same condition in the WHERE clause.
> This query will return an order and its lines, if any, for order number =100:
> {code:sql}
> select  *
> from
>         cp.`tpch/orders.parquet` o
>                 left outer join
>         cp.`tpch/lineitem.parquet` l
> on      (o.o_orderkey = l.l_orderkey)
> where   o.o_orderkey = 100
> ;
> {code}
> This query will return ALL orders, but only order 100 will have any lines associated with it:
> {code:sql}
> select  *
> from
>         cp.`tpch/orders.parquet` o
>                 left outer join
>         cp.`tpch/lineitem.parquet` l
> on      (o.o_orderkey = l.l_orderkey and o.o_orderkey = 100)
> ;
> {code}


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)