From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: luy70@psu.edu
Subject: BUG #17150: Unexpected outputs from the query
Date: Tue, 17 Aug 2021 16:30:10 +0000
Message-ID: <17150-d22e14860d93d32e@postgresql.org> (raw)
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".
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, luy70@psu.edu
Subject: Re: BUG #17150: Unexpected outputs from the query
In-Reply-To: <17150-d22e14860d93d32e@postgresql.org>
* 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