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]
