calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject git commit: [CALCITE-444] Filters wrongly pushed into full outer join
Date Thu, 23 Oct 2014 20:40:14 GMT
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 <jpullokkaran@hortonworks.com>
Authored: Thu Oct 23 13:04:13 2014 -0700
Committer: Julian Hyde <jhyde@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 (


Mime
View raw message