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

On Tue, Jan 31, 2023 at 6:53 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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