[CALCITE-6009] Add optimization to remove redundant LIMIT that is more than input row count - ASF JIRA

XML

Word

Printable

JSON

In calcite,Limit would be represented by Sort,such as `LogicalSort[fetch = 5]`.

When the Limit' source row number is less than the Limit's fetch,we could remove the the redundant Limit.

For example:

SELECT * FROM (VALUES 1,2,3,4,5,6) AS t1 LIMIT 10

The plan tree is :

 LogicalSort(fetch=[10])
  LogicalProject(t1=[$0])
    LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }]])

Because the Limit's source max row number is 6,the Limit's fetch is 10,so we could remove the redundant Limit.

Another example is :

SELECT count(*) FROM orders LIMIT 2

The plan tree is :

 LogicalSort(fetch=[2])
  LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
    LogicalTableScan(table=[[tpch, ORDERS]])

Because Limit's source max row number is 1,the Limit's fetch is 2, so we could remove the redundant Limit.

duplicates

CALCITE-3952 Improve SortRemoveRule to remove Sort based on rowcount

links to

GitHub Pull Request #3449