Repository: calcite Updated Branches: refs/heads/master 60b4f4eb1 -> 520c0ccc9
[CALCITE-1778] Query with "WHERE CASE ..." throws AssertionError "Cast for just nullability not allowed" Solve by making RelBuilder remove nullability casts before creating filters. Remove some spaces from test logs. Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/6b8e2c3d Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/6b8e2c3d Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/6b8e2c3d Branch: refs/heads/master Commit: 6b8e2c3d3e4468206ac974a090fa8e7db6b4e347 Parents: 60b4f4e Author: Julian Hyde <[email protected]> Authored: Mon May 8 14:55:29 2017 -0700 Committer: Julian Hyde <[email protected]> Committed: Mon May 8 17:12:55 2017 -0700 ---------------------------------------------------------------------- .../rel/rules/FilterProjectTransposeRule.java | 27 ++++---- .../rel/rules/ReduceExpressionsRule.java | 9 +-- .../org/apache/calcite/rex/RexSimplify.java | 10 +++ .../org/apache/calcite/tools/RelBuilder.java | 10 ++- .../apache/calcite/test/RelOptRulesTest.java | 15 ++++- .../org/apache/calcite/test/RelOptRulesTest.xml | 71 +++++++++++++------- 6 files changed, 94 insertions(+), 48 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/6b8e2c3d/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java index 2c0797b..d774b2a 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java @@ -24,10 +24,9 @@ import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Filter; import org.apache.calcite.rel.core.Project; import org.apache.calcite.rel.core.RelFactories; -import org.apache.calcite.rel.type.RelDataTypeFactory; -import org.apache.calcite.rex.RexCall; import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexOver; +import org.apache.calcite.rex.RexSimplify; import org.apache.calcite.rex.RexUtil; import org.apache.calcite.tools.RelBuilder; import org.apache.calcite.tools.RelBuilderFactory; @@ -117,20 +116,18 @@ public class FilterProjectTransposeRule extends RelOptRule { RexNode newCondition = RelOptUtil.pushPastProject(filter.getCondition(), project); - // Remove cast of BOOLEAN NOT NULL to BOOLEAN or vice versa. Filter accepts - // nullable and not-nullable conditions, but a CAST might get in the way of - // other rewrites. - final RelDataTypeFactory typeFactory = filter.getCluster().getTypeFactory(); - if (RexUtil.isNullabilityCast(typeFactory, newCondition)) { - newCondition = ((RexCall) newCondition).getOperands().get(0); - } - final RelBuilder relBuilder = call.builder(); - RelNode newFilterRel = - copyFilter - ? filter.copy(filter.getTraitSet(), project.getInput(), - newCondition) - : relBuilder.push(project.getInput()).filter(newCondition).build(); + RelNode newFilterRel; + if (copyFilter) { + final RexSimplify simplify = + new RexSimplify(relBuilder.getRexBuilder(), false, RexUtil.EXECUTOR); + newCondition = simplify.removeNullabilityCast(newCondition); + newFilterRel = filter.copy(filter.getTraitSet(), project.getInput(), + newCondition); + } else { + newFilterRel = + relBuilder.push(project.getInput()).filter(newCondition).build(); + } RelNode newProjRel = copyProject http://git-wip-us.apache.org/repos/asf/calcite/blob/6b8e2c3d/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 d5530c9..cc1b681 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 @@ -170,12 +170,9 @@ public abstract class ReduceExpressionsRule extends RelOptRule { || RexUtil.isNullLiteral(newConditionExp, true)) { call.transformTo(createEmptyRelOrEquivalent(call, filter)); } else if (reduced) { - if (RexUtil.isNullabilityCast(filter.getCluster().getTypeFactory(), - newConditionExp)) { - newConditionExp = ((RexCall) newConditionExp).getOperands().get(0); - } - call.transformTo(call.builder(). - push(filter.getInput()).filter(newConditionExp).build()); + call.transformTo(call.builder() + .push(filter.getInput()) + .filter(newConditionExp).build()); } else { if (newConditionExp instanceof RexCall) { boolean reverse = newConditionExp.getKind() == SqlKind.NOT; http://git-wip-us.apache.org/repos/asf/calcite/blob/6b8e2c3d/core/src/main/java/org/apache/calcite/rex/RexSimplify.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java index fb791f5..97656d6 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java +++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java @@ -821,6 +821,16 @@ public class RexSimplify { } } + /** Removes any casts that change nullability but not type. + * + * <p>For example, {@code CAST(1 = 0 AS BOOLEAN)} becomes {@code 1 = 0}. */ + public RexNode removeNullabilityCast(RexNode e) { + while (RexUtil.isNullabilityCast(rexBuilder.getTypeFactory(), e)) { + e = ((RexCall) e).operands.get(0); + } + return e; + } + private static RexNode processRange(RexBuilder rexBuilder, List<RexNode> terms, Map<String, Pair<Range, List<RexNode>>> rangeTerms, RexNode term, RexNode ref, RexLiteral constant, SqlKind comparison) { http://git-wip-us.apache.org/repos/asf/calcite/blob/6b8e2c3d/core/src/main/java/org/apache/calcite/tools/RelBuilder.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java index c2d76f0..2e6750d 100644 --- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java +++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java @@ -812,9 +812,15 @@ public class RelBuilder { if (x.isAlwaysFalse()) { return empty(); } - if (!x.isAlwaysTrue()) { + + // Remove cast of BOOLEAN NOT NULL to BOOLEAN or vice versa. Filter accepts + // nullable and not-nullable conditions, but a CAST might get in the way of + // other rewrites. + final RexNode x2 = simplifierUnknownAsFalse.removeNullabilityCast(x); + + if (!x2.isAlwaysTrue()) { final Frame frame = stack.pop(); - final RelNode filter = filterFactory.createFilter(frame.rel, x); + final RelNode filter = filterFactory.createFilter(frame.rel, x2); stack.push(new Frame(filter, frame.fields)); } return this; http://git-wip-us.apache.org/repos/asf/calcite/blob/6b8e2c3d/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 f70640f..3f22f52 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -904,6 +904,17 @@ public class RelOptRulesTest extends RelOptTestBase { + "and upper(ename) = 'FOO'"); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1778";>[CALCITE-1778] + * Query with "WHERE CASE" throws AssertionError "Cast for just nullability + * not allowed"</a>. */ + @Test public void testPushProjectPastFilter2() { + final String sql = "select count(*)\n" + + "from emp\n" + + "where case when mgr < 10 then true else false end"; + sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check(); + } + @Test public void testPushProjectPastJoin() { checkPlanning(ProjectJoinTransposeRule.INSTANCE, "select e.sal + b.comm from emp e inner join bonus b " @@ -911,10 +922,10 @@ public class RelOptRulesTest extends RelOptTestBase { } private static final String NOT_STRONG_EXPR = - "case when e.sal < 11 then 11 else -1 * e.sal end "; + "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 "; + "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] http://git-wip-us.apache.org/repos/asf/calcite/blob/6b8e2c3d/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 dd3861b..046505c 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -865,8 +865,8 @@ LogicalProject(EXPR$0=[+($1, $4)]) <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]]> +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[ @@ -894,8 +894,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -923,8 +923,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -952,8 +952,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -981,8 +981,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -1039,8 +1039,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -1067,9 +1067,10 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) </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]]> + <![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[ @@ -1097,8 +1098,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -1126,8 +1127,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -1155,8 +1156,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -1184,8 +1185,8 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0]) <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]]> +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[ @@ -3753,6 +3754,30 @@ LogicalAggregate(group=[{1}], EXPR$1=[$SUM0($2)]) ]]> </Resource> </TestCase> + <TestCase name="testPushProjectPastFilter2"> + <Resource name="sql"> + <![CDATA[select count(*) +from emp +where case when mgr < 10 then true else false end]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{}], EXPR$0=[COUNT()]) + LogicalProject($f0=[0]) + LogicalFilter(condition=[CASE(<($3, 10), true, false)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{}], EXPR$0=[COUNT()]) + LogicalProject($f0=[0]) + LogicalFilter(condition=[<($0, 10)]) + LogicalProject(MGR=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testTransitiveInferenceJoin"> <Resource name="sql"> <![CDATA[select 1 from sales.emp d inner join sales.emp e on d.deptno = e.deptno where e.deptno > 7]]>
