MIME-Version: 1.0
References: <17502-281a7aaacfaa872a@postgresql.org>
In-Reply-To: <17502-281a7aaacfaa872a@postgresql.org>
From: Magnus Hagander <magnus@hagander.net>
Date: Sun, 29 May 2022 16:21:54 +0200
Message-ID: 
 <CABUevEz7t3OWxaSSVSHOxa5KcYioOk=yRwoL3iMViyZQ8m2wAw@mail.gmail.com>
Subject: Re: BUG #17502: View based on window functions returns wrong results
 when queried
To: daniel.farkas@datoris.com, pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a24cbb05e02743d1"
Archived-At: 
 <https://www.postgresql.org/message-id/CABUevEz7t3OWxaSSVSHOxa5KcYioOk%3DyRwoL3iMViyZQ8m2wAw%40mail.gmail.com>
Precedence: bulk

--000000000000a24cbb05e02743d1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

> The following bug has been logged on the website:
>
> Bug reference:      17502
> Logged by:          Daniel Farka=C5=A1
> 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 h=
as
> 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

--000000000000a24cbb05e02743d1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Sun, May 29, 2022 at 4:20 PM PG Bug re=
porting form &lt;<a href=3D"mailto:noreply@postgresql.org">noreply@postgres=
ql.org</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex">The following bug has been logged on the website:<br>
<br>
Bug reference:=C2=A0 =C2=A0 =C2=A0 17502<br>
Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Daniel Farka=C5=A1<br>
Email address:=C2=A0 =C2=A0 =C2=A0 <a href=3D"mailto:daniel.farkas@datoris.=
com" target=3D"_blank">daniel.farkas@datoris.com</a><br>
PostgreSQL version: 10.10<br>
Operating system:=C2=A0 =C2=A0Linux<br>
Description:=C2=A0 =C2=A0 =C2=A0 =C2=A0 <br>
<br>
Hey,<br>
<br>
Please be gentle, I&#39;ve never been in contact with Postgres developers.<=
br>
In short, I&#39;ve created a view, which has rather sketchy window function=
s,<br>
but it gives me results I need.<br>
When I do select * on it, it gives me what I expect. One of the columns has=
<br>
five distinct values.<br>
But when I do group by on that column, it gives me only one of the values.<=
br>
When I drop the view and create materialized view, all is good, I get all<b=
r>
five values.<br>
<br>
My guess is that some parts of the inner select are affecting outer, view&#=
39;s<br>
select, which is not something I would expect.<br>
My current Postgres is PostgreSQL 10.10 on x86_64-pc-linux-musl, compiled b=
y<br>
gcc (Alpine 8.3.0) 8.3.0, 64-bit.<br>
If you think this is worth investigating further, I will try composing a<br=
>
simpler example, and test it in a more recent Postgres version.<br>
Maybe it&#39;s a known limitation I&#39;m not aware of.<br>
<br>
Let me know what you think.<br><br></blockquote><div><br></div><div>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.</div><div><br></div><div>If you can then yes, try to put together=
 a simpler example, because it certainly does not sound like correct behavi=
or.</div><div><br></div><div>//Magnus</div><div><br></div></div><div dir=3D=
"ltr" class=3D"gmail_signature"><div dir=3D"ltr"></div></div></div>

--000000000000a24cbb05e02743d1--