RE: BUG #15091: to_number() returns incorrect value

From: Michael Aiello <michael(dot)aiello(at)asg(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
Cc: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: RE: BUG #15091: to_number() returns incorrect value
Date: 2018-02-27 16:54:53
Message-ID: 1f38226a14144888909618324e39d56f@asg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Returning a truncated integer result seems preferrable to reporting an error to me.
Do you think this issue will be addressed with a fix? If so, what version would it be made in, and roughly how long would a fix take?
Thanks
Mike

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, February 26, 2018 3:32 PM
To: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
Cc: Michael Aiello <michael(dot)aiello(at)asg(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15091: to_number() returns incorrect value

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Tom> Tested at
> Tom>
> http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiY0ZjQwMTdiZDU1NWNhNDc0ND01QTk0N
> zBERl84MjUwM18yNTkwXzEmJjdlNzhlMTJhMTFhZTQ4Nz0xMjIzJiZ1cmw9aHR0cCUzQSU
> yRiUyRnJleHRlc3RlciUyRWNvbSUyRmwlMkZvcmFjbGUlNUZvbmxpbmUlNUZjb21waWxlc
> g==

> The problem is that this tester is defaulting to a German locale and
> using . as a thousands separator and , as decimal point, as shown by
> this query:
> select * from nls_session_parameters
> where parameter in ('NLS_TERRITORY','NLS_NUMERIC_CHARACTERS')

Oh, thanks ... I was wondering what locale it had chosen, but did not know how to find out.

> If you do
> ALTER SESSION SET NLS_TERRITORY = 'AMERICA' \\ SELECT
> to_number('123.0', 'FM9999999') FROM DUAL then the result is, as
> expected,
> ORA-01722: invalid number

Hmm. That doesn't seem too helpful. Between throwing an error and returning "123", seems like the latter would be better.
Do we want to be bug-compatible here?

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Chaudhary, Krishna /CS 2018-02-27 17:31:53 RE: pgAdmin Development Team || pgAdmin (PostgreSQL Management tool) 4 ||Version 2.0 || EULA questionnaire
Previous Message Tom Lane 2018-02-27 15:12:36 Re: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset