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);
  }
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to