Re: [HACKERS] Rounding to even for numeric data type

[Tom Lane]

Michael Paquier  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

[Fabien COELHO]

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

[Michael Paquier]

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 
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 SQL
  standard.
 
+
+
+ With using the round function, the numeric
+ type rounds ties away from zero, and the double precision
+ type rounds ties away to even.
+
+
+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)
+
+

 
 
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.
+SELECT i as pow,
+	round((-2.5 * 10 ^ i)::numeric, -i),
+	round((-1.5 * 10 ^ i)::numeric, -i),
+	round((-0

Re: [HACKERS] Rounding to even for numeric data type

[Fabien COELHO]

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

[Pedro Gimeno]

Dean Rasheed wrote, On 2015-03-28 10:01:
> On 28 March 2015 at 05:16, Andrew Gierth  wrote:
>>> "Tom" == Tom Lane  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]

On Wed, Apr 22, 2015 at 9:30 PM, Pedro Gimeno
 wrote:
> Dean Rasheed wrote, On 2015-03-28 10:01:
>> On 28 March 2015 at 05:16, Andrew Gierth  wrote:
 "Tom" == Tom Lane  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

[Albe Laurenz]

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

[Michael Paquier]

On Mon, Mar 30, 2015 at 4:51 AM, James Cloos  wrote:
>> "MP" == Michael Paquier  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 
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 SQL
  standard.
 
+
+
+ With using the round function, the numeric
+ type rounds ties away from zero, and the double precision
+ type rounds ties away to even.
+
+
+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)
+
+

 
 
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::

Re: [HACKERS] Rounding to even for numeric data type

[James Cloos]

> "MP" == Michael Paquier  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  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

[Michael Paquier]

On Sun, Mar 29, 2015 at 7:59 AM, Michael Paquier
 wrote:
> On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
>  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 
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 SQL
  standard.
 
+
+
+ With using the round function, the numeric
+ type rounds half-up, and the double precision type half-even.
+
+
+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)
+
+

 
 
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

Re: [HACKERS] Rounding to even for numeric data type

[Michael Paquier]

On Sun, Mar 29, 2015 at 9:21 AM, Gavin Flower
 wrote:
> On 29/03/15 13:07, David G. Johnston wrote:
>>
>> On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier
>> mailto:michael.paqu...@gmail.com>>wrote:
>>
>> On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
>> > > 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

[Gavin Flower]

On 29/03/15 13:07, David G. Johnston wrote:
On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier 
mailto:michael.paqu...@gmail.com>>wrote:


On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
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

[David G. Johnston]

On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier 
wrote:

> On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
>  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

[Michael Paquier]

On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
 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

[Gavin Flower]

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

[Kevin Grittner]

Dean Rasheed  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

[Michael Paquier]

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]

On 28 March 2015 at 05:16, Andrew Gierth  wrote:
>> "Tom" == Tom Lane  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

Re: [HACKERS] Rounding to even for numeric data type

[Dean Rasheed]

On 27 March 2015 at 23:26, Tom Lane  wrote:
> Michael Paquier  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

[Andrew Gierth]

> "Tom" == Tom Lane  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

Re: [HACKERS] Rounding to even for numeric data type

[Tom Lane]

Michael Paquier  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

[HACKERS] Rounding to even for numeric data type

[Michael Paquier]

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