Thread: BUG #17150: Unexpected outputs from the query

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".

"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