[
https://issues.apache.org/jira/browse/CALCITE-831?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14662463#comment-14662463
]
Jinfeng Ni commented on CALCITE-831:
------------------------------------
For SortJoinTransposeRule, by "the join to dept is just a 'lookup'", do you
mean join of 'emp' and 'dept' is using the primary-foreign key relationship
w.r.t deptno? Otherwise, I could not see how we can safely push limit 10 down
to table 'emp'.
If that's the case, my question is : does Calcite today have a way to expose
the primary-foreign key metadata to optimizer?
> 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)
