Re: [COMMITTERS] pgsql: Fix NUMERIC modulus to properly

From: Paul Tillotson <pntil(at)shentel(dot)net>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [COMMITTERS] pgsql: Fix NUMERIC modulus to properly
Date: 2005-06-07 01:26:21
Message-ID: 42A4F7BD.4060303@shentel.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers

Bruce Momjian wrote:

>Tom Lane wrote:
>
>
>>Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
>>
>>
>>>>No, I don't think so. It doesn't seem to be something that enough
>>>>people use to risk the change in behavior --- it might break something
>>>>that was working. But, if folks want it backported we can do it. It is
>>>>only a change to properly do modulus for numeric.
>>>>
>>>>
>>>Well, from my point of view it's an absolute mathematical error - i'd
>>>backport it. I can't see anyone relying on it :)
>>>
>>>
>>Doesn't this patch break the basic theorem that
>>
>> a = trunc(a / b) * b + (a mod b)
>>
>>? If division rounds and mod doesn't, you've got pretty serious issues.
>>
>>
>
>Well, this is a good question. In the equation above we assume '/' is
>an integer division. The problem with NUMERIC when used with zero-scale
>operands is that the result is already _rounded_ to the nearest hole
>number before it gets to trunc(), and that is why we used to get
>negative modulus values. I assume the big point is that we don't offer
>any way for users to get a NUMERIC division without rounding.
>
>With integers, we always round down to the nearest whole number on
>division; float doesn't offer a modulus operator, and C doesn't support
>it either.
>
>We round NUMERICs to the specific scale because we want to give the most
>accurate value:
>
> test=> select 100000000000000000000000::numeric(24,0) /
> 11::numeric(24,0);
> ?column?
> ------------------------
> 9090909090909090909091
>
>The actual values is:
> --
> 9090909090909090909090.90
>
>But the problem is that the equation at the top assumes the division is
>not rounded. Should we supply a NUMERIC division operator that doesn't
>round? integer doesn't need it, and float doesn't have the accurate
>precision needed for modulus operators. The user could supply some
>digits in the division:
>
> test=> select 100000000000000000000000::numeric(30,6) /
> 11::numeric(24,0);
> ?column?
> -------------------------------
> 9090909090909090909090.909091
> (1 row)
>
>but there really is no _right_ value to prevent rounding (think
>0.9999999). A non-rounding NUMERIC division would require duplicating
>numeric_div() but with a false for 'round', and adding operators.
>
>
>
I would prefer that division didn't round, as with integers. You can
always calculate your result to 1 more decimal place and then round, but
there is no way to unround a rounded result.

Tom had asked whether PG passed the regression tests if we change the
round_var() to a trunc_var() at the end of the function div_var().

It does not pass, but I think that is because the regression test is
expecting that division will round up. (Curiously, the regression test
for "numeric" passes, but the regression test for aggregation--sum() I
think--is the one that fails.) I have attached the diffs here if anyone
is interested.

Regards,
Paul Tillotson

Attachment Content-Type Size
regression.diffs text/plain 628 bytes
regression.out text/plain 3.1 KB

In response to

Responses

Browse pgsql-committers by date

  From Date Subject
Next Message Bruce Momjian 2005-06-07 01:36:41 pgsql: Ipcrm -> ipcclean in error message:
Previous Message Bruce Momjian 2005-06-06 22:12:01 pgsql: Add rtee box index discussion.

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2005-06-07 01:49:48 RESET CONNECTION behavior
Previous Message Joe Conway 2005-06-07 01:24:52 Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp.