[CALCITE-1023] Planner rule that removes Aggregate keys that are constant Recognize that if there is a "CAST(c) = literal" predicate, and the cast is widening, then c must be constant.
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/5197a714 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/5197a714 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/5197a714 Branch: refs/heads/master Commit: 5197a714705c7bfaa6a46a0907267b47870b18d1 Parents: d55ff83 Author: Julian Hyde <[email protected]> Authored: Fri Dec 11 16:32:47 2015 -0800 Committer: Julian Hyde <[email protected]> Committed: Sat Dec 12 13:41:15 2015 -0800 ---------------------------------------------------------------------- .../calcite/plan/RelOptPredicateList.java | 20 ++- .../org/apache/calcite/plan/RelOptUtil.java | 2 + .../calcite/rel/metadata/RelMdPredicates.java | 25 +++- .../rel/rules/AggregateConstantKeyRule.java | 130 +++++++++++++++++++ .../rel/rules/ReduceExpressionsRule.java | 87 +++++++++---- .../main/java/org/apache/calcite/util/Bug.java | 5 + .../java/org/apache/calcite/test/JdbcTest.java | 16 ++- .../apache/calcite/test/RelOptRulesTest.java | 43 +++++- .../org/apache/calcite/test/RelOptRulesTest.xml | 89 ++++++++++++- core/src/test/resources/sql/agg.iq | 27 ++++ 10 files changed, 408 insertions(+), 36 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java b/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java index aad89b4..658ae7f 100644 --- a/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java +++ b/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java @@ -20,7 +20,6 @@ import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexUtil; import com.google.common.collect.ImmutableList; -import com.google.common.collect.Iterables; /** * Predicates that are known to hold in the output of a particular relational @@ -74,10 +73,21 @@ public class RelOptPredicateList { public RelOptPredicateList union(RelOptPredicateList list) { return RelOptPredicateList.of( - Iterables.concat(pulledUpPredicates, list.pulledUpPredicates), - Iterables.concat(leftInferredPredicates, list.leftInferredPredicates), - Iterables.concat(rightInferredPredicates, - list.rightInferredPredicates)); + concat(pulledUpPredicates, list.pulledUpPredicates), + concat(leftInferredPredicates, list.leftInferredPredicates), + concat(rightInferredPredicates, list.rightInferredPredicates)); + } + + /** Concatenates two immutable lists, avoiding a copy it possible. */ + private static <E> ImmutableList<E> concat(ImmutableList<E> list1, + ImmutableList<E> list2) { + if (list1.isEmpty()) { + return list2; + } else if (list2.isEmpty()) { + return list1; + } else { + return ImmutableList.<E>builder().addAll(list1).addAll(list2).build(); + } } public RelOptPredicateList shift(int offset) { http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java index acbd512..a7b3e08 100644 --- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java +++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java @@ -39,6 +39,7 @@ import org.apache.calcite.rel.logical.LogicalCalc; import org.apache.calcite.rel.logical.LogicalFilter; import org.apache.calcite.rel.logical.LogicalJoin; import org.apache.calcite.rel.logical.LogicalProject; +import org.apache.calcite.rel.rules.AggregateConstantKeyRule; import org.apache.calcite.rel.rules.AggregateProjectPullUpConstantsRule; import org.apache.calcite.rel.rules.FilterMergeRule; import org.apache.calcite.rel.rules.MultiJoin; @@ -1564,6 +1565,7 @@ public abstract class RelOptUtil { public static void registerAbstractRels(RelOptPlanner planner) { planner.addRule(AggregateProjectPullUpConstantsRule.INSTANCE); + planner.addRule(AggregateConstantKeyRule.INSTANCE); planner.addRule(PruneEmptyRules.UNION_INSTANCE); planner.addRule(PruneEmptyRules.PROJECT_INSTANCE); planner.addRule(PruneEmptyRules.FILTER_INSTANCE); http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/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 7b20b68..35eb56b 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 @@ -21,6 +21,7 @@ import org.apache.calcite.linq4j.Ord; import org.apache.calcite.linq4j.function.Predicate1; import org.apache.calcite.plan.RelOptPredicateList; import org.apache.calcite.plan.RelOptUtil; +import org.apache.calcite.plan.volcano.RelSubset; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Aggregate; import org.apache.calcite.rel.core.Exchange; @@ -44,8 +45,10 @@ import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.util.BitSets; +import org.apache.calcite.util.Bug; import org.apache.calcite.util.BuiltInMethod; import org.apache.calcite.util.ImmutableBitSet; +import org.apache.calcite.util.Util; import org.apache.calcite.util.mapping.Mapping; import org.apache.calcite.util.mapping.MappingType; import org.apache.calcite.util.mapping.Mappings; @@ -207,9 +210,10 @@ public class RelMdPredicates { RelOptPredicateList childInfo = RelMetadataQuery.getPulledUpPredicates(child); - return RelOptPredicateList.of( - Iterables.concat(childInfo.pulledUpPredicates, - RelOptUtil.conjunctions(filter.getCondition()))); + return Util.first(childInfo, RelOptPredicateList.EMPTY) + .union( + RelOptPredicateList.of( + RelOptUtil.conjunctions(filter.getCondition()))); } /** Infers predicates for a {@link org.apache.calcite.rel.core.SemiJoin}. */ @@ -330,6 +334,21 @@ public class RelMdPredicates { return RelMetadataQuery.getPulledUpPredicates(child); } + /** @see RelMetadataQuery#getPulledUpPredicates(RelNode) */ + public RelOptPredicateList getPredicates(RelSubset r) { + if (!Bug.CALCITE_794_FIXED) { + return RelOptPredicateList.EMPTY; + } + RelOptPredicateList list = null; + for (RelNode r2 : r.getRels()) { + RelOptPredicateList list2 = RelMetadataQuery.getPulledUpPredicates(r2); + if (list2 != null) { + list = list == null ? list2 : list.union(list2); + } + } + return Util.first(list, RelOptPredicateList.EMPTY); + } + /** * Utility to infer predicates from one side of the join that apply on the * other side. http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/core/src/main/java/org/apache/calcite/rel/rules/AggregateConstantKeyRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateConstantKeyRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateConstantKeyRule.java new file mode 100644 index 0000000..ea31178 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateConstantKeyRule.java @@ -0,0 +1,130 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.rel.rules; + +import org.apache.calcite.plan.RelOptPredicateList; +import org.apache.calcite.plan.RelOptRule; +import org.apache.calcite.plan.RelOptRuleCall; +import org.apache.calcite.rel.core.Aggregate; +import org.apache.calcite.rel.core.RelFactories; +import org.apache.calcite.rel.metadata.RelMetadataQuery; +import org.apache.calcite.rel.type.RelDataTypeField; +import org.apache.calcite.rex.RexBuilder; +import org.apache.calcite.rex.RexInputRef; +import org.apache.calcite.rex.RexLiteral; +import org.apache.calcite.rex.RexNode; +import org.apache.calcite.tools.RelBuilder; +import org.apache.calcite.tools.RelBuilderFactory; +import org.apache.calcite.util.ImmutableBitSet; + +import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableMap; + +import java.util.ArrayList; +import java.util.List; +import java.util.NavigableMap; +import java.util.TreeMap; + +/** + * Planner rule that removes constant keys from an + * a {@link Aggregate}. + * + * <p>It never removes the last column, because {@code Aggregate([])} returns + * 1 row even if its input is empty. + */ +public class AggregateConstantKeyRule extends RelOptRule { + public static final AggregateConstantKeyRule INSTANCE = + new AggregateConstantKeyRule(RelFactories.LOGICAL_BUILDER, + "AggregateConstantKeyRule"); + + //~ Constructors ----------------------------------------------------------- + + /** Creates an AggregateConstantKeyRule. */ + private AggregateConstantKeyRule(RelBuilderFactory relBuilderFactory, + String description) { + super(operand(Aggregate.class, null, Aggregate.IS_SIMPLE, any()), + relBuilderFactory, description); + } + + //~ Methods ---------------------------------------------------------------- + + public void onMatch(RelOptRuleCall call) { + final Aggregate aggregate = call.rel(0); + assert !aggregate.indicator : "predicate ensured no grouping sets"; + + final RexBuilder rexBuilder = aggregate.getCluster().getRexBuilder(); + final RelOptPredicateList predicates = + RelMetadataQuery.getPulledUpPredicates(aggregate.getInput()); + if (predicates == null) { + return; + } + final ImmutableMap<RexNode, RexLiteral> constants = + ReduceExpressionsRule.predicateConstants(rexBuilder, predicates); + final NavigableMap<Integer, RexLiteral> map = new TreeMap<>(); + for (int key : aggregate.getGroupSet()) { + final RexInputRef ref = + rexBuilder.makeInputRef(aggregate.getInput(), key); + if (constants.containsKey(ref)) { + map.put(key, constants.get(ref)); + } + } + + if (map.isEmpty()) { + return; // none of the keys are constant + } + + if (map.size() == aggregate.getGroupCount()) { + if (map.size() == 1) { + // There is one key, and it is constant. We cannot remove it. + return; + } + map.remove(map.descendingKeySet().descendingIterator().next()); + } + + ImmutableBitSet newGroupSet = aggregate.getGroupSet(); + for (int key : map.keySet()) { + newGroupSet = newGroupSet.clear(key); + } + final Aggregate newAggregate = + aggregate.copy(aggregate.getTraitSet(), aggregate.getInput(), + false, newGroupSet, ImmutableList.of(newGroupSet), + aggregate.getAggCallList()); + final RelBuilder relBuilder = call.builder(); + relBuilder.push(newAggregate); + + final List<RexNode> projects = new ArrayList<>(); + int offset = 0; + for (RelDataTypeField field : aggregate.getRowType().getFieldList()) { + RexNode node = null; + if (field.getIndex() < aggregate.getGroupCount()) { + node = map.get(aggregate.getGroupSet().nth(field.getIndex())); + if (node != null) { + node = relBuilder.getRexBuilder().makeCast(field.getType(), node, true); + node = relBuilder.alias(node, field.getName()); + ++offset; + } + } + if (node == null) { + node = relBuilder.field(field.getIndex() - offset); + } + projects.add(node); + } + call.transformTo(relBuilder.project(projects).build()); + } +} + +// End AggregateConstantKeyRule.java http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/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 75943b8..8be202e 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 @@ -396,7 +396,7 @@ public abstract class ReduceExpressionsRule extends RelOptRule { List<Boolean> addCasts = Lists.newArrayList(); final List<RexNode> removableCasts = Lists.newArrayList(); final ImmutableMap<RexNode, RexLiteral> constants = - predicateConstants(predicates); + predicateConstants(rexBuilder, predicates); findReducibleExps(rel.getCluster().getTypeFactory(), expList, constants, constExps, addCasts, removableCasts); if (constExps.isEmpty() && removableCasts.isEmpty()) { @@ -508,7 +508,7 @@ public abstract class ReduceExpressionsRule extends RelOptRule { } protected static ImmutableMap<RexNode, RexLiteral> predicateConstants( - RelOptPredicateList predicates) { + RexBuilder rexBuilder, RelOptPredicateList predicates) { // We cannot use an ImmutableMap.Builder here. If there are multiple entries // with the same key (e.g. "WHERE deptno = 1 AND deptno = 2"), it doesn't // matter which we take, so the latter will replace the former. @@ -521,7 +521,7 @@ public abstract class ReduceExpressionsRule extends RelOptRule { final Map<RexNode, RexLiteral> map = new HashMap<>(); final Set<RexNode> excludeSet = new HashSet<>(); for (RexNode predicate : predicates.pulledUpPredicates) { - gatherConstraints(map, predicate, excludeSet); + gatherConstraints(predicate, map, excludeSet, rexBuilder); } final ImmutableMap.Builder<RexNode, RexLiteral> builder = ImmutableMap.builder(); @@ -559,8 +559,9 @@ public abstract class ReduceExpressionsRule extends RelOptRule { } } - private static void gatherConstraints(Map<RexNode, RexLiteral> map, - RexNode predicate, Set<RexNode> excludeSet) { + private static void gatherConstraints(RexNode predicate, + Map<RexNode, RexLiteral> map, Set<RexNode> excludeSet, + RexBuilder rexBuilder) { if (predicate.getKind() != SqlKind.EQUALS) { decompose(excludeSet, predicate); return; @@ -575,28 +576,68 @@ public abstract class ReduceExpressionsRule extends RelOptRule { final RexNode right = operands.get(1); // note that literals are immutable too and they can only be compared through // values. - if (right instanceof RexLiteral && !excludeSet.contains(left)) { - RexLiteral existedValue = map.get(left); - if (existedValue == null) { - map.put(left, (RexLiteral) right); - } else { - if (!existedValue.getValue().equals(((RexLiteral) right).getValue())) { - // we found conflict values. - map.remove(left); - excludeSet.add(left); + if (right instanceof RexLiteral) { + foo(left, (RexLiteral) right, map, excludeSet, rexBuilder); + } + if (left instanceof RexLiteral) { + foo(right, (RexLiteral) left, map, excludeSet, rexBuilder); + } + } + + private static void foo(RexNode left, RexLiteral right, + Map<RexNode, RexLiteral> map, Set<RexNode> excludeSet, + RexBuilder rexBuilder) { + if (excludeSet.contains(left)) { + return; + } + final RexLiteral existedValue = map.get(left); + if (existedValue == null) { + switch (left.getKind()) { + case CAST: + // Convert "CAST(c) = literal" to "c = literal", as long as it is a + // widening cast. + final RexNode operand = ((RexCall) left).getOperands().get(0); + if (canAssignFrom(left.getType(), operand.getType())) { + final RexNode castRight = + rexBuilder.makeCast(operand.getType(), right); + if (castRight instanceof RexLiteral) { + left = operand; + right = (RexLiteral) castRight; + } } } - } else if (left instanceof RexLiteral && !excludeSet.contains(right)) { - RexLiteral existedValue = map.get(right); - if (existedValue == null) { - map.put(right, (RexLiteral) left); - } else { - if (!existedValue.getValue().equals(((RexLiteral) left).getValue())) { - map.remove(right); - excludeSet.add(right); - } + map.put(left, right); + } else { + if (!existedValue.getValue().equals(right.getValue())) { + // we found conflicting values, e.g. left = 10 and left = 20 + map.remove(left); + excludeSet.add(left); + } + } + } + + /** Returns whether a value of {@code type2} can be assigned to a variable + * of {@code type1}. + * + * <p>For example: + * <ul> + * <li>{@code canAssignFrom(BIGINT, TINYINT)} returns {@code true}</li> + * <li>{@code canAssignFrom(TINYINT, BIGINT)} returns {@code false}</li> + * <li>{@code canAssignFrom(BIGINT, VARCHAR)} returns {@code false}</li> + * </ul> + */ + private static boolean canAssignFrom(RelDataType type1, RelDataType type2) { + final SqlTypeName name1 = type1.getSqlTypeName(); + final SqlTypeName name2 = type2.getSqlTypeName(); + if (name1.getFamily() == name2.getFamily()) { + switch (name1.getFamily()) { + case NUMERIC: + return name1.compareTo(name2) >= 0; + default: + return true; } } + return false; } /** Pushes predicates into a CASE. http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/core/src/main/java/org/apache/calcite/util/Bug.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java b/core/src/main/java/org/apache/calcite/util/Bug.java index a1309ab..6248684 100644 --- a/core/src/main/java/org/apache/calcite/util/Bug.java +++ b/core/src/main/java/org/apache/calcite/util/Bug.java @@ -170,6 +170,11 @@ public abstract class Bug { * Timeout executing joins against MySQL</a> is fixed. */ public static final boolean CALCITE_673_FIXED = false; + /** Whether + * <a href="https://issues.apache.org/jira/browse/CALCITE-794";>[CALCITE-794] + * Detect cycles when computing statistics</a> is fixed. */ + public static final boolean CALCITE_794_FIXED = false; + /** * Use this to flag temporary code. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/core/src/test/java/org/apache/calcite/test/JdbcTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java index 9d2597f..0eccd4a 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -4688,10 +4688,22 @@ public class JdbcTest { final Function<String, Object> env = new Function<String, Object>() { public Object apply(String varName) { - if (varName.equals("jdk18")) { + switch (varName) { + case "jdk18": return System.getProperty("java.version").startsWith("1.8"); + case "fixed": + return new Function<String, Object>() { + public Object apply(String v) { + switch (v) { + case "calcite794": + return Bug.CALCITE_794_FIXED; + } + return null; + } + }; + default: + return null; } - return null; } }; final Quidem.NewConnectionFactory connectionFactory = http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/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 1be8331..4061b0d 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -36,6 +36,7 @@ import org.apache.calcite.rel.metadata.CachingRelMetadataProvider; import org.apache.calcite.rel.metadata.ChainedRelMetadataProvider; import org.apache.calcite.rel.metadata.DefaultRelMetadataProvider; import org.apache.calcite.rel.metadata.RelMetadataProvider; +import org.apache.calcite.rel.rules.AggregateConstantKeyRule; import org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule; import org.apache.calcite.rel.rules.AggregateFilterTransposeRule; import org.apache.calcite.rel.rules.AggregateJoinTransposeRule; @@ -486,7 +487,7 @@ public class RelOptRulesTest extends RelOptTestBase { .addRuleInstance(ReduceExpressionsRule.JOIN_INSTANCE) .build(); final String sql = "select e1.sal\n" - + " from (select * from emp where deptno = 200) as e1\n" + + "from (select * from emp where deptno = 200) as e1\n" + "where e1.deptno in (\n" + " select e2.deptno from emp e2 where e2.sal = 100)"; checkPlanning(tester.withDecorrelation(false).withTrim(true), preProgram, @@ -2021,6 +2022,46 @@ public class RelOptRulesTest extends RelOptTestBase { checkPlanning(tester, preProgram, new HepPlanner(program), sql); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1023";>[CALCITE-1023] + * Planner rule that removes Aggregate keys that are constant</a>. */ + @Test public void testAggregateConstantKeyRule() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateConstantKeyRule.INSTANCE) + .build(); + final String sql = "select count(*) as c\n" + + "from sales.emp\n" + + "where deptno = 10\n" + + "group by deptno, sal"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Tests {@link AggregateConstantKeyRule} where reduction is not possible + * because "deptno" is the only key. */ + @Test public void testAggregateConstantKeyRule2() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateConstantKeyRule.INSTANCE) + .build(); + final String sql = "select count(*) as c\n" + + "from sales.emp\n" + + "where deptno = 10\n" + + "group by deptno"; + checkPlanUnchanged(new HepPlanner(program), sql); + } + + /** Tests {@link AggregateConstantKeyRule} where both keys are constants but + * only one can be removed. */ + @Test public void testAggregateConstantKeyRule3() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateConstantKeyRule.INSTANCE) + .build(); + final String sql = "select job\n" + + "from sales.emp\n" + + "where sal is null and job = 'Clerk'\n" + + "group by sal, job\n" + + "having count(*) > 3"; + checkPlanning(new HepPlanner(program), sql); + } } // End RelOptRulesTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/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 fc82b09..38edf69 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -199,7 +199,8 @@ ProjectRel(EXPR$0=[1]) </TestCase> <TestCase name="testSemiJoinReduceConstants"> <Resource name="sql"> - <![CDATA[select e1.sal from (select * from emp where deptno = 200) as e1 + <![CDATA[select e1.sal +from (select * from emp where deptno = 200) as e1 where e1.deptno in ( select e2.deptno from emp e2 where e2.sal = 100)]]> </Resource> @@ -3914,7 +3915,8 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT()]) </TestCase> <TestCase name="testPushFilterPastAggThree"> <Resource name="sql"> - <![CDATA[select deptno from emp group by deptno having count(*) > 1]]> + <![CDATA[select deptno from emp +group by deptno having count(*) > 1]]> </Resource> <Resource name="planBefore"> <![CDATA[ @@ -4234,4 +4236,87 @@ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[0]) ]]> </Resource> </TestCase> + <TestCase name="testAggregateConstantKeyRule"> + <Resource name="sql"> + <![CDATA[select count(*) as c +from sales.emp +where deptno = 10 +group by deptno, sal]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(C=[$2]) + LogicalAggregate(group=[{0, 1}], C=[COUNT()]) + LogicalProject(DEPTNO=[$7], SAL=[$5]) + LogicalFilter(condition=[=($7, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(C=[$2]) + LogicalProject(DEPTNO=[10], SAL=[$0], C=[$1]) + LogicalAggregate(group=[{1}], C=[COUNT()]) + LogicalProject(DEPTNO=[$7], SAL=[$5]) + LogicalFilter(condition=[=($7, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateConstantKeyRule2"> + <Resource name="sql"> + <![CDATA[select count(*) as c +from sales.emp +where deptno = 10 +group by deptno]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(C=[$1]) + LogicalAggregate(group=[{0}], C=[COUNT()]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[=($7, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(C=[$1]) + LogicalAggregate(group=[{0}], C=[COUNT()]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[=($7, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateConstantKeyRule3"> + <Resource name="sql"> + <![CDATA[select job +from sales.emp +where sal is null and job = 'Clerk' +group by sal, job +having count(*) > 3]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(JOB=[$1]) + LogicalFilter(condition=[>($2, 3)]) + LogicalAggregate(group=[{0, 1}], agg#0=[COUNT()]) + LogicalProject(SAL=[$5], JOB=[$2]) + LogicalFilter(condition=[AND(IS NULL($5), =($2, 'Clerk'))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(JOB=[$1]) + LogicalFilter(condition=[>($2, 3)]) + LogicalProject(SAL=[$0], JOB=['Clerk'], $f1=[$1]) + LogicalAggregate(group=[{0}], agg#0=[COUNT()]) + LogicalProject(SAL=[$5], JOB=[$2]) + LogicalFilter(condition=[AND(IS NULL($5), =($2, 'Clerk'))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> </Root> http://git-wip-us.apache.org/repos/asf/calcite/blob/5197a714/core/src/test/resources/sql/agg.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq index 28aca38..720b3df 100644 --- a/core/src/test/resources/sql/agg.iq +++ b/core/src/test/resources/sql/agg.iq @@ -1378,6 +1378,33 @@ select count('1') from "scott".emp join "scott".dept using (deptno) where false; !ok +# [CALCITE-1023] Planner rule that removes Aggregate keys that are constant +select job, sum(sal) as sum_sal, deptno +from "scott".emp +where deptno = 10 +group by deptno, job; ++-----------+---------+--------+ +| JOB | SUM_SAL | DEPTNO | ++-----------+---------+--------+ +| CLERK | 1300.00 | 10 | +| MANAGER | 2450.00 | 10 | +| PRESIDENT | 5000.00 | 10 | ++-----------+---------+--------+ +(3 rows) + +!ok +!if (fixed.calcite794) { +select job, sum(sal) as sum_sal, deptno +from "scott".emp +where deptno = 10 +group by deptno, job; +EnumerableCalc(expr#0..2=[{inputs}], JOB=[$t0], SUM_SAL=[$t2], DEPTNO=[$t1]) + EnumerableAggregate(group=[{2, 7}], SUM_SAL=[SUM($5)]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[10], expr#10=[=($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan +!} + # [CALCITE-729] IndexOutOfBoundsException in ROLLUP query on JDBC data source !use jdbc_scott select deptno, job, count(*) as c
