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);
}
