Re: Weird NULL behavior

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Thu, 7 Nov 2002, Ludwig Lim wrote:
>> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
>> Cannot cast type '"char"' to '"numeric"'

> It seems to me that it's trying to decide on a type
> for the expression NULL * NULL.  It's a NULL, but a
> NULL of what type?

Yeah, and it's picking "char" (the single-byte datatype), because
(a) the NULLs are initially regarded as type UNKNOWN, and (b) if we
don't have any other way to make a decision we try assuming that
UNKNOWNs are of string category, and (c) the only datatype in string
category that has a "*" operator is "char".

I am kind of inclined to remove the arithmetic operators on "char"
(+,-,*,/) in 7.4 --- they don't seem to have any real-world uses,
and as this example illustrates, they are perfectly positioned to
capture cases that probably ought to be errors.

But as you say, the proper solution for Ludwig's problem is to cast the
NULLs themselves to numeric, not the result of the multiplication.
        regards, tom lane