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