Affects Version/s: None
Fix Version/s: None
Add rules to push down limits, based on a conversation with Wei Xue.
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".
- SortProjectTransposeRule matches a Sort on a Project, already exists, and already handles offset and fetch. (DONE)
- 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.
- 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?