Re: How can the Aggregation move to the outer query

On Tue, May 25, 2021 at 10:23 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > On Tue, 25 May 2021 at 22:28, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> >> explain (costs off) select (select count(*) filter (where t2.b = 1)
> from m1 t1)
> >> from m1 t2 where t2.b % 2 = 1;
> >>
> >> This one is too confusing to me since the Aggregate happens
> >> on t2 rather than t1. What happens here? Would this query
> >> generate 1 row all the time like SELECT aggfunc(a) FROM t?
>
> > I think you're misreading the plan. There's a scan on t2 with a
> > subplan then an aggregate on top of that. Because you made the
> > subquery correlated by adding t2.b, it cannot be executed as an
> > initplan.
>
> Also keep in mind that adding that filter clause completely changed
> the meaning of the aggregate. Aggregates belong to the lowest
> query level containing any Var used in their arguments, so that
> where in your original query the count(*) was an aggregate of the
> subquery, now it's an aggregate of the outer query (and the subquery
> now perceives it as a constant outer reference). AFAIR this is per
> SQL spec.
>

Well, finally I know it's an aggregate of the outer query.. Thank you for
the explanation! so I would say the result set has 1 row for that query
all the time.

--
Best Regards
Andy Fan (https://www.aliyun.com/)