Horton

Thread

  1. select to_number('1,000', '999,999');

    David Schweikert <dws@ee.ethz.ch> — 2004-11-19T12:20:34Z

    ============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name		: David Schweikert
Your email address	: dws@ee.ethz.ch


System Configuration
---------------------
  Architecture (example: Intel Pentium)  	: Intel Pentium 4

  Operating System (example: Linux 2.4.18) 	: 2.4.25

  PostgreSQL version (example: PostgreSQL-7.4.6): PostgreSQL-7.4.6

  Compiler used (example:  gcc 2.95.2)		: gcc 3.3.3


Please enter a FULL description of your problem:
------------------------------------------------

select to_number('1,000', '999,999') returns '100'.



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible: 
----------------------------------------------------------------------

Proposed addition to the regression tests:

--- src/test/regress/sql/numeric.sql.orig	2000-04-07 21:17:50.000000000 +0200
+++ src/test/regress/sql/numeric.sql	2004-11-19 12:51:57.071152000 +0100
@@ -701,3 +701,4 @@
 SELECT '' AS to_number_11, to_number('.-01', 'S99.99');
 SELECT '' AS to_number_12, to_number('.01-', '99.99S');
 SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S');
+SELECT '' AS to_number_14, to_number(to_char('1000'::int, '999G999'),'999G999');

--- src/test/regress/expected/numeric.out.orig	2003-09-25 08:58:06.000000000 +0200
+++ src/test/regress/expected/numeric.out	2004-11-19 12:56:13.464401000 +0100
@@ -1112,3 +1112,9 @@
               |     -0.01
 (1 row)
 
+SELECT '' AS to_number_14, to_number(to_char('1000'::int, '999G999'),'999G999');
+ to_number_14 | to_number 
+--------------+-----------
+              |      1000
+(1 row)
+


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

  2. Re: select to_number('1,000', '999,999');

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-22T01:10:08Z

    David Schweikert <dws@ee.ethz.ch> writes:
> select to_number('1,000', '999,999') returns '100'.

I'm not entirely convinced this is a bug.  I get the right answer from

regression=# select to_number('001,000', '999,999') ;
 to_number 
-----------
      1000
(1 row)

It's arguable that to_number() should throw an error when the input
doesn't match the format, but right now it doesn't ...

			regards, tom lane

  3. Re: select to_number('1,000', '999,999');

    David Schweikert <dws@ee.ethz.ch> — 2004-11-22T06:50:59Z

    On Sun, Nov 21, 2004 at 20:10:08 -0500, Tom Lane wrote:
> I'm not entirely convinced this is a bug.  I get the right answer from
> 
> regression=# select to_number('001,000', '999,999') ;
>  to_number 
> -----------
>       1000
> (1 row)
> 
> It's arguable that to_number() should throw an error when the input
> doesn't match the format, but right now it doesn't ...

It seems strange to me that to_char(1000,'999,999') works (it returns
1,000), but the reverse doesn't.

I want to convert a formatted number with group separators, but I don't
know how many digits it has: should I count the digits myself and adapt
the mask (which is a customization and thus entered by the user)?

Cheers
David

-- 
David Schweikert        | phone: +41 44 632 7019
System manager ISG.EE   | walk:  ETH Zentrum, ETL F24.1
ETH Zurich, Switzerland | web:   http://people.ee.ethz.ch/dws

  4. Re: select to_number('1,000', '999,999');

    Stephan Szabo <sszabo@megazone.bigpanda.com> — 2004-11-22T13:39:38Z

    On Mon, 22 Nov 2004, David Schweikert wrote:

> On Sun, Nov 21, 2004 at 20:10:08 -0500, Tom Lane wrote:
> > I'm not entirely convinced this is a bug.  I get the right answer from
> >
> > regression=# select to_number('001,000', '999,999') ;
> >  to_number
> > -----------
> >       1000
> > (1 row)
> >
> > It's arguable that to_number() should throw an error when the input
> > doesn't match the format, but right now it doesn't ...
>
> It seems strange to me that to_char(1000,'999,999') works (it returns
> 1,000), but the reverse doesn't.
>
> I want to convert a formatted number with group separators, but I don't
> know how many digits it has: should I count the digits myself and adapt
> the mask (which is a customization and thus entered by the user)?

No, but I think you're supposed to use FM in such cases.

select to_number(1000, 'FM999,999');

  5. Re: select to_number('1,000', '999,999');

    Stephan Szabo <sszabo@megazone.bigpanda.com> — 2004-11-22T13:47:19Z

    On Mon, 22 Nov 2004, Stephan Szabo wrote:

> On Mon, 22 Nov 2004, David Schweikert wrote:
>
> > On Sun, Nov 21, 2004 at 20:10:08 -0500, Tom Lane wrote:
> > > I'm not entirely convinced this is a bug.  I get the right answer from
> > >
> > > regression=# select to_number('001,000', '999,999') ;
> > >  to_number
> > > -----------
> > >       1000
> > > (1 row)
> > >
> > > It's arguable that to_number() should throw an error when the input
> > > doesn't match the format, but right now it doesn't ...
> >
> > It seems strange to me that to_char(1000,'999,999') works (it returns
> > 1,000), but the reverse doesn't.
> >
> > I want to convert a formatted number with group separators, but I don't
> > know how many digits it has: should I count the digits myself and adapt
> > the mask (which is a customization and thus entered by the user)?
>
> No, but I think you're supposed to use FM in such cases.
>
> select to_number(1000, 'FM999,999');

Of course, I don't think the fact that it does that is actually documented
in the 7.4 docs now that I look. I must have just run into it through
experimentation, so I wonder if that's actually intended behavior or not.

  6. Re: select to_number('1,000', '999,999');

    David Schweikert <dws@ee.ethz.ch> — 2004-11-22T14:25:58Z

    On Mon, Nov 22, 2004 at 05:47:19 -0800, Stephan Szabo wrote:
>No, but I think you're supposed to use FM in such cases.
>
>select to_number(1000, 'FM999,999');

Indeed, it works with 'FM'.

Thanks!
David
-- 
David Schweikert        | phone: +41 44 632 7019
System manager ISG.EE   | walk:  ETH Zentrum, ETL F24.1
ETH Zurich, Switzerland | web:   http://people.ee.ethz.ch/dws

  7. Re: select to_number('1,000', '999,999');

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-22T16:08:56Z

    Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> No, but I think you're supposed to use FM in such cases.
>
> select to_number(1000, 'FM999,999');

Good point --- I had forgot about FM.  In that case there *is* a bug
here, but I'm not sure if it's with to_char or to_number:

regression=# select to_number(to_char(1000, 'FM999,999'),'FM999,999');
 to_number
-----------
      1000
(1 row)

regression=# select to_number(to_char(1000, '999,999'),'999,999');
 to_number
-----------
       100
(1 row)

Whatever your opinion is about the behavior of the non-FM format, surely
to_char and to_number should be inverses.

			regards, tom lane

  8. Re: select to_number('1,000', '999,999');

    Karel Zak <zakkr@zf.jcu.cz> — 2004-11-23T08:39:21Z

    On Mon, 2004-11-22 at 11:08 -0500, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > No, but I think you're supposed to use FM in such cases.
> >
> > select to_number(1000, 'FM999,999');
> 
> Good point --- I had forgot about FM.  In that case there *is* a bug
> here, but I'm not sure if it's with to_char or to_number:
> 
> regression=# select to_number(to_char(1000, 'FM999,999'),'FM999,999');
>  to_number
> -----------
>       1000
> (1 row)
> 
> regression=# select to_number(to_char(1000, '999,999'),'999,999');
>  to_number
> -----------
>        100
> (1 row)


It's to_number() bug. I'm not sure if now (before release) is good time
to fix it. The code of to_number() is not stable for changes and maybe
we can fix this bug add some other new...

I already work on new version for next release. It will use 
unit-tests -- I hope it will prevent a lot of bugs like this.

> Whatever your opinion is about the behavior of the non-FM format, surely
> to_char and to_number should be inverses.

Yes.

    Karel

-- 
Karel Zak
http://home.zf.jcu.cz/~zakkr

  9. Re: select to_number('1,000', '999,999');

    David Schweikert <dws@ee.ethz.ch> — 2004-11-23T16:21:42Z

    Hi Karel,

On Tue, Nov 23, 2004 at 09:39:21 +0100, Karel Zak wrote:
> It's to_number() bug. I'm not sure if now (before release) is good time
> to fix it. The code of to_number() is not stable for changes and maybe
> we can fix this bug add some other new...

I have the workaround with 'FM' so it is not urgent for me...

> I already work on new version for next release. It will use
> unit-tests -- I hope it will prevent a lot of bugs like this.

Sounds great, thank you.

Cheers
David
-- 
David Schweikert        | phone: +41 44 632 7019
System manager ISG.EE   | walk:  ETH Zentrum, ETL F24.1
ETH Zurich, Switzerland | web:   http://people.ee.ethz.ch/dws