[
https://issues.apache.org/jira/browse/CALCITE-2202?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16391279#comment-16391279
]
Zhong Yu commented on CALCITE-2202:
-----------------------------------
Thanks Julian. Your last example actually works in my favor. My argument is
that the original query is equivalent to the following two, where aggregate is
pushed down on only one side
{code:java}
select sum(e.s * d.c)
from (select deptno, (sal) as s from emp) as e
join (select deptno, count(*) as c from dept group by deptno) as d
on e.deptno = d.deptno
group by e.deptno
select sum(e.s * d.c)
from (select deptno, sum(sal) as s from emp group by deptno) as e
join (select deptno, (1) as c from dept) as d
on e.deptno = d.deptno
group by e.deptno{code}
The "cross-multiplier" effect is still there because the join multiplies the
side that doesn't do aggregate.
So, I'm probably on the right track. However the paper I wrote is full of
holes, obviously done by an amateur:) Please ignore it. I'll try to write up a
new one in the weekend. Your reminder of vacuous cases, and null handling
(different in group-by and equijoin) are all good points. And I'll focus on a
narrower proof that works only on inner equijoin.
> Aggregate Join Push-down on a Single Side
> -----------------------------------------
>
> Key: CALCITE-2202
> URL: https://issues.apache.org/jira/browse/CALCITE-2202
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: next
> Reporter: Zhong Yu
> Assignee: Julian Hyde
> Priority: Major
> Fix For: next
>
>
> While investigating https://issues.apache.org/jira/browse/CALCITE-2195, it's
> apparent that aggregation can be pushed on on a single side (either side),
> and leave the other side non-aggregated, regardless of whether grouping
> columns are unique on the other side. My analysis –
> [http://zhong-j-yu.github.io/aggregate-join-push-down.pdf] .
> This may be useful when the metadata is insufficient; in any case, we may try
> to provide all 3 possible transformations (aggregate on left only; right
> only; both sides) to the cost based optimizer, so that the cheapest one can
> be chosen based on stats.
> Does this make any sense, anybody? If it sounds good, I'll implement it and
> offer a PR.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
