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

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

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

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

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