Re: Weird NULL behavior

Hi:

Has anyone encountered this before?
SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));

returns the following error message:
Cannot cast type '"char"' to '"numeric"'

But the following sql statements returns NULL:
select NULL:
select NULL * NULL;
select cast ( NULL as NUMERIC(2,0));

Thank you in advance,
ludwig.

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

On Thu, 7 Nov 2002, Ludwig Lim wrote:

> Hi:
>
> Has anyone encountered this before?
> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
>
>
> returns the following error message:
> 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? I think the spec gets around
this by disallowing such structures AFAIK (NULL
can be used in like row value constructors, case
and cast). I think the "sql" way of doing the
above would be
select cast(cast(NULL as NUMERIC(2,0))*cast(NULL as NUMERIC(2,0))
as NUMERIC(2,0));

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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

On Thu, 7 Nov 2002, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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.

That seems to make sense. I assume that they were there so that someone
could treat it as a 1 byte integer?

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> On Thu, 7 Nov 2002, Tom Lane wrote:
>> 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.

> That seems to make sense. I assume that they were there so that someone
> could treat it as a 1 byte integer?

Presumably ... but defining a numeric type named "int1" would be a lot
more sensible than overloading "char" for the purpose.

regards, tom lane