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