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]]>
