calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject calcite git commit: [CALCITE-1753] Push expressions into null-generating side of a join if they are "strong" (null-preserving)
Date Tue, 25 Apr 2017 05:25:20 GMT
Repository: calcite
Updated Branches:
  refs/heads/master c850e227d -> 2ce41591b


[CALCITE-1753] Push expressions into null-generating side of a join if they are "strong" (null-preserving)

PushProjector now preserves expressions if the expression is strong
when pushing into the nullable-side of outer join. If an expression
f(x, y) is strong, we know that if it evaluates to null after the join
then x or y must be null, so it will also evaluate to null below the
join. Most SQL built-in functions are strong.

Close apache/calcite#425


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/2ce41591
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/2ce41591
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/2ce41591

Branch: refs/heads/master
Commit: 2ce41591be8986359ac7382359f6934be1fc402b
Parents: c850e22
Author: Minji Kim <minji@dremio.com>
Authored: Tue Apr 11 22:09:59 2017 -0700
Committer: Julian Hyde <jhyde@apache.org>
Committed: Mon Apr 24 20:20:19 2017 -0700

----------------------------------------------------------------------
 .../java/org/apache/calcite/plan/Strong.java    |  63 ++--
 .../rel/rules/ProjectFilterTransposeRule.java   |   3 +-
 .../rel/rules/ProjectJoinTransposeRule.java     |   7 +-
 .../rel/rules/ProjectSetOpTransposeRule.java    |   5 +-
 .../apache/calcite/rel/rules/PushProjector.java |  75 +++-
 .../apache/calcite/test/RelOptRulesTest.java    |  88 +++++
 .../org/apache/calcite/test/RelOptTestBase.java |   5 +-
 .../org/apache/calcite/test/RexProgramTest.java |  81 ++++-
 .../org/apache/calcite/test/RelOptRulesTest.xml | 352 ++++++++++++++++++-
 9 files changed, 626 insertions(+), 53 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/core/src/main/java/org/apache/calcite/plan/Strong.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/plan/Strong.java b/core/src/main/java/org/apache/calcite/plan/Strong.java
index 7fd6055..99a1b4e 100644
--- a/core/src/main/java/org/apache/calcite/plan/Strong.java
+++ b/core/src/main/java/org/apache/calcite/plan/Strong.java
@@ -20,12 +20,15 @@ import org.apache.calcite.rex.RexCall;
 import org.apache.calcite.rex.RexInputRef;
 import org.apache.calcite.rex.RexLiteral;
 import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
 import org.apache.calcite.sql.SqlKind;
 import org.apache.calcite.util.ImmutableBitSet;
 
 import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
 import com.google.common.collect.Iterables;
 
+import java.util.ArrayList;
 import java.util.EnumMap;
 import java.util.List;
 import java.util.Map;
@@ -103,40 +106,40 @@ public class Strong {
    * expressions, and you may override methods to test hypotheses such as
    * "if {@code x} is null, is {@code x + y} null? */
   public boolean isNull(RexNode node) {
+    final Policy policy = MAP.get(node.getKind());
+    switch (policy) {
+    case NOT_NULL:
+      return false;
+    case ANY:
+      return anyNull(((RexCall) node).getOperands());
+    default:
+      break;
+    }
+
     switch (node.getKind()) {
     case LITERAL:
       return ((RexLiteral) node).getValue() == null;
+    // We can only guarantee AND to return NULL if both inputs are NULL  (similar for OR)
+    // AND(NULL, FALSE) = FALSE
     case AND:
-    case NOT:
-    case EQUALS:
-    case NOT_EQUALS:
-    case LESS_THAN:
-    case LESS_THAN_OR_EQUAL:
-    case GREATER_THAN:
-    case GREATER_THAN_OR_EQUAL:
-    case PLUS_PREFIX:
-    case MINUS_PREFIX:
-    case PLUS:
-    case TIMESTAMP_ADD:
-    case MINUS:
-    case TIMESTAMP_DIFF:
-    case TIMES:
-    case DIVIDE:
-    case CAST:
-    case REINTERPRET:
-    case TRIM:
-    case LTRIM:
-    case RTRIM:
-    case CEIL:
-    case FLOOR:
-    case EXTRACT:
-    case GREATEST:
-    case LEAST:
-      return anyNull(((RexCall) node).getOperands());
     case OR:
+    case COALESCE:
       return allNull(((RexCall) node).getOperands());
+    case NULLIF:
+      // NULLIF(null, X) where X can be NULL, returns NULL
+      // NULLIF(X, Y) where X is not NULL, then this may return NULL if X = Y, otherwise
X.
+      return allNull(ImmutableList.of(((RexCall) node).getOperands().get(0)));
     case INPUT_REF:
       return isNull((RexInputRef) node);
+    case CASE:
+      final RexCall caseCall = (RexCall) node;
+      final List<RexNode> caseValues = new ArrayList<>();
+      for (int i = 0; i < caseCall.getOperands().size(); i++) {
+        if (!RexUtil.isCasePredicate(caseCall, i)) {
+          caseValues.add(caseCall.getOperands().get(i));
+        }
+      }
+      return allNull(caseValues);
     default:
       return false;
     }
@@ -183,9 +186,11 @@ public class Strong {
     // COALESCE(NULL, 2) yields 2
     map.put(SqlKind.COALESCE, Policy.AS_IS);
     map.put(SqlKind.NVL, Policy.AS_IS);
-    // FALSE OR NULL yields FALSE
+    // FALSE AND NULL yields FALSE
+    // TRUE AND NULL yields NULL
     map.put(SqlKind.AND, Policy.AS_IS);
     // TRUE OR NULL yields TRUE
+    // FALSE OR NULL yields NULL
     map.put(SqlKind.OR, Policy.AS_IS);
 
     // Expression types with custom handlers.
@@ -215,7 +220,9 @@ public class Strong {
     map.put(SqlKind.PLUS_PREFIX, Policy.ANY);
     map.put(SqlKind.MINUS_PREFIX, Policy.ANY);
     map.put(SqlKind.PLUS, Policy.ANY);
+    map.put(SqlKind.PLUS_PREFIX, Policy.ANY);
     map.put(SqlKind.MINUS, Policy.ANY);
+    map.put(SqlKind.MINUS_PREFIX, Policy.ANY);
     map.put(SqlKind.TIMES, Policy.ANY);
     map.put(SqlKind.DIVIDE, Policy.ANY);
     map.put(SqlKind.CAST, Policy.ANY);
@@ -228,6 +235,8 @@ public class Strong {
     map.put(SqlKind.EXTRACT, Policy.ANY);
     map.put(SqlKind.GREATEST, Policy.ANY);
     map.put(SqlKind.LEAST, Policy.ANY);
+    map.put(SqlKind.TIMESTAMP_ADD, Policy.ANY);
+    map.put(SqlKind.TIMESTAMP_DIFF, Policy.ANY);
 
     // Assume that any other expressions cannot be simplified.
     for (SqlKind k

http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java
index 0c7cb5c..f468f9c 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectFilterTransposeRule.java
@@ -100,8 +100,7 @@ public class ProjectFilterTransposeRule extends RelOptRule {
 
     PushProjector pushProjector =
         new PushProjector(
-            origProj, origFilter, rel, preserveExprCondition,
-            relBuilderFactory.create(origProj.getCluster(), null));
+            origProj, origFilter, rel, preserveExprCondition, call.builder());
     RelNode topProject = pushProjector.convertProject(null);
 
     if (topProject != null) {

http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinTransposeRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinTransposeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinTransposeRule.java
index b732635..2983703 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinTransposeRule.java
@@ -39,7 +39,7 @@ import java.util.List;
 public class ProjectJoinTransposeRule extends RelOptRule {
   public static final ProjectJoinTransposeRule INSTANCE =
       new ProjectJoinTransposeRule(
-          PushProjector.ExprCondition.FALSE,
+          PushProjector.ExprCondition.TRUE,
           RelFactories.LOGICAL_BUILDER);
 
   //~ Instance fields --------------------------------------------------------
@@ -87,7 +87,7 @@ public class ProjectJoinTransposeRule extends RelOptRule {
             join.getCondition(),
             join,
             preserveExprCondition,
-            relBuilderFactory.create(origProj.getCluster(), null));
+            call.builder());
     if (pushProject.locateAllRefs()) {
       return;
     }
@@ -109,8 +109,7 @@ public class ProjectJoinTransposeRule extends RelOptRule {
     RexNode newJoinFilter = null;
     int[] adjustments = pushProject.getAdjustments();
     if (join.getCondition() != null) {
-      List<RelDataTypeField> projJoinFieldList =
-          new ArrayList<RelDataTypeField>();
+      List<RelDataTypeField> projJoinFieldList = new ArrayList<>();
       projJoinFieldList.addAll(
           join.getSystemFieldList());
       projJoinFieldList.addAll(

http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/core/src/main/java/org/apache/calcite/rel/rules/ProjectSetOpTransposeRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectSetOpTransposeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/ProjectSetOpTransposeRule.java
index 5df05f6..48fb027 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ProjectSetOpTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectSetOpTransposeRule.java
@@ -84,11 +84,10 @@ public class ProjectSetOpTransposeRule extends RelOptRule {
     // locate all fields referenced in the projection
     PushProjector pushProject =
         new PushProjector(
-            origProj, null, setOp, preserveExprCondition,
-            relBuilderFactory.create(origProj.getCluster(), null));
+            origProj, null, setOp, preserveExprCondition, call.builder());
     pushProject.locateAllRefs();
 
-    List<RelNode> newSetOpInputs = new ArrayList<RelNode>();
+    List<RelNode> newSetOpInputs = new ArrayList<>();
     int[] adjustments = pushProject.getAdjustments();
 
     // push the projects completely below the setop; this

http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java b/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java
index 75b6a73..8b5f83e 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java
@@ -18,6 +18,7 @@ package org.apache.calcite.rel.rules;
 
 import org.apache.calcite.linq4j.Ord;
 import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.plan.Strong;
 import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.core.Join;
 import org.apache.calcite.rel.core.Project;
@@ -30,6 +31,7 @@ import org.apache.calcite.rex.RexInputRef;
 import org.apache.calcite.rex.RexNode;
 import org.apache.calcite.rex.RexUtil;
 import org.apache.calcite.rex.RexVisitorImpl;
+import org.apache.calcite.runtime.PredicateImpl;
 import org.apache.calcite.sql.SqlOperator;
 import org.apache.calcite.tools.RelBuilder;
 import org.apache.calcite.util.BitSets;
@@ -37,7 +39,9 @@ import org.apache.calcite.util.ImmutableBitSet;
 import org.apache.calcite.util.Pair;
 
 import com.google.common.base.Preconditions;
+import com.google.common.base.Predicate;
 import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
 import com.google.common.collect.Lists;
 
 import java.util.ArrayList;
@@ -103,6 +107,12 @@ public class PushProjector {
   final ImmutableBitSet rightBitmap;
 
   /**
+   * Bitmap containing the fields that should be strong, i.e. when preserving expressions
+   * we can only preserve them if the expressions if it is null when these fields are null.
+   */
+  final ImmutableBitSet strongBitmap;
+
+  /**
    * Number of fields in the RelNode that the projection is being pushed past,
    * if the RelNode is not a join. If the RelNode is a join, then this is the
    * number of fields in the left hand side of the join.
@@ -224,12 +234,29 @@ public class PushProjector {
           ImmutableBitSet.range(nSysFields, nFields + nSysFields);
       rightBitmap =
           ImmutableBitSet.range(nFields + nSysFields, nChildFields);
+
+      switch (joinRel.getJoinType()) {
+      case INNER:
+        strongBitmap = ImmutableBitSet.of();
+        break;
+      case RIGHT:  // All the left-input's columns must be strong
+        strongBitmap = ImmutableBitSet.range(nSysFields, nFields + nSysFields);
+        break;
+      case LEFT: // All the right-input's columns must be strong
+        strongBitmap = ImmutableBitSet.range(nFields + nSysFields, nChildFields);
+        break;
+      case FULL:
+      default:
+        strongBitmap = ImmutableBitSet.range(nSysFields, nChildFields);
+      }
+
     } else {
       nFields = nChildFields;
       nFieldsRight = 0;
       childBitmap = ImmutableBitSet.range(nChildFields);
       rightBitmap = null;
       nSysFields = 0;
+      strongBitmap = ImmutableBitSet.of();
     }
     assert nChildFields == nSysFields + nFields + nFieldsRight;
 
@@ -341,6 +368,7 @@ public class PushProjector {
             projRefs,
             childBitmap,
             rightBitmap,
+            strongBitmap,
             preserveExprCondition,
             childPreserveExprs,
             rightPreserveExprs),
@@ -587,14 +615,17 @@ public class PushProjector {
     private final BitSet rexRefs;
     private final ImmutableBitSet leftFields;
     private final ImmutableBitSet rightFields;
+    private final ImmutableBitSet strongFields;
     private final ExprCondition preserveExprCondition;
     private final List<RexNode> preserveLeft;
     private final List<RexNode> preserveRight;
+    private final Strong strong;
 
     public InputSpecialOpFinder(
         BitSet rexRefs,
         ImmutableBitSet leftFields,
         ImmutableBitSet rightFields,
+        final ImmutableBitSet strongFields,
         ExprCondition preserveExprCondition,
         List<RexNode> preserveLeft,
         List<RexNode> preserveRight) {
@@ -605,6 +636,9 @@ public class PushProjector {
       this.preserveExprCondition = preserveExprCondition;
       this.preserveLeft = preserveLeft;
       this.preserveRight = preserveRight;
+
+      this.strongFields = strongFields;
+      this.strong = Strong.of(strongFields);
     }
 
     public Void visitCall(RexCall call) {
@@ -615,6 +649,16 @@ public class PushProjector {
       return null;
     }
 
+    private boolean isStrong(final ImmutableBitSet exprArgs, final RexNode call) {
+      // If the expressions do not use any of the inputs that require output to be null,
+      // no need to check.  Otherwise, check that the expression is null.
+      // For example, in an "left outer join", we don't require that expressions
+      // pushed down into the left input to be strong.  On the other hand,
+      // expressions pushed into the right input must be.  In that case,
+      // strongFields == right input fields.
+      return !strongFields.intersects(exprArgs) || strong.isNull(call);
+    }
+
     private boolean preserve(RexNode call) {
       if (preserveExprCondition.test(call)) {
         // if the arguments of the expression only reference the
@@ -622,10 +666,10 @@ public class PushProjector {
         // it only references expressions on the right
         final ImmutableBitSet exprArgs = RelOptUtil.InputFinder.bits(call);
         if (exprArgs.cardinality() > 0) {
-          if (leftFields.contains(exprArgs)) {
+          if (leftFields.contains(exprArgs) && isStrong(exprArgs, call)) {
             addExpr(preserveLeft, call);
             return true;
-          } else if (rightFields.contains(exprArgs)) {
+          } else if (rightFields.contains(exprArgs) && isStrong(exprArgs, call))
{
             assert preserveRight != null;
             addExpr(preserveRight, call);
             return true;
@@ -759,7 +803,7 @@ public class PushProjector {
    *
    * @see org.apache.calcite.rel.rules.PushProjector.OperatorExprCondition
    */
-  public interface ExprCondition {
+  public interface ExprCondition extends Predicate<RexNode> {
     /**
      * Evaluates a condition for a given expression.
      *
@@ -772,18 +816,33 @@ public class PushProjector {
      * Constant condition that replies {@code false} for all expressions.
      */
     ExprCondition FALSE =
-        new ExprCondition() {
-          public boolean test(RexNode expr) {
+        new ExprConditionImpl() {
+          @Override public boolean test(RexNode expr) {
             return false;
           }
         };
+
+    /**
+     * Constant condition that replies {@code true} for all expressions.
+     */
+    ExprCondition TRUE =
+        new ExprConditionImpl() {
+          @Override public boolean test(RexNode expr) {
+            return true;
+          }
+        };
+  }
+
+  /** Implementation of {@link ExprCondition}. */
+  abstract static class ExprConditionImpl extends PredicateImpl<RexNode>
+      implements ExprCondition {
   }
 
   /**
    * An expression condition that evaluates to true if the expression is
    * a call to one of a set of operators.
    */
-  public static class OperatorExprCondition implements ExprCondition {
+  class OperatorExprCondition extends ExprConditionImpl {
     private final Set<SqlOperator> operatorSet;
 
     /**
@@ -791,8 +850,8 @@ public class PushProjector {
      *
      * @param operatorSet Set of operators
      */
-    public OperatorExprCondition(Set<SqlOperator> operatorSet) {
-      this.operatorSet = operatorSet;
+    public OperatorExprCondition(Iterable<? extends SqlOperator> operatorSet) {
+      this.operatorSet = ImmutableSet.copyOf(operatorSet);
     }
 
     public boolean test(RexNode expr) {

http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 8f553bb..f70640f 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -910,6 +910,94 @@ public class RelOptRulesTest extends RelOptTestBase {
             + "on e.ename = b.ename and e.deptno = 10");
   }
 
+  private static final String NOT_STRONG_EXPR =
+      "case when e.sal < 11 then 11 else -1 * e.sal end ";
+
+  private static final String STRONG_EXPR =
+      "case when e.sal < 11 then -1 * e.sal else e.sal end ";
+
+  /** Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-1753">[CALCITE-1753]
+   * PushProjector should only preserve expressions if the expression is strong
+   * when pushing into the nullable-side of outer join</a>. */
+  @Test public void testPushProjectPastInnerJoin() {
+    final String sql = "select count(*), " + NOT_STRONG_EXPR + "\n"
+        + "from emp e inner join bonus b on e.ename = b.ename\n"
+        + "group by " + NOT_STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastInnerJoinStrong() {
+    final String sql = "select count(*), " + STRONG_EXPR + "\n"
+        + "from emp e inner join bonus b on e.ename = b.ename\n"
+        + "group by " + STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastLeftJoin() {
+    final String sql = "select count(*), " + NOT_STRONG_EXPR + "\n"
+        + "from emp e left outer join bonus b on e.ename = b.ename\n"
+        + "group by case when e.sal < 11 then 11 else -1 * e.sal end";
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastLeftJoinSwap() {
+    final String sql = "select count(*), " + NOT_STRONG_EXPR + "\n"
+        + "from bonus b left outer join emp e on e.ename = b.ename\n"
+        + "group by " + NOT_STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastLeftJoinSwapStrong() {
+    final String sql = "select count(*), " + STRONG_EXPR + "\n"
+        + "from bonus b left outer join emp e on e.ename = b.ename\n"
+        + "group by " + STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastRightJoin() {
+    final String sql = "select count(*), " + NOT_STRONG_EXPR + "\n"
+        + "from emp e right outer join bonus b on e.ename = b.ename\n"
+        + "group by " + NOT_STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastRightJoinStrong() {
+    final String sql = "select count(*),\n"
+        + " case when e.sal < 11 then -1 * e.sal else e.sal end\n"
+        + "from emp e right outer join bonus b on e.ename = b.ename\n"
+        + "group by case when e.sal < 11 then -1 * e.sal else e.sal end";
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastRightJoinSwap() {
+    final String sql = "select count(*), " + NOT_STRONG_EXPR + "\n"
+        + "from bonus b right outer join emp e on e.ename = b.ename\n"
+        + "group by " + NOT_STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastRightJoinSwapStrong() {
+    final String sql = "select count(*), " + STRONG_EXPR + "\n"
+        + "from bonus b right outer join emp e on e.ename = b.ename\n"
+        + "group by " + STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastFullJoin() {
+    final String sql = "select count(*), " + NOT_STRONG_EXPR + "\n"
+        + "from emp e full outer join bonus b on e.ename = b.ename\n"
+        + "group by " + NOT_STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
+  @Test public void testPushProjectPastFullJoinStrong() {
+    final String sql = "select count(*), " + STRONG_EXPR + "\n"
+        + "from emp e full outer join bonus b on e.ename = b.ename\n"
+        + "group by " + STRONG_EXPR;
+    sql(sql).withRule(ProjectJoinTransposeRule.INSTANCE).check();
+  }
+
   @Test public void testPushProjectPastSetOp() {
     checkPlanning(ProjectSetOpTransposeRule.INSTANCE,
         "select sal from "

http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
index 28f0b9c..848918e 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
@@ -220,6 +220,10 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
           transforms);
     }
 
+    public Sql withRule(RelOptRule rule) {
+      return with(HepProgram.builder().addRuleInstance(rule).build());
+    }
+
     /** Adds a transform that will be applied to {@link #tester}
      * just before running the query. */
     private Sql withTransform(Function<Tester, Tester> transform) {
@@ -296,7 +300,6 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
         checkPlanning(t, preProgram, hepPlanner, sql, unchanged);
       }
     }
-
   }
 
 }

http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/core/src/test/java/org/apache/calcite/test/RexProgramTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RexProgramTest.java b/core/src/test/java/org/apache/calcite/test/RexProgramTest.java
index 671b685..ebc17f6 100644
--- a/core/src/test/java/org/apache/calcite/test/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RexProgramTest.java
@@ -170,6 +170,10 @@ public class RexProgramTest {
     return rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, node);
   }
 
+  private RexNode nullIf(RexNode node1, RexNode node2) {
+    return rexBuilder.makeCall(SqlStdOperatorTable.NULLIF, node1, node2);
+  }
+
   private RexNode not(RexNode node) {
     return rexBuilder.makeCall(SqlStdOperatorTable.NOT, node);
   }
@@ -537,16 +541,19 @@ public class RexProgramTest {
     final RexNode andTrueUnknown = and(trueLiteral, unknownLiteral);
     final RexNode andFalseTrue = and(falseLiteral, trueLiteral);
 
-    assertThat(Strong.isNull(andUnknownTrue, c), is(true));
-    assertThat(Strong.isNull(andTrueUnknown, c), is(true));
+    assertThat(Strong.isNull(andUnknownTrue, c), is(false));
+    assertThat(Strong.isNull(andTrueUnknown, c), is(false));
     assertThat(Strong.isNull(andFalseTrue, c), is(false));
 
     // If i0 is null, "i0 and i1 is null" is null
-    assertThat(Strong.isNull(and(i0, isNull(i1)), c0), is(true));
-    // If i1 is null, "i0 and i1 is null" is not necessarily null
+    assertThat(Strong.isNull(and(i0, isNull(i1)), c0), is(false));
+    // If i1 is null, "i0 and i1" is false
     assertThat(Strong.isNull(and(i0, isNull(i1)), c1), is(false));
-    // If i0 and i1 are both null, "i0 and i1 is null" is null
-    assertThat(Strong.isNull(and(i0, isNull(i1)), c01), is(true));
+    // If i0 and i1 are both null, "i0 and i1" is null
+    assertThat(Strong.isNull(and(i0, i1), c01), is(true));
+    assertThat(Strong.isNull(and(i0, i1), c1), is(false));
+    // If i0 and i1 are both null, "i0 and isNull(i1) is false"
+    assertThat(Strong.isNull(and(i0, isNull(i1)), c01), is(false));
     // If i0 and i1 are both null, "i0 or i1" is null
     assertThat(Strong.isNull(or(i0, i1), c01), is(true));
     // If i0 is null, "i0 or i1" is not necessarily null
@@ -563,6 +570,68 @@ public class RexProgramTest {
     RexNode notI0NotNull = not(isNotNull(i0));
     assertThat(Strong.isNull(notI0NotNull, c0), is(false));
     assertThat(Strong.isNotTrue(notI0NotNull, c0), is(false));
+
+    // NULLIF(null, null): null
+    // NULLIF(null, X): null
+    // NULLIF(X, X/Y): null or X
+    // NULLIF(X, null): X
+    assertThat(Strong.isNull(nullIf(nullLiteral, nullLiteral), c), is(true));
+    assertThat(Strong.isNull(nullIf(nullLiteral, trueLiteral), c), is(true));
+    assertThat(Strong.isNull(nullIf(trueLiteral, trueLiteral), c), is(false));
+    assertThat(Strong.isNull(nullIf(trueLiteral, falseLiteral), c), is(false));
+    assertThat(Strong.isNull(nullIf(trueLiteral, nullLiteral), c), is(false));
+
+    // ISNULL(null) is true, ISNULL(not null value) is false
+    assertThat(Strong.isNull(isNull(nullLiteral), c01), is(false));
+    assertThat(Strong.isNull(isNull(trueLiteral), c01), is(false));
+
+    // CASE ( <predicate1> <value1> <predicate2> <value2> <predicate3>
<value3> ...)
+    // only definitely null if all values are null.
+    assertThat(
+        Strong.isNull(
+            case_(eq(i0, i1), nullLiteral, ge(i0, i1), nullLiteral, nullLiteral), c01),
+        is(true));
+    assertThat(
+        Strong.isNull(
+            case_(eq(i0, i1), i0, ge(i0, i1), nullLiteral, nullLiteral), c01),
+        is(true));
+    assertThat(
+        Strong.isNull(
+            case_(eq(i0, i1), i0, ge(i0, i1), nullLiteral, nullLiteral), c1),
+        is(false));
+    assertThat(
+        Strong.isNull(
+            case_(eq(i0, i1), nullLiteral, ge(i0, i1), i0, nullLiteral), c01),
+        is(true));
+    assertThat(
+        Strong.isNull(
+            case_(eq(i0, i1), nullLiteral, ge(i0, i1), i0, nullLiteral), c1),
+        is(false));
+    assertThat(
+        Strong.isNull(
+            case_(eq(i0, i1), nullLiteral, ge(i0, i1), nullLiteral, i0), c01),
+        is(true));
+    assertThat(
+        Strong.isNull(
+            case_(eq(i0, i1), nullLiteral, ge(i0, i1), nullLiteral, i0), c1),
+        is(false));
+    assertThat(
+        Strong.isNull(
+            case_(isNotNull(i0), i0, i1), c),
+        is(false));
+    assertThat(
+        Strong.isNull(
+            case_(isNotNull(i0), i0, i1), c0),
+        is(false));
+    assertThat(
+        Strong.isNull(
+            case_(isNotNull(i0), i0, i1), c1),
+        is(false));
+    assertThat(
+        Strong.isNull(
+            case_(isNotNull(i0), i0, i1), c01),
+        is(true));
+
   }
 
   /** Unit test for {@link org.apache.calcite.rex.RexUtil#toCnf}. */

http://git-wip-us.apache.org/repos/asf/calcite/blob/2ce41591/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 cc6b417..2314612 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -854,14 +854,362 @@ LogicalProject(EXPR$0=[+($5, $12)])
         <Resource name="planAfter">
             <![CDATA[
 LogicalProject(EXPR$0=[+($1, $4)])
-  LogicalJoin(condition=[AND(=($0, $3), =($2, 10))], joinType=[inner])
-    LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
+  LogicalJoin(condition=[AND(=($0, $3), $2)], joinType=[inner])
+    LogicalProject(ENAME=[$1], SAL=[$5], ==[=($7, 10)])
       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
     LogicalProject(ENAME=[$0], COMM=[$3])
       LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testPushProjectPastInnerJoinStrong">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then -1 * e.sal else e.sal
end
+            from emp e inner join bonus b on e.ename = b.ename group by
+            case when e.sal < 11 then -1 * e.sal else e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($5, 11), *(-1, $5), $5)])
+      LogicalJoin(condition=[=($1, $9)], joinType=[inner])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$1])
+      LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastInnerJoin">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then 11 else -1 * e.sal
end
+            from emp e inner join bonus b on e.ename = b.ename group by
+            case when e.sal < 11 then 11 else -1 * e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($5, 11), 11, *(-1, $5))])
+      LogicalJoin(condition=[=($1, $9)], joinType=[inner])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$1])
+      LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), 11, *(-1, $5))])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastLeftJoin">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then 11 else -1 * e.sal
end
+            from emp e left outer join bonus b on e.ename = b.ename group by
+            case when e.sal < 11 then 11 else -1 * e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($5, 11), 11, *(-1, $5))])
+      LogicalJoin(condition=[=($1, $9)], joinType=[left])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$1])
+      LogicalJoin(condition=[=($0, $2)], joinType=[left])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), 11, *(-1, $5))])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastLeftJoinSwap">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then 11 else -1 * e.sal
end
+            from bonus b left outer join emp e on e.ename = b.ename group by
+            case when e.sal < 11 then 11 else -1 * e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($9, 11), 11, *(-1, $9))])
+      LogicalJoin(condition=[=($5, $0)], joinType=[left])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE($2, 11, $3)])
+      LogicalJoin(condition=[=($1, $0)], joinType=[left])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalProject(ENAME=[$1], <=[<($5, 11)], *=[*(-1, $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastLeftJoinSwapStrong">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then -1 * e.sal else e.sal
end
+            from bonus b left outer join emp e on e.ename = b.ename group by
+            case when e.sal < 11 then -1 * e.sal else e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($9, 11), *(-1, $9), $9)])
+      LogicalJoin(condition=[=($5, $0)], joinType=[left])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$2])
+      LogicalJoin(condition=[=($1, $0)], joinType=[left])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastLeftJoinStrong">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then -1 * e.sal else e.sal
end
+            from bonus b left outer join emp e on e.ename = b.ename group by
+            case when e.sal < 11 then -1 * e.sal else e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($9, 11), *(-1, $9), $9)])
+      LogicalJoin(condition=[=($5, $0)], joinType=[left])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$2])
+      LogicalJoin(condition=[=($1, $0)], joinType=[left])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastRightJoin">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then 11 else -1 * e.sal
end
+            from emp e right outer join bonus b on e.ename = b.ename group by
+            case when e.sal < 11 then 11 else -1 * e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($5, 11), 11, *(-1, $5))])
+      LogicalJoin(condition=[=($1, $9)], joinType=[right])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE($1, 11, $2)])
+      LogicalJoin(condition=[=($0, $3)], joinType=[right])
+        LogicalProject(ENAME=[$1], <=[<($5, 11)], *=[*(-1, $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastRightJoinStrong">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then -1 * e.sal else e.sal
end
+            from emp e right outer join bonus b on e.ename = b.ename group by
+            case when e.sal < 11 then -1 * e.sal else e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($5, 11), *(-1, $5), $5)])
+      LogicalJoin(condition=[=($1, $9)], joinType=[right])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$1])
+      LogicalJoin(condition=[=($0, $2)], joinType=[right])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastRightJoinSwap">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then 11 else -1 * e.sal
end
+            from bonus b right outer join emp e on e.ename = b.ename group by
+            case when e.sal < 11 then 11 else -1 * e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($9, 11), 11, *(-1, $9))])
+      LogicalJoin(condition=[=($5, $0)], joinType=[right])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$2])
+      LogicalJoin(condition=[=($1, $0)], joinType=[right])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), 11, *(-1, $5))])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastRightJoinSwapStrong">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then -1 * e.sal else e.sal
end
+            from bonus b right outer join emp e on e.ename = b.ename group by
+            case when e.sal < 11 then -1 * e.sal else e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($9, 11), *(-1, $9), $9)])
+      LogicalJoin(condition=[=($5, $0)], joinType=[right])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$2])
+      LogicalJoin(condition=[=($1, $0)], joinType=[right])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastFullJoin">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then 11 else -1 * e.sal
end
+            from emp e full outer join bonus b on e.ename = b.ename group by
+            case when e.sal < 11 then 11 else -1 * e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($5, 11), 11, *(-1, $5))])
+      LogicalJoin(condition=[=($1, $9)], joinType=[full])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE($1, 11, $2)])
+      LogicalJoin(condition=[=($0, $3)], joinType=[full])
+        LogicalProject(ENAME=[$1], <=[<($5, 11)], *=[*(-1, $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testPushProjectPastFullJoinStrong">
+        <Resource name="sql">
+            <![CDATA[select count(*), case when e.sal < 11 then -1 * e.sal else e.sal
end
+            from emp e full outer join bonus b on e.ename = b.ename group by
+            case when e.sal < 11 then -1 * e.sal else e.sal end]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[CASE(<($5, 11), *(-1, $5), $5)])
+      LogicalJoin(condition=[=($1, $9)], joinType=[full])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
+  LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+    LogicalProject(EXPR$1=[$1])
+      LogicalJoin(condition=[=($0, $2)], joinType=[full])
+        LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(ENAME=[$0])
+          LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testPushProjectPastSetOp">
         <Resource name="sql">
             <![CDATA[select sal from (select * from emp e1 union all select * from emp
e2)]]>


Mime
View raw message