to_char/to_number loses sign
This is from one of the examples in the documentation:
SELECT to_char(-485, '999S');
to_char
---------
485-
The reverse doesn't work as well:
SEKLECT to_number('485-', '999S');
to_number
-----------
485
Is this a bug or intentional?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
SELECT to_number('485-', '999S');
to_number
-----------
485
Is this a bug or intentional?
Tracing through this, it looks like the problem is that NUM_processor()
has no switch case for NUM_S (nor does the default case raise an error,
which seems a risky practice to me).
Karel, can you verify this and submit a fix?
regards, tom lane
On Sat, 2004-10-23 at 17:25 -0400, Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
SELECT to_number('485-', '999S');
to_number
-----------
485Is this a bug or intentional?
Tracing through this, it looks like the problem is that NUM_processor()
has no switch case for NUM_S (nor does the default case raise an error,
which seems a risky practice to me).Karel, can you verify this and submit a fix?
Yes, you're right. It strange, but NUM_S missing there. The conversion
from string to number is less stable part of formatting.c...
I have already 2000 lines of code of new generation of to_..()
functions. But all will available in 8.1.
The patch is in the attachment.
Karel
--
Karel Zak
http://home.zf.jcu.cz/~zakkr
Attachments:
pgsql-formatting-10252004.patchtext/x-patch; charset=us-ascii; name=pgsql-formatting-10252004.patchDownload
--- pgsql/src/backend/utils/adt/formatting.c.num_s 2004-10-25 13:51:58.009789928 +0200
+++ pgsql/src/backend/utils/adt/formatting.c 2004-10-25 15:23:09.315025104 +0200
@@ -3625,7 +3625,7 @@
{
#ifdef DEBUG_TO_FROM_CHAR
- elog(DEBUG_elog_output, " --- scan start --- ");
+ elog(DEBUG_elog_output, " --- scan start --- >>%s<<", Np->number);
#endif
if (*Np->inout_p == ' ')
@@ -3642,7 +3642,7 @@
/*
* read sign
*/
- if (*Np->number == ' ' && (id == NUM_0 || id == NUM_9 || NUM_S))
+ if (*Np->number == ' ' && (id == NUM_0 || id == NUM_9 || id == NUM_S))
{
#ifdef DEBUG_TO_FROM_CHAR
@@ -4138,6 +4138,7 @@
case NUM_0:
case NUM_DEC:
case NUM_D:
+ case NUM_S:
if (Np->type == TO_CHAR)
{
NUM_numpart_to_char(Np, n->key->id);
Karel Zak <zakkr@zf.jcu.cz> writes:
Yes, you're right. It strange, but NUM_S missing there. The conversion
from string to number is less stable part of formatting.c...
The patch is in the attachment.
This patch causes the regression tests to fail. I think you need to
consider the to_char() side of it more carefully.
regards, tom lane
On Tue, 2004-10-26 at 13:27 -0400, Tom Lane wrote:
Karel Zak <zakkr@zf.jcu.cz> writes:
Yes, you're right. It strange, but NUM_S missing there. The conversion
from string to number is less stable part of formatting.c...The patch is in the attachment.
This patch causes the regression tests to fail. I think you need to
consider the to_char() side of it more carefully.
Sorry of this in beta version...
The problem was bigger than I expected. I hope it's fixed in actual
patch. All regression tests pass.
The patch changes two lines in regression test too. It's because old
version of to_number() allows to use wrong number input and this input
was in regression test. The call to_char(-0.01, ' 9 9 . 9 9 S'); never
produced ' . 0 1 -' but always ' . 0 1-' only.
-SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S');
+SELECT '' AS to_number_13, to_number(' . 0 1-', ' 9 9 . 9 9 S');
^^^
'S' = locale sign and it must be always anchored to last or first
number.Again sorry,
Karel
--
Karel Zak
http://home.zf.jcu.cz/~zakkr