Fw: [pgadmin-support] (Bug) Numeric fault calculation - Mailing list pgsql-bugs

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