Re: Division by zero

Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> 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?

Huh, interesting example. I don't have time to trace through it right
now, but I think what is happening is that the decision about whether or
not the sub-select can be flattened is being made before the inlining of
the SQL functions in the sub-select happens. So at that point the
sub-select qualifier expression still looks volatile and the planner
chickens out of flattening it. The functions do both get inline'd
eventually, as you can see in EXPLAIN VERBOSE output ... but it's too
late to make any real difference in the plan shape.

So yeah, there are corner cases where it's useful to have the function
marked correctly rather than sloppily.

regards, tom lane