[
https://issues.apache.org/jira/browse/CALCITE-831?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14662831#comment-14662831
]
Jinfeng Ni commented on CALCITE-831:
------------------------------------
In both of your examples, we are talking about push down the limit into "emp"
table, right?
For left join, sounds to me that the uniqueness requirement of deptno is not
necessary. Whether there is a matching "deptno" from dept table, a row from
emp will always appear in the join output. Therefore, it would be safe to push
down the limit to "emp" table.
{code}
select * from emp left join (select * from dept where state = 'CA') using
(deptno) order by sal limit 10
{code}
In your first example which uses inner join, primary-foreign relationship is a
requirement if we want to push down the limit into "emp" table, since the PK
relationship guarantee for each emp.deptno, there is a matching deptno from
"dept" table. Without the PK constraint, we could not ensure that the 10 rows
from "emp" will lead to 10 rows in the join output ; meaning if we do push down
the limit, we may end up with less than 10 rows in the join output.
{code}
select * from emp join dept using (deptno) order by sal limit 10
{code}
In both of the two examples, sounds we could not push down the limit to "dept"
table.
Does the above make sense?
> 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)
