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>