Thank you both for the in-depth explanations! It is very interesting to know that the aggregated query is returning 1 row, which resulted in the output.
Best
Yu
From: Tom Lane <tgl@sss.pgh.pa.us> Date: Tuesday, August 17, 2021 at 2:56 PM To: David G. Johnston <david.g.johnston@gmail.com> Cc: Liang Sr., Yu <luy70@psu.edu>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org> Subject: Re: BUG #17150: Unexpected outputs from the query
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Tuesday, August 17, 2021, PG Bug reporting form <noreply@postgresql.org> > wrote: >> This >> unexpected return can be fixed by removing "ORDER BY ( SELECT COUNT ( v1 ) >> )", then the query returns sum="0" as expected.
> Well, PostgreSQL cannot remove the order by otherwise it would be a > different query. So your suggestion is spot on, and the user should > probably do that, but it doesn’t seem like a bug.
Yeah. PG interprets
SELECT x FROM v2 ORDER BY (SELECT COUNT(v1))
to behave the same as
SELECT x, (SELECT COUNT(v1)) FROM v2 ORDER BY 2
(modulo the fact that the ORDER BY column won't be output), and then it turns out that that's effectively the same as
SELECT x, COUNT(v1) FROM v2 ORDER BY 2
the reason being that since v1 is a variable of the outer query, the aggregate is considered to be an aggregate of the outer query *not* the sub-select. (That's required by the SQL standard.) So at this point you have an aggregated query that is certain to return 1 row, not more or less, regardless of how many rows are returned by v2.
