Re: Inconsistency between TO_CHAR() and TO_NUMBER()

From: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
To: Euler Taveira <euler(at)timbira(dot)com(dot)br>
Cc: Thomas Kellerer <spam_eater(at)gmx(dot)net>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Inconsistency between TO_CHAR() and TO_NUMBER()
Date: 2013-05-13 10:45:22
Message-ID: 5190C442.3000609@vmware.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On 11.05.2013 01:17, Euler Taveira wrote:
> On 10-05-2013 13:09, Thomas Kellerer wrote:
>> Tom Lane wrote on 10.05.2013 17:49:
>>> I looked into this, and find that the reason it misbehaves is that
>>> NUM_numpart_from_char() will treat a '.' as being a decimal point
>>> *without any regard to locale considerations*. So even if we have
>>> a locale-dependent format string and a locale that says '.' is a
>>> thousands separator, it does the wrong thing.
>>>
>>> It's a bit surprising nobody's complained of this before.
>>>
>>> I propose the attached patch. I'm slightly worried though about whether
>>> this might break any existing applications that are (incorrectly)
>>> depending on a D format specifier being able to match '.' regardless of
>>> locale. Perhaps we should only apply this to HEAD and not back-patch?
>>
> +1 only in HEAD. That's because (a) it doesn't crash, (b) it doesn't
> always produce the "wrong" answer (only in some specific situation) and
> (c) it has been like that for years without a complain. For those
> reasons, it is better to continue with this "wrong" behavior in back
> branches than prevent important security updates to be applied (without
> applying a patch to preserve the "wrong" answer). This argument is only
> valid for legacy closed-source apps but seems to have more weight than
> the bug scenario.

+1 for HEAD-only. The Finnish language and locale uses comma (,) as the
decimal separator, and it's a real pain in the ass. And if something
goes wrong there, it can be *really* subtle. I once had to debug an
application where all prices were suddenly rounded down to the nearest
euro. And it only happened on some servers (those with locale set to
Finnish). It was not a PostgreSQL application, but it turned out to be a
bug in the JDBC driver of another DBMS.

Would it be possible to be lenient, and also accept . as the decimal
separator, when there is no ambiguity? Ie. when . is not the thousands
separator.

- Heikki

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message marc 2013-05-13 13:30:24 BUG #8153: check constraint results in a lot of casts
Previous Message Chaya Gilburt 2013-05-12 16:10:24 Odd Behavior After Multiple Deletes