Обсуждение: BUG #15091: to_number() returns incorrect value
The following bug has been logged on the website:
Bug reference: 15091
Logged by: Michael Aiello
Email address: michael.aiello@asg.com
PostgreSQL version: 9.6.6
Operating system: Windows 10
Description:
When to_number is called with a string that contains a decimal point, but
there is no decimal indicator in the format string, the decimal digits are
appended to the integer portion, having the effect of increasing the the
integer value.
This can be reproduced with a simple SELECT, as follows:
SELECT to_number('123.0', 'FM9999999');
returns value 1230
by comparison, a decimal indicator in the format returns the correct
value:
SELECT to_number('123.0', 'FM99999D999');
returns 123.0
I would expect one of two behaviors when the decimal place is present in the
string to convert, but not the format, either:
a) truncate the value at the decimal point, discarding the decimal portion.
In this case, the first example above would return the integer 123, or
b) raise a number format exception.
Thanks
Mike Aiello
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> When to_number is called with a string that contains a decimal point, but
> there is no decimal indicator in the format string, the decimal digits are
> appended to the integer portion, having the effect of increasing the the
> integer value.
> This can be reproduced with a simple SELECT, as follows:
> SELECT to_number('123.0', 'FM9999999');
> returns value 1230
[ experiments ... ] I get the same result on Oracle, so I'd say this
is the "correct" behavior.
> by comparison, a decimal indicator in the format returns the correct
> value:
> SELECT to_number('123.0', 'FM99999D999');
> returns 123.0
On the other hand, Oracle also returns 1230 for this case :-(.
But that seems to be a locale issue --- if I change the D to "."
I get the expected result 123.
Tested at
http://rextester.com/l/oracle_online_compiler
regards, tom lane
Hi Tom,
Thanks for the quick reply.
I get different results when I tried the test with Oracle (using Oracle 12.1 running on Linux, from a Windows client)
SELECT to_number('123.0', 'FM9999999') FROM DUAL;
ORA-01722: invalid number
01722. 00000 - "invalid number"
SELECT to_number('123.0', 'FM9999.999') FROM DUAL;
123
SELECT to_number('123.0', 'FM9999D999') FROM DUAL;
123
I'm not seeing the locale issue, maybe because of the client running on Windows, but Oracle 12 doesn't seem to like the
firstcase.
Thanks
Mike
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, February 26, 2018 12:48 PM
To: Michael Aiello <michael.aiello@asg.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15091: to_number() returns incorrect value
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> When to_number is called with a string that contains a decimal point,
> but there is no decimal indicator in the format string, the decimal
> digits are appended to the integer portion, having the effect of
> increasing the the integer value.
> This can be reproduced with a simple SELECT, as follows:
> SELECT to_number('123.0', 'FM9999999'); returns value 1230
[ experiments ... ] I get the same result on Oracle, so I'd say this is the "correct" behavior.
> by comparison, a decimal indicator in the format returns the correct
> value:
> SELECT to_number('123.0', 'FM99999D999'); returns 123.0
On the other hand, Oracle also returns 1230 for this case :-(.
But that seems to be a locale issue --- if I change the D to "."
I get the expected result 123.
Tested at
http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiZjYjA3ZTMxZWNmOWYwMjc2ZD01QTk0NEQ3OV84MjUwM18yMTExXzEmJmI2ODRhNGYzODlkM2EwMT0xMjIzJiZ1cmw9aHR0cCUzQSUyRiUyRnJleHRlc3RlciUyRWNvbSUyRmwlMkZvcmFjbGUlNUZvbmxpbmUlNUZjb21waWxlcg==
regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> Tested at Tom> http://rextester.com/l/oracle_online_compiler 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') 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 -- Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes: > "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: > Tom> Tested at > Tom> http://rextester.com/l/oracle_online_compiler > 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
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> Tested at Tom> http://rextester.com/l/oracle_online_compiler >> 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') Tom> Oh, thanks ... I was wondering what locale it had chosen, but did Tom> not know how to find out. I had to ask around to find a way that worked both on rextester.com and on sqlfiddle.com (which also supports Oracle 11, and is where I usually test oracle query compatibility things). It was the fact that the two sites behaved differently for this query that got me looking at parameter settings to begin with; sqlfiddle defaults to US locale. -- Andrew (irc:RhodiumToad)
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
Hi Tom, Andrew, Could you let me know whether this issue will be addressed by a bug fix, and if so which version(s) would be targeted andabout how long it will take? We need this information for our own planning purposes. Thanks Mike -----Original Message----- From: Michael Aiello Sent: Tuesday, February 27, 2018 11:55 AM To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Andrew Gierth <andrew@tao11.riddles.org.uk> Cc: pgsql-bugs@lists.postgresql.org Subject: RE: BUG #15091: to_number() returns incorrect value 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
Michael Aiello <michael.aiello@asg.com> writes:
> Could you let me know whether this issue will be addressed by a bug fix, and if so which version(s) would be targeted
andabout how long it will take?
[ shrug... ] There is no proposed patch, nor has anyone offered to create
one, so that's an unanswerable question at the moment. If we had a patch
we could evaluate how invasive it is and how big the behavioral change
would be exactly, and then decide whether to back-patch. But my suspicion
is that we would not back-patch, but only change it in the next major
release. This is the sort of thing where somebody might be relying on the
current behavior as "correct" and not be happy if it changes under them in
a minor release.
regards, tom lane