On January 18, 2017 3:59:00 PM PST, Robert Haas <robertmh...@gmail.com> wrote: >On Wed, Jan 18, 2017 at 6:19 PM, Andres Freund <and...@anarazel.de> >wrote: >>> 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. > >So, one of the big reasons I use CASE is to avoid evaluating >expressions in cases where they might throw an ERROR. Like, you know: > >CASE WHEN d != 0 THEN n / d ELSE NULL END > >I guess it's not the end of the world if that only works for >non-set-returning functions, but it's something to think about.
That's already not reliable in a bunch of cases, particularly evaluation during planning... Not saying that's good, but it is. Andres -- Sent from my Android device with K-9 Mail. Please excuse my brevity. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
