Thread

1. Unnexpected results using to_number()

   Andrew Snow <andrew@modulus.org> — 2000-07-09T05:37:13Z

   
   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.
   
   
   
   

2. Re: Unnexpected results using to_number()

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-09T17:38:52Z

   "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
   
   

3. Re: [HACKERS] Re: Unnexpected results using to_number()

   Karel Zak <zakkr@zf.jcu.cz> — 2000-07-10T06:42:31Z

   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
   
   
   

4. Re: [HACKERS] Re: Unnexpected results using to_number()

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-10T06:53:15Z

   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
   
   

5. Re: Re: [BUGS] Unnexpected results using to_number()

   Karel Zak <zakkr@zf.jcu.cz> — 2000-07-10T07:32:37Z

   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
   
   
   
   
   

6. Re: Re: [BUGS] Unnexpected results using to_number()

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-10T14:12:46Z

   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