KYLIN-2579 Improvement on subqueries: reorder subqueries joins with RelOptRule
Project: http://git-wip-us.apache.org/repos/asf/kylin/repo Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/2fc47224 Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/2fc47224 Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/2fc47224 Branch: refs/heads/2.0.x-hbase0.98 Commit: 2fc472242f4e19902d66d847fa7a55c11366c976 Parents: 55bb64f Author: Hongbin Ma <mahong...@apache.org> Authored: Mon May 1 17:39:12 2017 +0800 Committer: Hongbin Ma <mahong...@apache.org> Committed: Mon May 1 17:41:56 2017 +0800 ---------------------------------------------------------------------- .../rel/rules/OLAPJoinPushThroughJoinRule.java | 172 ++++++++ .../rel/rules/OLAPJoinPushThroughJoinRule2.java | 205 +++++++++ .../java/org/apache/calcite/tools/Programs.java | 438 +++++++++++++++++++ .../resources/query/sql_subquery/query17.sql | 17 + .../resources/query/sql_subquery/query18.sql | 23 + .../resources/query/sql_subquery/query19.sql | 20 + .../query/sql_subquery/query20.sql.todo | 22 + .../resources/query/sql_subquery/query21.sql | 26 ++ .../resources/query/sql_subquery/query22.sql | 25 ++ .../resources/query/sql_subquery/query23.sql | 26 ++ .../resources/query/sql_subquery/query24.sql | 29 ++ .../resources/query/sql_subquery/query25.sql | 22 + .../resources/query/sql_subquery/query26.sql | 16 + .../apache/kylin/query/relnode/OLAPJoinRel.java | 13 +- .../kylin/query/relnode/OLAPProjectRel.java | 14 +- 15 files changed, 1065 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/atopcalcite/src/main/java/org/apache/calcite/rel/rules/OLAPJoinPushThroughJoinRule.java ---------------------------------------------------------------------- diff --git a/atopcalcite/src/main/java/org/apache/calcite/rel/rules/OLAPJoinPushThroughJoinRule.java b/atopcalcite/src/main/java/org/apache/calcite/rel/rules/OLAPJoinPushThroughJoinRule.java new file mode 100644 index 0000000..35f2ae6 --- /dev/null +++ b/atopcalcite/src/main/java/org/apache/calcite/rel/rules/OLAPJoinPushThroughJoinRule.java @@ -0,0 +1,172 @@ +/* + * 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 java.util.ArrayList; +import java.util.List; + +import javax.annotation.Nullable; + +import org.apache.calcite.plan.RelOptCluster; +import org.apache.calcite.plan.RelOptRule; +import org.apache.calcite.plan.RelOptRuleCall; +import org.apache.calcite.plan.RelOptUtil; +import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.core.Join; +import org.apache.calcite.rel.core.RelFactories; +import org.apache.calcite.rel.core.TableScan; +import org.apache.calcite.rel.logical.LogicalJoin; +import org.apache.calcite.rex.RexBuilder; +import org.apache.calcite.rex.RexNode; +import org.apache.calcite.rex.RexPermuteInputsShuttle; +import org.apache.calcite.rex.RexUtil; +import org.apache.calcite.tools.RelBuilder; +import org.apache.calcite.tools.RelBuilderFactory; +import org.apache.calcite.util.ImmutableBitSet; +import org.apache.calcite.util.mapping.Mappings; + +import com.google.common.base.Predicate; + +/** + * modified form org.apache.calcite.rel.rules.JoinPushThroughJoinRule. + * The goal is to move joins with sub-queries after joins with tables, + * so that pre-defined join with tables can be matched + */ +public class OLAPJoinPushThroughJoinRule extends RelOptRule { + /** + * Instance of the rule that works on logical joins only, and pushes to the + * right. + */ + public static final RelOptRule INSTANCE = new OLAPJoinPushThroughJoinRule("OLAPJoinPushThroughJoinRule", LogicalJoin.class, RelFactories.LOGICAL_BUILDER); + + public OLAPJoinPushThroughJoinRule(String description, Class<? extends Join> clazz, RelBuilderFactory relBuilderFactory) { + super(operand(clazz, + + operand(clazz, operand(RelNode.class, any()), operand(RelNode.class, null, new Predicate<RelNode>() { + @Override + public boolean apply(@Nullable RelNode input) { + return !(input instanceof TableScan); + } + }, any())), + + operand(TableScan.class, any())), relBuilderFactory, description); + } + + @Override + public void onMatch(RelOptRuleCall call) { + onMatchRight(call); + } + + private void onMatchRight(RelOptRuleCall call) { + final Join topJoin = call.rel(0); + final Join bottomJoin = call.rel(1); + final RelNode relC = call.rel(4); + final RelNode relA = bottomJoin.getLeft(); + final RelNode relB = bottomJoin.getRight(); + final RelOptCluster cluster = topJoin.getCluster(); + // Preconditions.checkState(relA == call.rel(2)); + // Preconditions.checkState(relB == call.rel(3)); + + // topJoin + // / \ + // bottomJoin C + // / \ + // A B + + final int aCount = relA.getRowType().getFieldCount(); + final int bCount = relB.getRowType().getFieldCount(); + final int cCount = relC.getRowType().getFieldCount(); + final ImmutableBitSet bBitSet = ImmutableBitSet.range(aCount, aCount + bCount); + + // becomes + // + // newTopJoin + // / \ + // newBottomJoin B + // / \ + // A C + + // If either join is not inner, we cannot proceed. + // (Is this too strict?) + // if (topJoin.getJoinType() != JoinRelType.INNER || bottomJoin.getJoinType() != JoinRelType.INNER) { + // return; + // } + + // Split the condition of topJoin into a conjunction. Each of the + // parts that does not use columns from B can be pushed down. + final List<RexNode> intersecting = new ArrayList<>(); + final List<RexNode> nonIntersecting = new ArrayList<>(); + split(topJoin.getCondition(), bBitSet, intersecting, nonIntersecting); + + // If there's nothing to push down, it's not worth proceeding. + if (nonIntersecting.isEmpty()) { + return; + } + + // Split the condition of bottomJoin into a conjunction. Each of the + // parts that use columns from B will need to be pulled up. + final List<RexNode> bottomIntersecting = new ArrayList<>(); + final List<RexNode> bottomNonIntersecting = new ArrayList<>(); + split(bottomJoin.getCondition(), bBitSet, bottomIntersecting, bottomNonIntersecting); + + // target: | A | C | + // source: | A | B | C | + // final Mappings.TargetMapping bottomMapping = Mappings + // .createShiftMapping(aCount + bCount + cCount, 0, 0, aCount, aCount, aCount + bCount, + // cCount); + + final Mappings.TargetMapping bottomMapping = Mappings.createShiftMapping(aCount + bCount + cCount, 0, 0, aCount, aCount + cCount, aCount, bCount, aCount, aCount + bCount, cCount); + final List<RexNode> newBottomList = new ArrayList<>(); + new RexPermuteInputsShuttle(bottomMapping, relA, relC).visitList(nonIntersecting, newBottomList); + new RexPermuteInputsShuttle(bottomMapping, relA, relC).visitList(bottomNonIntersecting, newBottomList); + final RexBuilder rexBuilder = cluster.getRexBuilder(); + RexNode newBottomCondition = RexUtil.composeConjunction(rexBuilder, newBottomList, false); + final Join newBottomJoin = bottomJoin.copy(bottomJoin.getTraitSet(), newBottomCondition, relA, relC, bottomJoin.getJoinType(), bottomJoin.isSemiJoinDone()); + + // target: | A | C | B | + // source: | A | B | C | + final Mappings.TargetMapping topMapping = Mappings.createShiftMapping(aCount + bCount + cCount, 0, 0, aCount, aCount + cCount, aCount, bCount, aCount, aCount + bCount, cCount); + final List<RexNode> newTopList = new ArrayList<>(); + new RexPermuteInputsShuttle(topMapping, newBottomJoin, relB).visitList(intersecting, newTopList); + new RexPermuteInputsShuttle(topMapping, newBottomJoin, relB).visitList(bottomIntersecting, newTopList); + RexNode newTopCondition = RexUtil.composeConjunction(rexBuilder, newTopList, false); + @SuppressWarnings("SuspiciousNameCombination") + final Join newTopJoin = topJoin.copy(topJoin.getTraitSet(), newTopCondition, newBottomJoin, relB, topJoin.getJoinType(), topJoin.isSemiJoinDone()); + + assert !Mappings.isIdentity(topMapping); + final RelBuilder relBuilder = call.builder(); + relBuilder.push(newTopJoin); + relBuilder.project(relBuilder.fields(topMapping)); + call.transformTo(relBuilder.build()); + } + + /** + * Splits a condition into conjunctions that do or do not intersect with + * a given bit set. + */ + static void split(RexNode condition, ImmutableBitSet bitSet, List<RexNode> intersecting, List<RexNode> nonIntersecting) { + for (RexNode node : RelOptUtil.conjunctions(condition)) { + ImmutableBitSet inputBitSet = RelOptUtil.InputFinder.bits(node); + if (bitSet.intersects(inputBitSet)) { + intersecting.add(node); + } else { + nonIntersecting.add(node); + } + } + } +} http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/atopcalcite/src/main/java/org/apache/calcite/rel/rules/OLAPJoinPushThroughJoinRule2.java ---------------------------------------------------------------------- diff --git a/atopcalcite/src/main/java/org/apache/calcite/rel/rules/OLAPJoinPushThroughJoinRule2.java b/atopcalcite/src/main/java/org/apache/calcite/rel/rules/OLAPJoinPushThroughJoinRule2.java new file mode 100644 index 0000000..a769cbd --- /dev/null +++ b/atopcalcite/src/main/java/org/apache/calcite/rel/rules/OLAPJoinPushThroughJoinRule2.java @@ -0,0 +1,205 @@ +/* + * 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 java.util.ArrayList; +import java.util.List; + +import javax.annotation.Nullable; + +import org.apache.calcite.plan.RelOptCluster; +import org.apache.calcite.plan.RelOptRule; +import org.apache.calcite.plan.RelOptRuleCall; +import org.apache.calcite.plan.RelOptUtil; +import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.core.Join; +import org.apache.calcite.rel.core.Project; +import org.apache.calcite.rel.core.RelFactories; +import org.apache.calcite.rel.core.TableScan; +import org.apache.calcite.rel.logical.LogicalJoin; +import org.apache.calcite.rex.RexBuilder; +import org.apache.calcite.rex.RexNode; +import org.apache.calcite.rex.RexPermuteInputsShuttle; +import org.apache.calcite.rex.RexUtil; +import org.apache.calcite.tools.RelBuilder; +import org.apache.calcite.tools.RelBuilderFactory; +import org.apache.calcite.util.ImmutableBitSet; +import org.apache.calcite.util.Permutation; +import org.apache.calcite.util.mapping.AbstractTargetMapping; +import org.apache.calcite.util.mapping.Mapping; +import org.apache.calcite.util.mapping.Mappings; + +import com.google.common.base.Preconditions; +import com.google.common.base.Predicate; + +/** + * modified form org.apache.calcite.rel.rules.JoinPushThroughJoinRule. + * The goal is to move joins with sub-queries after joins with tables, + * so that pre-defined join with tables can be matched + * + * differ from OLAPJoinPushThroughJoinRule in the pattern to match. OLAPJoinPushThroughJoinRule + * will generate a result pattern which cannot recursively match OLAPJoinPushThroughJoinRule's pattern. + * So OLAPJoinPushThroughJoinRule2 is introduced to allow recursive matching + */ +public class OLAPJoinPushThroughJoinRule2 extends RelOptRule { + /** + * Instance of the rule that works on logical joins only, and pushes to the + * right. + */ + public static final RelOptRule INSTANCE = new OLAPJoinPushThroughJoinRule2("OLAPJoinPushThroughJoinRule2", LogicalJoin.class, RelFactories.LOGICAL_BUILDER); + + public OLAPJoinPushThroughJoinRule2(String description, Class<? extends Join> clazz, RelBuilderFactory relBuilderFactory) { + super(operand(clazz, + + operand(Project.class, //project is added on top by OLAPJoinPushThroughJoinRule + null, new Predicate<Project>() { + @Override + public boolean apply(@Nullable Project input) { + return input.getPermutation() != null; + } + }, operand(clazz, // + operand(RelNode.class, any()), operand(RelNode.class, null, new Predicate<RelNode>() { + @Override + public boolean apply(@Nullable RelNode input) { + return !(input instanceof TableScan); + } + }, any()))), + + operand(TableScan.class, any())), relBuilderFactory, description); + } + + @Override + public void onMatch(RelOptRuleCall call) { + onMatchRight(call); + } + + private void onMatchRight(RelOptRuleCall call) { + final Join topJoin = call.rel(0); + final Project projectOnBottomJoin = call.rel(1); + final Join bottomJoin = call.rel(2); + final RelNode relC = call.rel(5); + final RelNode relA = bottomJoin.getLeft(); + final RelNode relB = bottomJoin.getRight(); + final RelOptCluster cluster = topJoin.getCluster(); + final Permutation projectPermu = projectOnBottomJoin.getPermutation(); + final Permutation inverseProjectPermu = projectPermu.inverse(); + // Preconditions.checkState(relA == call.rel(3)); + // Preconditions.checkState(relB == call.rel(4)); + Preconditions.checkNotNull(projectPermu); + + // topJoin + // / \ + // project C + // / + // bottomJoin + // / \ + // A B + + final int aCount = relA.getRowType().getFieldCount(); + final int bCount = relB.getRowType().getFieldCount(); + final int cCount = relC.getRowType().getFieldCount(); + final ImmutableBitSet bBitSetBelowProject = ImmutableBitSet.range(aCount, aCount + bCount); + final ImmutableBitSet bBitSetAboveProject = Mappings.apply(inverseProjectPermu, bBitSetBelowProject); + + final Mapping extendedProjectPerm = createAbstractTargetMapping(Mappings.append(projectPermu, Mappings.createIdentity(cCount))); + + // becomes + // + // project + // / + // newTopJoin + // / \ + // newBottomJoin B + // / \ + // A C + + // If either join is not inner, we cannot proceed. + // (Is this too strict?) + // if (topJoin.getJoinType() != JoinRelType.INNER || bottomJoin.getJoinType() != JoinRelType.INNER) { + // return; + // } + + // Split the condition of topJoin into a conjunction. Each of the + // parts that does not use columns from B can be pushed down. + final List<RexNode> intersecting = new ArrayList<>(); + final List<RexNode> nonIntersecting = new ArrayList<>(); + split(topJoin.getCondition(), bBitSetAboveProject, intersecting, nonIntersecting); + + // If there's nothing to push down, it's not worth proceeding. + if (nonIntersecting.isEmpty()) { + return; + } + + // Split the condition of bottomJoin into a conjunction. Each of the + // parts that use columns from B will need to be pulled up. + final List<RexNode> bottomIntersecting = new ArrayList<>(); + final List<RexNode> bottomNonIntersecting = new ArrayList<>(); + split(bottomJoin.getCondition(), bBitSetBelowProject, bottomIntersecting, bottomNonIntersecting); + Preconditions.checkState(bottomNonIntersecting.isEmpty()); + + // target: | A | C | + // source: | A | B | C | + final Mappings.TargetMapping tempMapping = Mappings.createShiftMapping(aCount + bCount + cCount, 0, 0, aCount, aCount + cCount, aCount, bCount, aCount, aCount + bCount, cCount); + final Mappings.TargetMapping thruProjectMapping = Mappings.multiply(extendedProjectPerm, createAbstractTargetMapping(tempMapping)); + final List<RexNode> newBottomList = new ArrayList<>(); + new RexPermuteInputsShuttle(thruProjectMapping, relA, relC).visitList(nonIntersecting, newBottomList); + final RexBuilder rexBuilder = cluster.getRexBuilder(); + RexNode newBottomCondition = RexUtil.composeConjunction(rexBuilder, newBottomList, false); + final Join newBottomJoin = bottomJoin.copy(bottomJoin.getTraitSet(), newBottomCondition, relA, relC, bottomJoin.getJoinType(), bottomJoin.isSemiJoinDone()); + + // target: | A | C | B | + // source: | A | B | C | + final Mappings.TargetMapping nonThruProjectMapping = Mappings.createShiftMapping(aCount + bCount + cCount, 0, 0, aCount, aCount + cCount, aCount, bCount, aCount, aCount + bCount, cCount); + final List<RexNode> newTopList = new ArrayList<>(); + new RexPermuteInputsShuttle(thruProjectMapping, newBottomJoin, relB).visitList(intersecting, newTopList); + new RexPermuteInputsShuttle(nonThruProjectMapping, newBottomJoin, relB).visitList(bottomIntersecting, newTopList); + RexNode newTopCondition = RexUtil.composeConjunction(rexBuilder, newTopList, false); + @SuppressWarnings("SuspiciousNameCombination") + final Join newTopJoin = topJoin.copy(topJoin.getTraitSet(), newTopCondition, newBottomJoin, relB, topJoin.getJoinType(), topJoin.isSemiJoinDone()); + + assert !Mappings.isIdentity(thruProjectMapping); + final RelBuilder relBuilder = call.builder(); + relBuilder.push(newTopJoin); + relBuilder.project(relBuilder.fields(thruProjectMapping)); + call.transformTo(relBuilder.build()); + } + + private AbstractTargetMapping createAbstractTargetMapping(final Mappings.TargetMapping targetMapping) { + return new AbstractTargetMapping(targetMapping.getSourceCount(), targetMapping.getTargetCount()) { + @Override + public int getTargetOpt(int source) { + return targetMapping.getTargetOpt(source); + } + }; + } + + /** + * Splits a condition into conjunctions that do or do not intersect with + * a given bit set. + */ + static void split(RexNode condition, ImmutableBitSet bitSet, List<RexNode> intersecting, List<RexNode> nonIntersecting) { + for (RexNode node : RelOptUtil.conjunctions(condition)) { + ImmutableBitSet inputBitSet = RelOptUtil.InputFinder.bits(node); + if (bitSet.intersects(inputBitSet)) { + intersecting.add(node); + } else { + nonIntersecting.add(node); + } + } + } +} http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/atopcalcite/src/main/java/org/apache/calcite/tools/Programs.java ---------------------------------------------------------------------- diff --git a/atopcalcite/src/main/java/org/apache/calcite/tools/Programs.java b/atopcalcite/src/main/java/org/apache/calcite/tools/Programs.java new file mode 100644 index 0000000..ec33c4c --- /dev/null +++ b/atopcalcite/src/main/java/org/apache/calcite/tools/Programs.java @@ -0,0 +1,438 @@ +/* + * 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.tools; + +import java.util.Arrays; +import java.util.List; + +import org.apache.calcite.adapter.enumerable.EnumerableRules; +import org.apache.calcite.config.CalciteConnectionConfig; +import org.apache.calcite.interpreter.NoneToBindableConverterRule; +import org.apache.calcite.plan.RelOptCostImpl; +import org.apache.calcite.plan.RelOptLattice; +import org.apache.calcite.plan.RelOptMaterialization; +import org.apache.calcite.plan.RelOptPlanner; +import org.apache.calcite.plan.RelOptRule; +import org.apache.calcite.plan.RelOptUtil; +import org.apache.calcite.plan.RelTraitSet; +import org.apache.calcite.plan.hep.HepMatchOrder; +import org.apache.calcite.plan.hep.HepPlanner; +import org.apache.calcite.plan.hep.HepProgram; +import org.apache.calcite.plan.hep.HepProgramBuilder; +import org.apache.calcite.prepare.CalcitePrepareImpl; +import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.core.Calc; +import org.apache.calcite.rel.core.RelFactories; +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.AggregateExpandDistinctAggregatesRule; +import org.apache.calcite.rel.rules.AggregateReduceFunctionsRule; +import org.apache.calcite.rel.rules.AggregateStarTableRule; +import org.apache.calcite.rel.rules.CalcMergeRule; +import org.apache.calcite.rel.rules.FilterAggregateTransposeRule; +import org.apache.calcite.rel.rules.FilterCalcMergeRule; +import org.apache.calcite.rel.rules.FilterJoinRule; +import org.apache.calcite.rel.rules.FilterProjectTransposeRule; +import org.apache.calcite.rel.rules.FilterTableScanRule; +import org.apache.calcite.rel.rules.FilterToCalcRule; +import org.apache.calcite.rel.rules.JoinAssociateRule; +import org.apache.calcite.rel.rules.JoinCommuteRule; +import org.apache.calcite.rel.rules.JoinPushThroughJoinRule; +import org.apache.calcite.rel.rules.JoinToMultiJoinRule; +import org.apache.calcite.rel.rules.LoptOptimizeJoinRule; +import org.apache.calcite.rel.rules.MultiJoinOptimizeBushyRule; +import org.apache.calcite.rel.rules.OLAPJoinPushThroughJoinRule; +import org.apache.calcite.rel.rules.OLAPJoinPushThroughJoinRule2; +import org.apache.calcite.rel.rules.ProjectCalcMergeRule; +import org.apache.calcite.rel.rules.ProjectMergeRule; +import org.apache.calcite.rel.rules.ProjectToCalcRule; +import org.apache.calcite.rel.rules.SemiJoinRule; +import org.apache.calcite.rel.rules.SortProjectTransposeRule; +import org.apache.calcite.rel.rules.SubQueryRemoveRule; +import org.apache.calcite.rel.rules.TableScanRule; +import org.apache.calcite.sql2rel.RelDecorrelator; +import org.apache.calcite.sql2rel.RelFieldTrimmer; +import org.apache.calcite.sql2rel.SqlToRelConverter; + +import com.google.common.base.Function; +import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableSet; +import com.google.common.collect.Lists; + +/** + * Utilities for creating {@link Program}s. + */ +/* + * The code has synced with calcite. Hope one day, we could remove the hardcode override point. + * OVERRIDE POINT: + * - add OLAPJoinPushThroughJoinRule OLAPJoinPushThroughJoinRule2 to org.apache.calcite.tools.Programs#subQuery + */ + +public class Programs { + private static final Function<RuleSet, Program> RULE_SET_TO_PROGRAM = + new Function<RuleSet, Program>() { + public Program apply(RuleSet ruleSet) { + return of(ruleSet); + } + }; + + public static final ImmutableList<RelOptRule> CALC_RULES = + ImmutableList.of( + NoneToBindableConverterRule.INSTANCE, + EnumerableRules.ENUMERABLE_CALC_RULE, + EnumerableRules.ENUMERABLE_FILTER_TO_CALC_RULE, + EnumerableRules.ENUMERABLE_PROJECT_TO_CALC_RULE, + CalcMergeRule.INSTANCE, + FilterCalcMergeRule.INSTANCE, + ProjectCalcMergeRule.INSTANCE, + FilterToCalcRule.INSTANCE, + ProjectToCalcRule.INSTANCE, + CalcMergeRule.INSTANCE, + + // REVIEW jvs 9-Apr-2006: Do we still need these two? Doesn't the + // combination of CalcMergeRule, FilterToCalcRule, and + // ProjectToCalcRule have the same effect? + FilterCalcMergeRule.INSTANCE, + ProjectCalcMergeRule.INSTANCE); + + /** Program that converts filters and projects to {@link Calc}s. */ + public static final Program CALC_PROGRAM = + calc(DefaultRelMetadataProvider.INSTANCE); + + /** Program that expands sub-queries. */ + public static final Program SUB_QUERY_PROGRAM = + subQuery(DefaultRelMetadataProvider.INSTANCE); + + public static final ImmutableSet<RelOptRule> RULE_SET = + ImmutableSet.of( + EnumerableRules.ENUMERABLE_JOIN_RULE, + EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE, + EnumerableRules.ENUMERABLE_SEMI_JOIN_RULE, + EnumerableRules.ENUMERABLE_CORRELATE_RULE, + EnumerableRules.ENUMERABLE_PROJECT_RULE, + EnumerableRules.ENUMERABLE_FILTER_RULE, + EnumerableRules.ENUMERABLE_AGGREGATE_RULE, + EnumerableRules.ENUMERABLE_SORT_RULE, + EnumerableRules.ENUMERABLE_LIMIT_RULE, + EnumerableRules.ENUMERABLE_UNION_RULE, + EnumerableRules.ENUMERABLE_INTERSECT_RULE, + EnumerableRules.ENUMERABLE_MINUS_RULE, + EnumerableRules.ENUMERABLE_TABLE_MODIFICATION_RULE, + EnumerableRules.ENUMERABLE_VALUES_RULE, + EnumerableRules.ENUMERABLE_WINDOW_RULE, + SemiJoinRule.PROJECT, + SemiJoinRule.JOIN, + TableScanRule.INSTANCE, + CalcitePrepareImpl.COMMUTE + ? JoinAssociateRule.INSTANCE + : ProjectMergeRule.INSTANCE, + AggregateStarTableRule.INSTANCE, + AggregateStarTableRule.INSTANCE2, + FilterTableScanRule.INSTANCE, + FilterProjectTransposeRule.INSTANCE, + FilterJoinRule.FILTER_ON_JOIN, + AggregateExpandDistinctAggregatesRule.INSTANCE, + AggregateReduceFunctionsRule.INSTANCE, + FilterAggregateTransposeRule.INSTANCE, + JoinCommuteRule.INSTANCE, + JoinPushThroughJoinRule.RIGHT, + JoinPushThroughJoinRule.LEFT, + SortProjectTransposeRule.INSTANCE); + + // private constructor for utility class + private Programs() {} + + /** Creates a program that executes a rule set. */ + public static Program of(RuleSet ruleSet) { + return new RuleSetProgram(ruleSet); + } + + /** Creates a list of programs based on an array of rule sets. */ + public static List<Program> listOf(RuleSet... ruleSets) { + return Lists.transform(Arrays.asList(ruleSets), RULE_SET_TO_PROGRAM); + } + + /** Creates a list of programs based on a list of rule sets. */ + public static List<Program> listOf(List<RuleSet> ruleSets) { + return Lists.transform(ruleSets, RULE_SET_TO_PROGRAM); + } + + /** Creates a program from a list of rules. */ + public static Program ofRules(RelOptRule... rules) { + return of(RuleSets.ofList(rules)); + } + + /** Creates a program from a list of rules. */ + public static Program ofRules(Iterable<? extends RelOptRule> rules) { + return of(RuleSets.ofList(rules)); + } + + /** Creates a program that executes a sequence of programs. */ + public static Program sequence(Program... programs) { + return new SequenceProgram(ImmutableList.copyOf(programs)); + } + + /** Creates a program that executes a list of rules in a HEP planner. */ + public static Program hep(Iterable<? extends RelOptRule> rules, + boolean noDag, RelMetadataProvider metadataProvider) { + final HepProgramBuilder builder = HepProgram.builder(); + for (RelOptRule rule : rules) { + builder.addRuleInstance(rule); + } + return of(builder.build(), noDag, metadataProvider); + } + + /** Creates a program that executes a {@link HepProgram}. */ + public static Program of(final HepProgram hepProgram, final boolean noDag, + final RelMetadataProvider metadataProvider) { + return new Program() { + public RelNode run(RelOptPlanner planner, RelNode rel, + RelTraitSet requiredOutputTraits, + List<RelOptMaterialization> materializations, + List<RelOptLattice> lattices) { + final HepPlanner hepPlanner = new HepPlanner(hepProgram, + null, noDag, null, RelOptCostImpl.FACTORY); + + List<RelMetadataProvider> list = Lists.newArrayList(); + if (metadataProvider != null) { + list.add(metadataProvider); + } + hepPlanner.registerMetadataProviders(list); + RelMetadataProvider plannerChain = + ChainedRelMetadataProvider.of(list); + rel.getCluster().setMetadataProvider(plannerChain); + + hepPlanner.setRoot(rel); + return hepPlanner.findBestExp(); + } + }; + } + + /** Creates a program that invokes heuristic join-order optimization + * (via {@link org.apache.calcite.rel.rules.JoinToMultiJoinRule}, + * {@link org.apache.calcite.rel.rules.MultiJoin} and + * {@link org.apache.calcite.rel.rules.LoptOptimizeJoinRule}) + * if there are 6 or more joins (7 or more relations). */ + public static Program heuristicJoinOrder( + final Iterable<? extends RelOptRule> rules, + final boolean bushy, final int minJoinCount) { + return new Program() { + public RelNode run(RelOptPlanner planner, RelNode rel, + RelTraitSet requiredOutputTraits, + List<RelOptMaterialization> materializations, + List<RelOptLattice> lattices) { + final int joinCount = RelOptUtil.countJoins(rel); + final Program program; + if (joinCount < minJoinCount) { + program = ofRules(rules); + } else { + // Create a program that gathers together joins as a MultiJoin. + final HepProgram hep = new HepProgramBuilder() + .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN) + .addMatchOrder(HepMatchOrder.BOTTOM_UP) + .addRuleInstance(JoinToMultiJoinRule.INSTANCE) + .build(); + final Program program1 = + of(hep, false, DefaultRelMetadataProvider.INSTANCE); + + // Create a program that contains a rule to expand a MultiJoin + // into heuristically ordered joins. + // We use the rule set passed in, but remove JoinCommuteRule and + // JoinPushThroughJoinRule, because they cause exhaustive search. + final List<RelOptRule> list = Lists.newArrayList(rules); + list.removeAll( + ImmutableList.of(JoinCommuteRule.INSTANCE, + JoinAssociateRule.INSTANCE, + JoinPushThroughJoinRule.LEFT, + JoinPushThroughJoinRule.RIGHT)); + list.add(bushy + ? MultiJoinOptimizeBushyRule.INSTANCE + : LoptOptimizeJoinRule.INSTANCE); + final Program program2 = ofRules(list); + + program = sequence(program1, program2); + } + return program.run( + planner, rel, requiredOutputTraits, materializations, lattices); + } + }; + } + + public static Program calc(RelMetadataProvider metadataProvider) { + return hep(CALC_RULES, true, metadataProvider); + } + + @Deprecated // to be removed before 2.0 + public static Program subquery(RelMetadataProvider metadataProvider) { + return subQuery(metadataProvider); + } + + public static Program subQuery(RelMetadataProvider metadataProvider) { + return hep( + ImmutableList.of((RelOptRule) SubQueryRemoveRule.FILTER, + SubQueryRemoveRule.PROJECT, + SubQueryRemoveRule.JOIN, OLAPJoinPushThroughJoinRule.INSTANCE, + OLAPJoinPushThroughJoinRule2.INSTANCE + ), true, metadataProvider); + } + + public static Program getProgram() { + return new Program() { + public RelNode run(RelOptPlanner planner, RelNode rel, + RelTraitSet requiredOutputTraits, + List<RelOptMaterialization> materializations, + List<RelOptLattice> lattices) { + return null; + } + }; + } + + /** Returns the standard program used by Prepare. */ + public static Program standard() { + return standard(DefaultRelMetadataProvider.INSTANCE); + } + + /** Returns the standard program with user metadata provider. */ + public static Program standard(RelMetadataProvider metadataProvider) { + + final Program program1 = + new Program() { + public RelNode run(RelOptPlanner planner, RelNode rel, + RelTraitSet requiredOutputTraits, + List<RelOptMaterialization> materializations, + List<RelOptLattice> lattices) { + planner.setRoot(rel); + + for (RelOptMaterialization materialization : materializations) { + planner.addMaterialization(materialization); + } + for (RelOptLattice lattice : lattices) { + planner.addLattice(lattice); + } + + final RelNode rootRel2 = + rel.getTraitSet().equals(requiredOutputTraits) + ? rel + : planner.changeTraits(rel, requiredOutputTraits); + assert rootRel2 != null; + + planner.setRoot(rootRel2); + final RelOptPlanner planner2 = planner.chooseDelegate(); + final RelNode rootRel3 = planner2.findBestExp(); + assert rootRel3 != null : "could not implement exp"; + return rootRel3; + } + }; + + return sequence(subQuery(metadataProvider), + new DecorrelateProgram(), + new TrimFieldsProgram(), + program1, + + // Second planner pass to do physical "tweaks". This the first time that + // EnumerableCalcRel is introduced. + calc(metadataProvider)); + } + + /** Program backed by a {@link RuleSet}. */ + static class RuleSetProgram implements Program { + final RuleSet ruleSet; + + private RuleSetProgram(RuleSet ruleSet) { + this.ruleSet = ruleSet; + } + + public RelNode run(RelOptPlanner planner, RelNode rel, + RelTraitSet requiredOutputTraits, + List<RelOptMaterialization> materializations, + List<RelOptLattice> lattices) { + planner.clear(); + for (RelOptRule rule : ruleSet) { + planner.addRule(rule); + } + for (RelOptMaterialization materialization : materializations) { + planner.addMaterialization(materialization); + } + for (RelOptLattice lattice : lattices) { + planner.addLattice(lattice); + } + if (!rel.getTraitSet().equals(requiredOutputTraits)) { + rel = planner.changeTraits(rel, requiredOutputTraits); + } + planner.setRoot(rel); + return planner.findBestExp(); + + } + } + + /** Program that runs sub-programs, sending the output of the previous as + * input to the next. */ + private static class SequenceProgram implements Program { + private final ImmutableList<Program> programs; + + SequenceProgram(ImmutableList<Program> programs) { + this.programs = programs; + } + + public RelNode run(RelOptPlanner planner, RelNode rel, + RelTraitSet requiredOutputTraits, + List<RelOptMaterialization> materializations, + List<RelOptLattice> lattices) { + for (Program program : programs) { + rel = program.run( + planner, rel, requiredOutputTraits, materializations, lattices); + } + return rel; + } + } + + /** Program that de-correlates a query. + * + * <p>To work around + * <a href="https://issues.apache.org/jira/browse/CALCITE-842";>[CALCITE-842] + * Decorrelator gets field offsets confused if fields have been trimmed</a>, + * disable field-trimming in {@link SqlToRelConverter}, and run + * {@link TrimFieldsProgram} after this program. */ + private static class DecorrelateProgram implements Program { + public RelNode run(RelOptPlanner planner, RelNode rel, + RelTraitSet requiredOutputTraits, + List<RelOptMaterialization> materializations, + List<RelOptLattice> lattices) { + final CalciteConnectionConfig config = + planner.getContext().unwrap(CalciteConnectionConfig.class); + if (config != null && config.forceDecorrelate()) { + return RelDecorrelator.decorrelateQuery(rel); + } + return rel; + } + } + + /** Program that trims fields. */ + private static class TrimFieldsProgram implements Program { + public RelNode run(RelOptPlanner planner, RelNode rel, + RelTraitSet requiredOutputTraits, + List<RelOptMaterialization> materializations, + List<RelOptLattice> lattices) { + final RelBuilder relBuilder = + RelFactories.LOGICAL_BUILDER.create(rel.getCluster(), null); + return new RelFieldTrimmer(null, relBuilder).trim(rel); + } + } +} + +// End Programs.java http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query17.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query17.sql b/kylin-it/src/test/resources/query/sql_subquery/query17.sql new file mode 100644 index 0000000..c5833b0 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query17.sql @@ -0,0 +1,17 @@ + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2010-02-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + + where test_category_groupings.meta_categ_name <> 'Baby' + group by test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query18.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query18.sql b/kylin-it/src/test/resources/query/sql_subquery/query18.sql new file mode 100644 index 0000000..57bb895 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query18.sql @@ -0,0 +1,23 @@ + + +select test_kylin_fact.lstg_format_name, sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + +-- inner JOIN test_category_groupings +-- ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + inner JOIN (select leaf_categ_id, site_id,meta_categ_name from test_category_groupings ) yyy + ON test_kylin_fact.leaf_categ_id = yyy.leaf_categ_id AND test_kylin_fact.lstg_site_id = yyy.site_id + + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2010-02-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + + where yyy.meta_categ_name > '' + group by test_kylin_fact.lstg_format_name + + http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query19.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query19.sql b/kylin-it/src/test/resources/query/sql_subquery/query19.sql new file mode 100644 index 0000000..6fee429 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query19.sql @@ -0,0 +1,20 @@ + + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2012-04-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + + where test_category_groupings.meta_categ_name <> 'Baby' + + group by test_kylin_fact.lstg_format_name + + http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query20.sql.todo ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query20.sql.todo b/kylin-it/src/test/resources/query/sql_subquery/query20.sql.todo new file mode 100644 index 0000000..810d0ca --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query20.sql.todo @@ -0,0 +1,22 @@ + +-- group by or aggregate on columns from subquery will fail! + + +select test_kylin_fact.lstg_format_name, xxx.week_beg_dt,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2010-02-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + + where test_category_groupings.meta_categ_name <> 'Baby' + group by test_kylin_fact.lstg_format_name, xxx.week_beg_dt + + http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query21.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query21.sql b/kylin-it/src/test/resources/query/sql_subquery/query21.sql new file mode 100644 index 0000000..5388d37 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query21.sql @@ -0,0 +1,26 @@ + + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2012-04-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + +inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2013-01-01' ) xxx2 +ON test_kylin_fact.cal_dt = xxx2.cal_dt + + + + where test_category_groupings.meta_categ_name <> 'Baby' + + group by test_kylin_fact.lstg_format_name + + http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query22.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query22.sql b/kylin-it/src/test/resources/query/sql_subquery/query22.sql new file mode 100644 index 0000000..198a25d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query22.sql @@ -0,0 +1,25 @@ + + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2012-04-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + +inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2013-01-01' ) xxx2 +ON test_kylin_fact.cal_dt = xxx2.cal_dt + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + where test_category_groupings.meta_categ_name <> 'Baby' and test_sites.site_name <> 'France' + + group by test_kylin_fact.lstg_format_name + + http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query23.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query23.sql b/kylin-it/src/test/resources/query/sql_subquery/query23.sql new file mode 100644 index 0000000..f1a60ed --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query23.sql @@ -0,0 +1,26 @@ + + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2012-04-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + +inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2013-01-01' ) xxx2 +ON test_kylin_fact.cal_dt = xxx2.cal_dt + + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + where test_category_groupings.meta_categ_name <> 'Baby' and test_sites.site_name <> 'France' + + group by test_kylin_fact.lstg_format_name + + http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query24.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query24.sql b/kylin-it/src/test/resources/query/sql_subquery/query24.sql new file mode 100644 index 0000000..eaf79e4 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query24.sql @@ -0,0 +1,29 @@ + + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2012-04-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + + +inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2013-01-01' ) xxx2 +ON test_kylin_fact.cal_dt = xxx2.cal_dt + + + + + + where test_category_groupings.meta_categ_name <> 'Baby' + + group by test_kylin_fact.lstg_format_name + + http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query25.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query25.sql b/kylin-it/src/test/resources/query/sql_subquery/query25.sql new file mode 100644 index 0000000..81ebc37 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query25.sql @@ -0,0 +1,22 @@ + + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2012-04-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + where test_category_groupings.meta_categ_name <> 'Baby' and test_sites.site_name <> 'France' + + group by test_kylin_fact.lstg_format_name + + http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/kylin-it/src/test/resources/query/sql_subquery/query26.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_subquery/query26.sql b/kylin-it/src/test/resources/query/sql_subquery/query26.sql new file mode 100644 index 0000000..925d0a1 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_subquery/query26.sql @@ -0,0 +1,16 @@ + +select test_kylin_fact.lstg_format_name,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from + + test_kylin_fact + + inner JOIN (select cal_dt,week_beg_dt from edw.test_cal_dt where week_beg_dt >= DATE '2010-02-10' ) xxx + ON test_kylin_fact.cal_dt = xxx.cal_dt + + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + + + where test_category_groupings.meta_categ_name <> 'Baby' + group by test_kylin_fact.lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java b/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java index 54b851f..addf8cf 100644 --- a/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java +++ b/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java @@ -41,6 +41,7 @@ import org.apache.calcite.plan.RelOptPlanner; import org.apache.calcite.plan.RelOptTable; import org.apache.calcite.plan.RelTrait; import org.apache.calcite.plan.RelTraitSet; +import org.apache.calcite.plan.volcano.RelSubset; import org.apache.calcite.rel.InvalidRelException; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.CorrelationId; @@ -107,13 +108,21 @@ public class OLAPJoinRel extends EnumerableJoin implements OLAPRel { return super.estimateRowCount(mq) * 0.1; } + private boolean isParentMerelyPermutation(OLAPImplementor implementor) { + if (implementor.getParentNode() instanceof OLAPProjectRel) { + return ((OLAPProjectRel) implementor.getParentNode()).isMerelyPermutation(); + } + return false; + } + @Override public void implementOLAP(OLAPImplementor implementor) { // create context for root join - if (!(implementor.getParentNode() instanceof OLAPJoinRel)) { + if (!(implementor.getParentNode() instanceof OLAPJoinRel) && !isParentMerelyPermutation(implementor)) { implementor.allocateContext(); } + this.context = implementor.getContext(); this.isTopJoin = !this.context.hasJoin; this.context.hasJoin = true; @@ -161,7 +170,7 @@ public class OLAPJoinRel extends EnumerableJoin implements OLAPRel { join.setType(joinType); this.context.joins.add(join); - } else if (leftHasSubquery != rightHasSubquery) { + } else { //When join contains subquery, the join-condition fields of fact_table will add into context. Map<TblColRef, TblColRef> joinCol = new HashMap<TblColRef, TblColRef>(); translateJoinColumn(this.getCondition(), joinCol); http://git-wip-us.apache.org/repos/asf/kylin/blob/2fc47224/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java b/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java index 1d0de40..358136f 100644 --- a/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java +++ b/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java @@ -65,6 +65,7 @@ public class OLAPProjectRel extends Project implements OLAPRel { private boolean hasJoin; private boolean afterJoin; private boolean afterAggregate; + private boolean isMerelyPermutation = false;//project additionally added by OLAPJoinPushThroughJoinRule public OLAPProjectRel(RelOptCluster cluster, RelTraitSet traitSet, RelNode child, List<RexNode> exps, RelDataType rowType) { super(cluster, traitSet, child, exps, rowType); @@ -106,6 +107,10 @@ public class OLAPProjectRel extends Project implements OLAPRel { @Override public void implementOLAP(OLAPImplementor implementor) { + if (this.getPermutation() != null && !(implementor.getParentNode() instanceof OLAPToEnumerableConverter)) { + isMerelyPermutation = true; + } + implementor.fixSharedOlapTableScan(this); implementor.visitChild(getInput(), this); @@ -173,7 +178,9 @@ public class OLAPProjectRel extends Project implements OLAPRel { if (index < inputColumnRowType.size()) { TblColRef column = inputColumnRowType.getColumnByIndex(index); if (!column.isInnerColumn() && !this.rewriting && !this.afterAggregate) { - context.allColumns.add(column); + if (!isMerelyPermutation) { + context.allColumns.add(column); + } sourceCollector.add(column); } return column; @@ -289,6 +296,7 @@ public class OLAPProjectRel extends Project implements OLAPRel { public OLAPContext getContext() { return context; } + @Override public boolean hasSubQuery() { @@ -302,4 +310,8 @@ public class OLAPProjectRel extends Project implements OLAPRel { this.traitSet = this.traitSet.replace(trait); return oldTraitSet; } + + public boolean isMerelyPermutation() { + return isMerelyPermutation; + } }
