From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "luy70(at)psu(dot)edu" <luy70(at)psu(dot)edu>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BUG #17150: Unexpected outputs from the query |
Date: | 2021-08-17 18:56:43 |
Message-ID: | 1369911.1629226603@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Tuesday, August 17, 2021, PG Bug reporting form <noreply(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Liang Sr., Yu | 2021-08-17 19:08:55 | Re: BUG #17150: Unexpected outputs from the query |
Previous Message | David G. Johnston | 2021-08-17 18:27:10 | Re: BUG #17150: Unexpected outputs from the query |