Re: Bug? Function with side effects not evaluated in CTE

On 21/10/2013 20:40, Moshe Jacobson wrote:
> I don't think your example above is analogous, because in your
> example, you are asking /how many times/ to execute the function,
> whereas in my example, the question is /whether/ to execute the query
> at all.
> If the outer statement of the CTE doesn't need to use the contents of
> the CTE, and there is no volatile function in there, then I agree that
> it's fine not to execute it.

Surely the point is that "not executing at all" is just a particular
case of "executing N times" where N is calculated as zero?

Your original example was this:

|with tt_created as
(
select fn_new_item(1) as item
)|

That CTE has a maximum of 1 row, so the choices are only 0 or 1. But how
is it fundamentally different from this:

|with tt_created as
(
select fn_new_item(i) as item from generate_series(1, 10) as i
)|

This CTE has up to 10 rows, but the current optimization (as I
understand it) might mean that only 5 of those are calculated. It might
also decide that 0 of them are calculated, but that's not a
qualitatively different decision, it's just a different value for the
"how many rows do you need" parameter.

Personally, I'm definitely in the "this is surprising behaviour" camp,
although "surprising" and "wrong" aren't *necessarily* the same thing,
so I'll leave it to greater minds to decide on the latter...

--
Rowan Collins
[IMSoP]