Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Fri, 25 Mar 2016 18:54:25 +0000 (UTC)
From: "Zelaine Fong (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.12953416.1458857047000.52748.1458932065459@Atlassian.JIRA>
In-Reply-To: <JIRA.12953416.1458857047000@Atlassian.JIRA>
References: <JIRA.12953416.1458857047000@Atlassian.JIRA>
 <JIRA.12953416.1458857047618@arcas>
Subject: [jira] [Commented] (DRILL-4539) Add support for Null Equality Joins
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DRILL-4539?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15212245#comment-15212245 ] 

Zelaine Fong commented on DRILL-4539:
-------------------------------------

Just for clarity, the proposed rewritten syntax using IS NOT DISTINCT FROM would be:

Original Query:
select ... FROM t1, t2 WHERE t1.c1 = t2.c2 OR (t1.c1 IS NULL AND t2.c2 IS NULL)

Rewritten Query:
select ... FROM t1, t2 WHERE t1.c1 IS NOT DISTINCT FROM t2.c2

> Add support for Null Equality Joins
> -----------------------------------
>
>                 Key: DRILL-4539
>                 URL: https://issues.apache.org/jira/browse/DRILL-4539
>             Project: Apache Drill
>          Issue Type: Improvement
>            Reporter: Jacques Nadeau
>            Assignee: Venki Korukanti
>
> Tableau frequently generates queries similar to this:
> {code}
> SELECT `t0`.`city` AS `city`,
>   `t2`.`X_measure__B` AS `max_Calculation_DFIDBHHAIIECCJFDAG_ok`,
>   `t0`.`state` AS `state`,
>   `t0`.`sum_stars_ok` AS `sum_stars_ok`
> FROM (
>   SELECT `business`.`city` AS `city`,
>     `business`.`state` AS `state`,
>     SUM(`business`.`stars`) AS `sum_stars_ok`
>   FROM `mongo.academic`.`business` `business`
>   GROUP BY `business`.`city`,
>     `business`.`state`
> ) `t0`
>   INNER JOIN (
>   SELECT MAX(`t1`.`X_measure__A`) AS `X_measure__B`,
>     `t1`.`city` AS `city`,
>     `t1`.`state` AS `state`
>   FROM (
>     SELECT `business`.`city` AS `city`,
>       `business`.`state` AS `state`,
>       `business`.`business_id` AS `business_id`,
>       SUM(`business`.`stars`) AS `X_measure__A`
>     FROM `mongo.academic`.`business` `business`
>     GROUP BY `business`.`city`,
>       `business`.`state`,
>       `business`.`business_id`
>   ) `t1`
>   GROUP BY `t1`.`city`,
>     `t1`.`state`
> ) `t2` ON (((`t0`.`city` = `t2`.`city`) OR ((`t0`.`city` IS NULL) AND (`t2`.`city` IS NULL))) AND ((`t0`.`state` = `t2`.`state`) OR ((`t0`.`state` IS NULL) AND (`t2`.`state` IS NULL))))
> {code}
> If you look at the join condition, you'll note that the join condition is an equality condition which also allows null=null. We should add a planning rewrite rule and execution join option to allow null equality so that we don't treat this as a cartesian join.


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