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