darcy@druid.net (D'Arcy J.M. Cain) writes:
>> My guess is that maybe this should not be fixed in the individual
>> datatypes at all; instead the generic function and operator code should
>> be modified so that if any input value is NULL, then NULL is returned as
>> the result without ever calling the datatype-specific code.
> Could it be tied to the return type? IOW, functions or operators
> that return bool return FALSE, text return "", etc.
That strikes me as really dangerous. What you are basically proposing
is to overload a perfectly good value of each type as meaning "maybe
it's really this data value, and maybe it's the result of an operation
on NULL". We have a good way to represent an unknown/undefined result,
and that is to return NULL; we shouldn't replace that with a kluge.
Compare for example the IEEE floating point standards, in which there
are specific "exceptional values" (Not a Numbers, or NaNs). NaNs
propagate through operations --- for example, NaN + 23 yields NaN, not
0 or any other ordinary data value. The people who defined it that way
knew what they were doing. Practically every formal computational model
invented in the last twenty years has had a similar concept of an
"undefined" or "bottom" value, and they all treat it that way.
Now, any Postgres implementation running on an IEEE-float platform will
automatically do the right things in the float4 and float8 operators,
so NaNs in a database should do the right things. (BTW, do the input
and output conversion operators for floats support a representation for
NaNs? If not that should be on the to-do list...) But we need a
similar concept for all the other data types. As far as I can see,
NULL should be it.
(In fact, I'd be surprised if the SQL standard doesn't mandate that
behavior. I don't have a copy to look at, however.)
It sounds like the function/operator support already gets this almost
right, but it shouldn't call the datatype-specific routine at all if
it's not going to use the result (IMHO anyway).
>> There might be specific operators for which this is not the right
>> behavior (although none spring to mind immediately). In that case,
>> I think the best bet would be to have a per-operator flag, defaulting
>> to OFF, which could be turned on for those specific operators that are
>> prepared to cope with null inputs.
> Obviously that will have to wait for 6.5 since it requires an initdb
> to add the field. Do we want to wait that long?
I haven't yet heard an example case demonstrating that it's necessary
at all...
regards, tom lane