Re: [HACKERS] Rounding to even for numeric data type
Michael Paquier michael.paqu...@gmail.com writes: On Sat, May 2, 2015 at 9:53 PM, Fabien COELHO wrote: Quick review: patches applies, make check is fine, all is well. Thanks for the feedback, Fabien! All the casting tests could be put in numeric.sql, as there are all related to numeric and that would avoid duplicating the values lists. Not sure about that, the tests are placed here to be consistent with for is done for float8. For the documentation, I would also add 3.5 so that rounding to even is even clearer:-) Good idea. I reworked the example in the docs. Pushed with minor adjustments --- you missed updating int8-exp-three-digits.out, and I thought the documentation wording could be better. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
v2 applied tested. [...] Not sure about that, the tests are placed here to be consistent with for is done for float8. Maybe float8 to numeric casts could have been in numeric too. [...] I reworked the example in the docs. Indeed, looks good. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On Sat, May 2, 2015 at 9:53 PM, Fabien COELHO wrote: Quick review: patches applies, make check is fine, all is well. Thanks for the feedback, Fabien! All the casting tests could be put in numeric.sql, as there are all related to numeric and that would avoid duplicating the values lists. Not sure about that, the tests are placed here to be consistent with for is done for float8. For the documentation, I would also add 3.5 so that rounding to even is even clearer:-) Good idea. I reworked the example in the docs. -- Michael From 7a40acab425f25f7c06344b2e039405542ed020e Mon Sep 17 00:00:00 2001 From: Michael Paquier michael@otacoo.com Date: Sat, 9 May 2015 22:15:47 +0900 Subject: [PATCH] Precise rounding behavior of numeric and double precision in docs Regression tests improving the coverage in this area are added as well. --- doc/src/sgml/datatype.sgml| 19 +++ src/test/regress/expected/int2.out| 20 src/test/regress/expected/int4.out| 20 src/test/regress/expected/int8.out| 20 src/test/regress/expected/numeric.out | 24 src/test/regress/sql/int2.sql | 10 ++ src/test/regress/sql/int4.sql | 10 ++ src/test/regress/sql/int8.sql | 10 ++ src/test/regress/sql/numeric.sql | 10 ++ 9 files changed, 143 insertions(+) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index da1f25f..24efe25 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -612,6 +612,25 @@ NUMERIC equivalent. Both types are part of the acronymSQL/acronym standard. /para + +para + With using the functionround/ function, the typenumeric/type + type rounds ties away from zero, and the typedouble precision/type + type rounds ties away to even. + +programlisting +SELECT num, + round(num::double precision) AS prec_round, + round(num::numeric) AS nume_round + FROM generate_series(1.5, 3.5, 1) as num; + num | prec_round | nume_round +-++ + 1.5 | 2 | 2 + 2.5 | 2 | 3 + 3.5 | 4 | 4 +(3 rows) +/programlisting +/para /sect2 diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out index 311fe73..3ea4ed9 100644 --- a/src/test/regress/expected/int2.out +++ b/src/test/regress/expected/int2.out @@ -286,3 +286,23 @@ FROM (VALUES (-2.5::float8), 2.5 | 2 (7 rows) +-- check rounding when casting from numeric +SELECT x, x::int2 AS int2_value +FROM (VALUES (-2.5::numeric), + (-1.5::numeric), + (-0.5::numeric), + (0.0::numeric), + (0.5::numeric), + (1.5::numeric), + (2.5::numeric)) t(x); + x | int2_value +--+ + -2.5 | -3 + -1.5 | -2 + -0.5 | -1 + 0.0 | 0 + 0.5 | 1 + 1.5 | 2 + 2.5 | 3 +(7 rows) + diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out index 83fe022..372fd4d 100644 --- a/src/test/regress/expected/int4.out +++ b/src/test/regress/expected/int4.out @@ -383,3 +383,23 @@ FROM (VALUES (-2.5::float8), 2.5 | 2 (7 rows) +-- check rounding when casting from numeric +SELECT x, x::int4 AS int4_value +FROM (VALUES (-2.5::numeric), + (-1.5::numeric), + (-0.5::numeric), + (0.0::numeric), + (0.5::numeric), + (1.5::numeric), + (2.5::numeric)) t(x); + x | int4_value +--+ + -2.5 | -3 + -1.5 | -2 + -0.5 | -1 + 0.0 | 0 + 0.5 | 1 + 1.5 | 2 + 2.5 | 3 +(7 rows) + diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out index da8be51..ed0bd34 100644 --- a/src/test/regress/expected/int8.out +++ b/src/test/regress/expected/int8.out @@ -866,3 +866,23 @@ FROM (VALUES (-2.5::float8), 2.5 | 2 (7 rows) +-- check rounding when casting from numeric +SELECT x, x::int8 AS int8_value +FROM (VALUES (-2.5::numeric), + (-1.5::numeric), + (-0.5::numeric), + (0.0::numeric), + (0.5::numeric), + (1.5::numeric), + (2.5::numeric)) t(x); + x | int8_value +--+ + -2.5 | -3 + -1.5 | -2 + -0.5 | -1 + 0.0 | 0 + 0.5 | 1 + 1.5 | 2 + 2.5 | 3 +(7 rows) + diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out index 9d68145..e6ee548 100644 --- a/src/test/regress/expected/numeric.out +++ b/src/test/regress/expected/numeric.out @@ -730,6 +730,30 @@ SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round; (7 rows) DROP TABLE ceil_floor_round; +-- Check rounding, it should round ties away from zero.
Re: [HACKERS] Rounding to even for numeric data type
So, attached is a patch that does 1) and 2) to make clear to the user how numeric and double precision behave regarding rounding. I am adding it to CF 2015-06 to keep track of it... Quick review: patches applies, make check is fine, all is well. Two minor suggestions: All the casting tests could be put in numeric.sql, as there are all related to numeric and that would avoid duplicating the values lists. For the documentation, I would also add 3.5 so that rounding to even is even clearer:-) -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On Wed, Apr 22, 2015 at 9:30 PM, Pedro Gimeno pgsql-...@personal.formauri.es wrote: Dean Rasheed wrote, On 2015-03-28 10:01: On 28 March 2015 at 05:16, Andrew Gierth and...@tao11.riddles.org.uk wrote: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom I think the concern over backwards compatibility here is probably Tom overblown; but if we're sufficiently worried about it, a possible Tom compromise is to invent a numeric_rounding_mode GUC, so that Tom people could get back the old behavior if they really care. I only see one issue with this, but it's a nasty one: do we really want to make all numeric operations that might do rounding stable rather than immutable? Yeah, making all numeric functions non-immutable seems like a really bad idea. Would it be possible to make it an unchangeable per-cluster or per-database setting, kinda like how encoding behaves? Wouldn't that allow to keep the functions immutable? Rounding is not something that can be enforced at the database or server level but at data type level, see for example the differences already present for double precision and numeric as mentioned upthread. In short, you could keep rounding functions immutable by having one data type with a different rounding method. At least that's an idea. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
Dean Rasheed wrote, On 2015-03-28 10:01: On 28 March 2015 at 05:16, Andrew Gierth and...@tao11.riddles.org.uk wrote: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom I think the concern over backwards compatibility here is probably Tom overblown; but if we're sufficiently worried about it, a possible Tom compromise is to invent a numeric_rounding_mode GUC, so that Tom people could get back the old behavior if they really care. I only see one issue with this, but it's a nasty one: do we really want to make all numeric operations that might do rounding stable rather than immutable? Yeah, making all numeric functions non-immutable seems like a really bad idea. Would it be possible to make it an unchangeable per-cluster or per-database setting, kinda like how encoding behaves? Wouldn't that allow to keep the functions immutable? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
Michael Paquier wrote: Well, I am not sure about that... But reading this thread changing the default rounding sounds unwelcome. So it may be better to just put in words the rounding method used now in the docs, with perhaps a mention that this is not completely in-line with the SQL spec if that's not the case. The SQL standard does not care, it says that numbers and other data types should, whenever necessary, be rounded or truncated in an implementation- defined fashion. I cannot find any mention of a round() function. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On Mon, Mar 30, 2015 at 4:51 AM, James Cloos cl...@jhcloos.com wrote: MP == Michael Paquier michael.paqu...@gmail.com writes: MP So, attached is a patch that does 1) and 2) to make clear to the MP user how numeric and double precision behave regarding rounding. MP I am adding it to CF 2015-06 to keep track of it... Given that the examples show -2.5 rounds to -3, the IEEE term is roundTiesToAway, and the typical conversational english is round ties away from zero. Ah, thanks for the correct wording. Fixed in the attached. RoundUp means mean towards +Infinity. 754 specifies that for decimal, either roundTiesToEven or roundTiesToAway are acceptable defaults, and which of the two applies is language dependent. Does ANSI SQL say anything about how numeric should round? In general, for decimals (or anything other than binary), there are twelve possible roundings: ToEven ToOdd AwayFromZero ToZero Up Down TiesToEven TiesToOdd TiesAwayFromZero TiesToZero TiesUp TiesDown (Up is the same as ceil(3), Down as floor(3).) Well, I am not sure about that... But reading this thread changing the default rounding sounds unwelcome. So it may be better to just put in words the rounding method used now in the docs, with perhaps a mention that this is not completely in-line with the SQL spec if that's not the case. -- Michael From ae28d91519854e6d47d2c864fa26b65c70bb0526 Mon Sep 17 00:00:00 2001 From: Michael Paquier michael@otacoo.com Date: Sun, 29 Mar 2015 19:46:50 +0900 Subject: [PATCH] Precise rounding behavior of numeric and double precision in docs Regression tests improving the coverage in this area are added as well. --- doc/src/sgml/datatype.sgml| 19 +++ src/test/regress/expected/int2.out| 20 src/test/regress/expected/int4.out| 20 src/test/regress/expected/int8.out| 20 src/test/regress/expected/numeric.out | 24 src/test/regress/sql/int2.sql | 10 ++ src/test/regress/sql/int4.sql | 10 ++ src/test/regress/sql/int8.sql | 10 ++ src/test/regress/sql/numeric.sql | 10 ++ 9 files changed, 143 insertions(+) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index da1f25f..eb131c3 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -612,6 +612,25 @@ NUMERIC equivalent. Both types are part of the acronymSQL/acronym standard. /para + +para + With using the functionround/ function, the typenumeric/type + type rounds ties away from zero, and the typedouble precision/type + type rounds ties away to even. + +programlisting +SELECT round(1.5::numeric), round(2.5::numeric); + round | round +---+--- + 2 | 3 +(1 row) +SELECT round(1.5::double precision), round(2.5::double precision); + round | round +---+--- + 2 | 2 +(1 row) +/programlisting +/para /sect2 diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out index 311fe73..3ea4ed9 100644 --- a/src/test/regress/expected/int2.out +++ b/src/test/regress/expected/int2.out @@ -286,3 +286,23 @@ FROM (VALUES (-2.5::float8), 2.5 | 2 (7 rows) +-- check rounding when casting from numeric +SELECT x, x::int2 AS int2_value +FROM (VALUES (-2.5::numeric), + (-1.5::numeric), + (-0.5::numeric), + (0.0::numeric), + (0.5::numeric), + (1.5::numeric), + (2.5::numeric)) t(x); + x | int2_value +--+ + -2.5 | -3 + -1.5 | -2 + -0.5 | -1 + 0.0 | 0 + 0.5 | 1 + 1.5 | 2 + 2.5 | 3 +(7 rows) + diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out index 83fe022..372fd4d 100644 --- a/src/test/regress/expected/int4.out +++ b/src/test/regress/expected/int4.out @@ -383,3 +383,23 @@ FROM (VALUES (-2.5::float8), 2.5 | 2 (7 rows) +-- check rounding when casting from numeric +SELECT x, x::int4 AS int4_value +FROM (VALUES (-2.5::numeric), + (-1.5::numeric), + (-0.5::numeric), + (0.0::numeric), + (0.5::numeric), + (1.5::numeric), + (2.5::numeric)) t(x); + x | int4_value +--+ + -2.5 | -3 + -1.5 | -2 + -0.5 | -1 + 0.0 | 0 + 0.5 | 1 + 1.5 | 2 + 2.5 | 3 +(7 rows) + diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out index da8be51..ed0bd34 100644 --- a/src/test/regress/expected/int8.out +++ b/src/test/regress/expected/int8.out @@ -866,3 +866,23 @@ FROM (VALUES (-2.5::float8), 2.5 | 2 (7 rows) +-- check rounding when casting from numeric +SELECT x, x::int8 AS int8_value +FROM (VALUES (-2.5::numeric), + (-1.5::numeric), +
Re: [HACKERS] Rounding to even for numeric data type
On Sun, Mar 29, 2015 at 9:21 AM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 29/03/15 13:07, David G. Johnston wrote: On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier michael.paqu...@gmail.com mailto:michael.paqu...@gmail.comwrote: On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower gavinflo...@archidevsys.co.nz mailto:gavinflo...@archidevsys.co.nz wrote: On 28/03/15 21:58, Dean Rasheed wrote: [...] Andrew mentioned that there have been complaints from people doing calculations with monetary data that we don't implement round-to-nearest-even (Banker's) rounding. It's actually the case that various different financial calculations demand different specific rounding modes, so it wouldn't be enough to simply change the default - we would have to provide a choice of modes. [...] Could the 2 current round functions have cousins that included an extra char parameter (or string), that indicated the type of rounding? So we don't end up with an explosion of rounding functions, yet could cope with a limited set of additional rounding modes initially, and possibly others in the future. Instead of extending round, isn't what we are looking at here a new data type? I have doubts that we only want to have a way to switch round() between different modes. Hence, what we could do is: 1) Mention in the docs that numeric does round-half-away-from-zero 2) Add regression tests for numeric(n,m) and round(numeric) 3) Add a TODO item for something like numeric2, doing rounding-at-even (this could be an extension as well), but with the number of duplication that it may have with numeric, an in-core type would make sense, to facilitate things exposing some of structures key structures would help. So, create a numeric type for each possible rounding mode? That implies at least two types, round-half-even and round-half-away-from-zero, with suitable abbreviations: numeric_rhe, numeric_raz. The existing numeric now does half-up rounding. If the goal is to make plain numeric IEEE standard conforming then giving the user a way to switch all existing numeric types to numeric_raz would be nice. Implicit casts between each of the various numeric types would be needed and understandable. That's exactly the thing I think would be helpful. I'm pondering calling them numeric_eng and numeric_bus (for engineering and business respectively)... In Java, there are 8 rounding modes specified: https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html Some of these may be relevant to pg. That's interesting. I didn't recall those details. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On Sun, Mar 29, 2015 at 7:59 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 28/03/15 21:58, Dean Rasheed wrote: [...] Andrew mentioned that there have been complaints from people doing calculations with monetary data that we don't implement round-to-nearest-even (Banker's) rounding. It's actually the case that various different financial calculations demand different specific rounding modes, so it wouldn't be enough to simply change the default - we would have to provide a choice of modes. [...] Could the 2 current round functions have cousins that included an extra char parameter (or string), that indicated the type of rounding? So we don't end up with an explosion of rounding functions, yet could cope with a limited set of additional rounding modes initially, and possibly others in the future. Instead of extending round, isn't what we are looking at here a new data type? I have doubts that we only want to have a way to switch round() between different modes. Hence, what we could do is: 1) Mention in the docs that numeric does round-half-away-from-zero 2) Add regression tests for numeric(n,m) and round(numeric) 3) Add a TODO item for something like numeric2, doing rounding-at-even (this could be an extension as well), but with the number of duplication that it may have with numeric, an in-core type would make sense, to facilitate things exposing some of structures key structures would help. So, attached is a patch that does 1) and 2) to make clear to the user how numeric and double precision behave regarding rounding. I am adding it to CF 2015-06 to keep track of it... -- Michael From 21e2da3d8c480f28c2cb469a004dbc225a522725 Mon Sep 17 00:00:00 2001 From: Michael Paquier michael@otacoo.com Date: Sun, 29 Mar 2015 19:46:50 +0900 Subject: [PATCH] Precise rounding behavior of numeric and double precision in docs Regression tests improving the coverage in this area are added as well. --- doc/src/sgml/datatype.sgml| 18 ++ src/test/regress/expected/int2.out| 20 src/test/regress/expected/int4.out| 20 src/test/regress/expected/int8.out| 20 src/test/regress/expected/numeric.out | 24 src/test/regress/sql/int2.sql | 10 ++ src/test/regress/sql/int4.sql | 10 ++ src/test/regress/sql/int8.sql | 10 ++ src/test/regress/sql/numeric.sql | 10 ++ 9 files changed, 142 insertions(+) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index da1f25f..0342c8a 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -612,6 +612,24 @@ NUMERIC equivalent. Both types are part of the acronymSQL/acronym standard. /para + +para + With using the functionround/ function, the typenumeric/type + type rounds half-up, and the typedouble precision/ type half-even. + +programlisting +SELECT round(1.5::numeric), round(2.5::numeric); + round | round +---+--- + 2 | 3 +(1 row) +SELECT round(1.5::double precision), round(2.5::double precision); + round | round +---+--- + 2 | 2 +(1 row) +/programlisting +/para /sect2 diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out index 311fe73..3ea4ed9 100644 --- a/src/test/regress/expected/int2.out +++ b/src/test/regress/expected/int2.out @@ -286,3 +286,23 @@ FROM (VALUES (-2.5::float8), 2.5 | 2 (7 rows) +-- check rounding when casting from numeric +SELECT x, x::int2 AS int2_value +FROM (VALUES (-2.5::numeric), + (-1.5::numeric), + (-0.5::numeric), + (0.0::numeric), + (0.5::numeric), + (1.5::numeric), + (2.5::numeric)) t(x); + x | int2_value +--+ + -2.5 | -3 + -1.5 | -2 + -0.5 | -1 + 0.0 | 0 + 0.5 | 1 + 1.5 | 2 + 2.5 | 3 +(7 rows) + diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out index 83fe022..372fd4d 100644 --- a/src/test/regress/expected/int4.out +++ b/src/test/regress/expected/int4.out @@ -383,3 +383,23 @@ FROM (VALUES (-2.5::float8), 2.5 | 2 (7 rows) +-- check rounding when casting from numeric +SELECT x, x::int4 AS int4_value +FROM (VALUES (-2.5::numeric), + (-1.5::numeric), + (-0.5::numeric), + (0.0::numeric), + (0.5::numeric), + (1.5::numeric), + (2.5::numeric)) t(x); + x | int4_value +--+ + -2.5 | -3 + -1.5 | -2 + -0.5 | -1 + 0.0 | 0 + 0.5 | 1 + 1.5 | 2 + 2.5 | 3 +(7 rows) + diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out index
Re: [HACKERS] Rounding to even for numeric data type
MP == Michael Paquier michael.paqu...@gmail.com writes: MP So, attached is a patch that does 1) and 2) to make clear to the MP user how numeric and double precision behave regarding rounding. MP I am adding it to CF 2015-06 to keep track of it... Given that the examples show -2.5 rounds to -3, the IEEE term is roundTiesToAway, and the typical conversational english is round ties away from zero. RoundUp means mean towards +Infinity. 754 specifies that for decimal, either roundTiesToEven or roundTiesToAway are acceptable defaults, and which of the two applies is language dependent. Does ANSI SQL say anything about how numeric should round? In general, for decimals (or anything other than binary), there are twelve possible roundings: ToEven ToOdd AwayFromZero ToZero Up Down TiesToEven TiesToOdd TiesAwayFromZero TiesToZero TiesUp TiesDown (Up is the same as ceil(3), Down as floor(3).) -JimC -- James Cloos cl...@jhcloos.com OpenPGP: 0x997A9F17ED7DAEA6 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 28/03/15 21:58, Dean Rasheed wrote: [...] Andrew mentioned that there have been complaints from people doing calculations with monetary data that we don't implement round-to-nearest-even (Banker's) rounding. It's actually the case that various different financial calculations demand different specific rounding modes, so it wouldn't be enough to simply change the default - we would have to provide a choice of modes. [...] Could the 2 current round functions have cousins that included an extra char parameter (or string), that indicated the type of rounding? So we don't end up with an explosion of rounding functions, yet could cope with a limited set of additional rounding modes initially, and possibly others in the future. Instead of extending round, isn't what we are looking at here a new data type? I have doubts that we only want to have a way to switch round() between different modes. Hence, what we could do is: 1) Mention in the docs that numeric does round-half-away-from-zero 2) Add regression tests for numeric(n,m) and round(numeric) 3) Add a TODO item for something like numeric2, doing rounding-at-even (this could be an extension as well), but with the number of duplication that it may have with numeric, an in-core type would make sense, to facilitate things exposing some of structures key structures would help. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On 28/03/15 21:58, Dean Rasheed wrote: [...] Andrew mentioned that there have been complaints from people doing calculations with monetary data that we don't implement round-to-nearest-even (Banker's) rounding. It's actually the case that various different financial calculations demand different specific rounding modes, so it wouldn't be enough to simply change the default - we would have to provide a choice of modes. [...] Could the 2 current round functions have cousins that included an extra char parameter (or string), that indicated the type of rounding? So we don't end up with an explosion of rounding functions, yet could cope with a limited set of additional rounding modes initially, and possibly others in the future. Cheers, Gavin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On 29/03/15 13:07, David G. Johnston wrote: On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier michael.paqu...@gmail.com mailto:michael.paqu...@gmail.comwrote: On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower gavinflo...@archidevsys.co.nz mailto:gavinflo...@archidevsys.co.nz wrote: On 28/03/15 21:58, Dean Rasheed wrote: [...] Andrew mentioned that there have been complaints from people doing calculations with monetary data that we don't implement round-to-nearest-even (Banker's) rounding. It's actually the case that various different financial calculations demand different specific rounding modes, so it wouldn't be enough to simply change the default - we would have to provide a choice of modes. [...] Could the 2 current round functions have cousins that included an extra char parameter (or string), that indicated the type of rounding? So we don't end up with an explosion of rounding functions, yet could cope with a limited set of additional rounding modes initially, and possibly others in the future. Instead of extending round, isn't what we are looking at here a new data type? I have doubts that we only want to have a way to switch round() between different modes. Hence, what we could do is: 1) Mention in the docs that numeric does round-half-away-from-zero 2) Add regression tests for numeric(n,m) and round(numeric) 3) Add a TODO item for something like numeric2, doing rounding-at-even (this could be an extension as well), but with the number of duplication that it may have with numeric, an in-core type would make sense, to facilitate things exposing some of structures key structures would help. So, create a numeric type for each possible rounding mode? That implies at least two types, round-half-even and round-half-away-from-zero, with suitable abbreviations: numeric_rhe, numeric_raz. If the goal is to make plain numeric IEEE standard conforming then giving the user a way to switch all existing numeric types to numeric_raz would be nice. Implicit casts between each of the various numeric types would be needed and understandable. I'm pondering calling them numeric_eng and numeric_bus (for engineering and business respectively)... David J. In Java, there are 8 rounding modes specified: https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html Some of these may be relevant to pg. Cheers, Gavin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 28/03/15 21:58, Dean Rasheed wrote: [...] Andrew mentioned that there have been complaints from people doing calculations with monetary data that we don't implement round-to-nearest-even (Banker's) rounding. It's actually the case that various different financial calculations demand different specific rounding modes, so it wouldn't be enough to simply change the default - we would have to provide a choice of modes. [...] Could the 2 current round functions have cousins that included an extra char parameter (or string), that indicated the type of rounding? So we don't end up with an explosion of rounding functions, yet could cope with a limited set of additional rounding modes initially, and possibly others in the future. Instead of extending round, isn't what we are looking at here a new data type? I have doubts that we only want to have a way to switch round() between different modes. Hence, what we could do is: 1) Mention in the docs that numeric does round-half-away-from-zero 2) Add regression tests for numeric(n,m) and round(numeric) 3) Add a TODO item for something like numeric2, doing rounding-at-even (this could be an extension as well), but with the number of duplication that it may have with numeric, an in-core type would make sense, to facilitate things exposing some of structures key structures would help. So, create a numeric type for each possible rounding mode? That implies at least two types, round-half-even and round-half-away-from-zero, with suitable abbreviations: numeric_rhe, numeric_raz. If the goal is to make plain numeric IEEE standard conforming then giving the user a way to switch all existing numeric types to numeric_raz would be nice. Implicit casts between each of the various numeric types would be needed and understandable. I'm pondering calling them numeric_eng and numeric_bus (for engineering and business respectively)... David J.
Re: [HACKERS] Rounding to even for numeric data type
On Sat, Mar 28, 2015 at 5:58 PM, Dean Rasheed wrote: On 27 March 2015 at 23:26, Tom Lane wrote: I think the concern over backwards compatibility here is probably overblown; but if we're sufficiently worried about it, a possible compromise is to invent a numeric_rounding_mode GUC, so that people could get back the old behavior if they really care. Backwards compatibility is certainly one concern. Michael also mentioned compatibility with other databases, and its worth noting that Oracle, MySQL, DB2 and SQL Server all use the same default round-half-away-from-zero Schoolbook rounding mode in round() for their equivalents of numeric. Most of those other DBs are also careful to document exactly how round() behaves. To make our round() function do something different by default isn't going to make porting any easier. I was not aware of that, and that's really an interesting point. Thanks! It would indeed not be welcome for people migrating an application to Postgres if we behave differently from the others. Then, perhaps the solution would be to have this rounding GUC, but pointing by default to round-half-away-from-zero and not round-to-even as mentioned upthread already. Andrew mentioned that there have been complaints from people doing calculations with monetary data that we don't implement round-to-nearest-even (Banker's) rounding. It's actually the case that various different financial calculations demand different specific rounding modes, so it wouldn't be enough to simply change the default - we would have to provide a choice of modes. I also agree with Andrew that all numeric functions should be kept immutable. This looks like a plan. Honestly by reading this thread the thing that IMO we should not do is closing ourselves into a single mode of calculation. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
Dean Rasheed dean.a.rash...@gmail.com wrote: Sure, using round-to-nearest-even for intermediate rounding in complex numeric methods would be a good way to reduce (but not completely eliminate) rounding errors. But that's a somewhat different proposition from changing the default for round(), which is a much more user-visible change. If we did implement a choice of rounding modes, I would still argue for keeping round-half-away-from-zero as the default mode for round(). I'm inclined to agree. In business software development, that's how I've seen the stakeholder expectations. Thinking back, I can remember dealing with rounding in manufacturing incentive pay calculation, interfacing long-range demand forcasting to production planning, interfacing engineers' CAD/CAM software to IBM MAPICS, professionals' timekeeping/billing/AR systems, and various general accounting software systems; and as I seem to remember those efforts, round half away from zero has always been when end users understood and expected when explicitly rounding a final result. I understand how rounding half to even in intermediate results minimizes rounding error, and would not be surprised to see some users with different expectations, but there is clearly a large base of people who would be surprised by it when rounding a final result. I also agree with Andrew that all numeric functions should be kept immutable. Which means no GUC should affect how it behaves, although a function with a parameter to control rounding behavior would be OK. It kinda seems like the SQL round() function should have a parameter to control this which defaults to the historical behavior when omitted. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On 27 March 2015 at 23:26, Tom Lane t...@sss.pgh.pa.us wrote: Michael Paquier michael.paqu...@gmail.com writes: It sounds appealing to switch the default behavior to something that is more IEEE-compliant, and not only for scale == 0. Now one can argue as well that changing the default is risky for existing applications, or the other way around that other RDBMs (?) are more compliant than us for their equivalent numeric data type, and people get confused when switching to Postgres. An idea, from Dean, would be to have a new specific version for round() able to do compliant IEEE rounding to even as well... I think confining the change to round() would be a fundamental error. The main reason why round-to-nearest-even is IEEE standard is that it reduces error accumulation over long chains of calculations, such as in numeric's power and trig functions; if we go to the trouble of implementing such a behavior, we certainly want to use it there. Sure, using round-to-nearest-even for intermediate rounding in complex numeric methods would be a good way to reduce (but not completely eliminate) rounding errors. But that's a somewhat different proposition from changing the default for round(), which is a much more user-visible change. If we did implement a choice of rounding modes, I would still argue for keeping round-half-away-from-zero as the default mode for round(). I think the concern over backwards compatibility here is probably overblown; but if we're sufficiently worried about it, a possible compromise is to invent a numeric_rounding_mode GUC, so that people could get back the old behavior if they really care. Backwards compatibility is certainly one concern. Michael also mentioned compatibility with other databases, and its worth noting that Oracle, MySQL, DB2 and SQL Server all use the same default round-half-away-from-zero Schoolbook rounding mode in round() for their equivalents of numeric. Most of those other DBs are also careful to document exactly how round() behaves. To make our round() function do something different by default isn't going to make porting any easier. Andrew mentioned that there have been complaints from people doing calculations with monetary data that we don't implement round-to-nearest-even (Banker's) rounding. It's actually the case that various different financial calculations demand different specific rounding modes, so it wouldn't be enough to simply change the default - we would have to provide a choice of modes. I also agree with Andrew that all numeric functions should be kept immutable. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
On 28 March 2015 at 05:16, Andrew Gierth and...@tao11.riddles.org.uk wrote: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom I think the concern over backwards compatibility here is probably Tom overblown; but if we're sufficiently worried about it, a possible Tom compromise is to invent a numeric_rounding_mode GUC, so that Tom people could get back the old behavior if they really care. I only see one issue with this, but it's a nasty one: do we really want to make all numeric operations that might do rounding stable rather than immutable? Yeah, making all numeric functions non-immutable seems like a really bad idea. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Rounding to even for numeric data type
Hi all, A couple of days ago a bug has showed up regarding rounding of float here: http://www.postgresql.org/message-id/flat/20150320194337.2573.72...@wrigleys.postgresql.org#20150320194337.2573.72...@wrigleys.postgresql.org The result being that the version of rint() shipped in src/port was not IEEE compliant when rounding to even (MSVC 2013 at least using it), leading to inconsistent results depending on if the platform uses src/port's rint() or the platform's one. During this thread, Tom has raised as well that rounding for numeric is not that IEEE-compliant: http://www.postgresql.org/message-id/22366.1427313...@sss.pgh.pa.us For example: =# SELECT round(2.5::numeric), round(1.5::numeric), round(0.5::numeric), round(-2.5::numeric); round | round | round | round ---+---+---+--- 3 | 2 | 1 |-3 (1 row) =# SELECT round(2.5::float), round(1.5::float), round(0.5::float), round(-2.5::float); round | round | round | round ---+---+---+--- 2 | 2 | 0 |-2 (1 row) It sounds appealing to switch the default behavior to something that is more IEEE-compliant, and not only for scale == 0. Now one can argue as well that changing the default is risky for existing applications, or the other way around that other RDBMs (?) are more compliant than us for their equivalent numeric data type, and people get confused when switching to Postgres. An idea, from Dean, would be to have a new specific version for round() able to do compliant IEEE rounding to even as well... Opinions? Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
Michael Paquier michael.paqu...@gmail.com writes: It sounds appealing to switch the default behavior to something that is more IEEE-compliant, and not only for scale == 0. Now one can argue as well that changing the default is risky for existing applications, or the other way around that other RDBMs (?) are more compliant than us for their equivalent numeric data type, and people get confused when switching to Postgres. An idea, from Dean, would be to have a new specific version for round() able to do compliant IEEE rounding to even as well... I think confining the change to round() would be a fundamental error. The main reason why round-to-nearest-even is IEEE standard is that it reduces error accumulation over long chains of calculations, such as in numeric's power and trig functions; if we go to the trouble of implementing such a behavior, we certainly want to use it there. I think the concern over backwards compatibility here is probably overblown; but if we're sufficiently worried about it, a possible compromise is to invent a numeric_rounding_mode GUC, so that people could get back the old behavior if they really care. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rounding to even for numeric data type
Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom I think the concern over backwards compatibility here is probably Tom overblown; but if we're sufficiently worried about it, a possible Tom compromise is to invent a numeric_rounding_mode GUC, so that Tom people could get back the old behavior if they really care. I only see one issue with this, but it's a nasty one: do we really want to make all numeric operations that might do rounding stable rather than immutable? -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
