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]])