Money type does not detect over/underflow unlike int/int8
Hi developers.
int/int8 detects over/underflow.
====================
yohgaki@[local] ~=> SELECT '999999999999999999999'::int8;
ERROR: 22003: value "999999999999999999999" is out of range for type bigint
行 1: SELECT '999999999999999999999'::int8;
^
LOCATION: scanint8, int8.c:115
時間: 0.176 ms
====================
However, money type does not detect over/underflow and results in strange
results.
Works within range (Tested with 9.5 and 9.6)
====================
yohgaki@[local] ~=> SELECT '9999999'::money;
money
---------------
$9,999,999.00
(1 行)
時間: 0.209 ms
====================
Strange results with out of range (Tested with 9.5 and 9.6)
====================
yohgaki@[local] ~=> SELECT '999999999999999999999'::money;
money
---------------------------
$2,003,764,205,206,895.64
(1 行)
時間: 0.149 ms
yohgaki@[local] ~=> SELECT '9999999999999999999999999999999999999'::money;
money
----------------------------
$68,739,955,140,067,327.00
(1 行)
時間: 0.197 ms
yohgaki@[local] ~=> SELECT
'9999999999999999999999999999999999999999999999999999999'::money;
money
----------------------------
$53,322,619,588,066,671.64
(1 行)
時間: 0.144 ms
yohgaki@[local] ~=> SELECT
'99999999999999999999999999999999999999999999999999'::money;
money
-----------------------------
-$26,616,273,797,759,632.36
(1 行)
時間: 0.226 ms
====================
--
Yasuo Ohgaki
yohgaki@ohgaki.net
On 3/31/17 19:03, Yasuo Ohgaki wrote:
However, money type does not detect over/underflow and results in
strange results.
This has been fixed in PostgreSQL 10. Please test.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
On 3/31/17 19:03, Yasuo Ohgaki wrote:
However, money type does not detect over/underflow and results in
strange results.
This has been fixed in PostgreSQL 10. Please test.
I think the only thing that's actually gotten done is fixing cash_in
to reject out-of-range input values. Somebody ought to fix all the
money arithmetic functions to notice overflow, too; though I'm not
very clear what would be a good strategy in places like cash_mul_flt8,
where you couldn't count on an exact result.
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
At Tue, 04 Apr 2017 12:52:47 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote in <18086.1491324767@sss.pgh.pa.us>
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
On 3/31/17 19:03, Yasuo Ohgaki wrote:
However, money type does not detect over/underflow and results in
strange results.This has been fixed in PostgreSQL 10. Please test.
I think the only thing that's actually gotten done is fixing cash_in
to reject out-of-range input values. Somebody ought to fix all the
money arithmetic functions to notice overflow, too; though I'm not
very clear what would be a good strategy in places like cash_mul_flt8,
where you couldn't count on an exact result.
I suppose that the result of them is not precisely defined, so
just converting the float into int64 will be enough, of course
after some range check. Anyway the result also cannot exceed the
limit of int64. After that, cheking overflow is a simple job.
# than handling signed values split into two parts... maybe.
Roughly something like this.
cash_mul_flt8(PG_FUNCTION_ARGS)
{
Cash c = PG_GETARG_CASH(0);
float8 f = PG_GETARG_FLOAT8(1);
Cash result;
if (f >= CASH_MAX / 100 || f <= CASH_MIN / 100)
ereport("out of range");
result = (Cash)f * 100;
if ((c > 0 && CASH_MAX / c <= result) ||
(c < 0 && CASH_MIN / c >= result))
ereport("overflow");
result *= c;
PG_RETURN_CASH(result);
}
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
i wrote:
result = (Cash)f * 100;
if ((c > 0 && CASH_MAX / c <= result) ||
(c < 0 && CASH_MIN / c >= result))
ereport("overflow");
ah... this is still wrong. Will be different not so much, though.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center