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.
As "SELECT 1" returns a single row (even though there isn't even a from clause) the fact that "select count(*)" returns a row with a count of 1 isn't surprising - it always returns at least one row. So the order by expression adds a row to the output. This may be surprising but is also a natural consequence of allowing pretty much any desired expression to appear in the order by clause.
David J.