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 2018/04/26 05:44:31 UTC
[3/3] calcite git commit: [CALCITE-2275] Do not push down NOT
condition in JOIN (Vitalii Diravka)
[CALCITE-2275] Do not push down NOT condition in JOIN (Vitalii Diravka)
Also, refactor two for loops into one.
Close apache/calcite#675
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/e78f8c61
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/e78f8c61
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/e78f8c61
Branch: refs/heads/master
Commit: e78f8c61e3ed9c9637a2061db426680b1b3ad042
Parents: b0f4704
Author: Vitalii Diravka <vi...@gmail.com>
Authored: Mon Apr 23 21:01:41 2018 +0300
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Apr 25 22:43:43 2018 -0700
----------------------------------------------------------------------
.../org/apache/calcite/plan/RelOptUtil.java | 1 +
.../calcite/rel/metadata/RelMdPredicates.java | 38 +++++-----
.../apache/calcite/test/RelOptRulesTest.java | 28 ++++---
.../org/apache/calcite/test/RelOptRulesTest.xml | 78 +++++++++++++-------
4 files changed, 87 insertions(+), 58 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/e78f8c61/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
index beaa2c1..b4d666b 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
@@ -3358,6 +3358,7 @@ public abstract class RelOptUtil {
case OR:
case INPUT_REF:
case LITERAL:
+ case NOT:
return node;
default:
final ImmutableBitSet bits = RelOptUtil.InputFinder.bits(node);
http://git-wip-us.apache.org/repos/asf/calcite/blob/e78f8c61/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java
index 2c69ed5..02f6bda 100644
--- a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java
+++ b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java
@@ -186,28 +186,13 @@ public class RelMdPredicates
input.getRowType().getFieldCount(),
project.getRowType().getFieldCount());
- for (Ord<RexNode> o : Ord.zip(project.getProjects())) {
- if (o.e instanceof RexInputRef) {
- int sIdx = ((RexInputRef) o.e).getIndex();
- m.set(sIdx, o.i);
- columnsMappedBuilder.set(sIdx);
- }
- }
-
- // Go over childPullUpPredicates. If a predicate only contains columns in
- // 'columnsMapped' construct a new predicate based on mapping.
- final ImmutableBitSet columnsMapped = columnsMappedBuilder.build();
- for (RexNode r : inputInfo.pulledUpPredicates) {
- RexNode r2 = projectPredicate(rexBuilder, input, r, columnsMapped);
- if (!r2.isAlwaysTrue()) {
- r2 = r2.accept(new RexPermuteInputsShuttle(m, input));
- projectPullUpPredicates.add(r2);
- }
- }
-
- // Project can also generate constants. We need to include them.
for (Ord<RexNode> expr : Ord.zip(project.getProjects())) {
- if (RexLiteral.isNullLiteral(expr.e)) {
+ if (expr.e instanceof RexInputRef) {
+ int sIdx = ((RexInputRef) expr.e).getIndex();
+ m.set(sIdx, expr.i);
+ columnsMappedBuilder.set(sIdx);
+ // Project can also generate constants. We need to include them.
+ } else if (RexLiteral.isNullLiteral(expr.e)) {
projectPullUpPredicates.add(
rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL,
rexBuilder.makeInputRef(project, expr.i)));
@@ -221,6 +206,17 @@ public class RelMdPredicates
projectPullUpPredicates.add(rexBuilder.makeCall(op, args));
}
}
+
+ // Go over childPullUpPredicates. If a predicate only contains columns in
+ // 'columnsMapped' construct a new predicate based on mapping.
+ final ImmutableBitSet columnsMapped = columnsMappedBuilder.build();
+ for (RexNode r : inputInfo.pulledUpPredicates) {
+ RexNode r2 = projectPredicate(rexBuilder, input, r, columnsMapped);
+ if (!r2.isAlwaysTrue()) {
+ r2 = r2.accept(new RexPermuteInputsShuttle(m, input));
+ projectPullUpPredicates.add(r2);
+ }
+ }
return RelOptPredicateList.of(rexBuilder, projectPullUpPredicates);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/e78f8c61/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
----------------------------------------------------------------------
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 d65a317..e86ecdc 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3047,36 +3047,44 @@ public class RelOptRulesTest extends RelOptTestBase {
.addRuleInstance(JoinPushTransitivePredicatesRule.INSTANCE)
.build();
- final HepPlanner hepPlanner =
- new HepPlanner(new HepProgramBuilder().build());
+ final HepProgram emptyProgram = new HepProgramBuilder().build();
final String sql = "select d.deptno from sales.emp d where d.deptno\n"
+ "IN (select e.deptno from sales.emp e "
+ "where e.deptno = d.deptno or e.deptno = 4)";
- sql(sql).withPre(preProgram).with(hepPlanner).checkUnchanged();
+ sql(sql).withPre(preProgram).with(emptyProgram).checkUnchanged();
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-2205">[CALCITE-2205]
* One more infinite loop for JoinPushTransitivePredicatesRule</a>. */
@Test public void testJoinPushTransitivePredicatesRule2() {
- HepProgram hepProgram = new HepProgramBuilder()
+ HepProgram program = new HepProgramBuilder()
.addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
.addRuleInstance(FilterJoinRule.JOIN)
.addRuleInstance(JoinPushTransitivePredicatesRule.INSTANCE)
.build();
- HepPlanner hepPlanner = new HepPlanner(hepProgram);
-
final String sql = "select n1.SAL\n"
+ "from EMPNULLABLES_20 n1\n"
+ "where n1.SAL IN (\n"
+ " select n2.SAL\n"
+ " from EMPNULLABLES_20 n2\n"
+ " where n1.SAL = n2.SAL or n1.SAL = 4)";
- sql(sql)
- .withDecorrelation(true)
- .with(hepPlanner)
- .check();
+ sql(sql).withDecorrelation(true).with(program).check();
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-2275">[CALCITE-2275]
+ * JoinPushTransitivePredicatesRule wrongly pushes down NOT condition</a>. */
+ @Test public void testInferringPredicatesWithNotOperatorInJoinCondition() {
+ HepProgram program = new HepProgramBuilder()
+ .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
+ .addRuleInstance(FilterJoinRule.JOIN)
+ .addRuleInstance(JoinPushTransitivePredicatesRule.INSTANCE)
+ .build();
+ final String sql = "select * from sales.emp d\n"
+ + "join sales.emp e on e.deptno = d.deptno and d.deptno not in (4, 6)";
+ sql(sql).withDecorrelation(true).with(program).check();
}
/** Test case for
http://git-wip-us.apache.org/repos/asf/calcite/blob/e78f8c61/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
----------------------------------------------------------------------
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 77ddbd3..abecad9 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2247,8 +2247,8 @@ LogicalProject(MGR=[CAST(10):INTEGER])
<TestCase name="testEmptyJoin">
<Resource name="sql">
<![CDATA[select * from (
-select * from emp where false)
-join dept using (deptno)]]>
+select * from emp where false) as e
+join dept as d on e.deptno = d.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -2270,8 +2270,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<TestCase name="testEmptyJoinRight">
<Resource name="sql">
<![CDATA[select * from (
-select * from emp where false)
-right join dept using (deptno)]]>
+ select * from emp where false) e
+right join dept d on e.deptno = d.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -2295,8 +2295,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<TestCase name="testEmptyJoinLeft">
<Resource name="sql">
<![CDATA[select * from (
-select * from emp where false)
-left join dept using (deptno)]]>
+ select * from emp where false) e
+left join dept d on e.deptno = d.deptno]]>
</Resource>
<Resource name="planBefore">
<![CDATA[
@@ -2538,6 +2538,44 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
]]>
</Resource>
</TestCase>
+ <TestCase name="testFilterRemoveIsNotDistinctFromRule">
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalFilter(condition=[IS NOT DISTINCT FROM($7, 20)])
+ LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalFilter(condition=[CASE(IS NULL($7), IS NULL(20), =(CAST($7):TINYINT NOT NULL, 20))])
+ LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testInferringPredicatesWithNotOperatorInJoinCondition">
+ <Resource name="sql">
+ <![CDATA[select * from sales.emp d
+join sales.emp e on e.deptno = d.deptno and d.deptno not in (4, 6)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10], JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15], DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalJoin(condition=[AND(=($16, $7), NOT(OR(=($7, 4), =($7, 6))))], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10], JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15], DEPTNO0=[$16], SLACKER0=[$17])
+ LogicalJoin(condition=[=($16, $7)], joinType=[inner])
+ LogicalFilter(condition=[AND(<>($7, 4), <>($7, 6))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[AND(<>($7, 4), <>($7, 6))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testIntersectToDistinct">
<Resource name="sql">
<![CDATA[select * from emp where deptno = 10
@@ -5039,7 +5077,7 @@ LogicalProject(EMPNO=[10], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<TestCase name="testStrengthenJoinType">
<Resource name="sql">
<![CDATA[select *
-from dept left join emp using (deptno)
+from dept left join emp on dept.deptno = emp.deptno
where emp.deptno is not null and emp.sal > 100]]>
</Resource>
<Resource name="planBefore">
@@ -6736,7 +6774,7 @@ LogicalProject(DEPTNO=[$0], NAME=[$1])
<TestCase name="testSortJoinTranspose1">
<Resource name="sql">
<![CDATA[select * from sales.emp e left join (
-select * from sales.dept d) using (deptno)
+ select * from sales.dept d) d on e.deptno = d.deptno
order by sal limit 10]]>
</Resource>
<Resource name="planBefore">
@@ -6832,7 +6870,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<TestCase name="testSortJoinTranspose2">
<Resource name="sql">
<![CDATA[select * from sales.emp e right join (
-select * from sales.dept d) using (deptno)
+ select * from sales.dept d) d on e.deptno = d.deptno
order by name]]>
</Resource>
<Resource name="planBefore">
@@ -6859,8 +6897,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
</TestCase>
<TestCase name="testSortJoinTranspose3">
<Resource name="sql">
- <![CDATA[select * from sales.emp left join (
-select * from sales.dept) using (deptno)
+ <![CDATA[select * from sales.emp e left join (
+ select * from sales.dept) d on e.deptno = d.deptno
order by sal, name limit 10]]>
</Resource>
<Resource name="planBefore">
@@ -6887,7 +6925,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<TestCase name="testSortJoinTranspose4">
<Resource name="sql">
<![CDATA[select * from sales.emp e right join (
-select * from sales.dept d) using (deptno)
+ select * from sales.dept d) d on e.deptno = d.deptno
order by name]]>
</Resource>
<Resource name="planBefore">
@@ -6915,7 +6953,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<TestCase name="testSortJoinTranspose5">
<Resource name="sql">
<![CDATA[select * from sales.emp e right join (
-select * from sales.dept d) using (deptno)
+ select * from sales.dept d) d on e.deptno = d.deptno
limit 10]]>
</Resource>
<Resource name="planBefore">
@@ -8284,18 +8322,4 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
]]>
</Resource>
</TestCase>
- <TestCase name="testFilterRemoveIsNotDistinctFromRule">
- <Resource name="planBefore">
- <![CDATA[
-LogicalFilter(condition=[IS NOT DISTINCT FROM($7, 20)])
- LogicalTableScan(table=[[scott, EMP]])
-]]>
- </Resource>
- <Resource name="planAfter">
- <![CDATA[
-LogicalFilter(condition=[CASE(IS NULL($7), IS NULL(20), =(CAST($7):TINYINT NOT NULL, 20))])
- LogicalTableScan(table=[[scott, EMP]])
-]]>
- </Resource>
- </TestCase>
</Root>