Re: Too many function calls in view with LEFT JOIN

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

Andreas Heiduk <Andreas(dot)Heiduk(at)web(dot)de> writes:
> But as far as I can tell both queries should always return the same
> results. So I don't understand why the STRICT does not matter in the
> first query but is necessary in the second one. Especially because the
> JOIN criterium is not affected by the function call.

Because if the function's not strict, you don't get the right answer
after flattening the join.  If we postpone the function call until after
the join, then we have a query that looks like

	select x.f1, x.f2, ..., myfunc(y.f3), ... from x left join y ...

The LEFT JOIN operator will produce y.f3 = null in join rows that are
generated from unmatched x rows.  If myfunc is not strict, it could
produce a non-null result despite being fed a null argument, and then
you would see wrong results from the SELECT: a column that ought to be
null is not.

The planner knows that it can postpone evaluation of strict functions in
this sort of context, but it won't risk it for non-strict.  This goes
back to this bug report:
	http://archives.postgresql.org/pgsql-bugs/2001-04/msg00223.php
which is exactly parallel to your query if you imagine a constant as
being like a function of no arguments.

I have some thoughts about changing this, but it's a major planner
re-engineering project not a bug fix.  Don't hold your breath.

			regards, tom lane