[jira] [Created] (CALCITE-831) Rules to push down limits

["Julian Hyde (JIRA)" <ji...@apache.org>]

Julian Hyde created CALCITE-831:
-----------------------------------

             Summary: Rules to push down limits
                 Key: CALCITE-831
                 URL: https://issues.apache.org/jira/browse/CALCITE-831
             Project: Calcite
          Issue Type: Bug
            Reporter: Julian Hyde
            Assignee: Julian Hyde


Add rules to push down limits, based on a conversation with [~maryannxue].

Recall that the SQL LIMIT clause becomes a Sort relational expression; the Sort.fetch attribute specifies the limit, or is null for no limit; the Sort has zero or more collations, corresponding to the expressions in the ORDER BY; there may also be a Sort.offset attribute.

A "naked limit" is a Sort with 0 sort columns and a not-null fetch clause, e.g. "LIMIT 10" without "ORDER BY".

Cases:
* SortProjectTransposeRule matches a Sort on a Project, already exists, and already handles offset and fetch.
* SortSortMergeRule (proposed) combines two Sort expressions. Among other cases, it handles a naked limit followed by a naked limit, and a sort followed by a naked limit.
* SortUnionTransposeRule pushes a Sort through a Union in some cases. It can push a naked limit through a union all, but needs to keep a limit after the union.
* SortJoinTransposeRule pushes a Sort through a Join in some cases. You could push 'select * from emp join dept using (deptno) order by sal limit 10' because the join to dept is just a 'lookup' and has no filtering effect.
* SortAggregateMergeRule could, if the limit applied to a measure such as sum or count, create a "topN" aggregate, for which there are known optimizations.

Non-cases:
* SortFilterTransposeRule would not be very useful. It is not safe to push a limit through a Filter. And you can perform a Sort (without limit) before a Filter but it is more effort, so why bother?



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)