pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17150: Unexpected outputs from the query
4+ messages / 4 participants
[nested] [flat]
* BUG #17150: Unexpected outputs from the query
@ 2021-08-17 16:30 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2021-08-17 16:30 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: luy70@psu.edu
The following bug has been logged on the website:
Bug reference: 17150
Logged by: Yu Liang
Email address: luy70@psu.edu
PostgreSQL version: 14beta3
Operating system: Ubuntu 20.04 LTS
Description:
For query
```SQL
CREATE TABLE v0 ( v1 INT );
CREATE VIEW v2 AS SELECT * FROM v0 WHERE v1 = 0;
SELECT SUM(result) FROM ( SELECT ALL( true )::INT as result FROM v2 ORDER BY
( SELECT COUNT ( v1 ) ) ) as res;
-- Expected sum = "0", returns sum = "1"
```
Detailed outputs:
```
1: sum (typeid = 20, len = 8, typmod = -1, byval = t)
----
1: sum = "1" (typeid = 20, len = 8, typmod = -1, byval = t)
----
```
In the query above, given TABLE v0 has no data inserted, and v2 is a view
that observes on v0, we expect the SELECT statement that tries to sum up the
entries of v2 would output sum = "0". However, sum = "1" is returned. This
unexpected return can be fixed by removing "ORDER BY ( SELECT COUNT ( v1 )
)", then the query returns sum="0" as expected.
This behavior can be reproduced with an empty database, using Postgres
single mode. Postgres version = "PostgreSQL 14beta3 on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit".
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17150: Unexpected outputs from the query
@ 2021-08-17 18:27 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2021-08-17 18:27 UTC (permalink / raw)
To: luy70@psu.edu <luy70@psu.edu>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17150: Unexpected outputs from the query
@ 2021-08-17 18:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-08-17 18:56 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: luy70@psu.edu <luy70@psu.edu>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17150: Unexpected outputs from the query
@ 2021-08-17 19:08 Liang Sr., Yu <luy70@psu.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Liang Sr., Yu @ 2021-08-17 19:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-08-17 19:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-17 16:30 BUG #17150: Unexpected outputs from the query PG Bug reporting form <noreply@postgresql.org>
2021-08-17 18:27 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-17 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-08-17 19:08 ` Liang Sr., Yu <luy70@psu.edu>
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