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@gmail.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