[
https://issues.apache.org/jira/browse/CALCITE-6038?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6038:
---------------------------------
Description:
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 5994, we restrict Sort must be
pure Order(Offset and Limit is null), but after this jira, this optimization
could be applied to 'Order By x' Or 'Order By x Limit n'.
was:
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'.
> Remove 'ORDER BY ... LIMIT n' when input has at most one row, n >= 1, and
> there is no 'OFFSET' clause
> -----------------------------------------------------------------------------------------------------
>
> 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 5994, we restrict Sort must be
> pure Order(Offset and Limit is null), but 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)
