Re: [GENERAL] numeric precision when raising one numeric to another. - Mailing list pgsql-patches

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > On Mon, May 30, 2005 at 11:29:48PM -0400, Bruce Momjian wrote:
> >
> > >     test=> select 12345678901234567890 / 123;
> > >           ?column?
> > >     --------------------
> > >      100371373180768845
> > >     (1 row)
> >
> > Well, that's a bug, right?
>
> I don't think so.  The fuller answer is
> 100371373180768844.63414634146341463414, and that rounded to the nearest
> integer is 100371373180768845.  I think people expect % do to that,
> except for integers.  You could argue that numerics with zero scale are
> integers, but NUMERIC % NUMERIC doesn't behave like an integer operator
> --- it rounds to the proper precision.

Attached is a fix for the problem:

    test=> select 12345678901234567890 % 123;
     ?column?
    ----------
           78
    (1 row)

I could have just forced X digits after the decimal point, but there was
really no _right_ number of digits, so it would have been a hack.  The
proper solution is to change div_var to accept 'round' as true/false,
and that's what I did in the patch.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/backend/utils/adt/numeric.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/numeric.c,v
retrieving revision 1.83
diff -c -c -r1.83 numeric.c
*** src/backend/utils/adt/numeric.c    6 Apr 2005 23:56:07 -0000    1.83
--- src/backend/utils/adt/numeric.c    4 Jun 2005 13:54:58 -0000
***************
*** 265,271 ****
  static void mul_var(NumericVar *var1, NumericVar *var2, NumericVar *result,
          int rscale);
  static void div_var(NumericVar *var1, NumericVar *var2, NumericVar *result,
!         int rscale);
  static int    select_div_scale(NumericVar *var1, NumericVar *var2);
  static void mod_var(NumericVar *var1, NumericVar *var2, NumericVar *result);
  static void ceil_var(NumericVar *var, NumericVar *result);
--- 265,271 ----
  static void mul_var(NumericVar *var1, NumericVar *var2, NumericVar *result,
          int rscale);
  static void div_var(NumericVar *var1, NumericVar *var2, NumericVar *result,
!         int rscale, bool round);
  static int    select_div_scale(NumericVar *var1, NumericVar *var2);
  static void mod_var(NumericVar *var1, NumericVar *var2, NumericVar *result);
  static void ceil_var(NumericVar *var, NumericVar *result);
***************
*** 906,919 ****
          sub_var(&operand_var, &bound1_var, &operand_var);
          sub_var(&bound2_var, &bound1_var, &bound2_var);
          div_var(&operand_var, &bound2_var, result_var,
!                 select_div_scale(&operand_var, &bound2_var));
      }
      else
      {
          sub_var(&bound1_var, &operand_var, &operand_var);
          sub_var(&bound1_var, &bound2_var, &bound1_var);
          div_var(&operand_var, &bound1_var, result_var,
!                 select_div_scale(&operand_var, &bound1_var));
      }

      mul_var(result_var, count_var, result_var,
--- 906,919 ----
          sub_var(&operand_var, &bound1_var, &operand_var);
          sub_var(&bound2_var, &bound1_var, &bound2_var);
          div_var(&operand_var, &bound2_var, result_var,
!                 select_div_scale(&operand_var, &bound2_var), true);
      }
      else
      {
          sub_var(&bound1_var, &operand_var, &operand_var);
          sub_var(&bound1_var, &bound2_var, &bound1_var);
          div_var(&operand_var, &bound1_var, result_var,
!                 select_div_scale(&operand_var, &bound1_var), true);
      }

      mul_var(result_var, count_var, result_var,
***************
*** 1266,1272 ****
      /*
       * Do the divide and return the result
       */
!     div_var(&arg1, &arg2, &result, rscale);

      res = make_result(&result);

--- 1266,1272 ----
      /*
       * Do the divide and return the result
       */
!     div_var(&arg1, &arg2, &result, rscale, true);

      res = make_result(&result);

***************
*** 2246,2252 ****
      {
          mul_var(&vN, &vNminus1, &vNminus1, 0);    /* N * (N - 1) */
          rscale = select_div_scale(&vsumX2, &vNminus1);
!         div_var(&vsumX2, &vNminus1, &vsumX, rscale);    /* variance */

          res = make_result(&vsumX);
      }
--- 2246,2252 ----
      {
          mul_var(&vN, &vNminus1, &vNminus1, 0);    /* N * (N - 1) */
          rscale = select_div_scale(&vsumX2, &vNminus1);
!         div_var(&vsumX2, &vNminus1, &vsumX, rscale, true);    /* variance */

          res = make_result(&vsumX);
      }
***************
*** 2322,2328 ****
      {
          mul_var(&vN, &vNminus1, &vNminus1, 0);    /* N * (N - 1) */
          rscale = select_div_scale(&vsumX2, &vNminus1);
!         div_var(&vsumX2, &vNminus1, &vsumX, rscale);    /* variance */
          sqrt_var(&vsumX, &vsumX, rscale);        /* stddev */

          res = make_result(&vsumX);
--- 2322,2328 ----
      {
          mul_var(&vN, &vNminus1, &vNminus1, 0);    /* N * (N - 1) */
          rscale = select_div_scale(&vsumX2, &vNminus1);
!         div_var(&vsumX2, &vNminus1, &vsumX, rscale, true);    /* variance */
          sqrt_var(&vsumX, &vsumX, rscale);        /* stddev */

          res = make_result(&vsumX);
***************
*** 3840,3846 ****
   */
  static void
  div_var(NumericVar *var1, NumericVar *var2, NumericVar *result,
!         int rscale)
  {
      int            div_ndigits;
      int            res_sign;
--- 3840,3846 ----
   */
  static void
  div_var(NumericVar *var1, NumericVar *var2, NumericVar *result,
!         int rscale, bool round)
  {
      int            div_ndigits;
      int            res_sign;
***************
*** 4079,4086 ****
      result->sign = res_sign;

      /* Round to target rscale (and set result->dscale) */
!     round_var(result, rscale);
!
      /* Strip leading and trailing zeroes */
      strip_var(result);
  }
--- 4079,4089 ----
      result->sign = res_sign;

      /* Round to target rscale (and set result->dscale) */
!     if (round)
!         round_var(result, rscale);
!     else
!         trunc_var(result, rscale);
!
      /* Strip leading and trailing zeroes */
      strip_var(result);
  }
***************
*** 4178,4184 ****
       */
      rscale = select_div_scale(var1, var2);

!     div_var(var1, var2, &tmp, rscale);

      trunc_var(&tmp, 0);

--- 4181,4187 ----
       */
      rscale = select_div_scale(var1, var2);

!     div_var(var1, var2, &tmp, rscale, false);

      trunc_var(&tmp, 0);

***************
*** 4294,4300 ****

      for (;;)
      {
!         div_var(&tmp_arg, result, &tmp_val, local_rscale);

          add_var(result, &tmp_val, result);
          mul_var(result, &const_zero_point_five, result, local_rscale);
--- 4297,4303 ----

      for (;;)
      {
!         div_var(&tmp_arg, result, &tmp_val, local_rscale, true);

          add_var(result, &tmp_val, result);
          mul_var(result, &const_zero_point_five, result, local_rscale);
***************
*** 4384,4390 ****

      /* Compensate for input sign, and round to requested rscale */
      if (xneg)
!         div_var(&const_one, result, result, rscale);
      else
          round_var(result, rscale);

--- 4387,4393 ----

      /* Compensate for input sign, and round to requested rscale */
      if (xneg)
!         div_var(&const_one, result, result, rscale, true);
      else
          round_var(result, rscale);

***************
*** 4450,4456 ****
          add_var(&ni, &const_one, &ni);
          mul_var(&xpow, &x, &xpow, local_rscale);
          mul_var(&ifac, &ni, &ifac, 0);
!         div_var(&xpow, &ifac, &elem, local_rscale);

          if (elem.ndigits == 0)
              break;
--- 4453,4459 ----
          add_var(&ni, &const_one, &ni);
          mul_var(&xpow, &x, &xpow, local_rscale);
          mul_var(&ifac, &ni, &ifac, 0);
!         div_var(&xpow, &ifac, &elem, local_rscale, true);

          if (elem.ndigits == 0)
              break;
***************
*** 4534,4540 ****
       */
      sub_var(&x, &const_one, result);
      add_var(&x, &const_one, &elem);
!     div_var(result, &elem, result, local_rscale);
      set_var_from_var(result, &xx);
      mul_var(result, result, &x, local_rscale);

--- 4537,4543 ----
       */
      sub_var(&x, &const_one, result);
      add_var(&x, &const_one, &elem);
!     div_var(result, &elem, result, local_rscale, true);
      set_var_from_var(result, &xx);
      mul_var(result, result, &x, local_rscale);

***************
*** 4544,4550 ****
      {
          add_var(&ni, &const_two, &ni);
          mul_var(&xx, &x, &xx, local_rscale);
!         div_var(&xx, &ni, &elem, local_rscale);

          if (elem.ndigits == 0)
              break;
--- 4547,4553 ----
      {
          add_var(&ni, &const_two, &ni);
          mul_var(&xx, &x, &xx, local_rscale);
!         div_var(&xx, &ni, &elem, local_rscale, true);

          if (elem.ndigits == 0)
              break;
***************
*** 4614,4620 ****
      /* Select scale for division result */
      rscale = select_div_scale(&ln_num, &ln_base);

!     div_var(&ln_num, &ln_base, result, rscale);

      free_var(&ln_num);
      free_var(&ln_base);
--- 4617,4623 ----
      /* Select scale for division result */
      rscale = select_div_scale(&ln_num, &ln_base);

!     div_var(&ln_num, &ln_base, result, rscale, true);

      free_var(&ln_num);
      free_var(&ln_base);
***************
*** 4752,4758 ****
              round_var(result, rscale);
              return;
          case -1:
!             div_var(&const_one, base, result, rscale);
              return;
          case 2:
              mul_var(base, base, result, rscale);
--- 4755,4761 ----
              round_var(result, rscale);
              return;
          case -1:
!             div_var(&const_one, base, result, rscale, true);
              return;
          case 2:
              mul_var(base, base, result, rscale);
***************
*** 4790,4796 ****

      /* Compensate for input sign, and round to requested rscale */
      if (neg)
!         div_var(&const_one, result, result, rscale);
      else
          round_var(result, rscale);
  }
--- 4793,4799 ----

      /* Compensate for input sign, and round to requested rscale */
      if (neg)
!         div_var(&const_one, result, result, rscale, true);
      else
          round_var(result, rscale);
  }