[CALCITE-5994] Add optimization rule to remove Sort when its input's row number is less or equal to one

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: LakeShen
• Reporter: LakeShen
• Created: 10/Sep/23 09:58
• Updated: 10/Nov/23 11:34
• Resolved: 21/Sep/23 07:14

Description

When a Sort's input source max row numer is 1,then we could remove the redundant Sort,the Sort could be a sorted semantic Sort(offset and fetch is null).

For example,the sql:

select * from (select * from tableA limit 1)  order by c ;

because the `(select * from tableA limit 1) ` max row number is 1, then we could remove order by c

select * from tableA limit 1;

The sql:

select max(totalprice) from orders order by 1 

could converted to:

select max(totalprice) from orders

Above logic are same as Presto/Trino's RemoveRedundantSort rule:

 

Issue Links

• relates to: CALCITE-6038 Remove 'ORDER BY ... LIMIT n' when input has at most one row, n >= 1, and there is no 'OFFSET' clause
• links to: GitHub Pull Request #3418