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
Sent: Wednesday, April 24, 2013 8:34 PM
To: Kanitchet Vaiassava
Subject: Re: ***(Updated)*** Re: [BUGS] Fw: [pgadmin-support] (Bug) Numeric
fault calculation
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 <kanic...@hotmail.com>
To: kgri...@ymail.com
Sent: Tuesday, April 23, 2013 9:46 PM
Subject: ***(Updated)*** Re: [BUGS] Fw: [pgadmin-support] (Bug) Numeric fault
calculation
Dear Mr.Kevin Grittner
First, sorry for another send on this. I’ve 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.
These features have several implications for numeric operations and provide a
high degree of compliance with standard SQL:
PostgreSQL server
for postgrsql may treat as numeric to numeric calculation
1/3 = 0.333333
0.33333 * 3 = 0.99999999
#1
PostgreSQL
SELECT (3.00::numeric * (1.00::numeric /3.00::numeric))
Result is : 0.9999999999999999999900
#2
PostgreSQL (However, I don’t 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’re using library fixed-point
arithmetic like MySQL and its not cause development time so much,
please consider this.
Thank you
Best Regards,
Kanitchet Vaiassava
ThaiAce Capital Co., Ltd
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
Sent: Wednesday, April 24, 2013 8:50 AM
To: Kevin Grittner
Cc: Kanitchet Vaiassava ; Kanitchet Vaiassava
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
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
Sent: Wednesday, April 24, 2013 3:46 AM
To: Kanitchet Vaiassava ; pgsql-bugs@postgresql.org
Cc: Kanitchet Vaiassava
Subject: Re: [BUGS] Fw: [pgadmin-support] (Bug) Numeric fault calculation
Kanitchet Vaiassava <kanic...@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=# select '1'::numeric / '3'::numeric;
?column?
------------------------
0.33333333333333333333
(1 row)
test=# select '3'::numeric * ('1'::numeric / '3'::numeric);
?column?
------------------------
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.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
