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

On Mon, May 30, 2022 at 9:12 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> The following is also pretty strange. Why should adding the SRF column
> to the ORDER BY change the number of output rows?
>
> postgres=# select distinct on (a) a,b, generate_Series(1,2) from ab
> order by a,b,3 desc;
> a | b | generate_series
> ---+---+-----------------
> 1 | 1 | 2
> 2 | 1 | 2
> (2 rows)
>
>
> postgres=# select distinct on (a) a,b, generate_Series(1,2) from ab
> order by a,b;
> a | b | generate_series
> ---+---+-----------------
> 1 | 1 | 1
> 1 | 1 | 2
> 2 | 1 | 1
> 2 | 1 | 2
> (4 rows)
>

This is indeed weird. This seems depends on at which plan level we add
ProjectSetPath. For the first query we insert ProjectSetPath at the
scan/join level, because the SRF is included in the ORDER BY, which
makes the SRF appear in the scan/join target. For the second query the
SRF is postponed to after the Sort.

Is this a bug we should fix?

Thanks
Richard