This is an automated email from the ASF dual-hosted git repository.
hyuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new c9adf94 [CALCITE-3363] JoinUnionTransposeRule.RIGHT_UNION should not
match SEMI/ANTI Join (Jin Xing)
c9adf94 is described below
commit c9adf94b0e07f2e9108ef4d1f2ee28c3e42063b3
Author: jinxing <[email protected]>
AuthorDate: Thu Sep 19 12:27:17 2019 +0800
[CALCITE-3363] JoinUnionTransposeRule.RIGHT_UNION should not match
SEMI/ANTI Join (Jin Xing)
Close apache/calcite#1466
---
.../calcite/rel/rules/JoinUnionTransposeRule.java | 3 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 94 ++++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 66 +++++++++++++++
3 files changed, 162 insertions(+), 1 deletion(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/JoinUnionTransposeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/JoinUnionTransposeRule.java
index 72be74a..fbf7651 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/JoinUnionTransposeRule.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/JoinUnionTransposeRule.java
@@ -92,7 +92,8 @@ public class JoinUnionTransposeRule extends RelOptRule {
return;
}
} else {
- if (join.getJoinType().generatesNullsOnRight()) {
+ if (join.getJoinType().generatesNullsOnRight()
+ || !join.getJoinType().projectsRight()) {
return;
}
}
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 4720fca..de55728 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -1779,6 +1779,100 @@ public class RelOptRulesTest extends RelOptTestBase {
+ "(select * from emp e1 union all select * from emp e2) r2");
}
+ @Test public void testPushJoinThroughUnionOnRightDoesNotMatchSemiJoin() {
+ final RelBuilder builder =
RelBuilder.create(RelBuilderTest.config().build());
+
+ // build a rel equivalent to sql:
+ // select r1.sal from
+ // emp r1 where r1.deptno in
+ // (select deptno from dept d1 where deptno > 100
+ // union all
+ // select deptno from dept d2 where deptno > 20)
+ RelNode left = builder.scan("EMP").build();
+ RelNode right = builder
+ .scan("DEPT")
+ .filter(
+ builder.call(SqlStdOperatorTable.GREATER_THAN,
+ builder.field("DEPTNO"),
+ builder.literal(100)))
+ .project(builder.field("DEPTNO"))
+ .scan("DEPT")
+ .filter(
+ builder.call(SqlStdOperatorTable.GREATER_THAN,
+ builder.field("DEPTNO"),
+ builder.literal(20)))
+ .project(builder.field("DEPTNO"))
+ .union(true)
+ .build();
+ RelNode relNode = builder.push(left).push(right)
+ .semiJoin(
+ builder.call(SqlStdOperatorTable.EQUALS,
+ builder.field(2, 0, "DEPTNO"),
+ builder.field(2, 1, "DEPTNO")))
+ .project(builder.field("SAL"))
+ .build();
+
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(JoinUnionTransposeRule.RIGHT_UNION)
+ .build();
+
+ HepPlanner hepPlanner = new HepPlanner(program);
+ hepPlanner.setRoot(relNode);
+ RelNode output = hepPlanner.findBestExp();
+
+ final String planAfter = NL + RelOptUtil.toString(output);
+ final DiffRepository diffRepos = getDiffRepos();
+ diffRepos.assertEquals("planAfter", "${planAfter}", planAfter);
+ SqlToRelTestBase.assertValid(output);
+ }
+
+ @Test public void testPushJoinThroughUnionOnRightDoesNotMatchAntiJoin() {
+ final RelBuilder builder =
RelBuilder.create(RelBuilderTest.config().build());
+
+ // build a rel equivalent to sql:
+ // select r1.sal from
+ // emp r1 where r1.deptno not in
+ // (select deptno from dept d1 where deptno < 10
+ // union all
+ // select deptno from dept d2 where deptno > 20)
+ RelNode left = builder.scan("EMP").build();
+ RelNode right = builder
+ .scan("DEPT")
+ .filter(
+ builder.call(SqlStdOperatorTable.LESS_THAN,
+ builder.field("DEPTNO"),
+ builder.literal(10)))
+ .project(builder.field("DEPTNO"))
+ .scan("DEPT")
+ .filter(
+ builder.call(SqlStdOperatorTable.GREATER_THAN,
+ builder.field("DEPTNO"),
+ builder.literal(20)))
+ .project(builder.field("DEPTNO"))
+ .union(true)
+ .build();
+ RelNode relNode = builder.push(left).push(right)
+ .antiJoin(
+ builder.call(SqlStdOperatorTable.EQUALS,
+ builder.field(2, 0, "DEPTNO"),
+ builder.field(2, 1, "DEPTNO")))
+ .project(builder.field("SAL"))
+ .build();
+
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(JoinUnionTransposeRule.RIGHT_UNION)
+ .build();
+
+ HepPlanner hepPlanner = new HepPlanner(program);
+ hepPlanner.setRoot(relNode);
+ RelNode output = hepPlanner.findBestExp();
+
+ final String planAfter = NL + RelOptUtil.toString(output);
+ final DiffRepository diffRepos = getDiffRepos();
+ diffRepos.assertEquals("planAfter", "${planAfter}", planAfter);
+ SqlToRelTestBase.assertValid(output);
+ }
+
@Ignore("cycles")
@Test public void testMergeFilterWithJoinCondition() throws Exception {
HepProgram program = new HepProgramBuilder()
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 4762ecb..4389093 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2076,6 +2076,72 @@ LogicalProject(SAL=[$5])
]]>
</Resource>
</TestCase>
+ <TestCase name="testPushJoinThroughUnionOnRightDoesNotMatchSemiJoin">
+ <Resource name="sql">
+ <![CDATA[select r1.sal from emp r1 where r1.deptno in (select
deptno from dept d1 where deptno > 100 union all select deptno from dept d2
where deptno > 20)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$5])
+ LogicalJoin(condition=[=($7, $8)], joinType=[semi])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalUnion(all=[true])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[>($0, 100)])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[>($0, 20)])
+ LogicalTableScan(table=[[scott, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$5])
+ LogicalJoin(condition=[=($7, $8)], joinType=[semi])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalUnion(all=[true])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[>($0, 100)])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[>($0, 20)])
+ LogicalTableScan(table=[[scott, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPushJoinThroughUnionOnRightDoesNotMatchAntiJoin">
+ <Resource name="sql">
+ <![CDATA[select r1.sal from emp r1 where r1.deptno not in (select
deptno from dept d1 where deptno < 10 union all select deptno from dept d2
where deptno > 20)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(SAL=[$5])
+ LogicalJoin(condition=[=($7, $8)], joinType=[anti])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalUnion(all=[true])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[<($0, 10)])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[>($0, 20)])
+ LogicalTableScan(table=[[scott, DEPT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(SAL=[$5])
+ LogicalJoin(condition=[=($7, $8)], joinType=[anti])
+ LogicalTableScan(table=[[scott, EMP]])
+ LogicalUnion(all=[true])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[<($0, 10)])
+ LogicalTableScan(table=[[scott, DEPT]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalFilter(condition=[>($0, 20)])
+ LogicalTableScan(table=[[scott, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testReduceConstants">
<Resource name="sql">
<![CDATA[select 1+2, d.deptno+(3+4), (5+6)+d.deptno, cast(null as
integer), coalesce(2,null), row(7+8) from dept d inner join emp e on d.deptno =
e.deptno + (5-5) where d.deptno=(7+8) and d.deptno=(8+7) and
d.deptno=coalesce(2,null)]]>
