[
https://issues.apache.org/jira/browse/CALCITE-6038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17774309#comment-17774309
]
LakeShen commented on CALCITE-6038:
-----------------------------------
Hi [~julianhyde] ,thank you for reminding me.You are right, using 'TopN' is not
appropriate to describe this JIRA.
I think that 'TopN' is closer to the business layer,so it has multiple
understandings,such as 'Order By x Limit n', ' ROW_NUMBER() OVER .. + where
rownum <= N' and so on.
In this jira,I just want to represent 'Order By x Limit n',so using 'Order By x
Limit n' directly may be more accurate.I want to change the title and
description of this Jira for using 'Order By x Limit n'.
WDYT?
In CALCITE-6009, if a RelNode is pure Limit which means that is 'Limit N',and
it doesn't have the 'Offset' and 'Order By' semantics,it could be removed when
its input RelNode's max row count is less than or equal to the Limit's fetch.
In {-}CALCITE-5994{-}, if a RelNode is pure Order which means that is 'Order
By',and it doesn't have the 'Offset' and 'Limit' semantics,it could be removed
when its input RelNode's max row count is less than or equal to one.
This JIRA is more relevant to {-}CALCITE-5994{-}, in -CALCITE-5994- ,we
restrict Sort must be pure Order(Offset and Limit is null),after this jira,this
optimization could be applied to 'Order By x' Or 'Order By x Limit n',this JIRA
is an enhancement.
> Remove 'Limit n' when the input is guaranteed to never return more than 'n'
> rows
> --------------------------------------------------------------------------------
>
> Key: CALCITE-6038
> URL: https://issues.apache.org/jira/browse/CALCITE-6038
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Reporter: LakeShen
> Assignee: LakeShen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> In Calcite , the TopN is represented by `Sort`,when a TopN's input source row
> count is provably at most one row
> ,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, it is at most one
> row,then we could remove `ORDER BY 1 LIMIT 10 `,after the optimization,the
> sql is :
> {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)
