This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 53561d2af027bbeaeef4843c7915d33b1af8fb4f Author: Chunwei Lei <[email protected]> AuthorDate: Wed Apr 10 12:20:36 2019 +0800 [CALCITE-1338] JoinProjectTransposeRule should not pull a literal up through the null-generating side of a join (Chunwei Lei) Close apache/calcite#1153 --- .../main/java/org/apache/calcite/plan/Strong.java | 32 ++++ .../rel/rules/JoinProjectTransposeRule.java | 13 ++ .../org/apache/calcite/test/RelOptRulesTest.java | 103 ++++++++++- .../org/apache/calcite/test/RelOptRulesTest.xml | 201 ++++++++++++++++++++- 4 files changed, 344 insertions(+), 5 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/plan/Strong.java b/core/src/main/java/org/apache/calcite/plan/Strong.java index 3b2b8d8..a987e7e 100644 --- a/core/src/main/java/org/apache/calcite/plan/Strong.java +++ b/core/src/main/java/org/apache/calcite/plan/Strong.java @@ -21,6 +21,7 @@ import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexUtil; +import org.apache.calcite.rex.RexVisitorImpl; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.util.ImmutableBitSet; @@ -89,6 +90,37 @@ public class Strong { return MAP.getOrDefault(kind, Policy.AS_IS); } + /** + * Returns whether a given expression is strong. + * + * <p>Examples:</p> + * <ul> + * <li>Returns true for {@code c = 1} since it returns null if and only if + * c is null + * <li>Returns false for {@code c IS NULL} since it always returns TRUE + * or FALSE + *</ul> + * + * @param e Expression + * @return true if the expression is strong, false otherwise + */ + public static boolean isStrong(RexNode e) { + final ImmutableBitSet.Builder nullColumns = ImmutableBitSet.builder(); + e.accept( + new RexVisitorImpl<Void>(true) { + public Void visitInputRef(RexInputRef inputRef) { + nullColumns.set(inputRef.getIndex()); + return super.visitInputRef(inputRef); + } + }); + return isNull(e, nullColumns.build()); + } + + /** Returns whether all expressions in a list are strong. */ + public static boolean allStrong(List<RexNode> operands) { + return operands.stream().allMatch(Strong::isStrong); + } + /** Returns whether an expression is definitely not true. */ public boolean isNotTrue(RexNode node) { switch (node.getKind()) { diff --git a/core/src/main/java/org/apache/calcite/rel/rules/JoinProjectTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/JoinProjectTransposeRule.java index 6e53ec2..4b6b613 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/JoinProjectTransposeRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/JoinProjectTransposeRule.java @@ -21,6 +21,7 @@ import org.apache.calcite.plan.RelOptRule; import org.apache.calcite.plan.RelOptRuleCall; import org.apache.calcite.plan.RelOptRuleOperand; import org.apache.calcite.plan.RelOptUtil; +import org.apache.calcite.plan.Strong; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Join; import org.apache.calcite.rel.core.JoinRelType; @@ -171,6 +172,18 @@ public class JoinProjectTransposeRule extends RelOptRule { return; } + if (includeOuter) { + if (leftProj != null && joinType.generatesNullsOnLeft() + && !Strong.allStrong(leftProj.getProjects())) { + return; + } + + if (rightProj != null && joinType.generatesNullsOnRight() + && !Strong.allStrong(rightProj.getProjects())) { + return; + } + } + // Construct two RexPrograms and combine them. The bottom program // is a join of the projection expressions from the left and/or // right projects that feed into the join. The top program contains diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index b7f21f3..bf3eb8e 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -491,7 +491,7 @@ public class RelOptRulesTest extends RelOptTestBase { checkPlanning(tester, preProgram, new HepPlanner(program), sql); } - @Test public void testJoinProjectTranspose() { + @Test public void testJoinProjectTranspose1() { final HepProgram preProgram = HepProgram.builder() .addRuleInstance(ProjectJoinTransposeRule.INSTANCE) @@ -513,6 +513,101 @@ public class RelOptRulesTest extends RelOptTestBase { } /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1338";>[CALCITE-1338] + * JoinProjectTransposeRule should not pull a literal above the + * null-generating side of a join</a>. */ + @Test public void testJoinProjectTranspose2() { + final String sql = "select *\n" + + "from dept a\n" + + "left join (select name, 1 from dept) as b\n" + + "on a.name = b.name"; + sql(sql) + .withRule(JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER) + .checkUnchanged(); + } + + /** As {@link #testJoinProjectTranspose2()}; + * should not transpose since the left project of right join has literal. */ + @Test public void testJoinProjectTranspose3() { + final String sql = "select *\n" + + "from (select name, 1 from dept) as a\n" + + "right join dept b\n" + + "on a.name = b.name"; + sql(sql) + .withRule(JoinProjectTransposeRule.LEFT_PROJECT_INCLUDE_OUTER) + .checkUnchanged(); + } + + /** As {@link #testJoinProjectTranspose2()}; + * should not transpose since the right project of left join has not-strong + * expression {@code y is not null}. */ + @Test public void testJoinProjectTranspose4() { + final String sql = "select *\n" + + "from dept a\n" + + "left join (select x name, y is not null from\n" + + "(values (2, cast(null as integer)), (2, 1)) as t(x, y)) b\n" + + "on a.name = b.name"; + sql(sql) + .withRule(JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER) + .checkUnchanged(); + } + + /** As {@link #testJoinProjectTranspose2()}; + * should not transpose since the right project of left join has not-strong + * expression {@code 1 + 1}. */ + @Test public void testJoinProjectTranspose5() { + final String sql = "select *\n" + + "from dept a\n" + + "left join (select name, 1 + 1 from dept) as b\n" + + "on a.name = b.name"; + sql(sql) + .withRule(JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER) + .checkUnchanged(); + } + + /** As {@link #testJoinProjectTranspose2()}; + * should not transpose since both the left project and right project have + * literal. */ + @Test public void testJoinProjectTranspose6() { + final String sql = "select *\n" + + "from (select name, 1 from dept) a\n" + + "full join (select name, 1 from dept) as b\n" + + "on a.name = b.name"; + sql(sql) + .withRule(JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER) + .checkUnchanged(); + } + + /** As {@link #testJoinProjectTranspose2()}; + * Should transpose since all expressions in the right project of left join + * are strong. */ + @Test public void testJoinProjectTranspose7() { + final String sql = "select *\n" + + "from dept a\n" + + "left join (select name from dept) as b\n" + + " on a.name = b.name"; + sql(sql) + .withRule(JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER) + .check(); + } + + /** As {@link #testJoinProjectTranspose2()}; + * should transpose since all expressions including + * {@code deptno > 10 and cast(null as boolean)} in the right project of left + * join are strong. */ + @Test public void testJoinProjectTranspose8() { + final String sql = "select *\n" + + "from dept a\n" + + "left join (\n" + + " select name, deptno > 10 and cast(null as boolean)\n" + + " from dept) as b\n" + + "on a.name = b.name"; + sql(sql) + .withRule(JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER) + .check(); + } + + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-889";>[CALCITE-889] * Implement SortUnionTransposeRule</a>. */ @Test public void testSortUnionTranspose() { @@ -4159,10 +4254,10 @@ public class RelOptRulesTest extends RelOptTestBase { } @Test public void testSelectNotInCorrelated() { - final String sql = "select sal, \n" + final String sql = "select sal,\n" + " empno NOT IN (\n" - + " select deptno from dept \n" - + " where emp.job=dept.name) \n" + + " select deptno from dept\n" + + " where emp.job=dept.name)\n" + " from emp"; checkSubQuery(sql).withLateDecorrelation(true).check(); } diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index ef28033..392c496 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -5778,7 +5778,7 @@ LogicalProject(NAME=[$1]) ]]> </Resource> </TestCase> - <TestCase name="testJoinProjectTranspose"> + <TestCase name="testJoinProjectTranspose1"> <Resource name="sql"> <![CDATA[select a.name from dept a @@ -5811,6 +5811,205 @@ LogicalProject(NAME=[$1]) ]]> </Resource> </TestCase> + <TestCase name="testJoinProjectTranspose2"> + <Resource name="sql"> + <![CDATA[select * +from dept a +left join (select name, 1 from dept) as b +on a.name = b.name +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) + LogicalJoin(condition=[=($1, $2)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$1], EXPR$1=[1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) + LogicalJoin(condition=[=($1, $2)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$1], EXPR$1=[1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testJoinProjectTranspose3"> + <Resource name="sql"> + <![CDATA[select * +from (select name, 1 from dept) as a +right join dept b +on a.name = b.name +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(NAME=[$0], EXPR$1=[$1], DEPTNO=[$2], NAME0=[$3]) + LogicalJoin(condition=[=($0, $3)], joinType=[right]) + LogicalProject(NAME=[$1], EXPR$1=[1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(NAME=[$0], EXPR$1=[$1], DEPTNO=[$2], NAME0=[$3]) + LogicalJoin(condition=[=($0, $3)], joinType=[right]) + LogicalProject(NAME=[$1], EXPR$1=[1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testJoinProjectTranspose4"> + <Resource name="sql"> + <![CDATA[select * +left join (select x name, y is not null from +(values (2, cast(null as integer)), (2, 1)) as t(x, y)) b +on a.name = b.name +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) + LogicalJoin(condition=[=($1, $2)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$0], EXPR$1=[IS NOT NULL($1)]) + LogicalUnion(all=[true]) + LogicalProject(EXPR$0=[2], EXPR$1=[null:INTEGER]) + LogicalValues(tuples=[[{ 0 }]]) + LogicalProject(EXPR$0=[2], EXPR$1=[1]) + LogicalValues(tuples=[[{ 0 }]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) + LogicalJoin(condition=[=($1, $2)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$0], EXPR$1=[IS NOT NULL($1)]) + LogicalUnion(all=[true]) + LogicalProject(EXPR$0=[2], EXPR$1=[null:INTEGER]) + LogicalValues(tuples=[[{ 0 }]]) + LogicalProject(EXPR$0=[2], EXPR$1=[1]) + LogicalValues(tuples=[[{ 0 }]]) +]]> + </Resource> + </TestCase> + <TestCase name="testJoinProjectTranspose5"> + <Resource name="sql"> + <![CDATA[select * +left join (select x name, y is not null from +(values (2, cast(null as integer)), (2, 1)) as t(x, y)) b +on a.name = b.name +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) + LogicalJoin(condition=[=($1, $2)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$1], EXPR$1=[+(1, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) + LogicalJoin(condition=[=($1, $2)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$1], EXPR$1=[+(1, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testJoinProjectTranspose6"> + <Resource name="sql"> + <![CDATA[select * +from (select name, 1 from dept) a +full join (select name, 1 from dept) as b +on a.name = b.name +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(NAME=[$0], EXPR$1=[$1], NAME0=[$2], EXPR$10=[$3]) + LogicalJoin(condition=[=($0, $2)], joinType=[full]) + LogicalProject(NAME=[$1], EXPR$1=[1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$1], EXPR$1=[1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(NAME=[$0], EXPR$1=[$1], NAME0=[$2], EXPR$10=[$3]) + LogicalJoin(condition=[=($0, $2)], joinType=[full]) + LogicalProject(NAME=[$1], EXPR$1=[1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$1], EXPR$1=[1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testJoinProjectTranspose7"> + <Resource name="sql"> + <![CDATA[select * +from dept a +left join (select name from dept) as b +on a.name = b.name +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2]) + LogicalJoin(condition=[=($1, $2)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2]) + LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3]) + LogicalJoin(condition=[=($1, $3)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testJoinProjectTranspose8"> + <Resource name="sql"> + <![CDATA[select * +from dept a +left join (select name, deptno > 10 and cast(null as boolean) from dept b) as b +on a.name = b.name +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) + LogicalJoin(condition=[=($1, $2)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(NAME=[$1], EXPR$1=[AND(>($0, 10), null)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) + LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[AND(>($2, 10), null)]) + LogicalJoin(condition=[=($1, $3)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> <TestCase name="testMergeFilter"> <Resource name="sql"> <