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 eedd40b [CALCITE-4012] Support trait propagation for
EnumerableHashJoin and EnumerableNestedLoopJoin (Rui Wang)
eedd40b is described below
commit eedd40bea825e0a00f5e463dd1f10c94eb233750
Author: amaliujia <[email protected]>
AuthorDate: Sun May 31 02:16:55 2020 -0700
[CALCITE-4012] Support trait propagation for EnumerableHashJoin and
EnumerableNestedLoopJoin (Rui Wang)
Both EnumerableHashJoin and EnumerableNestedLoopJoin can pass down
collation to
left input, and cannot push it down to right input, because
1) EnumerableHashJoin always builds hash table on right input.
2) EnumerableNestedLoopJoin always use left input as outer loop.
Thus only left input can preserve ordering. Similarly, both
EnumerableHashJoin
and EnumerableNestedLoopJoin can only derive collation from left input. In
top-down opt, traits propagation is supposed to replace
SortJoinTransposeRule
and SortJoinCopyRule.
Close #1995
---
.../adapter/enumerable/EnumerableHashJoin.java | 54 +++
.../adapter/enumerable/EnumerableMergeJoin.java | 4 +-
.../enumerable/EnumerableNestedLoopJoin.java | 62 +++
.../enumerable/EnumerableSortedAggregate.java | 3 +-
.../org/apache/calcite/test/TopDownOptTest.java | 222 ++++++++++
.../org/apache/calcite/test/TopDownOptTest.xml | 468 +++++++++++++++++++++
6 files changed, 809 insertions(+), 4 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableHashJoin.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableHashJoin.java
index a7a8ede..aa2f41f 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableHashJoin.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableHashJoin.java
@@ -19,11 +19,15 @@ package org.apache.calcite.adapter.enumerable;
import org.apache.calcite.linq4j.tree.BlockBuilder;
import org.apache.calcite.linq4j.tree.Expression;
import org.apache.calcite.linq4j.tree.Expressions;
+import org.apache.calcite.plan.DeriveMode;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptCost;
import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollationTraitDef;
+import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelNodes;
import org.apache.calcite.rel.core.CorrelationId;
@@ -36,11 +40,13 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.util.BuiltInMethod;
import org.apache.calcite.util.ImmutableIntList;
+import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
import java.lang.reflect.Method;
+import java.util.List;
import java.util.Set;
/** Implementation of {@link org.apache.calcite.rel.core.Join} in
@@ -100,6 +106,54 @@ public class EnumerableHashJoin extends Join implements
EnumerableRel {
condition, variablesSet, joinType);
}
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> passThroughTraits(
+ final RelTraitSet required) {
+ RelCollation collation = required.getCollation();
+ if (collation == null
+ || collation == RelCollations.EMPTY
+ || joinType == JoinRelType.FULL
+ || joinType == JoinRelType.RIGHT) {
+ return null;
+ }
+
+ for (RelFieldCollation fc : collation.getFieldCollations()) {
+ // If field collation belongs to right input: cannot push down collation.
+ if (fc.getFieldIndex() >= getLeft().getRowType().getFieldCount()) {
+ return null;
+ }
+ }
+
+ RelTraitSet passthroughTraitSet = traitSet.replace(collation);
+ return Pair.of(passthroughTraitSet,
+ ImmutableList.of(
+ passthroughTraitSet,
+ passthroughTraitSet.replace(RelCollations.EMPTY)));
+ }
+
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> deriveTraits(
+ final RelTraitSet childTraits, final int childId) {
+ // should only derive traits (limited to collation for now) from left join
input.
+ assert childId == 0;
+
+ RelCollation collation = childTraits.getCollation();
+ if (collation == null || collation == RelCollations.EMPTY) {
+ return null;
+ }
+
+ RelTraitSet derivedTraits = getTraitSet().replace(collation);
+ return Pair.of(
+ derivedTraits,
+ ImmutableList.of(derivedTraits, right.getTraitSet()));
+ }
+
+ @Override public DeriveMode getDeriveMode() {
+ if (joinType == JoinRelType.FULL || joinType == JoinRelType.RIGHT) {
+ return DeriveMode.PROHIBITED;
+ }
+
+ return DeriveMode.LEFT_FIRST;
+ }
+
@Override public RelOptCost computeSelfCost(RelOptPlanner planner,
RelMetadataQuery mq) {
double rowCount = mq.getRowCount(this);
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoin.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoin.java
index 5908212..4105682 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoin.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoin.java
@@ -99,7 +99,7 @@ public class EnumerableMergeJoin extends Join implements
EnumerableRel {
@Override public Pair<RelTraitSet, List<RelTraitSet>> passThroughTraits(
final RelTraitSet required) {
// Required collation keys can be subset or superset of merge join keys.
- RelCollation collation = required.getTrait(RelCollationTraitDef.INSTANCE);
+ RelCollation collation = required.getCollation();
List<Integer> reqKeys = RelCollations.ordinals(collation);
ImmutableBitSet reqKeySet = ImmutableBitSet.of(reqKeys);
@@ -132,7 +132,7 @@ public class EnumerableMergeJoin extends Join implements
EnumerableRel {
required.replace(leftCollation),
required.replace(rightCollation)));
}
- // TODO: support subset keys and superset keys
+ // TODO: support subset keys and superset keys (CALCITE-4015).
return null;
}
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableNestedLoopJoin.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableNestedLoopJoin.java
index 5b925b1..a10866e 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableNestedLoopJoin.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableNestedLoopJoin.java
@@ -19,11 +19,15 @@ package org.apache.calcite.adapter.enumerable;
import org.apache.calcite.linq4j.tree.BlockBuilder;
import org.apache.calcite.linq4j.tree.Expression;
import org.apache.calcite.linq4j.tree.Expressions;
+import org.apache.calcite.plan.DeriveMode;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptCost;
import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollationTraitDef;
+import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelNodes;
import org.apache.calcite.rel.core.CorrelationId;
@@ -34,9 +38,11 @@ import org.apache.calcite.rel.metadata.RelMdUtil;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.util.BuiltInMethod;
+import org.apache.calcite.util.Pair;
import com.google.common.collect.ImmutableList;
+import java.util.List;
import java.util.Set;
/** Implementation of {@link org.apache.calcite.rel.core.Join} in
@@ -118,6 +124,62 @@ public class EnumerableNestedLoopJoin extends Join
implements EnumerableRel {
return cost;
}
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> passThroughTraits(
+ final RelTraitSet required) {
+ RelCollation collation = required.getCollation();
+ if (collation == null
+ || collation == RelCollations.EMPTY
+ || joinType == JoinRelType.FULL
+ || joinType == JoinRelType.RIGHT) {
+ return null;
+ }
+
+ // EnumerableNestedLoopJoin traits passdown shall only pass through
collation to left input.
+ // It is because for EnumerableNestedLoopJoin always uses left input as
the outer loop,
+ // thus only left input can preserve ordering.
+ // Push sort both to left and right inputs does not help right outer join.
It's because in
+ // implementation, EnumerableNestedLoopJoin produces (null,
right_unmatched) all together,
+ // which does not preserve ordering from right side.
+
+
+ for (RelFieldCollation fc : collation.getFieldCollations()) {
+ // If field collation belongs to right input: cannot push down collation.
+ if (fc.getFieldIndex() >= getLeft().getRowType().getFieldCount()) {
+ return null;
+ }
+ }
+
+ RelTraitSet passthroughTraitSet = traitSet.replace(collation);
+ return Pair.of(passthroughTraitSet,
+ ImmutableList.of(
+ passthroughTraitSet,
+ passthroughTraitSet.replace(RelCollations.EMPTY)));
+ }
+
+ @Override public Pair<RelTraitSet, List<RelTraitSet>> deriveTraits(
+ final RelTraitSet childTraits, final int childId) {
+ // should only derive traits (limited to collation for now) from left join
input.
+ assert childId == 0;
+
+ RelCollation collation = childTraits.getCollation();
+ if (collation == null || collation == RelCollations.EMPTY) {
+ return null;
+ }
+
+ RelTraitSet derivedTraits = getTraitSet().replace(collation);
+ return Pair.of(
+ derivedTraits,
+ ImmutableList.of(derivedTraits, right.getTraitSet()));
+ }
+
+ @Override public DeriveMode getDeriveMode() {
+ if (joinType == JoinRelType.FULL || joinType == JoinRelType.RIGHT) {
+ return DeriveMode.PROHIBITED;
+ }
+
+ return DeriveMode.LEFT_FIRST;
+ }
+
public Result implement(EnumerableRelImplementor implementor, Prefer pref) {
final BlockBuilder builder = new BlockBuilder();
final Result leftResult =
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableSortedAggregate.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableSortedAggregate.java
index b1ce693..00ca78a 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableSortedAggregate.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableSortedAggregate.java
@@ -19,7 +19,6 @@ package org.apache.calcite.adapter.enumerable;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelTraitSet;
import org.apache.calcite.rel.RelCollation;
-import org.apache.calcite.rel.RelCollationTraitDef;
import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
@@ -64,7 +63,7 @@ public class EnumerableSortedAggregate extends Aggregate
implements EnumerableRe
}
RelTraitSet inputTraits = getInput().getTraitSet();
- RelCollation collation = required.getTrait(RelCollationTraitDef.INSTANCE);
+ RelCollation collation = required.getCollation();
ImmutableBitSet requiredKeys =
ImmutableBitSet.of(RelCollations.ordinals(collation));
ImmutableBitSet groupKeys = ImmutableBitSet.range(groupSet.cardinality());
diff --git a/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
b/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
index 7f40f08..fdac1d3 100644
--- a/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
@@ -25,6 +25,8 @@ import org.apache.calcite.plan.volcano.VolcanoPlanner;
import org.apache.calcite.rel.RelCollationTraitDef;
import org.apache.calcite.rel.rules.JoinCommuteRule;
import org.apache.calcite.rel.rules.JoinPushThroughJoinRule;
+import org.apache.calcite.rel.rules.SortJoinCopyRule;
+import org.apache.calcite.rel.rules.SortJoinTransposeRule;
import org.apache.calcite.rel.rules.SortProjectTransposeRule;
import com.google.common.collect.ImmutableList;
@@ -287,6 +289,222 @@ class TopDownOptTest extends RelOptTestBase {
.removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
.check();
}
+
+ // Not push down sort for hash join in full outer join case.
+ @Test void testHashJoinFullOuterJoinNotPushDownSort() {
+ final String sql = "select * from\n"
+ + "sales.emp r full outer join sales.bonus s on r.ename=s.ename and
r.job=s.job\n"
+ + "order by r.job desc nulls last, r.ename nulls first";
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .check();
+ }
+
+ // Push down sort to left input.
+ @Test void testHashJoinLeftOuterJoinPushDownSort() {
+ final String sql = "select * from\n"
+ + "(select contactno, email from customer.contact_peek) r left outer
join\n"
+ + "(select acctno, type from customer.account) s\n"
+ + "on r.contactno=s.acctno and r.email=s.type\n"
+ + "order by r.contactno desc, r.email desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // Push down sort to left input.
+ @Test void testHashJoinLeftOuterJoinPushDownSort2() {
+ final String sql = "select * from\n"
+ + "customer.contact_peek r left outer join\n"
+ + "customer.account s\n"
+ + "on r.contactno=s.acctno and r.email=s.type\n"
+ + "order by r.fname desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // Push down sort to left input.
+ @Test void testHashJoinInnerJoinPushDownSort() {
+ final String sql = "select * from\n"
+ + "(select contactno, email from customer.contact_peek) r inner join\n"
+ + "(select acctno, type from customer.account) s\n"
+ + "on r.contactno=s.acctno and r.email=s.type\n"
+ + "order by r.contactno desc, r.email desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // do not push down sort.
+ @Test void testHashJoinRightOuterJoinPushDownSort() {
+ final String sql = "select * from\n"
+ + "(select contactno, email from customer.contact_peek) r right outer
join\n"
+ + "(select acctno, type from customer.account) s\n"
+ + "on r.contactno=s.acctno and r.email=s.type\n"
+ + "order by s.acctno desc, s.type desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // push sort to left input
+ @Test void testNestedLoopJoinLeftOuterJoinPushDownSort() {
+ final String sql = "select * from\n"
+ + " customer.contact_peek r left outer join\n"
+ + "customer.account s\n"
+ + "on r.contactno>s.acctno and r.email<s.type\n"
+ + "order by r.contactno desc, r.email desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // push sort to left input
+ @Test void testNestedLoopJoinLeftOuterJoinPushDownSort2() {
+ final String sql = "select * from\n"
+ + " customer.contact_peek r left outer join\n"
+ + "customer.account s\n"
+ + "on r.contactno>s.acctno and r.email<s.type\n"
+ + "order by r.fname desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // do not push sort to left input cause sort keys are on right input.
+ @Test void testNestedLoopJoinLeftOuterJoinSortKeyOnRightInput() {
+ final String sql = "select * from\n"
+ + " customer.contact_peek r left outer join\n"
+ + "customer.account s\n"
+ + "on r.contactno>s.acctno and r.email<s.type\n"
+ + "order by s.acctno desc, s.type desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // do not push down sort to right input because traits propagation does not
work
+ // for right/full outer join.
+ @Test void testNestedLoopJoinRightOuterJoinSortPushDown() {
+ final String sql = "select r.contactno, r.email, s.acctno, s.type from\n"
+ + " customer.contact_peek r right outer join\n"
+ + "customer.account s\n"
+ + "on r.contactno>s.acctno and r.email<s.type\n"
+ + "order by s.acctno desc, s.type desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // Collation can be derived from left input so that top Sort is removed.
+ @Test void testHashJoinTraitDerivation() {
+ final String sql = "select * from\n"
+ + "(select ename, job, mgr from sales.emp order by ename desc, job
desc, mgr limit 10) r\n"
+ + "join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
+ + "order by r.ename desc, r.job desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // Collation can be derived from left input so that top Sort is removed.
+ @Test void testHashJoinTraitDerivation2() {
+ final String sql = "select * from\n"
+ + "(select ename, job, mgr from sales.emp order by mgr desc limit 10)
r\n"
+ + "join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
+ + "order by r.mgr desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // Collation derived from left input is not what the top Sort needs.
+ @Test void testHashJoinTraitDerivationNegativeCase() {
+ final String sql = "select * from\n"
+ + "(select ename, job, mgr from sales.emp order by mgr desc limit 10)
r\n"
+ + "join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
+ + "order by r.mgr";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // Collation can be derived from left input so that top Sort is removed.
+ @Test void testNestedLoopJoinTraitDerivation() {
+ final String sql = "select * from\n"
+ + "(select ename, job, mgr from sales.emp order by ename desc, job
desc, mgr limit 10) r\n"
+ + "join sales.bonus s on r.ename>s.ename and r.job<s.job\n"
+ + "order by r.ename desc, r.job desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // Collation can be derived from left input so that top Sort is removed.
+ @Test void testNestedLoopJoinTraitDerivation2() {
+ final String sql = "select * from\n"
+ + "(select ename, job, mgr from sales.emp order by mgr limit 10) r\n"
+ + "join sales.bonus s on r.ename>s.ename and r.job<s.job\n"
+ + "order by r.mgr";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
+
+ // Collation derived from left input is not what the top Sort needs.
+ @Test void testNestedLoopJoinTraitDerivationNegativeCase() {
+ final String sql = "select * from\n"
+ + "(select ename, job, mgr from sales.emp order by mgr limit 10) r\n"
+ + "join sales.bonus s on r.ename>s.ename and r.job<s.job\n"
+ + "order by r.mgr desc";
+
+ Query.create(sql)
+ .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+ .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
+ .check();
+ }
}
/**
@@ -323,6 +541,10 @@ class Query extends RelOptTestBase {
// pushing down sort should be handled by top-down optimization.
planner.removeRule(SortProjectTransposeRule.INSTANCE);
+
+ // Sort will only be pushed down by traits propagation.
+ planner.removeRule(SortJoinTransposeRule.INSTANCE);
+ planner.removeRule(SortJoinCopyRule.INSTANCE);
}
public static Query create(String sql) {
diff --git a/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
b/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
index f1ee19b..135dee9 100644
--- a/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/TopDownOptTest.xml
@@ -648,4 +648,472 @@ EnumerableMergeJoin(condition=[AND(=($1, $5), =($0,
$4))], joinType=[inner])
]]>
</Resource>
</TestCase>
+ <TestCase name="testHashJoinFullOuterJoinNotPushDownSort">
+ <Resource name="sql">
+ <![CDATA[
+"select * from
+sales.emp r full outer join sales.bonus s on r.ename=s.ename and r.job=s.job
+order by r.job desc nulls last, r.ename nulls first
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$2], sort1=[$1], dir0=[DESC-nulls-last],
dir1=[ASC-nulls-first])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], ENAME0=[$9], JOB0=[$10],
SAL0=[$11], COMM0=[$12])
+ LogicalJoin(condition=[AND(=($1, $9), =($2, $10))], joinType=[full])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableSort(sort0=[$2], sort1=[$1], dir0=[DESC-nulls-last],
dir1=[ASC-nulls-first])
+ EnumerableHashJoin(condition=[AND(=($1, $9), =($2, $10))], joinType=[full])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testHashJoinLeftOuterJoinPushDownSort">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select contactno, email from customer.contact_peek) r left outer join
+(select acctno, type from customer.account) s
+on r.contactno=s.acctno and r.email=s.type
+order by r.contactno desc, r.email desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], sort1=[$1], dir0=[DESC], dir1=[DESC])
+ LogicalProject(CONTACTNO=[$0], EMAIL=[$1], ACCTNO=[$2], TYPE=[$3])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
+ LogicalProject(CONTACTNO=[$0], EMAIL=[$3])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalProject(ACCTNO=[$0], TYPE=[$1])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
+ EnumerableProject(CONTACTNO=[$0], EMAIL=[$3])
+ EnumerableSort(sort0=[$0], sort1=[$3], dir0=[DESC], dir1=[DESC])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ EnumerableProject(ACCTNO=[$0], TYPE=[$1])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testHashJoinLeftOuterJoinPushDownSort2">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+customer.contact_peek r left outer join customer.account s
+on r.contactno=s.acctno and r.email=s.type
+order by r.fname desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[ROW($7, ROW($8, $9))], ACCTNO=[$10], TYPE=[$11],
BALANCE=[$12])
+ LogicalSort(sort0=[$1], dir0=[DESC])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[$7], COORD_NE8=[$8], COORD_NE9=[$9], ACCTNO=[$10],
TYPE=[$11], BALANCE=[$12])
+ LogicalJoin(condition=[AND(=($0, $10), =($3, $11))], joinType=[left])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[ROW($7, ROW($8, $9))], ACCTNO=[$10], TYPE=[$11],
BALANCE=[$12])
+ EnumerableHashJoin(condition=[AND(=($0, $10), =($3, $11))], joinType=[left])
+ EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ EnumerableSort(sort0=[$1], dir0=[DESC])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testHashJoinInnerJoinPushDownSort">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select contactno, email from customer.contact_peek) r inner join
+(select acctno, type from customer.account) s
+on r.contactno=s.acctno and r.email=s.type
+order by r.contactno desc, r.email desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], sort1=[$1], dir0=[DESC], dir1=[DESC])
+ LogicalProject(CONTACTNO=[$0], EMAIL=[$1], ACCTNO=[$2], TYPE=[$3])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
+ LogicalProject(CONTACTNO=[$0], EMAIL=[$3])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalProject(ACCTNO=[$0], TYPE=[$1])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
+ EnumerableProject(CONTACTNO=[$0], EMAIL=[$3])
+ EnumerableSort(sort0=[$0], sort1=[$3], dir0=[DESC], dir1=[DESC])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ EnumerableProject(ACCTNO=[$0], TYPE=[$1])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testHashJoinRightOuterJoinPushDownSort">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select contactno, email from customer.contact_peek) r right outer join
+(select acctno, type from customer.account) s
+on r.contactno=s.acctno and r.email=s.type
+order by s.acctno desc, s.type desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$2], sort1=[$3], dir0=[DESC], dir1=[DESC])
+ LogicalProject(CONTACTNO=[$0], EMAIL=[$1], ACCTNO=[$2], TYPE=[$3])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[right])
+ LogicalProject(CONTACTNO=[$0], EMAIL=[$3])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalProject(ACCTNO=[$0], TYPE=[$1])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableSort(sort0=[$2], sort1=[$3], dir0=[DESC], dir1=[DESC])
+ EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[right])
+ EnumerableProject(CONTACTNO=[$0], EMAIL=[$3])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ EnumerableProject(ACCTNO=[$0], TYPE=[$1])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testHashJoinTraitDerivation">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, job, mgr from sales.emp order by ename desc, job desc, mgr
limit 10) r
+join sales.bonus s on r.ename=s.ename and r.job=s.job
+order by r.ename desc, r.job desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], sort1=[$1], dir0=[DESC], dir1=[DESC])
+ LogicalProject(ENAME=[$0], JOB=[$1], MGR=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner])
+ LogicalSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[DESC],
dir1=[DESC], dir2=[ASC], fetch=[10])
+ LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableHashJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner])
+ EnumerableLimit(fetch=[10])
+ EnumerableProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ EnumerableSort(sort0=[$1], sort1=[$2], sort2=[$3], dir0=[DESC],
dir1=[DESC], dir2=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testHashJoinTraitDerivation2">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, job, mgr from sales.emp order by mgr desc limit 10) r
+join sales.bonus s on r.ename=s.ename and r.job=s.job
+order by r.mgr desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$2], dir0=[DESC])
+ LogicalProject(ENAME=[$0], JOB=[$1], MGR=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner])
+ LogicalSort(sort0=[$2], dir0=[DESC], fetch=[10])
+ LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableHashJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner])
+ EnumerableLimit(fetch=[10])
+ EnumerableProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ EnumerableSort(sort0=[$3], dir0=[DESC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testHashJoinTraitDerivationNegativeCase">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, job, mgr from sales.emp order by mgr desc limit 10) r
+join sales.bonus s on r.ename=s.ename and r.job=s.job
+order by r.mgr
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$2], dir0=[ASC])
+ LogicalProject(ENAME=[$0], JOB=[$1], MGR=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner])
+ LogicalSort(sort0=[$2], dir0=[DESC], fetch=[10])
+ LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableSort(sort0=[$2], dir0=[ASC])
+ EnumerableHashJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner])
+ EnumerableLimit(fetch=[10])
+ EnumerableProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ EnumerableSort(sort0=[$3], dir0=[DESC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedLoopJoinTraitDerivation">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, job, mgr from sales.emp order by ename desc, job desc, mgr
limit 10) r
+join sales.bonus s on r.ename>s.ename and r.job<s.job
+order by r.ename desc, r.job desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], sort1=[$1], dir0=[DESC], dir1=[DESC])
+ LogicalProject(ENAME=[$0], JOB=[$1], MGR=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(>($0, $3), <($1, $4))], joinType=[inner])
+ LogicalSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[DESC],
dir1=[DESC], dir2=[ASC], fetch=[10])
+ LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableNestedLoopJoin(condition=[AND(>($0, $3), <($1, $4))],
joinType=[inner])
+ EnumerableLimit(fetch=[10])
+ EnumerableProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ EnumerableSort(sort0=[$1], sort1=[$2], sort2=[$3], dir0=[DESC],
dir1=[DESC], dir2=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedLoopJoinTraitDerivation2">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, job, mgr from sales.emp order by mgr limit 10) r
+join sales.bonus s on r.ename>s.ename and r.job<s.job
+order by r.mgr
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$2], dir0=[ASC])
+ LogicalProject(ENAME=[$0], JOB=[$1], MGR=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(>($0, $3), <($1, $4))], joinType=[inner])
+ LogicalSort(sort0=[$2], dir0=[ASC], fetch=[10])
+ LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableNestedLoopJoin(condition=[AND(>($0, $3), <($1, $4))],
joinType=[inner])
+ EnumerableLimit(fetch=[10])
+ EnumerableProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ EnumerableSort(sort0=[$3], dir0=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedLoopJoinTraitDerivationNegativeCase">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select ename, job, mgr from sales.emp order by mgr limit 10) r
+join sales.bonus s on r.ename>s.ename and r.job<s.job
+order by r.mgr desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$2], dir0=[DESC])
+ LogicalProject(ENAME=[$0], JOB=[$1], MGR=[$2], ENAME0=[$3], JOB0=[$4],
SAL=[$5], COMM=[$6])
+ LogicalJoin(condition=[AND(>($0, $3), <($1, $4))], joinType=[inner])
+ LogicalSort(sort0=[$2], dir0=[ASC], fetch=[10])
+ LogicalProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableSort(sort0=[$2], dir0=[DESC])
+ EnumerableNestedLoopJoin(condition=[AND(>($0, $3), <($1, $4))],
joinType=[inner])
+ EnumerableLimit(fetch=[10])
+ EnumerableProject(ENAME=[$1], JOB=[$2], MGR=[$3])
+ EnumerableSort(sort0=[$3], dir0=[ASC])
+ EnumerableTableScan(table=[[CATALOG, SALES, EMP]])
+ EnumerableTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedLoopJoinLeftOuterJoinPushDownSort">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+customer.contact_peek r left outer join
+customer.account s
+on r.contactno>s.acctno and r.email<s.type
+order by r.contactno desc, r.email desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[ROW($7, ROW($8, $9))], ACCTNO=[$10], TYPE=[$11],
BALANCE=[$12])
+ LogicalSort(sort0=[$0], sort1=[$3], dir0=[DESC], dir1=[DESC])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[$7], COORD_NE8=[$8], COORD_NE9=[$9], ACCTNO=[$10],
TYPE=[$11], BALANCE=[$12])
+ LogicalJoin(condition=[AND(>($0, $10), <($3, $11))], joinType=[left])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[ROW($7, ROW($8, $9))], ACCTNO=[$10], TYPE=[$11],
BALANCE=[$12])
+ EnumerableNestedLoopJoin(condition=[AND(>($0, $10), <($3, $11))],
joinType=[left])
+ EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ EnumerableSort(sort0=[$0], sort1=[$3], dir0=[DESC], dir1=[DESC])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedLoopJoinLeftOuterJoinPushDownSort2">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+customer.contact_peek r left outer join
+customer.account s
+on r.contactno>s.acctno and r.email<s.type
+order by r.fname desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[ROW($7, ROW($8, $9))], ACCTNO=[$10], TYPE=[$11],
BALANCE=[$12])
+ LogicalSort(sort0=[$1], dir0=[DESC])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[$7], COORD_NE8=[$8], COORD_NE9=[$9], ACCTNO=[$10],
TYPE=[$11], BALANCE=[$12])
+ LogicalJoin(condition=[AND(>($0, $10), <($3, $11))], joinType=[left])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[ROW($7, ROW($8, $9))], ACCTNO=[$10], TYPE=[$11],
BALANCE=[$12])
+ EnumerableNestedLoopJoin(condition=[AND(>($0, $10), <($3, $11))],
joinType=[left])
+ EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ EnumerableSort(sort0=[$1], dir0=[DESC])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedLoopJoinLeftOuterJoinSortKeyOnRightInput">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select contactno, email from customer.contact_peek) r left outer join
+(select acctno, type from customer.account) s
+on r.contactno>s.acctno and r.email<s.type
+order by s.acctno desc, s.type desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[ROW($7, ROW($8, $9))], ACCTNO=[$10], TYPE=[$11],
BALANCE=[$12])
+ LogicalSort(sort0=[$10], sort1=[$11], dir0=[DESC], dir1=[DESC])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[$7], COORD_NE8=[$8], COORD_NE9=[$9], ACCTNO=[$10],
TYPE=[$11], BALANCE=[$12])
+ LogicalJoin(condition=[AND(>($0, $10), <($3, $11))], joinType=[left])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4],
Y=[$5], unit=[$6], COORD_NE=[ROW($7, ROW($8, $9))], ACCTNO=[$10], TYPE=[$11],
BALANCE=[$12])
+ EnumerableSort(sort0=[$10], sort1=[$11], dir0=[DESC], dir1=[DESC])
+ EnumerableNestedLoopJoin(condition=[AND(>($0, $10), <($3, $11))],
joinType=[left])
+ EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedLoopJoinRightOuterJoinSortPushDown">
+ <Resource name="sql">
+ <![CDATA[
+select * from
+(select contactno, email from customer.contact_peek) r right outer join
+(select acctno, type from customer.account) s
+on r.contactno>s.acctno and r.email<s.type
+order by s.acctno desc, s.type desc
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$2], sort1=[$3], dir0=[DESC], dir1=[DESC])
+ LogicalProject(CONTACTNO=[$0], EMAIL=[$3], ACCTNO=[$10], TYPE=[$11])
+ LogicalJoin(condition=[AND(>($0, $10), <($3, $11))], joinType=[right])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+EnumerableProject(CONTACTNO=[$0], EMAIL=[$3], ACCTNO=[$10], TYPE=[$11])
+ EnumerableSort(sort0=[$10], sort1=[$11], dir0=[DESC], dir1=[DESC])
+ EnumerableNestedLoopJoin(condition=[AND(>($0, $10), <($3, $11))],
joinType=[right])
+ EnumerableProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3],
X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ EnumerableTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
+]]>
+ </Resource>
+ </TestCase>
</Root>
