You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/04/15 03:37:24 UTC
[calcite] 01/02: [CALCITE-1338] JoinProjectTransposeRule should not
pull a literal up through the null-generating side of a join (Chunwei Lei)
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 <ch...@alibaba-inc.com>
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">
<![CDATA[select name from (