Re: BUG #17502: View based on window functions returns wrong results when queried

On Sun, May 29, 2022 at 4:20 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17502
> Logged by: Daniel Farkaš
> Email address: daniel(dot)farkas(at)datoris(dot)com
> PostgreSQL version: 10.10
> Operating system: Linux
> Description:
>
> Hey,
>
> Please be gentle, I've never been in contact with Postgres developers.
> In short, I've created a view, which has rather sketchy window functions,
> but it gives me results I need.
> When I do select * on it, it gives me what I expect. One of the columns has
> five distinct values.
> But when I do group by on that column, it gives me only one of the values.
> When I drop the view and create materialized view, all is good, I get all
> five values.
>
> My guess is that some parts of the inner select are affecting outer, view's
> select, which is not something I would expect.
> My current Postgres is PostgreSQL 10.10 on x86_64-pc-linux-musl, compiled
> by
> gcc (Alpine 8.3.0) 8.3.0, 64-bit.
> If you think this is worth investigating further, I will try composing a
> simpler example, and test it in a more recent Postgres version.
> Maybe it's a known limitation I'm not aware of.
>
> Let me know what you think.
>
>
Please see if you can reproduce this on a current version of PostgreSQL 10,
which is 10.21. Version 10.10 is lacking more than two and a half years
worth of bugfixes.

If you can then yes, try to put together a simpler example, because it
certainly does not sound like correct behavior.

//Magnus