Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling)

On 2017-01-18 18:14:26 -0500, Tom Lane wrote:
> I wrote:
> > I'll try to write something about the SRF-in-CASE issue too. Seeing
> > whether we can document that adequately seems like an important part
> > of making the decision about whether we need to block it.
>
> Here's what I came up with:
>
> This behavior also means that set-returning functions will be evaluated
> even when it might appear that they should be skipped because of a
> conditional-evaluation construct, such as CASE or COALESCE. For example,
> consider
>
> SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
>
> It might seem that this should produce five repetitions of input rows
> that have x > 0, and a single repetition of those that do not; but
> actually it will produce five repetitions of every input row. This is
> because generate_series() is run first, and then the CASE expression is
> applied to its result rows. The behavior is thus comparable to
>
> SELECT x, CASE WHEN x > 0 THEN g ELSE 0 END
> FROM tab, LATERAL generate_series(1,5) AS g;
>
> It would be exactly the same, except that in this specific example, the
> planner could choose to put g on the outside of the nestloop join, since
> g has no actual lateral dependency on tab. That would result in a
> different output row order. Set-returning functions in the select list
> are always evaluated as though they are on the inside of a nestloop join
> with the rest of the FROM clause, so that the function(s) are run to
> completion before the next row from the FROM clause is considered.
>
> So is this too ugly to live, or shall we put up with it?

I'm very tentatively in favor of living with it.

Greetings,

Andres Freund