public inbox for [email protected]  

help / color / mirror / Atom feed
From: Richard Guo <[email protected]>
To: Tom Lane <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Daniel Farkaš <[email protected]>
Cc: Magnus Hagander <[email protected]>
Cc: PostgreSQL mailing lists <[email protected]>
Subject: Re: BUG #17502: View based on window functions returns wrong results when queried
Date: Tue, 31 Jan 2023 15:01:04 +0800
Message-ID: <CAMbWs48sQSaMhe_ejb=yA+GEpS41X=wWO++zjNOjSnxdSP3q+w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<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>
	<[email protected]>

On Tue, Jan 31, 2023 at 6:53 AM Tom Lane <[email protected]> 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


view thread (18+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: BUG #17502: View based on window functions returns wrong results when queried
  In-Reply-To: <CAMbWs48sQSaMhe_ejb=yA+GEpS41X=wWO++zjNOjSnxdSP3q+w@mail.gmail.com>

* 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