kylin-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From liy...@apache.org
Subject [12/17] kylin git commit: KYLIN-2579 Improvement on subqueries: reorder subqueries joins with RelOptRule
Date Mon, 01 May 2017 12:55:47 GMT
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 <mahongbin@apache.org>
Authored: Mon May 1 17:39:12 2017 +0800
Committer: Hongbin Ma <mahongbin@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;
+    }
 }


Mime
View raw message