Repository: calcite Updated Branches: refs/heads/master f21719623 -> 5bf7b9a4d
[CALCITE-2205] JoinPushTransitivePredicatesRule should not create Filter on top of equivalent Filter (Vitalii Diravka) Close apache/calcite#645 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/499f0c6e Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/499f0c6e Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/499f0c6e Branch: refs/heads/master Commit: 499f0c6e9c3b2c35b803037f5b60e54fd4a6a5f3 Parents: f217196 Author: Vitalii Diravka <[email protected]> Authored: Tue Mar 6 14:49:26 2018 +0200 Committer: Julian Hyde <[email protected]> Committed: Sat Mar 24 15:58:00 2018 -0700 ---------------------------------------------------------------------- .../calcite/rel/metadata/RelMdPredicates.java | 102 ++++++++++--------- .../org/apache/calcite/rex/RexSimplify.java | 19 ++++ .../org/apache/calcite/tools/RelBuilder.java | 14 +-- .../org/apache/calcite/test/RelBuilderTest.java | 18 ++++ .../apache/calcite/test/RelMetadataTest.java | 8 +- .../apache/calcite/test/RelOptRulesTest.java | 26 +++++ .../org/apache/calcite/test/RexProgramTest.java | 21 +++- .../org/apache/calcite/test/RelOptRulesTest.xml | 58 +++++++++-- 8 files changed, 195 insertions(+), 71 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/499f0c6e/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java index bf3a241..2c69ed5 100644 --- a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java +++ b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdPredicates.java @@ -290,40 +290,31 @@ public class RelMdPredicates RelOptUtil.conjunctions(filter.getCondition())))); } - /** Infers predicates for a {@link org.apache.calcite.rel.core.SemiJoin}. */ - public RelOptPredicateList getPredicates(SemiJoin semiJoin, - RelMetadataQuery mq) { - RexBuilder rB = semiJoin.getCluster().getRexBuilder(); - final RelNode left = semiJoin.getInput(0); - final RelNode right = semiJoin.getInput(1); - - final RelOptPredicateList leftInfo = mq.getPulledUpPredicates(left); - final RelOptPredicateList rightInfo = mq.getPulledUpPredicates(right); - - JoinConditionBasedPredicateInference jI = - new JoinConditionBasedPredicateInference(semiJoin, - RexUtil.composeConjunction(rB, leftInfo.pulledUpPredicates, false), - RexUtil.composeConjunction(rB, rightInfo.pulledUpPredicates, false)); - - return jI.inferPredicates(false); - } - - /** Infers predicates for a {@link org.apache.calcite.rel.core.Join}. */ + /** + * Infers predicates for a {@link org.apache.calcite.rel.core.Join} (including + * {@link org.apache.calcite.rel.core.SemiJoin}). + */ public RelOptPredicateList getPredicates(Join join, RelMetadataQuery mq) { - RexBuilder rB = join.getCluster().getRexBuilder(); - RelNode left = join.getInput(0); - RelNode right = join.getInput(1); + RelOptCluster cluster = join.getCluster(); + RexBuilder rexBuilder = cluster.getRexBuilder(); + final RexExecutor executor = + Util.first(cluster.getPlanner().getExecutor(), RexUtil.EXECUTOR); + final RelNode left = join.getInput(0); + final RelNode right = join.getInput(1); final RelOptPredicateList leftInfo = mq.getPulledUpPredicates(left); final RelOptPredicateList rightInfo = mq.getPulledUpPredicates(right); - JoinConditionBasedPredicateInference jI = + final RexSimplify simplifier = + new RexSimplify(rexBuilder, RelOptPredicateList.EMPTY, true, executor); + + JoinConditionBasedPredicateInference joinInference = new JoinConditionBasedPredicateInference(join, - RexUtil.composeConjunction(rB, leftInfo.pulledUpPredicates, false), - RexUtil.composeConjunction(rB, rightInfo.pulledUpPredicates, - false)); + RexUtil.composeConjunction(rexBuilder, leftInfo.pulledUpPredicates, false), + RexUtil.composeConjunction(rexBuilder, rightInfo.pulledUpPredicates, false), + simplifier); - return jI.inferPredicates(false); + return joinInference.inferPredicates(false); } /** @@ -374,7 +365,7 @@ public class RelMdPredicates * Infers predicates for a Union. */ public RelOptPredicateList getPredicates(Union union, RelMetadataQuery mq) { - RexBuilder rB = union.getCluster().getRexBuilder(); + RexBuilder rexBuilder = union.getCluster().getRexBuilder(); Map<String, RexNode> finalPreds = new HashMap<>(); List<RexNode> finalResidualPreds = new ArrayList<>(); @@ -399,14 +390,14 @@ public class RelMdPredicates } } // Add new residual preds - finalResidualPreds.add(RexUtil.composeConjunction(rB, residualPreds, false)); + finalResidualPreds.add(RexUtil.composeConjunction(rexBuilder, residualPreds, false)); // Add those that are not part of the final set to residual for (Entry<String, RexNode> e : finalPreds.entrySet()) { if (!preds.containsKey(e.getKey())) { // This node was in previous union inputs, but it is not in this one for (int j = 0; j < i; j++) { finalResidualPreds.set(j, - RexUtil.composeConjunction(rB, + RexUtil.composeConjunction(rexBuilder, Lists.newArrayList(finalResidualPreds.get(j), e.getValue()), false)); } } @@ -421,12 +412,12 @@ public class RelMdPredicates Util.first(cluster.getPlanner().getExecutor(), RexUtil.EXECUTOR); final RelOptPredicateList predicates = RelOptPredicateList.EMPTY; final RexSimplify simplify = - new RexSimplify(rB, predicates, true, executor); + new RexSimplify(rexBuilder, predicates, true, executor); RexNode disjPred = simplify.simplifyOrs(finalResidualPreds); if (!disjPred.isAlwaysTrue()) { preds.add(disjPred); } - return RelOptPredicateList.of(rB, preds); + return RelOptPredicateList.of(rexBuilder, preds); } /** @@ -497,21 +488,23 @@ public class RelMdPredicates final ImmutableBitSet allFieldsBitSet; SortedMap<Integer, BitSet> equivalence; final Map<String, ImmutableBitSet> exprFields; - final Set<String> allExprsDigests; + final Set<String> allExprDigests; final Set<String> equalityPredicates; final RexNode leftChildPredicates; final RexNode rightChildPredicates; + final RexSimplify simplifier; JoinConditionBasedPredicateInference(Join joinRel, - RexNode lPreds, RexNode rPreds) { - this(joinRel, joinRel instanceof SemiJoin, lPreds, rPreds); + RexNode lPreds, RexNode rPreds, RexSimplify simplifier) { + this(joinRel, joinRel instanceof SemiJoin, lPreds, rPreds, simplifier); } private JoinConditionBasedPredicateInference(Join joinRel, boolean isSemiJoin, - RexNode lPreds, RexNode rPreds) { + RexNode lPreds, RexNode rPreds, RexSimplify simplifier) { super(); this.joinRel = joinRel; this.isSemiJoin = isSemiJoin; + this.simplifier = simplifier; nFieldsLeft = joinRel.getLeft().getRowType().getFieldList().size(); nFieldsRight = joinRel.getRight().getRowType().getFieldList().size(); nSysFields = joinRel.getSystemFieldList().size(); @@ -523,7 +516,7 @@ public class RelMdPredicates nSysFields + nFieldsLeft + nFieldsRight); exprFields = Maps.newHashMap(); - allExprsDigests = new HashSet<>(); + allExprDigests = new HashSet<>(); if (lPreds == null) { leftChildPredicates = null; @@ -533,9 +526,10 @@ public class RelMdPredicates leftChildPredicates = lPreds.accept( new RexPermuteInputsShuttle(leftMapping, joinRel.getInput(0))); + allExprDigests.add(leftChildPredicates.toString()); for (RexNode r : RelOptUtil.conjunctions(leftChildPredicates)) { exprFields.put(r.toString(), RelOptUtil.InputFinder.bits(r)); - allExprsDigests.add(r.toString()); + allExprDigests.add(r.toString()); } } if (rPreds == null) { @@ -547,9 +541,10 @@ public class RelMdPredicates rightChildPredicates = rPreds.accept( new RexPermuteInputsShuttle(rightMapping, joinRel.getInput(1))); + allExprDigests.add(rightChildPredicates.toString()); for (RexNode r : RelOptUtil.conjunctions(rightChildPredicates)) { exprFields.put(r.toString(), RelOptUtil.InputFinder.bits(r)); - allExprsDigests.add(r.toString()); + allExprDigests.add(r.toString()); } } @@ -595,12 +590,12 @@ public class RelMdPredicates public RelOptPredicateList inferPredicates( boolean includeEqualityInference) { final List<RexNode> inferredPredicates = new ArrayList<>(); - final Set<String> allExprsDigests = new HashSet<>(this.allExprsDigests); + final Set<String> allExprDigests = new HashSet<>(this.allExprDigests); final JoinRelType joinType = joinRel.getJoinType(); switch (joinType) { case INNER: case LEFT: - infer(leftChildPredicates, allExprsDigests, inferredPredicates, + infer(leftChildPredicates, allExprDigests, inferredPredicates, includeEqualityInference, joinType == JoinRelType.LEFT ? rightFieldsBitSet : allFieldsBitSet); @@ -609,7 +604,7 @@ public class RelMdPredicates switch (joinType) { case INNER: case RIGHT: - infer(rightChildPredicates, allExprsDigests, inferredPredicates, + infer(rightChildPredicates, allExprDigests, inferredPredicates, includeEqualityInference, joinType == JoinRelType.RIGHT ? leftFieldsBitSet : allFieldsBitSet); @@ -678,7 +673,7 @@ public class RelMdPredicates } private void infer(RexNode predicates, Set<String> allExprsDigests, - List<RexNode> inferedPredicates, boolean includeEqualityInference, + List<RexNode> inferredPredicates, boolean includeEqualityInference, ImmutableBitSet inferringFields) { for (RexNode r : RelOptUtil.conjunctions(predicates)) { if (!includeEqualityInference @@ -689,11 +684,15 @@ public class RelMdPredicates RexNode tr = r.accept( new RexPermuteInputsShuttle(m, joinRel.getInput(0), joinRel.getInput(1))); - if (inferringFields.contains(RelOptUtil.InputFinder.bits(tr)) - && !allExprsDigests.contains(tr.toString()) - && !isAlwaysTrue(tr)) { - inferedPredicates.add(tr); - allExprsDigests.add(tr.toString()); + // Filter predicates can be already simplified, so we should work with + // simplified RexNode versions as well. It also allows prevent of having + // some duplicates in in result pulledUpPredicates + RexNode simplifiedTarget = + simplifier.simplifyFilterPredicates(RelOptUtil.conjunctions(tr)); + if (checkTarget(inferringFields, allExprsDigests, tr) + && checkTarget(inferringFields, allExprsDigests, simplifiedTarget)) { + inferredPredicates.add(simplifiedTarget); + allExprsDigests.add(simplifiedTarget.toString()); } } } @@ -711,6 +710,13 @@ public class RelMdPredicates }; } + private boolean checkTarget(ImmutableBitSet inferringFields, + Set<String> allExprsDigests, RexNode tr) { + return inferringFields.contains(RelOptUtil.InputFinder.bits(tr)) + && !allExprsDigests.contains(tr.toString()) + && !isAlwaysTrue(tr); + } + private void equivalent(int p1, int p2) { BitSet b = equivalence.get(p1); b.set(p2); http://git-wip-us.apache.org/repos/asf/calcite/blob/499f0c6e/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 568ddbc..9be7a99 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java +++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java @@ -1357,6 +1357,25 @@ public class RexSimplify { return false; } } + + /** + * Combines predicates AND, optimizes, and returns null if the result is + * always false. + * + * @param predicates Filter condition predicates + * @return simplified conjunction of predicates for the filter, null if always false + */ + public RexNode simplifyFilterPredicates(Iterable<? extends RexNode> predicates) { + final RexNode simplifiedAnds = simplifyAnds(predicates); + if (simplifiedAnds.isAlwaysFalse()) { + return null; + } + + // 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. + return removeNullabilityCast(simplifiedAnds); + } } // End RexSimplify.java http://git-wip-us.apache.org/repos/asf/calcite/blob/499f0c6e/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 bdfcb59..680cded 100644 --- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java +++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java @@ -933,19 +933,15 @@ public class RelBuilder { * and optimized in a similar way to the {@link #and} method. * If the result is TRUE no filter is created. */ public RelBuilder filter(Iterable<? extends RexNode> predicates) { - final RexNode x = simplifierUnknownAsFalse.simplifyAnds(predicates); - if (x.isAlwaysFalse()) { + final RexNode simplifiedPredicates = + simplifierUnknownAsFalse.simplifyFilterPredicates(predicates); + if (simplifiedPredicates == null) { return empty(); } - // 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()) { + if (!simplifiedPredicates.isAlwaysTrue()) { final Frame frame = stack.pop(); - final RelNode filter = filterFactory.createFilter(frame.rel, x2); + final RelNode filter = filterFactory.createFilter(frame.rel, simplifiedPredicates); stack.push(new Frame(filter, frame.fields)); } return this; http://git-wip-us.apache.org/repos/asf/calcite/blob/499f0c6e/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java index 1290191..1f166f2 100644 --- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java @@ -2067,6 +2067,24 @@ public class RelBuilderTest { + " LogicalTableScan(table=[[scott, EMP]])\n"; assertThat(root, hasTree(expected)); } + + @Test public void testFilterCastNull() { + final RelBuilder builder = RelBuilder.create(config().build()); + final RelDataTypeFactory typeFactory = builder.getTypeFactory(); + final RelNode root = + builder.scan("EMP") + .filter( + builder.getRexBuilder().makeCast( + typeFactory.createTypeWithNullability( + typeFactory.createSqlType(SqlTypeName.BOOLEAN), true), + builder.equals(builder.field("DEPTNO"), + builder.literal(10)))) + .build(); + final String expected = "" + + "LogicalFilter(condition=[=($7, 10)])\n" + + " LogicalTableScan(table=[[scott, EMP]])\n"; + assertThat(root, hasTree(expected)); + } } // End RelBuilderTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/499f0c6e/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java index 580a196..9da50f2 100644 --- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java @@ -1391,7 +1391,7 @@ public class RelMetadataTest extends SqlToRelTestBase { } /** Unit test for - * {@link org.apache.calcite.rel.metadata.RelMdPredicates#getPredicates(SemiJoin, RelMetadataQuery)}. */ + * {@link org.apache.calcite.rel.metadata.RelMdPredicates#getPredicates(Join, RelMetadataQuery)}. */ @Test public void testPredicates() { final Project rel = (Project) convertSql("select * from emp, dept"); final Join join = (Join) rel.getInput(); @@ -1529,7 +1529,9 @@ public class RelMetadataTest extends SqlToRelTestBase { /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1960";>[CALCITE-1960] * RelMdPredicates.getPredicates is slow if there are many equivalent - * columns</a>. Since this is a performance problem, the test result does not + * columns</a>. There are much less duplicates after + * <a href="https://issues.apache.org/jira/browse/CALCITE-2205";>[CALCITE-2205]</a>. + * Since this is a performance problem, the test result does not * change, but takes over 15 minutes before the fix and 6 seconds after. */ @Test(timeout = 20_000) public void testPullUpPredicatesForExprsItr() { // If we're running Windows, we are probably in a VM and the test may @@ -1557,7 +1559,7 @@ public class RelMetadataTest extends SqlToRelTestBase { final RelNode rel = convertSql(sql); final RelMetadataQuery mq = RelMetadataQuery.instance(); RelOptPredicateList inputSet = mq.getPulledUpPredicates(rel.getInput(0)); - assertThat(inputSet.pulledUpPredicates.size(), is(131089)); + assertThat(inputSet.pulledUpPredicates.size(), is(18)); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/499f0c6e/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 e24aaad..24adbb6 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -3010,6 +3010,9 @@ public class RelOptRulesTest extends RelOptTestBase { sql(sql).withPre(preProgram).with(program).check(); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2200";>[CALCITE-2200] + * Infinite loop for JoinPushTransitivePredicatesRule</a>. */ @Test public void testJoinPushTransitivePredicatesRule() { HepProgram preProgram = new HepProgramBuilder() .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN) @@ -3027,6 +3030,29 @@ public class RelOptRulesTest extends RelOptTestBase { } /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2205";>[CALCITE-2205] + * One more infinite loop for JoinPushTransitivePredicatesRule</a>. */ + @Test public void testJoinPushTransitivePredicatesRule2() { + HepProgram hepProgram = new HepProgramBuilder() + .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN) + .addRuleInstance(FilterJoinRule.JOIN) + .addRuleInstance(JoinPushTransitivePredicatesRule.INSTANCE) + .build(); + HepPlanner hepPlanner = new HepPlanner(hepProgram); + + final String sql = "select n1.SAL\n" + + "from EMPNULLABLES_20 n1\n" + + "where n1.SAL IN (\n" + + " select n2.SAL\n" + + " from EMPNULLABLES_20 n2\n" + + " where n1.SAL = n2.SAL or n1.SAL = 4)"; + sql(sql) + .withDecorrelation(true) + .with(hepPlanner) + .check(); + } + + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-2195";>[CALCITE-2195] * AggregateJoinTransposeRule fails to aggregate over unique column</a>. */ @Test public void testPushAggregateThroughJoin6() { http://git-wip-us.apache.org/repos/asf/calcite/blob/499f0c6e/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 94eb792..ea1f449 100644 --- a/core/src/test/java/org/apache/calcite/test/RexProgramTest.java +++ b/core/src/test/java/org/apache/calcite/test/RexProgramTest.java @@ -76,6 +76,7 @@ import java.util.TreeMap; import static org.hamcrest.CoreMatchers.equalTo; import static org.hamcrest.CoreMatchers.is; +import static org.hamcrest.CoreMatchers.nullValue; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertThat; @@ -1300,9 +1301,27 @@ public class RexProgramTest { "false"); // simplify equals boolean - checkSimplifyFilter(and(eq(eq(aRef, literal1), trueLiteral), eq(bRef, literal1)), + final ImmutableList<RexNode> args = + ImmutableList.of(eq(eq(aRef, literal1), trueLiteral), + eq(bRef, literal1)); + checkSimplifyFilter(and(args), "AND(=(?0.a, 1), =(?0.b, 1))"); + // as previous, using simplifyFilterPredicates + assertThat(simplify.withUnknownAsFalse(true) + .simplifyFilterPredicates(args) + .toString(), + equalTo("AND(=(?0.a, 1), =(?0.b, 1))")); + + // "a = 1 and a = 10" is always false + final ImmutableList<RexNode> args2 = + ImmutableList.of(eq(aRef, literal1), eq(aRef, literal10)); + checkSimplifyFilter(and(args2), "false"); + + assertThat(simplify.withUnknownAsFalse(true) + .simplifyFilterPredicates(args2), + nullValue()); + // equality on constants, can remove the equality on the variables checkSimplifyFilter(and(eq(aRef, literal1), eq(bRef, literal1), eq(aRef, bRef)), "AND(=(?0.a, 1), =(?0.b, 1))"); http://git-wip-us.apache.org/repos/asf/calcite/blob/499f0c6e/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 89f7dc7..290799d 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -2626,20 +2626,58 @@ LogicalProject(DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> + </TestCase> + <TestCase name="testJoinPushTransitivePredicatesRule2"> + <Resource name="sql"> + <![CDATA[select n1.SAL +from EMPNULLABLES_20 n1 +where n1.SAL IN ( + select n2.SAL + from EMPNULLABLES_20 n2 + where n1.SAL = n2.SAL or n1.SAL = 4)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(SAL=[$5]) + LogicalJoin(condition=[AND(=($5, $9), =($5, $8))], joinType=[inner]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))]) + LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(SAL=[$0], SAL0=[$1]) + LogicalProject(SAL=[$5], SAL0=[$8]) + LogicalJoin(condition=[OR(=($8, $5), =($8, 4))], joinType=[inner]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))]) + LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) + LogicalAggregate(group=[{0}]) + LogicalProject(SAL=[$5]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))]) + LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) +]]> + </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(DEPTNO=[$7]) - LogicalJoin(condition=[AND(=($7, $10), =($7, $9))], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalFilter(condition=[OR(=($1, $0), =($1, 4))]) +LogicalProject(SAL=[$5]) + LogicalJoin(condition=[AND(=($5, $9), =($5, $8))], joinType=[inner]) + LogicalFilter(condition=[OR(IS NOT NULL($5), =($5, 4))]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))]) + LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) + LogicalFilter(condition=[AND(OR(IS NOT NULL($0), =($0, 4)), OR(=($0, $1), =($0, 4)), OR(IS NOT NULL($1), =($1, 4)))]) LogicalAggregate(group=[{0, 1}]) - LogicalProject(DEPTNO=[$0], DEPTNO0=[$1]) - LogicalProject(DEPTNO=[$7], DEPTNO0=[$9]) - LogicalJoin(condition=[OR(=($7, $9), =($7, 4))], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(SAL=[$0], SAL0=[$1]) + LogicalProject(SAL=[$5], SAL0=[$8]) + LogicalJoin(condition=[OR(=($8, $5), =($8, 4))], joinType=[inner]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))]) + LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(SAL=[$5]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8]) + LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))]) + LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) ]]> </Resource> </TestCase>
