[CALCITE-2066] RelOptUtil.splitJoinCondition() could not split CASE condition after applying FilterReduceExpressionsRule (Volodymyr Vysotskyi)
The solution is to less aggressively push "=" into CASE. Close apache/calcite#573 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/b60b67eb Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/b60b67eb Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/b60b67eb Branch: refs/heads/master Commit: b60b67eb8f62463ccbc230358969ef2450cdbe05 Parents: c1749ad Author: Volodymyr Vysotskyi <[email protected]> Authored: Tue Nov 28 14:22:12 2017 +0200 Committer: Julian Hyde <[email protected]> Committed: Mon Dec 11 14:24:53 2017 -0800 ---------------------------------------------------------------------- .../rel/rules/ReduceExpressionsRule.java | 12 ++++++++++ .../apache/calcite/test/RelOptRulesTest.java | 13 +++++++++++ .../org/apache/calcite/test/RelOptRulesTest.xml | 23 ++++++++++++++++++++ 3 files changed, 48 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/b60b67eb/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 47a859c..264d123 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 @@ -20,6 +20,7 @@ import org.apache.calcite.plan.RelOptCluster; import org.apache.calcite.plan.RelOptPredicateList; import org.apache.calcite.plan.RelOptRule; import org.apache.calcite.plan.RelOptRuleCall; +import org.apache.calcite.plan.RelOptUtil; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Calc; import org.apache.calcite.rel.core.EquiJoin; @@ -61,6 +62,7 @@ import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.tools.RelBuilder; import org.apache.calcite.tools.RelBuilderFactory; +import org.apache.calcite.util.ImmutableBitSet; import org.apache.calcite.util.Pair; import org.apache.calcite.util.Util; @@ -702,6 +704,16 @@ public abstract class ReduceExpressionsRule extends RelOptRule { case AND: case OR: return call; // don't push CASE into CASE! + case EQUALS: { + // checks that the EQUALS operands may be splitted and + // doesn't push EQUALS into CASE + List<RexNode> equalsOperands = call.getOperands(); + ImmutableBitSet left = RelOptUtil.InputFinder.bits(equalsOperands.get(0)); + ImmutableBitSet right = RelOptUtil.InputFinder.bits(equalsOperands.get(1)); + if (!left.isEmpty() && !right.isEmpty() && left.intersect(right).isEmpty()) { + return call; + } + } } int caseOrdinal = -1; final List<RexNode> operands = call.getOperands(); http://git-wip-us.apache.org/repos/asf/calcite/blob/b60b67eb/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 7e1d1ec..b711ec8 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -1609,6 +1609,19 @@ public class RelOptRulesTest extends RelOptTestBase { + " else 0 end = 1"); } + @Test + public void testSkipReduceConstantsCaseEquals() throws Exception { + HepProgram program = new HepProgramBuilder() + .addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE) + .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE) + .addRuleInstance(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN) + .build(); + + checkPlanning(program, + "select * from emp e1, emp e2\n" + + "where coalesce(e1.mgr, -1) = coalesce(e2.mgr, -1)"); + } + @Test public void testReduceConstantsEliminatesFilter() throws Exception { HepProgram program = new HepProgramBuilder() .addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE) http://git-wip-us.apache.org/repos/asf/calcite/blob/b60b67eb/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 8061eac..1b2287b 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -6128,6 +6128,29 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT()]) ]]> </Resource> </TestCase> + <TestCase name="testSkipReduceConstantsCaseEquals"> + <Resource name="sql"> + <![CDATA[select * from emp e1, emp e2 +where coalesce(e1.mgr, -1) = coalesce(e2.mgr, -1)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10], JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15], DEPTNO0=[$16], SLACKER0=[$17]) + LogicalFilter(condition=[=(CASE(IS NOT NULL($3), $3, -1), CASE(IS NOT NULL($12), $12, -1))]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10], JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15], DEPTNO0=[$16], SLACKER0=[$17]) + LogicalJoin(condition=[=(CASE(IS NOT NULL($3), $3, -1), CASE(IS NOT NULL($12), $12, -1))], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testPushFilterPastAggThree"> <Resource name="sql"> <