drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jacques Nadeau (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-4539) Add support for Null Equality Joins
Date Fri, 25 Mar 2016 19:01:25 GMT

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

Jacques Nadeau commented on DRILL-4539:
---------------------------------------

Quick note, that rewrite is not sufficient to correctly plan the query without cartesian join.
To get it to run, we still have to do:

{code}
SET `planner.enable_nljoin_for_scalar_only` = false;
{code}

{code}
explain plan for select 
  t1.id, t1.name, t2.id, t2.name 
  from dfs.`/opt/data/example.json` t1
  join dfs.`/opt/data/example.json` t2
  on t1.id is not distinct from t2.id
{code}

{code}
00-00    Screen
00-01      Project(id=[$0], name=[$1], id0=[$2], name0=[$3])
00-02        Project(id=[$0], name=[$1], id0=[$2], name0=[$3])
00-03          SelectionVectorRemover
00-04            Filter(condition=[CAST(CASE(IS NULL($0), IS NULL($2), IS NULL($2), IS NULL($0),
=($0, $2))):BOOLEAN NOT NULL])
00-05              NestedLoopJoin(condition=[true], joinType=[inner])
00-07                Project(id=[$1], name=[$0])
00-09                  Scan(groupscan=[EasyGroupScan [selectionRoot=file:/opt/data/example.json,
numFiles=1, columns=[`id`, `name`], files=[file:/opt/data/example.json]]])
00-06                Project(id0=[$0], name0=[$1])
00-08                  Project(id=[$1], name=[$0])
00-10                    Scan(groupscan=[EasyGroupScan [selectionRoot=file:/opt/data/example.json,
numFiles=1, columns=[`id`, `name`], files=[file:/opt/data/example.json]]])
{code}

> 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)

Mime
View raw message