[CALCITE-5940] Add the Rule to merge Limit

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.36.0
• Labels: pull-request-available
• Assignee: LakeShen
• Reporter: LakeShen
• Created: 20/Aug/23 02:30
• Updated: 10/Nov/23 11:34
• Resolved: 27/Sep/23 10:43

Description

Now in calcite,the Limit will be represented using LogicalSort(fetch=[xx]),but there are few rules to optimize Limit.

In trino and presto,there are many optimization rules to optimize Limit.

For example,the sql:

select * from nation limit 0 

The limit 0 will use empty ValuesNode(Calcite LogicalValues) to optimize,so the SQL is not delivered to the Worker compute,the rule could see: EvaluateZeroLimit

The sql:

select concat('-',N_REGIONKEY) from (select * from nation limit 10000) limit 10 

It would be optimized by MergeLimits rule to:

select concat('-',N_REGIONKEY) from nation limit 10  

The value of limit takes the minimum of the outer limit and the inner limit.

The sql:

select concat('-',N_REGIONKEY) from (SELECT * FROM nation order BY N_REGIONKEY DESC LIMIT 10000) limit 10 

It would be optimized by MergeLimitWithTopN rule to:

SELECT concat('-',N_REGIONKEY) FROM nation order BY N_REGIONKEY DESC LIMIT 10

So I propose to add these three rules to Calcite as well, to optimize the Limit case.

 
 

Issue Links

• relates to: CALCITE-2540 Streaming Sort relational operator
• 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 #3396