[
https://issues.apache.org/jira/browse/CALCITE-6038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17774309#comment-17774309
]
LakeShen edited comment on CALCITE-6038 at 10/12/23 5:43 AM:
-------------------------------------------------------------
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 for {-}CALCITE-5994{-}.
was (Author: shenlang):
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 'Order By x Limit n' when the input is guaranteed that is at most one
> row
> ---------------------------------------------------------------------------------
>
> Key: CALCITE-6038
> URL: https://issues.apache.org/jira/browse/CALCITE-6038
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: LakeShen
> Assignee: LakeShen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> When a Sort represents 'Order By x Limit n',and its input is guaranteed that
> is at most one row,we could remove this Sort.
> 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 JIRA is enhancement for {-}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 message was sent by Atlassian Jira
(v8.20.10#820010)
