Re: Re: BUG #12885: The result of casting a double to an integer depends on the database version

On 27 March 2015 at 12:32, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
> On Thu, Mar 26, 2015 at 11:15 AM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
>> On Thu, Mar 26, 2015 at 11:12 AM, Andrew Gierth wrote:
>>>>>>>> "Michael" == Michael Paquier writes:
>>>
>>> Michael> Indeed...
>>> Michael> =# select column1 AS double_value, cast(column1 AS INT) AS
>>> Michael> int_value
>>>
>>> The complication for numeric is that there's also the case of round(x,n)
>>> and casting to numeric(m,n) to consider.
>>
>> OK, thanks for the reminder... I forgot this case.
>
> Attached is a patch that adds nearest-to-even rounding for numeric
> when dscale = 0.

I'm not convinced that it is a good idea to change the default
rounding mode for numeric.

For one thing, numeric is commonly used for monetary data, and
changing the default rounding mode might well break existing
applications where rounding is important, and which rely on the
current behaviour. Granted the current rounding mode doesn't appear to
be documented anywhere (which it probably should be), but still, it
seems like a risky thing to change.

I'd also argue that the current "round half away from zero" mode is
the most widely known rounding mode, since it is commonly taught at
school, so it seems like the more sensible default.

ISTM that there would have to be a good reason to change the default,
and compatibility with IEEE floats doesn't seem very convincing to me.
I would never assume numerics are meant to have anything in common
with IEEE floats.

Adding a version of round() with support for a choice of various
rounding modes might be a better way to go.

Also, if the default is being changed, I think that merits wider
discussion (on a thread with a more appropriate title) to see if it is
likely to be an issue for anyone.

Regards,
Dean