Thread: 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@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
On Thu, 7 Nov 2002, Tom Lane wrote: > 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. 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@megazone23.bigpanda.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