[ https://issues.apache.org/jira/browse/DRILL-4539?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15228833#comment-15228833 ]
ASF GitHub Bot commented on DRILL-4539: --------------------------------------- Github user amansinha100 commented on a diff in the pull request: https://github.com/apache/drill/pull/462#discussion_r58757060 --- Diff: exec/java-exec/src/test/java/org/apache/drill/TestJoinNullable.java --- @@ -407,11 +342,94 @@ public void testMergeLOJNullableBothInputsOrderedDescNullsLastVsAscNullsLast() t + " ORDER BY 1 ASC NULLS LAST ) t2 " + " USING ( key )", TEST_RES_PATH, TEST_RES_PATH); - final int expectedRecordCount = 6; + testHelper(query, 6, false, true); + } + + @Test + public void withDistinctFromJoinConditionHashJoin() throws Exception { + final String query = "SELECT * FROM " + + "cp.`jsonInput/nullableOrdered1.json` t1 JOIN " + + "cp.`jsonInput/nullableOrdered2.json` t2 " + + "ON t1.key IS NOT DISTINCT FROM t2.key AND t1.data is NOT null"; + nullEqualJoinHelper(query); + } + + @Test + public void withDistinctFromJoinConditionMergeJoin() throws Exception { + try { + test("alter session set `planner.enable_hashjoin` = false"); + final String query = "SELECT * FROM " + + "cp.`jsonInput/nullableOrdered1.json` t1 JOIN " + + "cp.`jsonInput/nullableOrdered2.json` t2 " + + "ON t1.key IS NOT DISTINCT FROM t2.key"; + nullEqualJoinHelper(query); + } finally { + test("alter session set `planner.enable_hashjoin` = true"); + } + } + + @Test + public void withNullEqualHashJoin() throws Exception { + final String query = "SELECT * FROM " + + "cp.`jsonInput/nullableOrdered1.json` t1 JOIN " + + "cp.`jsonInput/nullableOrdered2.json` t2 " + + "ON t1.key = t2.key OR (t1.key IS NULL AND t2.key IS NULL)"; + nullEqualJoinHelper(query); + } - enableJoin(false, true); - final int actualRecordCount = testSql(query); - assertEquals("Number of output rows", expectedRecordCount, actualRecordCount); + @Test + public void withNullEqualMergeJoin() throws Exception { + try { + test("alter session set `planner.enable_hashjoin` = false"); + final String query = "SELECT * FROM " + + "cp.`jsonInput/nullableOrdered1.json` t1 JOIN " + + "cp.`jsonInput/nullableOrdered2.json` t2 " + + "ON t1.key = t2.key OR (t1.key IS NULL AND t2.key IS NULL)"; + nullEqualJoinHelper(query); + } finally { + test("alter session set `planner.enable_hashjoin` = true"); + } + } + + public void nullEqualJoinHelper(final String query) throws Exception { + testBuilder() + .sqlQuery(query) + .unOrdered() + .baselineColumns("key", "data", "data0", "key0") + .baselineValues(null, "L_null_1", "R_null_1", null) + .baselineValues(null, "L_null_2", "R_null_1", null) + .baselineValues("A", "L_A_1", "R_A_1", "A") + .baselineValues("A", "L_A_2", "R_A_1", "A") + .baselineValues(null, "L_null_1", "R_null_2", null) + .baselineValues(null, "L_null_2", "R_null_2", null) + .baselineValues(null, "L_null_1", "R_null_3", null) + .baselineValues(null, "L_null_2", "R_null_3", null) + .go(); } + @Test + public void withNullEqualAdditionFilter() throws Exception { --- End diff -- Could you also do similar test with the join condition in the WHERE clause instead of ON clause ? i.e something like: SELECT * FROM t1, t2 WHERE t1.a = t2.a OR (t1.a is null and t2.a is null) For such cases, Calcite filter pushdown into join needs to be applied first. > 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)