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 2015/07/24 06:40:01 UTC
[4/6] incubator-calcite git commit: [CALCITE-714] When de-correlating,
push join condition into subquery
[CALCITE-714] When de-correlating, push join condition into subquery
Decorrelation enhancements to allow pushing Filter past Correlate.
In FilterProjectTransposeRule, check if the filter condition has correlation and
don't push in such cases.
Close apache/incubator-calcite#110
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/29030d8c
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/29030d8c
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/29030d8c
Branch: refs/heads/master
Commit: 29030d8c12709525cf874f4c28a13abe165f46aa
Parents: cf7a7a9
Author: Aman Sinha <as...@maprtech.com>
Authored: Tue May 12 13:53:39 2015 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Thu Jul 23 14:10:57 2015 -0700
----------------------------------------------------------------------
.../org/apache/calcite/rel/core/Filter.java | 28 +++
.../calcite/rel/rules/FilterCorrelateRule.java | 138 +++++++++++++
.../rel/rules/FilterProjectTransposeRule.java | 7 +
.../apache/calcite/sql2rel/RelDecorrelator.java | 23 ++-
.../calcite/test/SqlToRelConverterTest.java | 39 ++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 58 +++---
.../calcite/test/SqlToRelConverterTest.xml | 200 ++++++++++++++-----
core/src/test/resources/sql/misc.oq | 4 +-
8 files changed, 410 insertions(+), 87 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/29030d8c/core/src/main/java/org/apache/calcite/rel/core/Filter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Filter.java b/core/src/main/java/org/apache/calcite/rel/core/Filter.java
index 0dc3009..4b6ebfa 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Filter.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Filter.java
@@ -26,12 +26,17 @@ import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelWriter;
import org.apache.calcite.rel.SingleRel;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
+import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexChecker;
+import org.apache.calcite.rex.RexCorrelVariable;
import org.apache.calcite.rex.RexLocalRef;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexProgram;
import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.rex.RexVisitor;
+import org.apache.calcite.rex.RexVisitorImpl;
+import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
@@ -110,6 +115,29 @@ public abstract class Filter extends SingleRel {
return condition;
}
+ /**
+ * Check if any of the operands of the filter contains a
+ * correlation variable
+ */
+ public boolean hasCorrelation() {
+ if (condition instanceof RexCall) {
+ try {
+ RexVisitor<Void> visitor =
+ new RexVisitorImpl<Void>(true) {
+ public Void visitCorrelVariable(RexCorrelVariable var) {
+ throw new Util.FoundOne(var);
+ }
+ };
+ condition.accept(visitor);
+ return false;
+ } catch (Util.FoundOne e) {
+ Util.swallow(e, null);
+ return true;
+ }
+ }
+ return false;
+ }
+
@Override public boolean isValid(boolean fail) {
if (RexUtil.isNullabilityCast(getCluster().getTypeFactory(), condition)) {
assert !fail : "Cast for just nullability not allowed";
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/29030d8c/core/src/main/java/org/apache/calcite/rel/rules/FilterCorrelateRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/FilterCorrelateRule.java b/core/src/main/java/org/apache/calcite/rel/rules/FilterCorrelateRule.java
new file mode 100644
index 0000000..f156ec0
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rel/rules/FilterCorrelateRule.java
@@ -0,0 +1,138 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.rel.rules;
+
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Correlate;
+import org.apache.calcite.rel.core.Filter;
+import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+
+import com.google.common.collect.Lists;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * Planner rule that pushes filters above Correlate node into the children of the Correlate.
+ */
+public class FilterCorrelateRule extends RelOptRule {
+
+ public static final FilterCorrelateRule INSTANCE =
+ new FilterCorrelateRule(RelFactories.DEFAULT_FILTER_FACTORY,
+ RelFactories.DEFAULT_PROJECT_FACTORY);
+
+ private final RelFactories.FilterFactory filterFactory;
+
+ private final RelFactories.ProjectFactory projectFactory;
+
+ //~ Constructors -----------------------------------------------------------
+
+ /**
+ * Creates a FilterCorrelateRule with an explicit root operand and
+ * factories.
+ */
+ public FilterCorrelateRule(RelFactories.FilterFactory filterFactory,
+ RelFactories.ProjectFactory projectFactory) {
+ super(
+ operand(Filter.class,
+ operand(Correlate.class, RelOptRule.any())),
+ "FilterCorrelateRule");
+ this.filterFactory = filterFactory;
+ this.projectFactory = projectFactory;
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ public void onMatch(RelOptRuleCall call) {
+ Filter filter = call.rel(0);
+ Correlate corr = call.rel(1);
+
+ if (filter == null) {
+ return;
+ }
+
+ final List<RexNode> aboveFilters =
+ filter != null
+ ? RelOptUtil.conjunctions(filter.getCondition())
+ : Lists.<RexNode>newArrayList();
+
+ List<RexNode> leftFilters = new ArrayList<RexNode>();
+ List<RexNode> rightFilters = new ArrayList<RexNode>();
+
+ // Try to push down above filters. These are typically where clause
+ // filters. They can be pushed down if they are not on the NULL
+ // generating side.
+ RelOptUtil.classifyFilters(
+ corr,
+ aboveFilters,
+ JoinRelType.INNER,
+ false,
+ !corr.getJoinType().toJoinType().generatesNullsOnLeft(),
+ !corr.getJoinType().toJoinType().generatesNullsOnRight(),
+ aboveFilters,
+ leftFilters,
+ rightFilters);
+
+ if (leftFilters.isEmpty()
+ && rightFilters.isEmpty()) {
+ // no filters got pushed
+ return;
+ }
+
+ // create FilterRels on top of the children if any filters were
+ // pushed to them
+ final RexBuilder rexBuilder = corr.getCluster().getRexBuilder();
+ RelNode leftRel =
+ RelOptUtil.createFilter(corr.getLeft(), leftFilters, filterFactory);
+ RelNode rightRel =
+ RelOptUtil.createFilter(corr.getRight(), rightFilters, filterFactory);
+
+ // create the new LogicalCorrelate rel
+ List<RelNode> corrInputs = Lists.newArrayList();
+ corrInputs.add(leftRel);
+ corrInputs.add(rightRel);
+
+ RelNode newCorrRel = corr.copy(corr.getTraitSet(), corrInputs);
+
+ call.getPlanner().onCopy(corr, newCorrRel);
+
+ if (!leftFilters.isEmpty()) {
+ call.getPlanner().onCopy(filter, leftRel);
+ }
+ if (!rightFilters.isEmpty()) {
+ call.getPlanner().onCopy(filter, rightRel);
+ }
+
+ // create a LogicalFilter on top of the join if needed
+ RelNode newRel =
+ RelOptUtil.createFilter(newCorrRel,
+ RexUtil.fixUp(rexBuilder, aboveFilters, newCorrRel.getRowType()),
+ filterFactory);
+
+ call.transformTo(newRel);
+ }
+
+}
+
+// End FilterCorrelateRule.java
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/29030d8c/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java
index ff20f5a..ff006a9 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java
@@ -86,6 +86,13 @@ public class FilterProjectTransposeRule extends RelOptRule {
return;
}
+ if (filter.hasCorrelation()) {
+ // If there is a correlation condition anywhere in the filter, don't
+ // push this filter past project since in some cases it can prevent a
+ // Correlate from being decorrelated
+ return;
+ }
+
// convert the filter to one that references the child of the project
RexNode newCondition =
RelOptUtil.pushPastProject(filter.getCondition(), project);
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/29030d8c/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index 627c5b0..80767c2 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -44,7 +44,11 @@ import org.apache.calcite.rel.logical.LogicalJoin;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.logical.LogicalSort;
import org.apache.calcite.rel.metadata.RelMdUtil;
+import org.apache.calcite.rel.rules.FilterAggregateTransposeRule;
+import org.apache.calcite.rel.rules.FilterCorrelateRule;
import org.apache.calcite.rel.rules.FilterJoinRule;
+import org.apache.calcite.rel.rules.FilterProjectTransposeRule;
+import org.apache.calcite.rel.rules.ProjectMergeRule;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeField;
@@ -219,6 +223,8 @@ public class RelDecorrelator implements ReflectiveVisitor {
.addRuleInstance(new AdjustProjectForCountAggregateRule(false))
.addRuleInstance(new AdjustProjectForCountAggregateRule(true))
.addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
+ .addRuleInstance(FilterProjectTransposeRule.INSTANCE)
+ .addRuleInstance(FilterCorrelateRule.INSTANCE)
.build();
HepPlanner planner = createPlanner(program);
@@ -234,8 +240,21 @@ public class RelDecorrelator implements ReflectiveVisitor {
decorrelateVisitor.visit(root, 0, null);
if (mapOldToNewRel.containsKey(root)) {
- // has been rewritten
- return mapOldToNewRel.get(root);
+ // has been rewritten; apply rules post-decorrelation
+ HepProgram program2 = HepProgram.builder()
+ .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN)
+ .addRuleInstance(FilterJoinRule.JOIN)
+ .build();
+
+ HepPlanner planner2 = createPlanner(program2);
+
+ RelNode newroot = mapOldToNewRel.get(root);
+ // planner.setRoot(newroot);
+ planner2.setRoot(newroot);
+ // newroot = planner.findBestExp();
+ newroot = planner2.findBestExp();
+
+ return newroot;
} else {
// not rewritten
return root;
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/29030d8c/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 3a08601..cb0310c 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1345,6 +1345,45 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
}
/**
+ * Test case (correlated scalar aggregate subquery) for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
+ * When de-correlating, push join condition into subquery</a>.
+ */
+ @Test public void testCorrelationScalarAggAndFilter() {
+ tester.withDecorrelation(true).assertConvertsTo(
+ "SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
+ + "and e1.deptno < 10 and d1.deptno < 15\n"
+ + "and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)",
+ "${plan}");
+ }
+
+ /**
+ * Test case (correlated EXISTS subquery) for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
+ * When de-correlating, push join condition into subquery</a>.
+ */
+ @Test public void testCorrelationExistsAndFilter() {
+ tester.withDecorrelation(true).assertConvertsTo(
+ "SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
+ + "and e1.deptno < 10 and d1.deptno < 15\n"
+ + "and exists (select * from emp e2 where e1.empno = e2.empno)",
+ "${plan}");
+ }
+
+ /**
+ * Test case (correlated NOT EXISTS subquery) for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
+ * When de-correlating, push join condition into subquery</a>.
+ */
+ @Test public void testCorrelationNotExistsAndFilter() {
+ tester.withDecorrelation(true).assertConvertsTo(
+ "SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
+ + "and e1.deptno < 10 and d1.deptno < 15\n"
+ + "and not exists (select * from emp e2 where e1.empno = e2.empno)",
+ "${plan}");
+ }
+
+ /**
* Visitor that checks that every {@link RelNode} in a tree is valid.
*
* @see RelNode#isValid(boolean)
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/29030d8c/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 044b6b2..04d0fe2 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2791,37 +2791,33 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<Resource name="planBefore">
<![CDATA[
LogicalProject(DEPTNO=[$0], NAME=[$1])
- LogicalProject(DEPTNO=[$0], NAME=[$1], $f0=[$3])
- LogicalFilter(condition=[IS NOT NULL($3)])
- LogicalJoin(condition=[=($0, $2)], joinType=[left])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
- LogicalProject(DEPTNO0=[$2], $f0=[true])
- LogicalFilter(condition=[AND(=($1, $2), >($0, 100))])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalProject(SAL=[$5], DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$0])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO0=[$2], $f0=[true])
+ LogicalJoin(condition=[=($1, $2)], joinType=[inner])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
LogicalProject(DEPTNO=[$0], NAME=[$1])
- LogicalProject(DEPTNO=[$0], NAME=[$1], $f0=[$3])
- LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[CAST($2):INTEGER], $f1=[CAST($3):BOOLEAN])
- LogicalJoin(condition=[=($0, $2)], joinType=[inner])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
- LogicalProject(DEPTNO0=[$2], $f0=[true])
- LogicalJoin(condition=[=($1, $2)], joinType=[inner])
- LogicalFilter(condition=[>($0, 100)])
- LogicalProject(SAL=[$5], DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$0])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO0=[$2], $f0=[true])
+ LogicalJoin(condition=[=($1, $2)], joinType=[inner])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalFilter(condition=[>($5, 100)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -2837,11 +2833,11 @@ LogicalProject(DEPTNO=[$0], NAME=[$1])
LogicalProject(DEPTNO=[$0], NAME=[$1])
LogicalJoin(condition=[=($0, $2)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO0=[$2], $f0=[true])
LogicalJoin(condition=[=($1, $2)], joinType=[inner])
- LogicalFilter(condition=[>($0, 100)])
- LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalFilter(condition=[>($5, 100)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$0])
@@ -2854,8 +2850,8 @@ SemiJoin(condition=[=($0, $2)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(DEPTNO0=[$2], $f0=[true])
LogicalJoin(condition=[=($1, $2)], joinType=[inner])
- LogicalFilter(condition=[>($0, 100)])
- LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalFilter(condition=[>($5, 100)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$0])
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/29030d8c/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index fc9fe91..a9f7727 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1367,12 +1367,11 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalJoin(condition=[=($7, $11)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2])
- LogicalFilter(condition=[=($2, $0)])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[=($2, $0)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -1416,17 +1415,17 @@ LogicalProject(D2=[$0], D3=[$1])
<Resource name="plan">
<![CDATA[
LogicalProject(D2=[$0], D3=[$1])
- LogicalFilter(condition=[IS NOT NULL($2)])
- LogicalProject(D2=[$0], D3=[$1], $f0=[$4])
- LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
+ LogicalProject(D2=[$0], D3=[$1], $f0=[$4])
+ LogicalProject(D2=[$0], D3=[$1], D20=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
LogicalProject(D2=[$1], D3=[$2], $f0=[$0])
LogicalProject($f0=[true], D2=[$1], D3=[$2])
LogicalProject(EXPR$0=[1], D2=[$3], D3=[$2])
- LogicalFilter(condition=[AND(=($0, $3), IS NOT NULL($1))])
- LogicalJoin(condition=[true], joinType=[inner])
+ LogicalJoin(condition=[=($0, $3)], joinType=[inner])
+ LogicalFilter(condition=[IS NOT NULL($1)])
LogicalProject(D1=[$0], $f0=[$4], D3=[$3])
LogicalJoin(condition=[AND(=($0, $1), =($0, $2))], joinType=[left])
LogicalProject(D1=[+($0, 1)])
@@ -1435,22 +1434,21 @@ LogicalProject(D2=[$0], D3=[$1])
LogicalProject(D1=[$1], D12=[$2], D3=[$3], $f0=[$0])
LogicalProject($f0=[true], D1=[$1], D12=[$2], D3=[$3])
LogicalProject(EXPR$0=[2], D1=[$3], D12=[$3], D3=[$4])
- LogicalFilter(condition=[AND(=($0, $3), =($1, $3), =($2, $4))])
+ LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner])
+ LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalJoin(condition=[true], joinType=[inner])
- LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalAggregate(group=[{0}])
- LogicalProject(D1=[+($0, 1)])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0}])
- LogicalProject(D3=[$1])
- LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}])
- LogicalProject(D2=[$0])
- LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(D1=[+($0, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(D3=[$1])
+ LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(D2=[$0])
+ LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -1464,26 +1462,24 @@ where exists (
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[IS NOT NULL($9)])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$11])
- LogicalJoin(condition=[AND(=($7, $9), =($7, $10))], joinType=[left])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$11])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO1=[CAST($9):INTEGER], DEPTNO0=[CAST($10):INTEGER], $f2=[CAST($11):BOOLEAN])
+ LogicalJoin(condition=[AND(=($7, $9), =($7, $10))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
LogicalProject(DEPTNO1=[$1], DEPTNO0=[$2], $f0=[$0])
LogicalProject($f0=[true], DEPTNO1=[$1], DEPTNO0=[$2])
LogicalProject(EXPR$0=[1], DEPTNO1=[$3], DEPTNO0=[$2])
- LogicalFilter(condition=[<=($0, $3)])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2])
- LogicalFilter(condition=[>=($0, $2)])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[<=($0, $3)], joinType=[inner])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2])
+ LogicalJoin(condition=[>=($0, $2)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -1494,20 +1490,19 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[IS NOT NULL($9)])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$10])
- LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$10])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[CAST($9):INTEGER], $f1=[CAST($10):BOOLEAN])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(DEPTNO0=[$1], $f0=[$0])
LogicalProject($f0=[true], DEPTNO0=[$1])
LogicalProject(EXPR$0=[1], DEPTNO0=[$2])
- LogicalFilter(condition=[=($2, $0)])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[=($2, $0)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -1539,8 +1534,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalFilter(condition=[IS NOT NULL($9)])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9])
+ LogicalFilter(condition=[IS NOT NULL($9)])
LogicalCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{7}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], agg#0=[MIN($0)])
@@ -2414,4 +2409,105 @@ LogicalAggregate(group=[{0}], EMPID=[MIN($1)])
]]>
</Resource>
</TestCase>
+
+ <TestCase name="testCorrelationScalarAggAndFilter">
+ <Resource name="sql">
+ <![CDATA[SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno
+ and e1.deptno < 10 and d1.deptno < 15
+ and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EXPR$0=[$12])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO0=[CAST($11):INTEGER], EXPR$0=[CAST($12):INTEGER])
+ LogicalJoin(condition=[AND(=($0, $11), >($5, $12))], joinType=[inner])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalFilter(condition=[<($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<($0, 15)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
+ LogicalProject(EMPNO0=[$1], SAL=[$0])
+ LogicalProject(SAL=[$5], EMPNO0=[$9])
+ LogicalJoin(condition=[=($9, $0)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(EMPNO=[$0])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalFilter(condition=[<($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<($0, 15)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+
+ <TestCase name="testCorrelationExistsAndFilter">
+ <Resource name="sql">
+ <![CDATA[SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno
+ and e1.deptno < 10 and d1.deptno < 15
+ and exists (select * from emp e2 where e1.empno = e2.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$12])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO0=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN])
+ LogicalJoin(condition=[=($0, $11)], joinType=[inner])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalFilter(condition=[<($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<($0, 15)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(EMPNO0=[$1], $f0=[$0])
+ LogicalProject($f0=[true], EMPNO0=[$9])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9])
+ LogicalJoin(condition=[=($9, $0)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(EMPNO=[$0])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalFilter(condition=[<($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<($0, 15)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+
+ <TestCase name="testCorrelationNotExistsAndFilter">
+ <Resource name="sql">
+ <![CDATA[SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno
+ and e1.deptno < 10 and d1.deptno < 15
+ and not exists (select * from emp e2 where e1.empno = e2.empno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$12])
+ LogicalFilter(condition=[NOT(IS NOT NULL($12))])
+ LogicalJoin(condition=[=($0, $11)], joinType=[left])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalFilter(condition=[<($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<($0, 15)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(EMPNO0=[$1], $f0=[$0])
+ LogicalProject($f0=[true], EMPNO0=[$9])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9])
+ LogicalJoin(condition=[=($9, $0)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(EMPNO=[$0])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalFilter(condition=[<($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[<($0, 15)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
</Root>
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/29030d8c/core/src/test/resources/sql/misc.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/misc.oq b/core/src/test/resources/sql/misc.oq
index 1d1bd57..9bb8e58 100644
--- a/core/src/test/resources/sql/misc.oq
+++ b/core/src/test/resources/sql/misc.oq
@@ -240,9 +240,9 @@ where exists (
(3 rows)
!ok
-EnumerableSemiJoin(condition=[=($1, $5)], joinType=[inner])
+EnumerableSemiJoin(condition=[=($1, $6)], joinType=[inner])
EnumerableTableScan(table=[[hr, emps]])
- EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], deptno0=[$t0], $f0=[$t5])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
EnumerableAggregate(group=[{1}])
EnumerableTableScan(table=[[hr, emps]])