agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: daniel.farkas@datoris.com
Subject: BUG #17502: View based on window functions returns wrong results when queried
Date: Sat, 28 May 2022 22:54:18 +0000
Message-ID: <17502-281a7aaacfaa872a@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      17502
Logged by:          Daniel Farkaš
Email address:      daniel.farkas@datoris.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.

Daniel

view thread (18+ messages)  latest in thread

Message-ID: <17502-281a7aaacfaa872a@postgresql.org>
Permalink:  ../../17502-281a7aaacfaa872a@postgresql.org/
Also on:    postgresql.org/message-id/17502-281a7aaacfaa872a@postgresql.org

reply

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, daniel.farkas@datoris.com
  Subject: Re: BUG #17502: View based on window functions returns wrong results when queried
  In-Reply-To: <17502-281a7aaacfaa872a@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox