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 <[email protected]> Authored: Tue Apr 11 22:09:59 2017 -0700 Committer: Julian Hyde <[email protected]> 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)]]>
