From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | 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 truncate |
Date: | 2005-06-06 15:25:07 |
Message-ID: | 200506061525.j56FP7j13002@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers pgsql-hackers |
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.
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-06-06 16:29:01 | pgsql: Trivial markup improvement. |
Previous Message | KÖPFERL Robert | 2005-06-06 10:20:22 | Re: Returning a Cross Tab record set from a function |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2005-06-06 15:34:47 | Re: regexp_replace |
Previous Message | Jonah H. Harris | 2005-06-06 15:21:47 | Re: PGDN source browser |