[
https://issues.apache.org/jira/browse/CALCITE-6038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17772605#comment-17772605
]
LakeShen commented on CALCITE-6038:
-----------------------------------
Hi [~julianhyde] ,thanks for your reply.
Sorry,my description is not very clear,I have changed the description.
For the `select count(*) from t limit 0` case, I think that uses
`PruneEmptyRules#SORT_FETCH_ZERO_INSTANCE`could be better choice,and this rule
optimizes the plan earlier than TOPN's optimization,so handling empty
situations should be left to `PruneEmptyRules`.
In this jira,I would compare the maximum row count of the TOPN's input, and if
it is at most one,we could remove the redundant TOPN.In Trino or Presto,this
rule is called
[RemoveRedundantTopN.|https://github.com/prestodb/presto/blob/21ab1ea2425e4bc65532ab156c60333e5a72dd09/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/RemoveRedundantTopN.java#L27C1-L28C34]And
they also use `EvaluateZeroLimit` rule to deal with limit 0
situations,`EvaluateZeroLimit` appears earlier than `RemoveRedundantTopN,`
which means `EvaluateZeroLimit` will optimize the plan earlier, so in actuality
there will be no Limit 0 scenario in `RemoveRedundantTopN`
So my idea is to extend this optimization in Calcite,and this JIRA is only for
TOPN.
WDYT?
> Add optimization to remove redundant TopN when its input's row number is less
> or equal to one
> ---------------------------------------------------------------------------------------------
>
> Key: CALCITE-6038
> URL: https://issues.apache.org/jira/browse/CALCITE-6038
> Project: Calcite
> Issue Type: New Feature
> Reporter: LakeShen
> Priority: Major
>
> In Calcite , the TopN is represented by `Sort`,when a TopN's input source max
> row count is less or equal to 1,then we could remove the redundant TopN.
> For example,the sql:
> {code:java}
> SELECT count(*) FROM orders ORDER BY 1 LIMIT 10 {code}
> because the `SELECT count(*) FROM orders ` row count is 1, then we could
> remove `ORDER BY 1 LIMIT 10 `,after the optimization:
> {code:java}
> SELECT count(*) FROM orders {code}
> Above logic are same as Presto/Trino's
> [RemoveRedundantTopN|https://github.com/prestodb/presto/blob/21ab1ea2425e4bc65532ab156c60333e5a72dd09/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/RemoveRedundantTopN.java#L27C1-L28C34]
> rule:
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
