From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: luy70@psu.edu <luy70@psu.edu>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17150: Unexpected outputs from the query
Date: Tue, 17 Aug 2021 14:56:43 -0400
Message-ID: <1369911.1629226603@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwaxSxw+T3dmyOoNLW=zrL5yPz3_ecaVMdVyihYCTMa9rw@mail.gmail.com>
References: <17150-d22e14860d93d32e@postgresql.org>
<CAKFQuwaxSxw+T3dmyOoNLW=zrL5yPz3_ecaVMdVyihYCTMa9rw@mail.gmail.com>
"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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, luy70@psu.edu, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17150: Unexpected outputs from the query
In-Reply-To: <1369911.1629226603@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox