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

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]