Обсуждение: [BUGS] BUG #14663: Unexpected rounding changes for money type divided bybigint
[BUGS] BUG #14663: Unexpected rounding changes for money type divided bybigint
От
pistole@rhp.org
Дата:
The following bug has been logged on the website: Bug reference: 14663 Logged by: Richard Pistole Email address: pistole@rhp.org PostgreSQL version: 10beta1 Operating system: Centos 7 Description: Running the following query: select 878.08::money/11::bigint; Returns this following result in 9.6.3: $79.83 In 10beta1, it returns: $79.82 I think this may be related to this commit and doing the running sums as negatives, but I'm not sure: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=656df624c0d7b50e1714f2a3a14e143e63799a80 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
pistole@rhp.org writes: > Running the following query: > select 878.08::money/11::bigint; > Returns this following result in 9.6.3: > $79.83 > In 10beta1, it returns: > $79.82 Confirmed here. > I think this may be related to this commit and doing the running sums as > negatives, but I'm not sure: No, I think the relevant commit is Author: Peter Eisentraut <peter_e@gmx.net> Branch: master [323b96aa3] 2017-01-17 12:36:02 -0500 Register missing money operators in system catalogs The operators money*int8, int8*money, and money/int8 were implementedin code but not registered in pg_operator or pg_proc. Previously, money/int8 would have ended up as money/(int8::float8) which rounds the conversion, whereas cash_div_int8 truncates. While this is certainly a behavior change, I think it's more consistent than before, because cash_div_int4 has always truncated. So in prior versions you got regression=# select '878.08'::money/11::bigint;?column? ---------- $79.83 (1 row) regression=# select '878.08'::money/11::int; ?column? ---------- $79.82 (1 row) which hardly seems desirable. Now they both give $79.82. If you want the old behavior you can cast the divisor to float8 explicitly. We do have a couple of to-do items here though: 1. The v10 release notes mention this commit but fail to point out that it might be a behavioral change. 2. I notice that the cash_div_intX functions all use rint() on the result, which is completely useless because the result is an integer already. Much worse, forcing the value to double and back will lose precision if the value exceeds 2^52 or so. This is visible here for instance: regression=# select '90000000000000012.00'::money / 10::int; ?column? ---------------------------$9,000,000,000,000,001.28 (1 row) That's just wrong. We should remove the rint() calls and acknowledge that these divisor functions truncate rather than rounding. I think this is a back-patchable bug fix ... does anyone want to argue that preserving the precision loss in the back branches is a good thing? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs