This is an automated email from the ASF dual-hosted git repository.
rubenql pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 1775baf218 [CALCITE-5994] Add optimization rule to remove Sort when
its input's row number is less or equal to one
1775baf218 is described below
commit 1775baf2184bc2d10b4991a2dea7e60c474bb5fc
Author: shenlang <shenl...@zbyte-inc.com>
AuthorDate: Mon Sep 11 20:34:51 2023 +0800
[CALCITE-5994] Add optimization rule to remove Sort when its input's row
number is less or equal to one
---
.../org/apache/calcite/rel/rules/CoreRules.java | 5 ++
.../calcite/rel/rules/SortRemoveRedundantRule.java | 77 ++++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 27 ++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 43 ++++++++++++
4 files changed, 152 insertions(+)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index 61ce78587b..50411d2462 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -721,6 +721,11 @@ public class CoreRules {
public static final SortRemoveConstantKeysRule SORT_REMOVE_CONSTANT_KEYS =
SortRemoveConstantKeysRule.Config.DEFAULT.toRule();
+ /** Rule that removes redundant {@link Sort} if its input max row number
+ * is less than or equal to one. */
+ public static final SortRemoveRedundantRule SORT_REMOVE_REDUNDANT =
+ SortRemoveRedundantRule.Config.DEFAULT.toRule();
+
/** Rule that pushes a {@link Sort} past a {@link Join}. */
public static final SortJoinTransposeRule SORT_JOIN_TRANSPOSE =
SortJoinTransposeRule.Config.DEFAULT.toRule();
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SortRemoveRedundantRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SortRemoveRedundantRule.java
new file mode 100644
index 0000000000..68a449b43d
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/rel/rules/SortRemoveRedundantRule.java
@@ -0,0 +1,77 @@
+/*
+ * 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.RelOptRuleCall;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.rel.core.Sort;
+
+import org.immutables.value.Value;
+
+/**
+ * Planner rule that removes
+ * the redundant {@link org.apache.calcite.rel.core.Sort} if its input
+ * max row number is less than or equal to one.
+ *
+ * <p> For example:
+ * <blockquote><pre>{@code
+ * select max(totalprice) from orders order by 1}
+ * </pre></blockquote>
+ *
+ * <p> could be converted to
+ * <blockquote><pre>{@code
+ * select max(totalprice) from orders}
+ * </pre></blockquote>
+ *
+ * @see CoreRules#SORT_REMOVE_REDUNDANT
+ */
+@Value.Enclosing
+public class SortRemoveRedundantRule
+ extends RelRule<SortRemoveRedundantRule.Config>
+ implements TransformationRule {
+ protected SortRemoveRedundantRule(final SortRemoveRedundantRule.Config
config) {
+ super(config);
+ }
+
+ @Override public void onMatch(final RelOptRuleCall call) {
+ final Sort sort = call.rel(0);
+ if (sort.offset != null || sort.fetch != null) {
+ // Don't remove sort if it has explicit OFFSET and LIMIT
+ return;
+ }
+
+ // Get the max row count for sort's input RelNode.
+ final Double maxRowCount =
call.getMetadataQuery().getMaxRowCount(sort.getInput());
+ // If the max row count is not null and less than or equal to 1,
+ // then we could remove the sort.
+ if (maxRowCount != null && maxRowCount <= 1D) {
+ call.transformTo(sort.getInput());
+ }
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = ImmutableSortRemoveRedundantRule.Config.of()
+ .withOperandSupplier(b ->
+ b.operand(Sort.class).anyInputs());
+
+ @Override default SortRemoveRedundantRule toRule() {
+ return new SortRemoveRedundantRule(this);
+ }
+ }
+}
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 2a7677f165..eb46822eb3 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -1214,6 +1214,33 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5994";>[CALCITE-5994]
+ * Add optimization rule to remove Sort when its input's row number
+ * is less or equal to one</a>. */
+ @Test void testSortRemoveWhenAggregateMaxRowCntIsOne() {
+ final String sql = "select count(*) as c\n"
+ + "from sales.emp order by c";
+ sql(sql)
+ .withRule(CoreRules.SORT_REMOVE_REDUNDANT)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5994";>[CALCITE-5994]
+ * Add optimization rule to remove Sort when its input's row number
+ * is less or equal to one</a>. */
+ @Test void testSortRemoveWhenLimitMaxRowCntIsOne() {
+ final String sql = "select *\n"
+ + "from (select * from sales.emp limit 1)\n"
+ + "order by deptno";
+ sql(sql)
+ .withRule(CoreRules.SORT_REMOVE_REDUNDANT,
+ CoreRules.SORT_PROJECT_TRANSPOSE,
+ CoreRules.PROJECT_REMOVE)
+ .check();
+ }
+
/** Tests that an {@link EnumerableLimit} and {@link EnumerableSort} are
* replaced by an {@link EnumerableLimitSort}, per
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3920";>[CALCITE-3920]
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 11f92bddd2..c62309fbc3 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -13913,6 +13913,49 @@ LogicalProject(C=[$0])
LogicalProject(DEPTNO=[$7], SAL=[$5])
LogicalFilter(condition=[=($7, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortRemoveWhenAggregateMaxRowCntIsOne">
+ <Resource name="sql">
+ <![CDATA[select count(*) as c
+from sales.emp order by c]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], dir0=[ASC])
+ LogicalAggregate(group=[{}], C=[COUNT()])
+ LogicalProject($f0=[0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalAggregate(group=[{}], C=[COUNT()])
+ LogicalProject($f0=[0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortRemoveWhenLimitMaxRowCntIsOne">
+ <Resource name="sql">
+ <![CDATA[select *
+from (select * from sales.emp limit 1)
+order by deptno]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$7], dir0=[ASC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalSort(fetch=[1])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalSort(fetch=[1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
