MIME-Version: 1.0
References: <17502-281a7aaacfaa872a@postgresql.org>
 <CABUevEz7t3OWxaSSVSHOxa5KcYioOk=yRwoL3iMViyZQ8m2wAw@mail.gmail.com>
 <CAGckUK2GLF=d9J5ErEWgK5x8ECqCw4equnq3jEzrqtfJw+iHYw@mail.gmail.com>
 <CAApHDvoR4BXm7oxSrYhpEAFOZ-qMPkkYnn14y6sxtPf=5w5OOw@mail.gmail.com>
 <CAApHDvqKO00nNQtchBs65VTfjEMUsYiB5r2P4VUreTdXE9RY1g@mail.gmail.com>
 <CAMbWs48yS=f8tj6zstiMd6yfV3U4YpssyB55PXqjcLUyUA5bww@mail.gmail.com>
 <965990.1675119213@sss.pgh.pa.us>
In-Reply-To: <965990.1675119213@sss.pgh.pa.us>
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 31 Jan 2023 15:01:04 +0800
Message-ID: 
 <CAMbWs48sQSaMhe_ejb=yA+GEpS41X=wWO++zjNOjSnxdSP3q+w@mail.gmail.com>
Subject: Re: BUG #17502: View based on window functions returns wrong results
 when queried
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <dgrowleyml@gmail.com>,
 =?UTF-8?Q?Daniel_Farka=C5=A1?= <daniel.farkas@datoris.com>,
	Magnus Hagander <magnus@hagander.net>,
	PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000dbed3e05f389e51b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMbWs48sQSaMhe_ejb%3DyA%2BGEpS41X%3DwWO%2B%2BzjNOjSnxdSP3q%2Bw%40mail.gmail.com>
Precedence: bulk

--000000000000dbed3e05f389e51b
Content-Type: text/plain; charset="UTF-8"

On Tue, Jan 31, 2023 at 6:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> This bug seems to have slipped off the radar screen, but it's still
> a bug.  I continue to believe that the best fix is to disallow SRFs
> in window definitions, and present the trivial patch to do so.


This patch fixes the original issue reported by Daniel.  Another issue
discussed in this thread is the weirdness when there are SRFs in the
targetlist of a query with DISTINCT ON clause, as described by David.

create table ab (a int, b int);
insert into ab values(1,1),(1,2),(2,1),(2,2);

# select distinct on (a) a, b, generate_series(1,2) g from ab order by a, b;
 a | b | g
---+---+---
 1 | 1 | 1
 1 | 1 | 2
 2 | 1 | 1
 2 | 1 | 2
(4 rows)

# select distinct on (a) a, b, g from ab, lateral generate_series(1,2) as g
order by a, b;
 a | b | g
---+---+---
 1 | 1 | 1
 2 | 1 | 1
(2 rows)

According to the doc these two queries are supposed to be 'almost
exactly the same'.  So it's weird to see they give different number of
output rows.

Should we also fix this issue?  If so it seems we need some changes
about postponing SRFs in make_sort_input_target().

Thanks
Richard

--000000000000dbed3e05f389e51b
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 Tue, Jan 31, 2023 at 6:53 AM Tom Lane =
&lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:</=
div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex">
This bug seems to have slipped off the radar screen, but it&#39;s still<br>
a bug.=C2=A0 I continue to believe that the best fix is to disallow SRFs<br=
>
in window definitions, and present the trivial patch to do so.</blockquote>=
<div>=C2=A0</div><div>This patch fixes the original issue reported by Danie=
l.=C2=A0 Another issue<br>discussed in this thread is the weirdness when th=
ere are SRFs in the<br>targetlist of a query with DISTINCT ON clause, as de=
scribed by David.<br><br>create table ab (a int, b int);<br>insert into ab =
values(1,1),(1,2),(2,1),(2,2);<br><br># select distinct on (a) a, b, genera=
te_series(1,2) g from ab order by a, b;<br>=C2=A0a | b | g<br>---+---+---<b=
r>=C2=A01 | 1 | 1<br>=C2=A01 | 1 | 2<br>=C2=A02 | 1 | 1<br>=C2=A02 | 1 | 2<=
br>(4 rows)<br><br># select distinct on (a) a, b, g from ab, lateral genera=
te_series(1,2) as g order by a, b;<br>=C2=A0a | b | g<br>---+---+---<br>=C2=
=A01 | 1 | 1<br>=C2=A02 | 1 | 1<br>(2 rows)<br><br>According to the doc the=
se two queries are supposed to be &#39;almost<br>exactly the same&#39;.=C2=
=A0 So it&#39;s weird to see they give different number of<br>output rows.<=
br><br>Should we also fix this issue?=C2=A0 If so it seems we need some cha=
nges<br>about postponing SRFs in make_sort_input_target().<br><br>Thanks<br=
>Richard<br></div></div></div>

--000000000000dbed3e05f389e51b--