On Tue, May 25, 2021 at 10:23 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> David Rowley <dgrowle...@gmail.com> writes: > > On Tue, 25 May 2021 at 22:28, Andy Fan <zhihui.fan1...@gmail.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/)
