This is an automated email from the ASF dual-hosted git repository. chunwei pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push: new 996579a [CALCITE-2712] Add rule to remove null-generating side of a Join 996579a is described below commit 996579a20cc6a66481055a93ca634622f69b1bfd Author: Chunwei Lei <chunwei....@alibaba-inc.com> AuthorDate: Mon Apr 1 20:40:47 2019 +0800 [CALCITE-2712] Add rule to remove null-generating side of a Join --- .../java/org/apache/calcite/plan/RelOptUtil.java | 15 + .../rel/rules/AggregateJoinJoinRemoveRule.java | 158 ++++++ .../calcite/rel/rules/AggregateJoinRemoveRule.java | 127 +++++ .../rel/rules/AggregateProjectMergeRule.java | 13 +- .../rel/rules/ProjectJoinJoinRemoveRule.java | 142 ++++++ .../calcite/rel/rules/ProjectJoinRemoveRule.java | 127 +++++ .../org/apache/calcite/test/RelOptRulesTest.java | 268 +++++++++- .../org/apache/calcite/test/RelOptRulesTest.xml | 538 +++++++++++++++++++++ 8 files changed, 1376 insertions(+), 12 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java index 15733d9..de96a95 100644 --- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java +++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java @@ -24,6 +24,7 @@ import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.RelShuttle; import org.apache.calcite.rel.RelVisitor; import org.apache.calcite.rel.RelWriter; +import org.apache.calcite.rel.core.Aggregate; import org.apache.calcite.rel.core.AggregateCall; import org.apache.calcite.rel.core.Calc; import org.apache.calcite.rel.core.Correlate; @@ -735,6 +736,20 @@ public abstract class RelOptUtil { } } + /** Gets all fields in an aggregate. */ + public static Set<Integer> getAllFields(Aggregate aggregate) { + final Set<Integer> allFields = new TreeSet<>(); + allFields.addAll(aggregate.getGroupSet().asList()); + for (AggregateCall aggregateCall : aggregate.getAggCallList()) { + allFields.addAll(aggregateCall.getArgList()); + if (aggregateCall.filterArg >= 0) { + allFields.add(aggregateCall.filterArg); + } + allFields.addAll(RelCollations.ordinals(aggregateCall.collation)); + } + return allFields; + } + /** * Creates a LogicalAggregate that removes all duplicates from the result of * an underlying relational expression. diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinJoinRemoveRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinJoinRemoveRule.java new file mode 100644 index 0000000..9f7e192 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinJoinRemoveRule.java @@ -0,0 +1,158 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.rel.rules; + +import org.apache.calcite.plan.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.Aggregate; +import org.apache.calcite.rel.core.AggregateCall; +import org.apache.calcite.rel.core.Join; +import org.apache.calcite.rel.core.JoinRelType; +import org.apache.calcite.rel.core.RelFactories; +import org.apache.calcite.rel.logical.LogicalAggregate; +import org.apache.calcite.rel.logical.LogicalJoin; +import org.apache.calcite.rex.RexNode; +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.collect.ImmutableList; + +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; + +/** + * Planner rule that matches an {@link org.apache.calcite.rel.core.Aggregate} + * on a {@link org.apache.calcite.rel.core.Join} and removes the left input + * of the join provided that the left input is also a left join if possible. + * + * <p>For instance,</p> + * + * <blockquote> + * <pre>select distinct s.product_id, pc.product_id from + * sales as s + * left join product as p + * on s.product_id = p.product_id + * left join product_class pc + * on s.product_id = pc.product_id</pre></blockquote> + * + * <p>becomes + * + * <blockquote> + * <pre>select distinct s.product_id, pc.product_id from + * sales as s + * left join product_class pc + * on s.product_id = pc.product_id</pre></blockquote> + * + */ +public class AggregateJoinJoinRemoveRule extends RelOptRule { + public static final AggregateJoinJoinRemoveRule INSTANCE + = new AggregateJoinJoinRemoveRule(LogicalAggregate.class, + LogicalJoin.class, RelFactories.LOGICAL_BUILDER); + + /** Creates an AggregateJoinJoinRemoveRule. */ + public AggregateJoinJoinRemoveRule( + Class<? extends Aggregate> aggregateClass, + Class<? extends Join> joinClass, RelBuilderFactory relBuilderFactory) { + super( + operand(aggregateClass, + operandJ(joinClass, null, + join -> join.getJoinType() == JoinRelType.LEFT, + operandJ(joinClass, null, + join -> join.getJoinType() == JoinRelType.LEFT, any()))), + relBuilderFactory, null); + } + + @Override public void onMatch(RelOptRuleCall call) { + final Aggregate aggregate = call.rel(0); + final Join topJoin = call.rel(1); + final Join bottomJoin = call.rel(2); + int leftBottomChildSize = bottomJoin.getLeft().getRowType() + .getFieldCount(); + + // Check whether the aggregate uses columns in the right input of + // bottom join. + final Set<Integer> allFields = RelOptUtil.getAllFields(aggregate); + if (allFields.stream().anyMatch(i -> i >= leftBottomChildSize + && i < bottomJoin.getRowType().getFieldCount())) { + return; + } + + if (aggregate.getAggCallList().stream().anyMatch(aggregateCall -> + !aggregateCall.isDistinct())) { + return; + } + + // Check whether the top join uses columns in the right input of bottom join. + final List<Integer> leftKeys = new ArrayList<>(); + RelOptUtil.splitJoinCondition(topJoin.getLeft(), topJoin.getRight(), + topJoin.getCondition(), leftKeys, new ArrayList<>(), + new ArrayList<>()); + if (leftKeys.stream().anyMatch(s -> s >= leftBottomChildSize)) { + return; + } + + // Check whether left join keys in top join and bottom join are equal. + final List<Integer> leftChildKeys = new ArrayList<>(); + RelOptUtil.splitJoinCondition(bottomJoin.getLeft(), bottomJoin.getRight(), + bottomJoin.getCondition(), leftChildKeys, new ArrayList<>(), + new ArrayList<>()); + if (!leftKeys.equals(leftChildKeys)) { + return; + } + + int offset = bottomJoin.getRight().getRowType().getFieldCount(); + final RelBuilder relBuilder = call.builder(); + RexNode condition = RexUtil.shift(topJoin.getCondition(), + leftBottomChildSize, -offset); + RelNode join = relBuilder.push(bottomJoin.getLeft()) + .push(topJoin.getRight()) + .join(topJoin.getJoinType(), condition) + .build(); + + final Map<Integer, Integer> map = new HashMap<>(); + allFields.forEach( + index -> + map.put(index, + index < leftBottomChildSize ? index : index - offset)); + final ImmutableBitSet groupSet = aggregate.getGroupSet().permute(map); + + final ImmutableList.Builder<AggregateCall> aggCalls = + ImmutableList.builder(); + final int sourceCount = aggregate.getInput().getRowType().getFieldCount(); + final Mappings.TargetMapping targetMapping = + Mappings.target(map, sourceCount, sourceCount); + aggregate.getAggCallList().forEach( + aggregateCall -> + aggCalls.add(aggregateCall.transform(targetMapping))); + + RelNode newAggregate = relBuilder.push(join) + .aggregate(relBuilder.groupKey(groupSet), aggCalls.build()) + .build(); + + call.transformTo(newAggregate); + } +} + +// End AggregateJoinJoinRemoveRule.java diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinRemoveRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinRemoveRule.java new file mode 100644 index 0000000..823b0ec --- /dev/null +++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateJoinRemoveRule.java @@ -0,0 +1,127 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.rel.rules; + +import org.apache.calcite.plan.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.Aggregate; +import org.apache.calcite.rel.core.AggregateCall; +import org.apache.calcite.rel.core.Join; +import org.apache.calcite.rel.core.JoinRelType; +import org.apache.calcite.rel.core.RelFactories; +import org.apache.calcite.rel.logical.LogicalAggregate; +import org.apache.calcite.rel.logical.LogicalJoin; +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.collect.ImmutableList; + +import java.util.HashMap; +import java.util.Map; +import java.util.Set; + +/** + * Planner rule that matches an {@link org.apache.calcite.rel.core.Aggregate} + * on a {@link org.apache.calcite.rel.core.Join} and removes the join + * provided that the join is a left join or right join and it computes no + * aggregate functions or all the aggregate calls have distinct. + * + * <p>For instance,</p> + * + * <blockquote> + * <pre>select distinct s.product_id from + * sales as s + * left join product as p + * on s.product_id = p.product_id</pre></blockquote> + * + * <p>becomes + * + * <blockquote> + * <pre>select distinct s.product_id from sales as s</pre></blockquote> + * + */ +public class AggregateJoinRemoveRule extends RelOptRule { + public static final AggregateJoinRemoveRule INSTANCE + = new AggregateJoinRemoveRule(LogicalAggregate.class, LogicalJoin.class, + RelFactories.LOGICAL_BUILDER); + + /** Creates an AggregateJoinRemoveRule. */ + public AggregateJoinRemoveRule( + Class<? extends Aggregate> aggregateClass, + Class<? extends Join> joinClass, RelBuilderFactory relBuilderFactory) { + super( + operand(aggregateClass, + operandJ(joinClass, null, + join -> join.getJoinType() == JoinRelType.LEFT + || join.getJoinType() == JoinRelType.RIGHT, any())), + relBuilderFactory, null); + } + + @Override public void onMatch(RelOptRuleCall call) { + final Aggregate aggregate = call.rel(0); + final Join join = call.rel(1); + boolean isLeftJoin = join.getJoinType() == JoinRelType.LEFT; + int lower = isLeftJoin + ? join.getLeft().getRowType().getFieldCount() - 1 : 0; + int upper = isLeftJoin ? join.getRowType().getFieldCount() + : join.getLeft().getRowType().getFieldCount(); + + // Check whether the aggregate uses columns whose index is between + // lower(included) and upper(excluded). + final Set<Integer> allFields = RelOptUtil.getAllFields(aggregate); + if (allFields.stream().anyMatch(i -> i >= lower && i < upper)) { + return; + } + + if (aggregate.getAggCallList().stream().anyMatch( + aggregateCall -> !aggregateCall.isDistinct())) { + return; + } + + RelNode node; + if (isLeftJoin) { + node = aggregate + .copy(aggregate.getTraitSet(), join.getLeft(), aggregate.indicator, + aggregate.getGroupSet(), aggregate.getGroupSets(), + aggregate.getAggCallList()); + } else { + final Map<Integer, Integer> map = new HashMap<>(); + allFields.forEach(index -> map.put(index, index - upper)); + final ImmutableBitSet groupSet = aggregate.getGroupSet().permute(map); + + final ImmutableList.Builder<AggregateCall> aggCalls = + ImmutableList.builder(); + final int sourceCount = aggregate.getInput().getRowType().getFieldCount(); + final Mappings.TargetMapping targetMapping = + Mappings.target(map, sourceCount, sourceCount); + aggregate.getAggCallList().forEach(aggregateCall -> + aggCalls.add(aggregateCall.transform(targetMapping))); + + final RelBuilder relBuilder = call.builder(); + node = relBuilder.push(join.getRight()) + .aggregate(relBuilder.groupKey(groupSet), aggCalls.build()) + .build(); + } + call.transformTo(node); + } +} + +// End AggregateJoinRemoveRule.java diff --git a/core/src/main/java/org/apache/calcite/rel/rules/AggregateProjectMergeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/AggregateProjectMergeRule.java index 925afbe..411d6af 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateProjectMergeRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateProjectMergeRule.java @@ -18,7 +18,7 @@ package org.apache.calcite.rel.rules; import org.apache.calcite.plan.RelOptRule; import org.apache.calcite.plan.RelOptRuleCall; -import org.apache.calcite.rel.RelCollations; +import org.apache.calcite.plan.RelOptUtil; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Aggregate; import org.apache.calcite.rel.core.Aggregate.Group; @@ -40,7 +40,6 @@ import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; -import java.util.TreeSet; /** * Planner rule that recognizes a {@link org.apache.calcite.rel.core.Aggregate} @@ -79,15 +78,7 @@ public class AggregateProjectMergeRule extends RelOptRule { public static RelNode apply(RelOptRuleCall call, Aggregate aggregate, Project project) { // Find all fields which we need to be straightforward field projections. - final Set<Integer> interestingFields = new TreeSet<>(); - interestingFields.addAll(aggregate.getGroupSet().asList()); - for (AggregateCall aggregateCall : aggregate.getAggCallList()) { - interestingFields.addAll(aggregateCall.getArgList()); - if (aggregateCall.filterArg >= 0) { - interestingFields.add(aggregateCall.filterArg); - } - interestingFields.addAll(RelCollations.ordinals(aggregateCall.collation)); - } + final Set<Integer> interestingFields = RelOptUtil.getAllFields(aggregate); // Build the map from old to new; abort if any entry is not a // straightforward field projection. diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinJoinRemoveRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinJoinRemoveRule.java new file mode 100644 index 0000000..391c8a9 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinJoinRemoveRule.java @@ -0,0 +1,142 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.rel.rules; + +import org.apache.calcite.plan.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.JoinRelType; +import org.apache.calcite.rel.core.Project; +import org.apache.calcite.rel.core.RelFactories; +import org.apache.calcite.rel.logical.LogicalJoin; +import org.apache.calcite.rel.logical.LogicalProject; +import org.apache.calcite.rel.metadata.RelMetadataQuery; +import org.apache.calcite.rex.RexNode; +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 java.util.ArrayList; +import java.util.List; +import java.util.stream.Collectors; + +/** + * Planner rule that matches an {@link org.apache.calcite.rel.core.Project} + * on a {@link org.apache.calcite.rel.core.Join} and removes the left input + * of the join provided that the left input is also a left join if possible. + * + * <p>For instance,</p> + * + * <blockquote> + * <pre>select s.product_id, pc.product_id from + * sales as s + * left join product as p + * on s.product_id = p.product_id + * left join product_class pc + * on s.product_id = pc.product_id</pre></blockquote> + * + * <p>becomes + * + * <blockquote> + * <pre>select s.product_id, pc.product_id from + * sales as s + * left join product_class pc + * on s.product_id = pc.product_id</pre></blockquote> + * + */ +public class ProjectJoinJoinRemoveRule extends RelOptRule { + public static final ProjectJoinJoinRemoveRule INSTANCE + = new ProjectJoinJoinRemoveRule(LogicalProject.class, + LogicalJoin.class, RelFactories.LOGICAL_BUILDER); + + /** Creates a ProjectJoinJoinRemoveRule. */ + public ProjectJoinJoinRemoveRule( + Class<? extends Project> projectClass, + Class<? extends Join> joinClass, RelBuilderFactory relBuilderFactory) { + super( + operand(projectClass, + operandJ(joinClass, null, + join -> join.getJoinType() == JoinRelType.LEFT, + operandJ(joinClass, null, + join -> join.getJoinType() == JoinRelType.LEFT, any()))), + relBuilderFactory, null); + } + + @Override public void onMatch(RelOptRuleCall call) { + final Project project = call.rel(0); + final Join topJoin = call.rel(1); + final Join bottomJoin = call.rel(2); + int leftBottomChildSize = bottomJoin.getLeft().getRowType().getFieldCount(); + + // Check whether the project uses columns in the right input of bottom join. + for (RexNode expr: project.getProjects()) { + if (RelOptUtil.InputFinder.bits(expr).asList().stream().anyMatch( + i -> i >= leftBottomChildSize + && i < bottomJoin.getRowType().getFieldCount())) { + return; + } + } + + // Check whether the top join uses columns in the right input of bottom join. + final List<Integer> leftKeys = new ArrayList<>(); + RelOptUtil.splitJoinCondition(topJoin.getLeft(), topJoin.getRight(), + topJoin.getCondition(), leftKeys, new ArrayList<>(), + new ArrayList<>()); + if (leftKeys.stream().anyMatch(s -> s >= leftBottomChildSize)) { + return; + } + + // Check whether left join keys in top join and bottom join are equal. + final List<Integer> leftChildKeys = new ArrayList<>(); + final List<Integer> rightChildKeys = new ArrayList<>(); + RelOptUtil.splitJoinCondition(bottomJoin.getLeft(), bottomJoin.getRight(), + bottomJoin.getCondition(), leftChildKeys, rightChildKeys, + new ArrayList<>()); + if (!leftKeys.equals(leftChildKeys)) { + return; + } + + // Make sure that right keys of bottom join are unique. + final ImmutableBitSet.Builder columns = ImmutableBitSet.builder(); + rightChildKeys.forEach(key -> columns.set(key)); + final RelMetadataQuery mq = call.getMetadataQuery(); + if (!mq.areColumnsUnique(bottomJoin.getRight(), columns.build())) { + return; + } + + int offset = bottomJoin.getRight().getRowType().getFieldCount(); + final RelBuilder relBuilder = call.builder(); + + final RexNode condition = RexUtil.shift(topJoin.getCondition(), + leftBottomChildSize, -offset); + final RelNode join = relBuilder.push(bottomJoin.getLeft()) + .push(topJoin.getRight()) + .join(topJoin.getJoinType(), condition) + .build(); + + final List<RexNode> newExprs = project.getProjects().stream() + .map(expr -> RexUtil.shift(expr, leftBottomChildSize, -offset)) + .collect(Collectors.toList()); + relBuilder.push(join).project(newExprs); + call.transformTo(relBuilder.build()); + } +} + +// End ProjectJoinJoinRemoveRule.java diff --git a/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinRemoveRule.java b/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinRemoveRule.java new file mode 100644 index 0000000..2fcdcf1 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinRemoveRule.java @@ -0,0 +1,127 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.rel.rules; + +import org.apache.calcite.plan.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.JoinRelType; +import org.apache.calcite.rel.core.Project; +import org.apache.calcite.rel.core.RelFactories; +import org.apache.calcite.rel.logical.LogicalJoin; +import org.apache.calcite.rel.logical.LogicalProject; +import org.apache.calcite.rel.metadata.RelMetadataQuery; +import org.apache.calcite.rex.RexNode; +import org.apache.calcite.rex.RexUtil; +import org.apache.calcite.tools.RelBuilderFactory; +import org.apache.calcite.util.ImmutableBitSet; + +import java.util.ArrayList; +import java.util.List; +import java.util.stream.Collectors; + +/** + * Planner rule that matches an {@link Project} + * on a {@link Join} and removes the join provided that the join is a left join + * or right join and the join keys are unique. + * + * <p>For instance,</p> + * + * <blockquote> + * <pre>select s.product_id from + * sales as s + * left join product as p + * on s.product_id = p.product_id</pre></blockquote> + * + * <p>becomes + * + * <blockquote> + * <pre>select s.product_id from sales as s</pre></blockquote> + * + */ +public class ProjectJoinRemoveRule extends RelOptRule { + public static final ProjectJoinRemoveRule INSTANCE + = new ProjectJoinRemoveRule(LogicalProject.class, + LogicalJoin.class, RelFactories.LOGICAL_BUILDER); + + /** Creates a ProjectJoinRemoveRule. */ + public ProjectJoinRemoveRule( + Class<? extends Project> projectClass, + Class<? extends Join> joinClass, RelBuilderFactory relBuilderFactory) { + super( + operand(projectClass, + operandJ(joinClass, null, + join -> join.getJoinType() == JoinRelType.LEFT + || join.getJoinType() == JoinRelType.RIGHT, any())), + relBuilderFactory, null); + } + + @Override public void onMatch(RelOptRuleCall call) { + final Project project = call.rel(0); + final Join join = call.rel(1); + final boolean isLeftJoin = join.getJoinType() == JoinRelType.LEFT; + int lower = isLeftJoin + ? join.getLeft().getRowType().getFieldCount() - 1 : 0; + int upper = isLeftJoin + ? join.getRowType().getFieldCount() + : join.getLeft().getRowType().getFieldCount(); + + // Check whether the project uses columns whose index is between + // lower(included) and upper(excluded). + for (RexNode expr: project.getProjects()) { + if (RelOptUtil.InputFinder.bits(expr).asList().stream().anyMatch( + i -> i >= lower && i < upper)) { + return; + } + } + + final List<Integer> leftKeys = new ArrayList<>(); + final List<Integer> rightKeys = new ArrayList<>(); + RelOptUtil.splitJoinCondition(join.getLeft(), join.getRight(), + join.getCondition(), leftKeys, rightKeys, + new ArrayList<>()); + + final List<Integer> joinKeys = isLeftJoin ? rightKeys : leftKeys; + final ImmutableBitSet.Builder columns = ImmutableBitSet.builder(); + joinKeys.forEach(key -> columns.set(key)); + + final RelMetadataQuery mq = call.getMetadataQuery(); + if (!mq.areColumnsUnique(isLeftJoin ? join.getRight() : join.getLeft(), + columns.build())) { + return; + } + + RelNode node; + if (isLeftJoin) { + node = project + .copy(project.getTraitSet(), join.getLeft(), project.getProjects(), + project.getRowType()); + } else { + final int offset = join.getLeft().getRowType().getFieldCount(); + final List<RexNode> newExprs = project.getProjects().stream() + .map(expr -> RexUtil.shift(expr, -offset)) + .collect(Collectors.toList()); + node = project.copy(project.getTraitSet(), join.getRight(), newExprs, + project.getRowType()); + } + call.transformTo(node); + } +} + +// End ProjectJoinRemoveRule.java diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index aa307f0..d4b386d 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -57,6 +57,8 @@ import org.apache.calcite.rel.metadata.RelMetadataProvider; import org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule; import org.apache.calcite.rel.rules.AggregateExtractProjectRule; import org.apache.calcite.rel.rules.AggregateFilterTransposeRule; +import org.apache.calcite.rel.rules.AggregateJoinJoinRemoveRule; +import org.apache.calcite.rel.rules.AggregateJoinRemoveRule; import org.apache.calcite.rel.rules.AggregateJoinTransposeRule; import org.apache.calcite.rel.rules.AggregateMergeRule; import org.apache.calcite.rel.rules.AggregateProjectMergeRule; @@ -89,6 +91,8 @@ import org.apache.calcite.rel.rules.JoinToMultiJoinRule; import org.apache.calcite.rel.rules.JoinUnionTransposeRule; import org.apache.calcite.rel.rules.ProjectCorrelateTransposeRule; import org.apache.calcite.rel.rules.ProjectFilterTransposeRule; +import org.apache.calcite.rel.rules.ProjectJoinJoinRemoveRule; +import org.apache.calcite.rel.rules.ProjectJoinRemoveRule; import org.apache.calcite.rel.rules.ProjectJoinTransposeRule; import org.apache.calcite.rel.rules.ProjectMergeRule; import org.apache.calcite.rel.rules.ProjectMultiJoinMergeRule; @@ -4350,6 +4354,269 @@ public class RelOptRulesTest extends RelOptTestBase { checkPlanUnchanged(new HepPlanner(program), sql); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2712">[CALCITE-2712] + * Should remove the left join since the aggregate has no call and + * only uses column in the left input of the bottom join as group key.</a>. */ + @Test public void testAggregateJoinRemove1() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinRemoveRule.INSTANCE) + .build(); + final String sql = + "select distinct e.deptno from sales.emp e\n" + + "left outer join sales.dept d on e.deptno = d.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()} but has aggregate + * call with distinct. */ + @Test public void testAggregateJoinRemove2() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinRemoveRule.INSTANCE) + .build(); + final String sql = + "select e.deptno, count(distinct e.job) from sales.emp e\n" + + "left outer join sales.dept d on e.deptno = d.deptno\n" + + "group by e.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()} but should not + * remove the left join since the aggregate uses column in the right + * input of the bottom join. */ + @Test public void testAggregateJoinRemove3() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinRemoveRule.INSTANCE) + .build(); + final String sql = + "select e.deptno, count(distinct d.name) from sales.emp e\n" + + "left outer join sales.dept d on e.deptno = d.deptno\n" + + "group by e.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()} but right join. */ + @Test public void testAggregateJoinRemove4() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinRemoveRule.INSTANCE) + .build(); + final String sql = + "select distinct d.deptno from sales.emp e\n" + + "right outer join sales.dept d on e.deptno = d.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove2()} but right join. */ + @Test public void testAggregateJoinRemove5() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinRemoveRule.INSTANCE) + .build(); + final String sql = + "select d.deptno, count(distinct d.name) from sales.emp e\n" + + "right outer join sales.dept d on e.deptno = d.deptno\n" + + "group by d.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove3()} but right join. */ + @Test public void testAggregateJoinRemove6() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinRemoveRule.INSTANCE) + .build(); + final String sql = + "select d.deptno, count(distinct e.job) from sales.emp e\n" + + "right outer join sales.dept d on e.deptno = d.deptno\n" + + "group by d.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should remove the bottom join since the aggregate has no aggregate + * call. */ + @Test public void testAggregateJoinRemove7() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT distinct e.deptno\n" + + "FROM sales.emp e\n" + + "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n" + + "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + + /** Similar to {@link #testAggregateJoinRemove7()} but has aggregate + * call. */ + @Test public void testAggregateJoinRemove8() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e.deptno, COUNT(DISTINCT d2.name)\n" + + "FROM sales.emp e\n" + + "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n" + + "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno\n" + + "GROUP BY e.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove7()} but use columns in + * the right input of the top join. */ + @Test public void testAggregateJoinRemove9() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT distinct e.deptno, d2.name\n" + + "FROM sales.emp e\n" + + "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n" + + "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should not remove the bottom join since the aggregate uses column in the + * right input of bottom join. */ + @Test public void testAggregateJoinRemove10() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(AggregateProjectMergeRule.INSTANCE) + .addRuleInstance(AggregateJoinJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e.deptno, COUNT(DISTINCT d1.name, d2.name)\n" + + "FROM sales.emp e\n" + + "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n" + + "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno\n" + + "GROUP BY e.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should remove the bottom join since the project uses column in the + * right input of bottom join. */ + @Test public void testProjectJoinRemove1() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e.deptno, d2.deptno\n" + + "FROM sales.emp e\n" + + "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n" + + "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should not remove the bottom join since the project uses column in the + * left input of bottom join. */ + @Test public void testProjectJoinRemove2() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e.deptno, d1.deptno\n" + + "FROM sales.emp e\n" + + "LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno\n" + + "LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno"; + checkPlanUnchanged(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should not remove the bottom join since the right join keys of bottom + * join are not unique. */ + @Test public void testProjectJoinRemove3() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e1.deptno, d.deptno\n" + + "FROM sales.emp e1\n" + + "LEFT JOIN sales.emp e2 ON e1.deptno = e2.deptno\n" + + "LEFT JOIN sales.dept d ON e1.deptno = d.deptno"; + checkPlanUnchanged(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should remove the left join since the join key of the right input is + * unique. */ + @Test public void testProjectJoinRemove4() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e.deptno\n" + + "FROM sales.emp e\n" + + "LEFT JOIN sales.dept d ON e.deptno = d.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should not remove the left join since the join key of the right input is + * not unique. */ + @Test public void testProjectJoinRemove5() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e1.deptno\n" + + "FROM sales.emp e1\n" + + "LEFT JOIN sales.emp e2 ON e1.deptno = e2.deptno"; + checkPlanUnchanged(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should not remove the left join since the project use columns in the right + * input of the join. */ + @Test public void testProjectJoinRemove6() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e.deptno, d.name\n" + + "FROM sales.emp e\n" + + "LEFT JOIN sales.dept d ON e.deptno = d.deptno"; + checkPlanUnchanged(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should remove the right join since the join key of the left input is + * unique. */ + @Test public void testProjectJoinRemove7() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e.deptno\n" + + "FROM sales.dept d\n" + + "RIGHT JOIN sales.emp e ON e.deptno = d.deptno"; + checkPlanning(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should not remove the right join since the join key of the left input is + * not unique. */ + @Test public void testProjectJoinRemove8() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e2.deptno\n" + + "FROM sales.emp e1\n" + + "RIGHT JOIN sales.emp e2 ON e1.deptno = e2.deptno"; + checkPlanUnchanged(new HepPlanner(program), sql); + } + + /** Similar to {@link #testAggregateJoinRemove1()}; + * Should not remove the right join since the project uses columns in the + * left input of the join. */ + @Test public void testProjectJoinRemove9() { + final HepProgram program = new HepProgramBuilder() + .addRuleInstance(ProjectJoinRemoveRule.INSTANCE) + .build(); + final String sql = "SELECT e.deptno, d.name\n" + + "FROM sales.dept d\n" + + "RIGHT JOIN sales.emp e ON e.deptno = d.deptno"; + checkPlanUnchanged(new HepPlanner(program), sql); + } + @Test public void testSwapOuterJoin() { final HepProgram program = new HepProgramBuilder() .addMatchLimit(1) @@ -4360,7 +4627,6 @@ public class RelOptRulesTest extends RelOptTestBase { + " on d.deptno = e.deptno"); } - @Test public void testPushJoinCondDownToProject() { final HepProgram program = new HepProgramBuilder() .addRuleInstance(FilterJoinRule.FILTER_ON_JOIN) diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index fcd662b..191afb5 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -7717,6 +7717,62 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT(DISTINCT $5)]) ]]> </Resource> </TestCase> + <TestCase name="testAggregateJoinRemoveRule1"> + <Resource name="sql"> + <![CDATA[select count(distinct sal) from sales.emp e +left outer join sales.dept d1 on e.job = d1.name +left outer join sales.dept d2 on e.job = d2.name +group by e.job +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)]) + LogicalProject(JOB=[$2], NAME0=[$12]) + LogicalJoin(condition=[=($2, $12)], joinType=[left]) + LogicalJoin(condition=[=($2, $10)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{2}], EXPR$1=[COUNT(DISTINCT $10)]) + LogicalJoin(condition=[=($2, $10)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemoveRule2"> + <Resource name="sql"> + <![CDATA[select count(distinct sal) from sales.emp e +left outer join sales.dept d1 on e.job = d1.name +left outer join sales.dept d2 on e.job = d2.name +group by e.job +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0, 1}]) + LogicalProject(JOB=[$2], NAME=[$12]) + LogicalJoin(condition=[=($2, $12)], joinType=[left]) + LogicalJoin(condition=[=($2, $10)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{2, 10}]) + LogicalJoin(condition=[=($2, $10)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> <TestCase name="testPushAggregateThroughJoinDistinct"> <Resource name="sql"> <![CDATA[select d.name, @@ -8038,6 +8094,488 @@ LogicalAggregate(group=[{0}], EXPR$1=[MAX($1)]) ]]> </Resource> </TestCase> + <TestCase name="testAggregateJoinRemove1"> + <Resource name="sql"> + <![CDATA[select distinct e.deptno from sales.emp e +left outer join sales.dept d on e.deptno = d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}]) + LogicalProject(DEPTNO=[$7]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{7}]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove2"> + <Resource name="sql"> + <![CDATA[select e.deptno, count(distinct e.job) from sales.emp e +left outer join sales.dept d on e.deptno = d.deptno +group by e.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)]) + LogicalProject(DEPTNO=[$7], JOB=[$2]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{7}], EXPR$1=[COUNT(DISTINCT $2)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove3"> + <Resource name="sql"> + <![CDATA[select e.deptno, count(distinct d.name) from sales.emp e +left outer join sales.dept d on e.deptno = d.deptno +group by e.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)]) + LogicalProject(DEPTNO=[$7], NAME=[$10]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{7}], EXPR$1=[COUNT(DISTINCT $10)]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove4"> + <Resource name="sql"> + <![CDATA[select distinct d.deptno from sales.emp e +right outer join sales.dept d on e.deptno = d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}]) + LogicalProject(DEPTNO=[$9]) + LogicalJoin(condition=[=($7, $9)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove5"> + <Resource name="sql"> + <![CDATA[select d.deptno, count(distinct d.name) from sales.emp e +right outer join sales.dept d on e.deptno = d.deptno +group by d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)]) + LogicalProject(DEPTNO=[$9], NAME=[$10]) + LogicalJoin(condition=[=($7, $9)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove6"> + <Resource name="sql"> + <![CDATA[select d.deptno, count(distinct e.job) from sales.emp e +right outer join sales.dept d on e.deptno = d.deptno +group by d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)]) + LogicalProject(DEPTNO=[$9], JOB=[$2]) + LogicalJoin(condition=[=($7, $9)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{9}], EXPR$1=[COUNT(DISTINCT $2)]) + LogicalJoin(condition=[=($7, $9)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove7"> + <Resource name="sql"> + <![CDATA[SELECT distinct e.deptno +FROM sales.emp e +LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno +LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}]) + LogicalProject(DEPTNO=[$7]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{7}]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove8"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno, COUNT(DISTINCT d2.name) +FROM sales.emp e +LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno +LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno +GROUP BY e.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1)]) + LogicalProject(DEPTNO=[$7], NAME0=[$12]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{7}], EXPR$1=[COUNT(DISTINCT $10)]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove9"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno, d2.name +FROM sales.emp e +LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno +LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno +GROUP BY e.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0, 1}]) + LogicalProject(DEPTNO=[$7], NAME=[$12]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{7, 10}]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testAggregateJoinRemove10"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno, COUNT(DISTINCT d1.name, d2.name) +FROM sales.emp e +LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno +LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno +GROUP BY e.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalAggregate(group=[{0}], EXPR$1=[COUNT(DISTINCT $1, $2)]) + LogicalProject(DEPTNO=[$7], NAME=[$10], NAME0=[$12]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{7}], EXPR$1=[COUNT(DISTINCT $10, $12)]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove1"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno, d2.deptno +FROM sales.emp e +LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno +LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$7], DEPTNO0=[$11]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$7], DEPTNO0=[$9]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove2"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno, d1.deptno +FROM sales.emp e +LEFT JOIN sales.dept d1 ON e.deptno = d1.deptno +LEFT JOIN sales.dept d2 ON e.deptno = d2.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$7], DEPTNO0=[$9]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$7], DEPTNO0=[$9]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove3"> + <Resource name="sql"> + <![CDATA[SELECT e1.deptno, d.deptno +FROM sales.emp e1 +LEFT JOIN sales.emp e2 ON e1.deptno = e2.deptno +LEFT JOIN sales.dept d ON e1.deptno = d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$7], DEPTNO0=[$18]) + LogicalJoin(condition=[=($7, $18)], joinType=[left]) + LogicalJoin(condition=[=($7, $16)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$7], DEPTNO0=[$18]) + LogicalJoin(condition=[=($7, $18)], joinType=[left]) + LogicalJoin(condition=[=($7, $16)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove4"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno +FROM sales.emp e +LEFT JOIN sales.dept d ON e.deptno = d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$7]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove5"> + <Resource name="sql"> + <![CDATA[SELECT e1.deptno +FROM sales.emp e1 +LEFT JOIN sales.emp e2 ON e1.deptno = e2.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$7]) + LogicalJoin(condition=[=($7, $16)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$7]) + LogicalJoin(condition=[=($7, $16)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove6"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno, d.name +FROM sales.emp e +LEFT JOIN sales.dept d ON e.deptno = d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$7], NAME=[$10]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$7], NAME=[$10]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove7"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno +FROM sales.dept +LEFT JOIN sales.emp e ON e.deptno = d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$9]) + LogicalJoin(condition=[=($9, $0)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove8"> + <Resource name="sql"> + <![CDATA[SELECT e2.deptno +FROM sales.emp e1 +LEFT JOIN sales.emp e2 ON e1.deptno = e2.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$16]) + LogicalJoin(condition=[=($7, $16)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$16]) + LogicalJoin(condition=[=($7, $16)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testProjectJoinRemove9"> + <Resource name="sql"> + <![CDATA[SELECT e.deptno, d.name +FROM sales.dept d +RIGHT JOIN sales.emp e ON e.deptno = d.deptno +]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(DEPTNO=[$9], NAME=[$1]) + LogicalJoin(condition=[=($9, $0)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$9], NAME=[$1]) + LogicalJoin(condition=[=($9, $0)], joinType=[right]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testReduceNullableCase2"> <Resource name="sql"> <![CDATA[SELECT deptno, ename, CASE WHEN 1=2 THEN substring(ename, 1, cast(2 as int)) ELSE NULL end from emp group by deptno, ename, case when 1=2 then substring(ename,1, cast(2 as int)) else null end]]>