Fw: [pgadmin-support] (Bug) Numeric fault calculation

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 <kanichet(at)hotmail(dot)com>
To: kgrittn(at)ymail(dot)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(at)postgresql(dot)org
Cc: Kanitchet Vaiassava
Subject: Re: [BUGS] Fw: [pgadmin-support] (Bug) Numeric fault calculation

Kanitchet Vaiassava <kanichet(at)hotmail(dot)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