Re: Changed SRF in targetlist handling

On Mon, May 23, 2016 at 4:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> We should consider single and multiple SRFs in a targetlist as distinct
> use-cases; only the latter has got weird properties.
>
> There are several things we could potentially do with multiple SRFs in
> the same targetlist. In increasing order of backwards compatibility and
> effort required:
>
> 1. Throw error if there's more than one SRF.
>
> 2. Rewrite into LATERAL ROWS FROM (srf1(), srf2(), ...). This would
> have the same behavior as before if the SRFs all return the same number
> of rows, and otherwise would behave differently.

I thought the idea was to rewrite it as LATERAL ROWS FROM (srf1()),
LATERAL ROWS FROM (srf2()), ...

The rewrite you propose here seems to NULL-pad rows after the first
SRF is exhausted:

rhaas=# select * from dual, lateral rows from (generate_series(1,3),
generate_series(1,4));
x | generate_series | generate_series
-------+-----------------+-----------------
dummy | 1 | 1
dummy | 2 | 2
dummy | 3 | 3
dummy | | 4
(4 rows)

...whereas with a separate LATERAL clause for each row you get this:

rhaas=# select * from dual, lateral rows from (generate_series(1,3))
a, lateral rows from (generate_series(1,4)) b;
x | a | b
-------+---+---
dummy | 1 | 1
dummy | 1 | 2
dummy | 1 | 3
dummy | 1 | 4
dummy | 2 | 1
dummy | 2 | 2
dummy | 2 | 3
dummy | 2 | 4
dummy | 3 | 1
dummy | 3 | 2
dummy | 3 | 3
dummy | 3 | 4
(12 rows)

The latter is how I'd expect SRF-in-targetlist to work.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company