Re: SQL question

Philip Warner <pjw@rhyme.com.au> writes:
> At 02:26 17/07/00 -0400, Tom Lane wrote:
>> Well before my time, I guess --- as long as I've been paying attention,
>> the function manager's approach was to call the routine first and *then*
>> insert a NULL result ... if the routine hadn't crashed first.  That's
>> about as braindead a choice as I can think of, but that's what it did.

> Out of curiosity, what does it do now? 

As of current sources there is a distinction between "strict" and
"non-strict" functions.  A strict function must return NULL if any
input is NULL, therefore the function manager won't call it at all
if there is a NULL input value, but just assume the NULL result.
(Some other DBMSes have the same concept under different names,
like "NOT NULL CALL".)

A non-strict function is assumed to be able to take care of itself.
It gets called anyway and must check to see if any of its inputs
are NULL, then decide what it wants to do about it.

Both strict and nonstrict functions can return NULL if they wish,
though I've not seen many cases where a strict function would want to.

This is all predicated on a new function call interface that provides
an explicit isNull flag for each input, as well as an isNull flag for
the function result.  The real problem with the old code was that we
didn't have that, and there is no non-kluge workaround for not having
the information.

If I understand Thomas' remarks correctly, at one time in the past
the function-call code operated as though all functions were strict.
I suppose that foundered on the little problem that certain operations
like IS NULL and IS NOT NULL *must* be non-strict.  So someone changed
it to the opposite convention, but didn't follow through to the bitter
end: there has to be an explicit null flag for *each* argument, as
well as a clean way for the function to say whether it is returning
a null or not.

Defaulting to non-strict also created a ton of potential crash sites
in routines that couldn't cope with null-pointer inputs.  We've been
gradually "fixing" those by adding explicit tests for nulls, but it's
always been a stopgap solution IMHO.  Now there's a better way.
99% of the built-in functions in the backend are "strict" and so
will no longer need special checks to defend against null inputs,
because they'll never see 'em again.
        regards, tom lane