Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id ECC41476676 for ; Thu, 7 Nov 2002 14:09:30 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id DBB294765F0 for ; Thu, 7 Nov 2002 14:09:29 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id gA7J9PhR028165; Thu, 7 Nov 2002 14:09:25 -0500 (EST) To: Stephan Szabo Cc: Ludwig Lim , PostgreSQL Mailing List Subject: Re: Weird NULL behavior In-reply-to: <20021107083423.A97075-100000@megazone23.bigpanda.com> References: <20021107083423.A97075-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Thu, 07 Nov 2002 08:41:26 -0800" Date: Thu, 07 Nov 2002 14:09:24 -0500 Message-ID: <28164.1036696164@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/117 X-Sequence-Number: 10259 Stephan Szabo 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