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 2014/10/23 22:40:14 UTC
git commit: [CALCITE-444] Filters wrongly pushed into full outer join
Repository: incubator-calcite
Updated Branches:
refs/heads/master 3afe4b149 -> e4fcf2a3b
[CALCITE-444] Filters wrongly pushed into full outer join
This change contains deprecated APIs that will be removed in a day or two.
Project: http://git-wip-us.apache.org/repos/asf/incubator-calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/e4fcf2a3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-calcite/tree/e4fcf2a3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-calcite/diff/e4fcf2a3
Branch: refs/heads/master
Commit: e4fcf2a3be10c53e72276ca42789cf5fbd7fe7c7
Parents: 3afe4b1
Author: John Pullokkaran <jp...@hortonworks.com>
Authored: Thu Oct 23 13:04:13 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Thu Oct 23 13:06:54 2014 -0700
----------------------------------------------------------------------
.../org/eigenbase/rel/metadata/RelMdUtil.java | 5 +-
.../rel/rules/PushFilterPastJoinRule.java | 107 ++++++++--------
.../java/org/eigenbase/relopt/RelOptUtil.java | 114 ++++++++++++-----
.../org/eigenbase/test/RelOptRulesTest.java | 35 +++++-
.../org/eigenbase/test/RelOptRulesTest.xml | 121 +++++++++++++++++++
5 files changed, 293 insertions(+), 89 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e4fcf2a3/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java b/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java
index d01c012..043c293 100644
--- a/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java
+++ b/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java
@@ -26,7 +26,6 @@ import org.eigenbase.rex.*;
import org.eigenbase.sql.*;
import org.eigenbase.sql.type.*;
import org.eigenbase.util.Bug;
-import org.eigenbase.util.Holder;
import org.eigenbase.util14.*;
import net.hydromatic.optiq.util.BitSets;
@@ -657,9 +656,7 @@ public class RelMdUtil {
!joinType.generatesNullsOnRight(),
joinFilters,
leftFilters,
- rightFilters,
- Holder.of(joinType),
- false);
+ rightFilters);
RexBuilder rexBuilder = joinRel.getCluster().getRexBuilder();
leftPred =
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e4fcf2a3/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java b/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java
index 90369ad..b169568 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java
@@ -21,9 +21,9 @@ import java.util.*;
import org.eigenbase.rel.*;
import org.eigenbase.relopt.*;
import org.eigenbase.rex.*;
-import org.eigenbase.util.Holder;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
/**
@@ -85,7 +85,17 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
final List<RexNode> aboveFilters =
filter != null
? RelOptUtil.conjunctions(filter.getCondition())
- : ImmutableList.<RexNode>of();
+ : Lists.<RexNode>newArrayList();
+ final ImmutableList<RexNode> origAboveFilters =
+ ImmutableList.copyOf(aboveFilters);
+
+ // Simplify Outer Joins
+ JoinRelType joinType = join.getJoinType();
+ if (smart
+ && !origAboveFilters.isEmpty()
+ && join.getJoinType() != JoinRelType.INNER) {
+ joinType = RelOptUtil.simplifyJoin(join, origAboveFilters, joinType);
+ }
List<RexNode> leftFilters = new ArrayList<RexNode>();
List<RexNode> rightFilters = new ArrayList<RexNode>();
@@ -100,26 +110,23 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
// filters. They can be pushed down if they are not on the NULL
// generating side.
boolean filterPushed = false;
- final Holder<JoinRelType> joinTypeHolder = Holder.of(join.getJoinType());
if (RelOptUtil.classifyFilters(
join,
aboveFilters,
- join.getJoinType(),
+ joinType,
!(join instanceof EquiJoinRel),
- !join.getJoinType().generatesNullsOnLeft(),
- !join.getJoinType().generatesNullsOnRight(),
+ !joinType.generatesNullsOnLeft(),
+ !joinType.generatesNullsOnRight(),
joinFilters,
leftFilters,
- rightFilters,
- joinTypeHolder,
- smart)) {
+ rightFilters)) {
filterPushed = true;
}
// Move join filters up if needed
- validateJoinFilters(aboveFilters, joinFilters, join);
+ validateJoinFilters(aboveFilters, joinFilters, join, joinType);
- // If no filter got pushed after validate reset filterPushed flag
+ // If no filter got pushed after validate, reset filterPushed flag
if (leftFilters.isEmpty()
&& rightFilters.isEmpty()
&& joinFilters.size() == origJoinFilters.size()) {
@@ -135,27 +142,23 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
if (RelOptUtil.classifyFilters(
join,
joinFilters,
- join.getJoinType(),
+ joinType,
false,
- !join.getJoinType().generatesNullsOnRight(),
- !join.getJoinType().generatesNullsOnLeft(),
+ !joinType.generatesNullsOnRight(),
+ !joinType.generatesNullsOnLeft(),
joinFilters,
leftFilters,
- rightFilters,
- joinTypeHolder,
- false)) {
+ rightFilters)) {
filterPushed = true;
}
- if (!filterPushed) {
- return;
- }
-
// if nothing actually got pushed and there is nothing leftover,
// then this rule is a no-op
- if (joinFilters.isEmpty()
- && leftFilters.isEmpty()
- && rightFilters.isEmpty()) {
+ if ((!filterPushed
+ && joinType == join.getJoinType())
+ || (joinFilters.isEmpty()
+ && leftFilters.isEmpty()
+ && rightFilters.isEmpty())) {
return;
}
@@ -163,15 +166,9 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
// pushed to them
final RexBuilder rexBuilder = join.getCluster().getRexBuilder();
RelNode leftRel =
- createFilterOnRel(
- rexBuilder,
- join.getLeft(),
- leftFilters);
+ RelOptUtil.createFilter(join.getLeft(), leftFilters, filterFactory);
RelNode rightRel =
- createFilterOnRel(
- rexBuilder,
- join.getRight(),
- rightFilters);
+ RelOptUtil.createFilter(join.getRight(), rightFilters, filterFactory);
// create the new join node referencing the new children and
// containing its new join filters (if there are any)
@@ -183,7 +180,7 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
if (joinFilter.isAlwaysTrue()
&& leftFilters.isEmpty()
&& rightFilters.isEmpty()
- && joinTypeHolder.get() == join.getJoinType()) {
+ && joinType == join.getJoinType()) {
return;
}
@@ -193,7 +190,7 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
joinFilter,
leftRel,
rightRel,
- joinTypeHolder.get(),
+ joinType,
join.isSemiJoinDone());
call.getPlanner().onCopy(join, newJoinRel);
if (!leftFilters.isEmpty()) {
@@ -210,7 +207,7 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
// create a FilterRel on top of the join if needed
RelNode newRel =
- createFilterOnRel(rexBuilder, newJoinRel, aboveFilters);
+ RelOptUtil.createFilter(newJoinRel, aboveFilters, filterFactory);
call.transformTo(newRel);
}
@@ -218,7 +215,7 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
/**
* Validates that target execution framework can satisfy join filters.
*
- * <p>If the join filter cannot be satifisfied (for example, if it is
+ * <p>If the join filter cannot be satisfied (for example, if it is
* {@code l.c1 > r.c2} and the join only supports equi-join), removes the
* filter from {@code joinFilters} and adds it to {@code aboveFilters}.
*
@@ -228,31 +225,35 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
* @param aboveFilters Filter above Join
* @param joinFilters Filters in join condition
* @param join Join
+ *
+ * @deprecated Use {@link #validateJoinFilters(java.util.List, java.util.List, org.eigenbase.rel.JoinRelBase, org.eigenbase.rel.JoinRelType)};
+ * very short-term; will be removed before
+ * {@link org.eigenbase.util.Bug#upgrade(String) calcite-0.9.2}.
*/
protected void validateJoinFilters(List<RexNode> aboveFilters,
List<RexNode> joinFilters, JoinRelBase join) {
- return;
+ validateJoinFilters(aboveFilters, joinFilters, join, join.getJoinType());
}
/**
- * If the filter list passed in is non-empty, creates a FilterRel on top of
- * the existing RelNode; otherwise, just returns the RelNode
+ * Validates that target execution framework can satisfy join filters.
+ *
+ * <p>If the join filter cannot be satisfied (for example, if it is
+ * {@code l.c1 > r.c2} and the join only supports equi-join), removes the
+ * filter from {@code joinFilters} and adds it to {@code aboveFilters}.
*
- * @param rexBuilder rex builder
- * @param rel the RelNode that the filter will be put on top of
- * @param filters list of filters
- * @return new RelNode or existing one if no filters
+ * <p>The default implementation does nothing; i.e. the join can handle all
+ * conditions.
+ *
+ * @param aboveFilters Filter above Join
+ * @param joinFilters Filters in join condition
+ * @param join Join
+ * @param joinType JoinRelType could be different from type in Join due to
+ * outer join simplification.
*/
- private RelNode createFilterOnRel(
- RexBuilder rexBuilder,
- RelNode rel,
- List<RexNode> filters) {
- RexNode andFilters =
- RexUtil.composeConjunction(rexBuilder, filters, false);
- if (andFilters.isAlwaysTrue()) {
- return rel;
- }
- return filterFactory.createFilter(rel, andFilters);
+ protected void validateJoinFilters(List<RexNode> aboveFilters,
+ List<RexNode> joinFilters, JoinRelBase join, JoinRelType joinType) {
+ return;
}
/** Rule that pushes parts of the join condition to its inputs. */
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e4fcf2a3/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java b/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
index 9034b7c..79a59e7 100644
--- a/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
+++ b/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
@@ -1943,6 +1943,44 @@ public abstract class RelOptUtil {
}
/**
+ * Simplifies outer joins if filter above would reject nulls.
+ *
+ * @param joinRel Join
+ * @param aboveFilters Filters from above
+ * @param joinType Join type, can not be inner join
+ */
+ public static JoinRelType simplifyJoin(RelNode joinRel,
+ ImmutableList<RexNode> aboveFilters,
+ JoinRelType joinType) {
+ final int nTotalFields = joinRel.getRowType().getFieldCount();
+ final int nSysFields = 0;
+ final int nFieldsLeft =
+ joinRel.getInputs().get(0).getRowType().getFieldCount();
+ final int nFieldsRight =
+ joinRel.getInputs().get(1).getRowType().getFieldCount();
+ assert nTotalFields == nSysFields + nFieldsLeft + nFieldsRight;
+
+ // set the reference bitmaps for the left and right children
+ BitSet leftBitmap = BitSets.range(nSysFields, nSysFields + nFieldsLeft);
+ BitSet rightBitmap = BitSets.range(nSysFields + nFieldsLeft, nTotalFields);
+
+ for (RexNode filter : aboveFilters) {
+ if (joinType.generatesNullsOnLeft()
+ && Strong.is(filter, leftBitmap)) {
+ joinType = joinType.cancelNullsOnLeft();
+ }
+ if (joinType.generatesNullsOnRight()
+ && Strong.is(filter, rightBitmap)) {
+ joinType = joinType.cancelNullsOnRight();
+ }
+ if (joinType == JoinRelType.INNER) {
+ break;
+ }
+ }
+ return joinType;
+ }
+
+ /**
* Classifies filters according to where they should be processed. They
* either stay where they are, are pushed to the join (if they originated
* from above the join), or are pushed to one of the children. Filters that
@@ -1960,6 +1998,10 @@ public abstract class RelOptUtil {
* @param smart Whether to try to strengthen the join type
* @return whether at least one filter was pushed, or join type was
* strengthened
+ *
+ * @deprecated Use the other {@link #classifyFilters};
+ * very short-term; will be removed before
+ * {@link org.eigenbase.util.Bug#upgrade(String) calcite-0.9.2}.
*/
public static boolean classifyFilters(
RelNode joinRel,
@@ -1973,8 +2015,47 @@ public abstract class RelOptUtil {
List<RexNode> rightFilters,
Holder<JoinRelType> joinTypeHolder,
boolean smart) {
- RexBuilder rexBuilder = joinRel.getCluster().getRexBuilder();
final JoinRelType oldJoinType = joinType;
+ final int filterCount = filters.size();
+ if (smart) {
+ joinType = simplifyJoin(joinRel, ImmutableList.copyOf(joinFilters),
+ joinType);
+ joinTypeHolder.set(joinType);
+ }
+ classifyFilters(joinRel, filters, joinType, pushInto, pushLeft, pushRight,
+ joinFilters, leftFilters, rightFilters);
+
+ return filters.size() < filterCount || joinType != oldJoinType;
+ }
+
+ /**
+ * Classifies filters according to where they should be processed. They
+ * either stay where they are, are pushed to the join (if they originated
+ * from above the join), or are pushed to one of the children. Filters that
+ * are pushed are added to list passed in as input parameters.
+ *
+ * @param joinRel join node
+ * @param filters filters to be classified
+ * @param joinType join type
+ * @param pushInto whether filters can be pushed into the ON clause
+ * @param pushLeft true if filters can be pushed to the left
+ * @param pushRight true if filters can be pushed to the right
+ * @param joinFilters list of filters to push to the join
+ * @param leftFilters list of filters to push to the left child
+ * @param rightFilters list of filters to push to the right child
+ * @return whether at least one filter was pushed
+ */
+ public static boolean classifyFilters(
+ RelNode joinRel,
+ List<RexNode> filters,
+ JoinRelType joinType,
+ boolean pushInto,
+ boolean pushLeft,
+ boolean pushRight,
+ List<RexNode> joinFilters,
+ List<RexNode> leftFilters,
+ List<RexNode> rightFilters) {
+ RexBuilder rexBuilder = joinRel.getCluster().getRexBuilder();
List<RelDataTypeField> joinFields = joinRel.getRowType().getFieldList();
final int nTotalFields = joinFields.size();
final int nSysFields = 0; // joinRel.getSystemFieldList().size();
@@ -2061,35 +2142,6 @@ public abstract class RelOptUtil {
}
filtersToRemove.add(filter);
}
-
- // If the filter will only evaluate to true if fields from the left
- // are not null, and the left is null-generating, then we can make the
- // left. Similarly for the right.
- if (smart
- && joinType.generatesNullsOnRight()
- && Strong.is(filter, rightBitmap)) {
- joinType = joinType.cancelNullsOnRight();
- joinTypeHolder.set(joinType);
- if (pushInto) {
- filtersToRemove.add(filter);
- if (!joinFilters.contains(filter)) {
- joinFilters.add(filter);
- }
- }
- }
- if (smart
- && joinType.generatesNullsOnLeft()
- && Strong.is(filter, leftBitmap)) {
- filtersToRemove.add(filter);
- joinType = joinType.cancelNullsOnLeft();
- joinTypeHolder.set(joinType);
- if (pushInto) {
- filtersToRemove.add(filter);
- if (!joinFilters.contains(filter)) {
- joinFilters.add(filter);
- }
- }
- }
}
}
@@ -2099,7 +2151,7 @@ public abstract class RelOptUtil {
}
// Did anything change?
- return !filtersToRemove.isEmpty() || joinType != oldJoinType;
+ return !filtersToRemove.isEmpty();
}
private static RexNode shiftFilter(
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e4fcf2a3/core/src/test/java/org/eigenbase/test/RelOptRulesTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/eigenbase/test/RelOptRulesTest.java b/core/src/test/java/org/eigenbase/test/RelOptRulesTest.java
index f49a846..1d35d91 100644
--- a/core/src/test/java/org/eigenbase/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/eigenbase/test/RelOptRulesTest.java
@@ -157,11 +157,44 @@ public class RelOptRulesTest extends RelOptTestBase {
+ " and emp.sal > 100)");
}
- @Test public void testPushFilterThroughOuterJoin() {
+ @Test public void testFullOuterJoinSimplificationToLeftOuter() {
+ checkPlanning(
+ PushFilterPastJoinRule.FILTER_ON_JOIN,
+ "select 1 from sales.dept d full outer join sales.emp e"
+ + " on d.deptno = e.deptno"
+ + " where d.name = 'Charlie'");
+ }
+
+ @Test public void testFullOuterJoinSimplificationToRightOuter() {
+ checkPlanning(
+ PushFilterPastJoinRule.FILTER_ON_JOIN,
+ "select 1 from sales.dept d full outer join sales.emp e"
+ + " on d.deptno = e.deptno"
+ + " where e.sal > 100");
+ }
+
+ @Test public void testFullOuterJoinSimplificationToInner() {
+ checkPlanning(
+ PushFilterPastJoinRule.FILTER_ON_JOIN,
+ "select 1 from sales.dept d full outer join sales.emp e"
+ + " on d.deptno = e.deptno"
+ + " where d.name = 'Charlie' and e.sal > 100");
+ }
+
+ @Test public void testLeftOuterJoinSimplificationToInner() {
checkPlanning(
PushFilterPastJoinRule.FILTER_ON_JOIN,
"select 1 from sales.dept d left outer join sales.emp e"
+ " on d.deptno = e.deptno"
+ + " where e.sal > 100");
+ }
+
+
+ @Test public void testRightOuterJoinSimplificationToInner() {
+ checkPlanning(
+ PushFilterPastJoinRule.FILTER_ON_JOIN,
+ "select 1 from sales.dept d right outer join sales.emp e"
+ + " on d.deptno = e.deptno"
+ " where d.name = 'Charlie'");
}
http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/e4fcf2a3/core/src/test/resources/org/eigenbase/test/RelOptRulesTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/eigenbase/test/RelOptRulesTest.xml b/core/src/test/resources/org/eigenbase/test/RelOptRulesTest.xml
index 26c9cf1..da967a8 100644
--- a/core/src/test/resources/org/eigenbase/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/eigenbase/test/RelOptRulesTest.xml
@@ -108,6 +108,127 @@ ProjectRel(EXPR$0=[1])
]]>
</Resource>
</TestCase>
+ <TestCase name="testFullOuterJoinSimplificationToLeftOuter">
+ <Resource name="sql">
+ <![CDATA[select 1 from sales.dept d full outer join sales.emp e on d.deptno = e.deptno where d.name = 'Charlie']]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ FilterRel(condition=[=($1, 'Charlie')])
+ JoinRel(condition=[=($0, $9)], joinType=[full])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ ProjectRel(DEPTNO=[CAST($0):INTEGER], NAME=[CAST($1):VARCHAR(10) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], EMPNO=[$2], ENAME=[$3], JOB=[$4], MGR=[$5], HIREDATE=[$6], SAL=[$7], COMM=[$8], DEPTNO0=[$9], SLACKER=[$10])
+ JoinRel(condition=[=($0, $9)], joinType=[left])
+ FilterRel(condition=[=($1, 'Charlie')])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testFullOuterJoinSimplificationToRightOuter">
+ <Resource name="sql">
+ <![CDATA[select 1 from sales.dept d full outer join sales.emp e on d.deptno = e.deptno where e.sal > 100]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ FilterRel(condition=[>($7, 100)])
+ JoinRel(condition=[=($0, $9)], joinType=[full])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ ProjectRel(DEPTNO=[$0], NAME=[$1], EMPNO=[CAST($2):INTEGER], ENAME=[CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], JOB=[CAST($4):VARCHAR(10) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], MGR=[$5], HIREDATE=[CAST($6):TIMESTAMP(0)], SAL=[CAST($7):INTEGER], COMM=[CAST($8):INTEGER], DEPTNO0=[CAST($9):INTEGER], SLACKER=[CAST($10):BOOLEAN])
+ JoinRel(condition=[=($0, $9)], joinType=[right])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ FilterRel(condition=[>($5, 100)])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testFullOuterJoinSimplificationToInner">
+ <Resource name="sql">
+ <![CDATA[select 1 from sales.dept d full outer join sales.emp e on d.deptno = e.deptno where d.name = 'Charlie' and e.sal > 100]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ FilterRel(condition=[AND(=($1, 'Charlie'), >($7, 100))])
+ JoinRel(condition=[=($0, $9)], joinType=[full])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ ProjectRel(DEPTNO=[CAST($0):INTEGER], NAME=[CAST($1):VARCHAR(10) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], EMPNO=[CAST($2):INTEGER], ENAME=[CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], JOB=[CAST($4):VARCHAR(10) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], MGR=[$5], HIREDATE=[CAST($6):TIMESTAMP(0)], SAL=[CAST($7):INTEGER], COMM=[CAST($8):INTEGER], DEPTNO0=[CAST($9):INTEGER], SLACKER=[CAST($10):BOOLEAN])
+ JoinRel(condition=[=($0, $9)], joinType=[inner])
+ FilterRel(condition=[=($1, 'Charlie')])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ FilterRel(condition=[>($5, 100)])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLeftOuterJoinSimplificationToInner">
+ <Resource name="sql">
+ <![CDATA[select 1 from sales.dept d left outer join sales.emp e on d.deptno = e.deptno where e.sal > 100]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ FilterRel(condition=[>($7, 100)])
+ JoinRel(condition=[=($0, $9)], joinType=[left])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ ProjectRel(DEPTNO=[$0], NAME=[$1], EMPNO=[CAST($2):INTEGER], ENAME=[CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], JOB=[CAST($4):VARCHAR(10) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], MGR=[$5], HIREDATE=[CAST($6):TIMESTAMP(0)], SAL=[CAST($7):INTEGER], COMM=[CAST($8):INTEGER], DEPTNO0=[CAST($9):INTEGER], SLACKER=[CAST($10):BOOLEAN])
+ JoinRel(condition=[=($0, $9)], joinType=[inner])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ FilterRel(condition=[>($5, 100)])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRightOuterJoinSimplificationToInner">
+ <Resource name="sql">
+ <![CDATA[select 1 from sales.dept d right outer join sales.emp e on d.deptno = e.deptno where d.name = 'Charlie']]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ FilterRel(condition=[=($1, 'Charlie')])
+ JoinRel(condition=[=($0, $9)], joinType=[right])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+ProjectRel(EXPR$0=[1])
+ ProjectRel(DEPTNO=[CAST($0):INTEGER], NAME=[CAST($1):VARCHAR(10) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], EMPNO=[$2], ENAME=[$3], JOB=[$4], MGR=[$5], HIREDATE=[$6], SAL=[$7], COMM=[$8], DEPTNO0=[$9], SLACKER=[$10])
+ JoinRel(condition=[=($0, $9)], joinType=[inner])
+ FilterRel(condition=[=($1, 'Charlie')])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testPushFilterThroughSemiJoin">
<Resource name="sql">
<![CDATA[select * from (select * from dept where dept.deptno in (