Re: trouble caused by change in 7.3 handling of '' in

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> --On Thursday, December 19, 2002 12:01:21 -0500 Bruce Momjian
> <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>> Yes, this was really it --- we didn't realize how many apps used this.
>> Also, we expected to hit them before final release, when we could have
>> reverted the change.

> IT DID! (see my post during Beta).

> You said, well, that's only one.

It's still only two. And I still haven't heard any cogent argument why
we shouldn't regard these apps as broken --- regardless of how many
there are. I cannot see any rational reason for accepting '' as meaning
zero in an integer field. If someone were to present an example where
this actually makes sense (and is not just evidence of very sloppy
programming) I'd be more sympathetic ...

BTW, I note that we seem to have missed a couple of cases: float4,
float8, and OID still accept '' as zero in CVS tip. On the other hand,
int8 and numeric never have accepted '' AFAIR.

Another thing we should probably look at doing is allowing trailing
spaces in all these input routines. Currently, leading spaces are
ok but trailing are not:

regression=# select ' 1'::int;
int4
------
1
(1 row)

regression=# select ' 1 '::int;
ERROR: pg_atoi: error in " 1 ": can't parse " "

It appears to me that SQL expects trailing spaces to be legal; for
example, the SQL99 rules for CASTing strings to numerics say

b) If SD is character string, then SV is replaced by SV with any
leading or trailing <space>s removed.
^^^^^^^^^^^^^^^^^^^

Case:

i) If SV does not comprise a <signed numeric literal> as
defined by the rules for <literal> in Subclause 5.3,
"<literal>", then an exception condition is raised: data
exception - invalid character value for cast.

ii) Otherwise, let LT be that <signed numeric literal>. The
<cast specification> is equivalent to

CAST ( LT AS TD )

regards, tom lane