sent to pgsql-bugs list.
Best Regards,
Kanitchet Vaiassava
999 Nawamin Rd., Nuanjun, Buengkum, Bangkok 10230, Thailand
Mobile +66 89 515 9955; Office +66 2 944 2000 Ext.1204; Fax +66 2 944 =
2020
-------------------------------------------------------------------------=
--------------------------------------
From: Kevin Grittner=20
Sent: Wednesday, April 24, 2013 8:34 PM
To: Kanitchet Vaiassava=20
Subject: Re: ***(Updated)*** Re: [BUGS] Fw: [pgadmin-support] (Bug) =
Numeric fault calculation=20
Hi Kanitchet,
It is best to keep the list copied so that everyone is aware of these =
things. Could you please resend with a copy to the list?
Thanks,
-Kevin
-------------------------------------------------------------------------=
-----
From: Kanitchet Vaiassava <kanichet@hotmail.com>
To: kgrittn@ymail.com=20
Sent: Tuesday, April 23, 2013 9:46 PM
Subject: ***(Updated)*** Re: [BUGS] Fw: [pgadmin-support] (Bug) =
Numeric fault calculation=20
Dear Mr.Kevin Grittner
First, sorry for another send on this. I=E2=80=99ve tried this on =
MySQL server
1# SELECT CAST((3.00 * (1.00/3.00)) AS DECIMAL(15,10))
2# SELECT CAST(3.00 AS DECIMAL) * (CAST(1.00 AS DECIMAL) / CAST(3.00 =
AS DECIMAL))
Result is (same) : 1.0000000000
http://dev.mysql.com/doc/refman/5.5/en/precision-math.html
> The MySQL library for fixed-point arithmetic.=20
These features have several implications for numeric operations and =
provide a high degree of compliance with standard SQL:=20
PostgreSQL server
for postgrsql may treat as numeric to numeric calculation
1/3 =3D 0.333333
0.33333 * 3 =3D 0.99999999
#1
PostgreSQL
SELECT (3.00::numeric * (1.00::numeric /3.00::numeric))
Result is : 0.9999999999999999999900
#2
PostgreSQL (However, I don=E2=80=99t know if this should be =
1.0000000000 or maybe postgres auto cast 3.00 to 3.00::numeric)
SELECT (3.00 * (1.00 /3.00))::numeric
Result is : 0.9999999999999999999900
I knows the demand may not sufficient but if we=E2=80=99re using =
library fixed-point arithmetic like MySQL and its not cause development =
time so much,=20
please consider this.
Thank you
Best Regards,
Kanitchet Vaiassava
ThaiAce Capital Co., Ltd=20
999 Nawamin Rd., Nuanjun, Buengkum, Bangkok 10230, Thailand
Mobile +66 89 515 9955; Office +66 2 944 2000 Ext.1204; Fax +66 2 944 =
2020
=
-------------------------------------------------------------------------=
--------------------------------------
From: Kanitchet Vaiassava=20
Sent: Wednesday, April 24, 2013 8:50 AM
To: Kevin Grittner=20
Cc: Kanitchet Vaiassava ; Kanitchet Vaiassava=20
Subject: Re: [BUGS] Fw: [pgadmin-support] (Bug) Numeric fault =
calculation
Dear Mr.Kevin Grittner
Thank you very much for your quick reply with clearly =
explanation and useful suggestion.
I hope this can be done someway in the future because I think it =
affected when we use postgresql to do some calculation about monetary
by using SQL or Stored Procedure (that I affected and more when the =
money is lage) and maybe affected some critical scientific calculation =
that needed to use many multiply & division or others math function that =
may result to this problem.
Sorry for bad English.
Best Regards,
Kanitchet Vaiassava
ThaiAce Capital Co., Ltd=20
999 Nawamin Rd., Nuanjun, Buengkum, Bangkok 10230, Thailand
Mobile +66 89 515 9955; Office +66 2 944 2000 Ext.1204; Fax +66 2 944 =
2020
=
-------------------------------------------------------------------------=
--------------------------------------
From: Kevin Grittner=20
Sent: Wednesday, April 24, 2013 3:46 AM
To: Kanitchet Vaiassava ; pgsql-bugs@postgresql.org=20
Cc: Kanitchet Vaiassava=20
Subject: Re: [BUGS] Fw: [pgadmin-support] (Bug) Numeric fault =
calculation
Kanitchet Vaiassava <kanichet@hotmail.com> wrote:
> [division and some math functions using the numeric type can
> sometimes have a result which the numeric type cannot represent
> exactly]
Yeah, you can use a simpler example:
test=3D# select '1'::numeric / '3'::numeric;
?column? =20
------------------------
0.33333333333333333333
(1 row)
test=3D# select '3'::numeric * ('1'::numeric / '3'::numeric);
?column? =20
------------------------
0.99999999999999999999
(1 row)
> So I think this problem should be solve? or at least, it should
> be note in document for other developer to be more careful.
Yeah, there should probably be something in the docs to indicate
that not all rational numbers (and certainly no irrational or
imaginary numbers) can be stored as a single numeric value without
loss of precision.
It might be interesting to create a "rational" type which would
internally hold two numeric values, and which would be capable of
doing what you want. I'm not sure that the demand is sufficient to
back the development of it, though. (Is there a convention for how
to indicate the repeating part of a decimal fraction when you can't
draw a line over those digits?)
In the absence of such a type, you might want to arrange your
calculations such that any division or square root calculations
are done last. That way the effect of the truncation of the
repeating (or not) infinite decimal fraction won't be multiplied by
a later phase of the calculation.
--=20
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company