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
skippedbecause 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
thosethat do not; but actually it will produce five repetitions of every input row. This is because generate_series()
isrun 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
ofthe 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
joinwith the rest of the FROM clause, so that the function(s) are run to completion before the next row from the FROM
clauseis considered.
So is this too ugly to live, or shall we put up with it?
regards, tom lane
