Re: Division by zero

On Thu, Jun 04, 2009 at 10:34:31AM -0400, Tom Lane wrote:
> create function foo(int) returns int as
> $$ select coalesce($1, 42); $$ language sql strict;
>
> Because this function is declared STRICT, it must return null on null
> input. However, the contained expression would not act that way ---
> it would in fact return 42 for null input. Therefore inlining would
> change the behavior, and therefore we don't inline.

Bah, not sure what I was thinking--that's kind of obvious isn't it! I
think I was thinking about every language apart from SQL, but they can't
be inlined and hence it would never apply to them.

> The same sorts of considerations arise for marking the function as
> less volatile than the contained expression really is. In this case
> the "behavioral change" has to do with what later phases of the planner
> will think they can do with the function or expression. The bottom line
> is the same though: easier to leave off the marking.

Hum, I think that with 8.3 at least I'm going to carry on putting
IMMUTABLE on where I think it should be. Consider:

CREATE FUNCTION fn_i(INTEGER) RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE AS $$
SELECT $1 < 1000 $$;
CREATE FUNCTION fn_v(INTEGER) RETURNS BOOLEAN LANGUAGE SQL AS $$
SELECT $1 < 1000 $$;

and then doing:

SELECT * FROM (
SELECT fn_i(num), fn_v(num)
FROM bigtable) x
WHERE fn_i;

I get very different plans out if I replace "WHERE fn_i" with "WHERE
fn_v". I read this as it not inlining where I'd expect it to be, or am
I missing something else?

--
Sam http://samason.me.uk/