[CALCITE-6038] Remove 'ORDER BY ... LIMIT n' when input has at most one row, n >= 1, and there is no 'OFFSET' clause

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: LakeShen
• Reporter: LakeShen
• Created: 06/Oct/23 11:55
• Updated: 14/Jan/24 02:09
• Resolved: 26/Oct/23 00:33

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:

SELECT count(*) FROM orders ORDER BY 1 LIMIT 10 

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:

SELECT count(*) FROM orders  

Above logic are same as Presto/Trino's RemoveRedundantTopN 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'.

Issue Links

• duplicates: CALCITE-3952 Improve SortRemoveRule to remove Sort based on rowcount
• is related to: CALCITE-5940 Add the Rule to merge Limit
• is related to: CALCITE-5994 Add optimization rule to remove Sort when its input's row number is less or equal to one
• links to: GitHub Pull Request #3467