You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/09/28 22:19:37 UTC

[6/7] incubator-calcite git commit: [CALCITE-895] Simplify "(CASE ... END) = constant" inside AND or OR (Hsuan-Yi Chu)

[CALCITE-895] Simplify "(CASE ... END) = constant" inside AND or OR (Hsuan-Yi Chu)

Close apache/incubator-calcite#135


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

Branch: refs/heads/master
Commit: 2fa63dda5809d519dc60aa36f464476bed961df3
Parents: 43b88db
Author: Hsuan-Yi Chu <hs...@usc.edu>
Authored: Mon Sep 21 15:39:25 2015 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Sep 28 12:07:15 2015 -0700

----------------------------------------------------------------------
 .../rel/rules/ReduceExpressionsRule.java        | 91 ++++++++++++++------
 .../apache/calcite/test/RelOptRulesTest.java    | 35 +++++++-
 .../org/apache/calcite/test/RelOptRulesTest.xml | 47 +++++++++-
 3 files changed, 146 insertions(+), 27 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/2fa63dda/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
index 5ab9192..61c64a8 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
@@ -479,36 +479,77 @@ public abstract class ReduceExpressionsRule extends RelOptRule {
     return ImmutableMap.copyOf(builder);
   }
 
+  /** Return if a given operator can be pushed into Case */
+  private static boolean isPushableIntoCase(RexNode node) {
+    if (!(node instanceof RexCall)) {
+      return false;
+    }
+
+    final RexCall call = (RexCall) node;
+    return call.getType().getSqlTypeName() == SqlTypeName.BOOLEAN
+        && (SqlKind.COMPARISON.contains(call.getKind()) || call.getOperands().size() == 1);
+  }
+
   private static RexCall pushPredicateIntoCase(RexCall call) {
     if (call.getType().getSqlTypeName() != SqlTypeName.BOOLEAN) {
       return call;
     }
-    int caseOrdinal = -1;
-    final List<RexNode> operands = call.getOperands();
-    for (int i = 0; i < operands.size(); i++) {
-      RexNode operand = operands.get(i);
-      switch (operand.getKind()) {
-      case CASE:
-        caseOrdinal = i;
-      }
-    }
-    if (caseOrdinal < 0) {
-      return call;
-    }
-    // Convert
-    //   f(CASE WHEN p1 THEN v1 ... END, arg)
-    // to
-    //   CASE WHEN p1 THEN f(v1, arg) ... END
-    final RexCall case_ = (RexCall) operands.get(caseOrdinal);
-    final List<RexNode> nodes = new ArrayList<>();
-    for (int i = 0; i < case_.getOperands().size(); i++) {
-      RexNode node = case_.getOperands().get(i);
-      if (!RexUtil.isCasePredicate(case_, i)) {
-        node = substitute(call, caseOrdinal, node);
+    final RexShuttle rexShuttle = new RexShuttle() {
+      @Override
+      public RexNode visitCall(RexCall call) {
+        final List<RexNode> newOperands = new ArrayList<>();
+        for (RexNode operand : call.getOperands()) {
+          newOperands.add(operand.accept(this));
+        }
+
+        call = call.clone(call.getType(), newOperands);
+        if (!isPushableIntoCase(call)) {
+          return call;
+        }
+
+        final List<RexNode> operands = call.getOperands();
+        int caseOrdinal = -1;
+        for (int i = 0; i < operands.size(); i++) {
+          RexNode operand = operands.get(i);
+          switch (operand.getKind()) {
+          case CASE:
+            caseOrdinal = i;
+          }
+        }
+        if (caseOrdinal < 0) {
+          return call;
+        }
+
+        // Convert
+        //   f(CASE WHEN p1 THEN v1 ... END, arg)
+        // to
+        //   CASE WHEN p1 THEN f(v1, arg) ... END
+        final RexCall case_ = (RexCall) operands.get(caseOrdinal);
+        final List<RexNode> nodes = new ArrayList<>();
+        for (int i = 0; i < case_.getOperands().size(); i++) {
+          RexNode node = case_.getOperands().get(i);
+          if (!RexUtil.isCasePredicate(case_, i)) {
+            node = substitute(call, caseOrdinal, node);
+
+            // For nested Case-When, the function should be pushed further deeper
+            // For example,
+            // Convert
+            //   f(CASE WHEN p1 THEN (CASE WHEN p2 THEN v2 ... END) ... END, arg)
+            // to
+            //   CASE WHEN p1 THEN f((CASE WHEN p2 THEN v2 ... END), arg) ... END
+            // further to
+            //   CASE WHEN p1 THEN (CASE WHEN p2 THEN f(v2, arg) ... END) ... END
+            if (isPushableIntoCase(node)) {
+              node = node.accept(this);
+            }
+          }
+          nodes.add(node);
+        }
+        return case_.clone(call.getType(), nodes);
       }
-      nodes.add(node);
-    }
-    return case_.clone(call.getType(), nodes);
+    };
+
+    return (RexCall) call.accept(rexShuttle);
   }
 
   /** Converts op(arg0, ..., argOrdinal, ..., argN) to op(arg0,..., node, ..., argN). */

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/2fa63dda/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 7ba514d..329bf76 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -144,8 +144,41 @@ public class RelOptRulesTest extends RelOptTestBase {
     return DiffRepository.lookup(RelOptRulesTest.class);
   }
 
+  @Test public void testReduceNestedCaseWhen() {
+    HepProgram preProgram = new HepProgramBuilder()
+        .build();
+
+    HepProgramBuilder builder = new HepProgramBuilder();
+    builder.addRuleClass(ReduceExpressionsRule.class);
+    HepPlanner hepPlanner = new HepPlanner(builder.build());
+    hepPlanner.addRule(ReduceExpressionsRule.FILTER_INSTANCE);
+
+    final String sql = "select sal\n"
+            + "from emp\n"
+            + "where case when (sal = 1000) then\n"
+            + "(case when sal = 1000 then null else 1 end is null) else\n"
+            + "(case when sal = 2000 then null else 1 end is null) end is true";
+    checkPlanning(tester, preProgram, hepPlanner, sql);
+  }
+
+  @Test public void testReduceORCaseWhen() {
+    HepProgram preProgram = new HepProgramBuilder()
+        .build();
+
+    HepProgramBuilder builder = new HepProgramBuilder();
+    builder.addRuleClass(ReduceExpressionsRule.class);
+    HepPlanner hepPlanner = new HepPlanner(builder.build());
+    hepPlanner.addRule(ReduceExpressionsRule.FILTER_INSTANCE);
+
+    final String sql = "select sal\n"
+        + "from emp\n"
+        + "where case when sal = 1000 then null else 1 end is null\n"
+        + "OR case when sal = 2000 then null else 1 end is null";
+    checkPlanning(tester, preProgram, hepPlanner, sql);
+  }
+
   @Test public void testProjectToWindowRuleForMultipleWindows() {
-    HepProgram preProgram =  new HepProgramBuilder()
+    HepProgram preProgram = new HepProgramBuilder()
         .build();
 
     HepProgramBuilder builder = new HepProgramBuilder();

http://git-wip-us.apache.org/repos/asf/incubator-calcite/blob/2fa63dda/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 80d547d..52948a9 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -16,6 +16,51 @@ See the License for the specific language governing permissions and
 limitations under the License.
 -->
 <Root>
+    <TestCase name="testReduceNestedCaseWhen">
+        <Resource name="sql">
+            <![CDATA[select sal
+from emp
+where case when (sal = 1000) then
+(case when sal = 1000 then null else 1 end is null) else
+(case when sal = 2000 then null else 1 end is null) end is true]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(SAL=[$5])
+  LogicalFilter(condition=[CASE(=($5, 1000), =($5, 1000), =($5, 2000))])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(SAL=[$5])
+  LogicalFilter(condition=[IS TRUE(CASE(=($5, 1000), IS NULL(CASE(=($5, 1000), null, 1)), IS NULL(CASE(=($5, 2000), null, 1))))])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testReduceORCaseWhen">
+        <Resource name="sql">
+            <![CDATA[select sal
+from emp
+where case when sal = 1000 then null else 1 end is null
+OR case when sal = 2000 then null else 1 end is null]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(SAL=[$5])
+  LogicalFilter(condition=[OR(=($5, 1000), =($5, 2000))])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(SAL=[$5])
+  LogicalFilter(condition=[OR(IS NULL(CASE(=($5, 1000), null, 1)), IS NULL(CASE(=($5, 2000), null, 1)))])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testWindowInParenthesis">
         <Resource name="sql">
             <![CDATA[select count(*) over (w), count(*) over w
@@ -583,7 +628,7 @@ LogicalProject(EXPR$0=[+(1, 2)], EXPR$1=[+($0, +(3, 4))], EXPR$2=[+(+(5, 6), $0)
         <Resource name="planAfter">
             <![CDATA[
 LogicalProject(EXPR$0=[3], EXPR$1=[+($0, 7)], EXPR$2=[+(11, $0)], EXPR$3=[null], EXPR$4=[CAST(2):INTEGER], EXPR$5=[ROW(15)])
-  LogicalFilter(condition=[AND(=($0, 15), =($0, CAST(2):INTEGER))])
+  LogicalFilter(condition=[AND(=($0, 15), =($0, 2))])
     LogicalProject(DEPTNO=[$0], NAME=[$1], EMPNO=[$2], ENAME=[$3], JOB=[$4], MGR=[$5], HIREDATE=[$6], SAL=[$7], COMM=[$8], DEPTNO0=[$9], SLACKER=[$10])
       LogicalJoin(condition=[=($0, $11)], joinType=[inner])
         LogicalTableScan(table=[[CATALOG, SALES, DEPT]])