DRILL-1337 : Fix incorrect query result for TPCH Q13. Stop pulling up local filter from an outer join condition.
Project: http://git-wip-us.apache.org/repos/asf/incubator-drill/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-drill/commit/746a0c7a Tree: http://git-wip-us.apache.org/repos/asf/incubator-drill/tree/746a0c7a Diff: http://git-wip-us.apache.org/repos/asf/incubator-drill/diff/746a0c7a Branch: refs/heads/master Commit: 746a0c7a6a151cfe97d7f69a4b3395eaf6b5e4b8 Parents: 4f59d4f Author: Jinfeng Ni <j...@maprtech.com> Authored: Mon Aug 25 17:08:49 2014 -0700 Committer: Jacques Nadeau <jacq...@apache.org> Committed: Wed Aug 27 13:33:47 2014 -0700 ---------------------------------------------------------------------- .../drill/exec/planner/logical/DrillJoinRule.java | 5 ++++- .../test/java/org/apache/drill/TestBugFixes.java | 15 +++++++++++++++ .../java/org/apache/drill/TestExampleQueries.java | 16 ++++++++-------- 3 files changed, 27 insertions(+), 9 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/746a0c7a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillJoinRule.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillJoinRule.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillJoinRule.java index b861897..cf15411 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillJoinRule.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillJoinRule.java @@ -22,6 +22,7 @@ import java.util.logging.Logger; import org.eigenbase.rel.InvalidRelException; import org.eigenbase.rel.JoinRel; +import org.eigenbase.rel.JoinRelType; import org.eigenbase.rel.RelNode; import org.eigenbase.relopt.Convention; import org.eigenbase.relopt.RelOptRule; @@ -73,8 +74,10 @@ public class DrillJoinRule extends RelOptRule { // If the join involves equijoins and non-equijoins, then we can process the non-equijoins through // a filter right after the join + // DRILL-1337: We can only pull up a non-equivjoin filter for INNER join. + // For OUTER join, pulling up a non-eqivjoin filter will lead to incorrectly discarding qualified rows. if (! remaining.isAlwaysTrue()) { - if (hasEquijoins) { + if (hasEquijoins && join.getJoinType()== JoinRelType.INNER) { addFilter = true; List<RexNode> equijoinList = Lists.newArrayList(); List<RelDataTypeField> leftTypes = convertedLeft.getRowType().getFieldList(); http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/746a0c7a/exec/java-exec/src/test/java/org/apache/drill/TestBugFixes.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestBugFixes.java b/exec/java-exec/src/test/java/org/apache/drill/TestBugFixes.java index e6d698a..743aeae 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/TestBugFixes.java +++ b/exec/java-exec/src/test/java/org/apache/drill/TestBugFixes.java @@ -17,6 +17,7 @@ */ package org.apache.drill; +import org.apache.drill.exec.rpc.RpcException; import org.junit.Ignore; import org.junit.Test; @@ -60,4 +61,18 @@ public class TestBugFixes extends BaseTestQuery { String query = "select sum(cast(employee_id as decimal(38, 18))), avg(cast(employee_id as decimal(38, 18))) from cp.`employee.json` group by (department_id)"; test(query); } + + + @Test (expected = RpcException.class) + // Should be "Failure while parsing sql. Node [rel#26:Subset#6.LOGICAL.ANY([]).[]] could not be implemented;". + // Drill will hit CanNotPlan, until we add code fix to transform the local LHS filter in left outer join properly. + public void testDRILL1337_LocalLeftFilterLeftOutJoin() throws Exception { + test("select count(*) from cp.`tpch/nation.parquet` n left outer join cp.`tpch/region.parquet` r on n.n_regionkey = r.r_regionkey and n.n_nationkey > 10;"); + } + + @Test + public void testDRILL1337_LocalRightFilterLeftOutJoin() throws Exception { + test("select * from cp.`tpch/nation.parquet` n left outer join cp.`tpch/region.parquet` r on n.n_regionkey = r.r_regionkey and r.r_name not like '%ASIA' order by r.r_name;"); + } + } http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/746a0c7a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java b/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java index 0053388..57d4043 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java +++ b/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java @@ -148,20 +148,20 @@ public class TestExampleQueries extends BaseTestQuery{ @Test public void testPushExpInJoinConditionLeftJoin() throws Exception { - test("select a.n_nationkey from cp.`tpch/nation.parquet` a left join cp.`tpch/region.parquet` b " + "" + + test("select a.n_nationkey, b.r_regionkey from cp.`tpch/nation.parquet` a left join cp.`tpch/region.parquet` b " + "" + " on a.n_regionkey +100 = b.r_regionkey +200 " + // expressions in both sides of equal join filter - " and (substr(a.n_name,1,3)= 'L1' or substr(a.n_name,2,2) = 'L2') " + // left filter - " and (substr(b.r_name,1,3)= 'R1' or substr(b.r_name,2,2) = 'R2') " + // right filter - " and (substr(a.n_name,2,3)= 'L3' or substr(b.r_name,3,2) = 'R3');"); // non-equal join filter + // " and (substr(a.n_name,1,3)= 'L1' or substr(a.n_name,2,2) = 'L2') " + // left filter + " and (substr(b.r_name,1,3)= 'R1' or substr(b.r_name,2,2) = 'R2') ") ; // right filter + // " and (substr(a.n_name,2,3)= 'L3' or substr(b.r_name,3,2) = 'R3');"); // non-equal join filter } @Test public void testPushExpInJoinConditionRightJoin() throws Exception { - test("select a.n_nationkey from cp.`tpch/nation.parquet` a right join cp.`tpch/region.parquet` b " + "" + + test("select a.n_nationkey, b.r_regionkey from cp.`tpch/nation.parquet` a right join cp.`tpch/region.parquet` b " + "" + " on a.n_regionkey +100 = b.r_regionkey +200 " + // expressions in both sides of equal join filter - " and (substr(a.n_name,1,3)= 'L1' or substr(a.n_name,2,2) = 'L2') " + // left filter - " and (substr(b.r_name,1,3)= 'R1' or substr(b.r_name,2,2) = 'R2') " + // right filter - " and (substr(a.n_name,2,3)= 'L3' or substr(b.r_name,3,2) = 'R3');"); // non-equal join filter + " and (substr(a.n_name,1,3)= 'L1' or substr(a.n_name,2,2) = 'L2') "); // left filter + // " and (substr(b.r_name,1,3)= 'R1' or substr(b.r_name,2,2) = 'R2') " + // right filter + // " and (substr(a.n_name,2,3)= 'L3' or substr(b.r_name,3,2) = 'R3');"); // non-equal join filter } @Test
