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
woulda fix take?
Thanks
Mike
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, February 26, 2018 3:32 PM
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Michael Aiello <michael.aiello@asg.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15091: to_number() returns incorrect value
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.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