[ https://issues.apache.org/jira/browse/CALCITE-5940?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jiajun Xie resolved CALCITE-5940. --------------------------------- Resolution: Fixed > Add the Rule to merge Limit > --------------------------- > > Key: CALCITE-5940 > URL: https://issues.apache.org/jira/browse/CALCITE-5940 > Project: Calcite > Issue Type: New Feature > Reporter: LakeShen > Assignee: LakeShen > Priority: Major > Labels: pull-request-available > Fix For: 1.36.0 > > > 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: > {code:java} > select * from nation limit 0 {code} > 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|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/EvaluateZeroLimit.java#L28C1-L28C31] > The sql: > {code:java} > select concat('-',N_REGIONKEY) from (select * from nation limit 10000) limit > 10 {code} > It would be optimized by > [MergeLimits|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26] > rule to: > {code:java} > select concat('-',N_REGIONKEY) from nation limit 10 {code} > The value of limit takes the minimum of the outer limit and the inner limit. > The sql: > {code:java} > select concat('-',N_REGIONKEY) from (SELECT * FROM nation order BY > N_REGIONKEY DESC LIMIT 10000) limit 10 {code} > It would be optimized by > [MergeLimitWithTopN|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimitWithTopN.java#L28C1-L28C31] > rule to: > {code:java} > SELECT concat('-',N_REGIONKEY) FROM nation order BY N_REGIONKEY DESC LIMIT > 10{code} > So I propose to add these three rules to Calcite as well, to optimize the > Limit case. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)