

Is this supposed to happen? I discovered this when I was experimenting with
converting a string to a number.

# SELECT to_number('12,454.8-', '99G999D9S');
 to_number
-----------
  -12454.8
(1 row)

# SELECT to_number('12,454.8-', '');
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#




I am running PostgreSQL 7.0.2 on FreeBSD 3.4-STABLE (x86).  Thanks,


- Andrew.



Sun, 9 Jul 2000 15:37:13 +1000
"Andrew Snow" <andrew@modulus.org>
Unnexpected results using to_number()
"Andrew Snow" <andrew@modulus.org> writes:
> # SELECT to_number('12,454.8-', '');
> pqReadData() -- backend closed the channel unexpectedly.

In current sources I get a NULL result, which seems to be what the
code author intended originally.  However this seems a little bit
inconsistent --- shouldn't it raise a bad-format error instead?
For example,

regression=#  SELECT to_number('12,454.8-', ' ');
ERROR:  Bad numeric input format ' '

Seems odd that no spaces means "return NULL" but 1 or more spaces
doesn't.

			regards, tom lane

Sun, 09 Jul 2000 13:38:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unnexpected results using to_number() 
On Sun, 9 Jul 2000, Tom Lane wrote:

> "Andrew Snow" <andrew@modulus.org> writes:
> > # SELECT to_number('12,454.8-', '');
> > pqReadData() -- backend closed the channel unexpectedly.
> 
> In current sources I get a NULL result, which seems to be what the
> code author intended originally.  However this seems a little bit

 my original code not return NULL, but return numeric_in(NULL, 0, 0) for
this situation.

> inconsistent --- shouldn't it raise a bad-format error instead?
> For example,
> 
> regression=#  SELECT to_number('12,454.8-', ' ');
> ERROR:  Bad numeric input format ' '

 Thanks for fix Tom.

					Karel


Mon, 10 Jul 2000 08:42:31 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: [HACKERS] Re: Unnexpected results using to_number() 
Karel Zak <zakkr@zf.jcu.cz> writes:
> On Sun, 9 Jul 2000, Tom Lane wrote:
>> "Andrew Snow" <andrew@modulus.org> writes:
>>>> # SELECT to_number('12,454.8-', '');
>>>> pqReadData() -- backend closed the channel unexpectedly.
>> 
>> In current sources I get a NULL result, which seems to be what the
>> code author intended originally.  However this seems a little bit

>  my original code not return NULL, but return numeric_in(NULL, 0, 0) for
> this situation.

Yeah, I know.  What did you expect that to produce, if not a NULL?

>> inconsistent --- shouldn't it raise a bad-format error instead?

What do you think about raising an error instead of returning NULL?

			regards, tom lane

Mon, 10 Jul 2000 02:53:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: Unnexpected results using to_number() 

On Mon, 10 Jul 2000, Tom Lane wrote:

> Karel Zak <zakkr@zf.jcu.cz> writes:
> > On Sun, 9 Jul 2000, Tom Lane wrote:
> >> "Andrew Snow" <andrew@modulus.org> writes:
> >>>> # SELECT to_number('12,454.8-', '');
> >>>> pqReadData() -- backend closed the channel unexpectedly.
> >> 
> >> In current sources I get a NULL result, which seems to be what the
> >> code author intended originally.  However this seems a little bit
> 
> >  my original code not return NULL, but return numeric_in(NULL, 0, 0) for
> > this situation.
> 
> Yeah, I know.  What did you expect that to produce, if not a NULL?

 It is a numeric_in() problem :-), but yes, it is still NULL.

> 
> >> inconsistent --- shouldn't it raise a bad-format error instead?
> 
> What do you think about raising an error instead of returning NULL?

Oracle:
SVRMGR> select to_number('12,454.8-', '') from dual;
TO_NUMBER(
----------
ORA-01722: invalid number


I mean that we can use ERROR here too. My original idea was same form for 
to_char and for to_number --- for to_char() Oracle say:

SVRMGR> select to_char(SYSDATE, '') from dual;
TO_CHAR(S
---------

1 row selected.


I not sure here what is better. If you mean that ERROR is better I will 
change it in some next patch fot formattin.c.

 Comments?
 
						Karel




Mon, 10 Jul 2000 09:32:37 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: Re: [BUGS] Unnexpected results using to_number() 
Karel Zak <zakkr@zf.jcu.cz> writes:
>> What do you think about raising an error instead of returning NULL?

> Oracle:
> SVRMGR> select to_number('12,454.8-', '') from dual;
> TO_NUMBER(
> ----------
> ORA-01722: invalid number

> I mean that we can use ERROR here too. My original idea was same form for 
> to_char and for to_number --- for to_char() Oracle say:

> SVRMGR> select to_char(SYSDATE, '') from dual;
> TO_CHAR(S
> ---------
>
> 1 row selected.

> I not sure here what is better.

Well, I think there is a good reason for the difference in Oracle's
behavior.  The second case is presumably returning a zero-length string,
not a NULL, and that is a perfectly valid string.  to_number() has no
comparable option, so I think it makes sense for it to raise an error.

			regards, tom lane

Mon, 10 Jul 2000 10:12:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: [BUGS] Unnexpected results using to_number() 