Обсуждение: Question about the TODO, numerics, and division

Поиск
Список
Период
Сортировка

Question about the TODO, numerics, and division

От
Chris Travers
Дата:
Hi all;

I have been looking at the TODO and have found something that I find
sort of odd and we should probably reconsider:

One of the items under data types is:

    * Add NUMERIC division operator that doesn't round?

      Currently NUMERIC _rounds_ the result to the specified precision.
      This means division can return a result that multiplied by the
      divisor is greater than the dividend, e.g. this returns a value > 10:

SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;

This does not seem to me to be an division op issue but rather a simple
casting mistake.  Note that the result of 10/6 is cast as numeric(2,0)
and then multiplied by 6.

The following example shows that the problem is with the query and
casting, not with the division op:
SELECT ((10::numeric(2,0) / 6::numeric(2,0)) * 6)::numeric(2,0);
 numeric
---------
      10
(1 row)


Am I missing something?

Best Wishes,
Chris Travers



Вложения

Re: Question about the TODO, numerics, and division

От
Tom Lane
Дата:
Chris Travers <chris@verkiel.metatrontech.com> writes:
> I have been looking at the TODO and have found something that I find 
> sort of odd and we should probably reconsider:

> One of the items under data types is:

>     * Add NUMERIC division operator that doesn't round?

>       Currently NUMERIC _rounds_ the result to the specified precision.
>       This means division can return a result that multiplied by the
>       divisor is greater than the dividend, e.g. this returns a value > 10:
>       SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;

I agree that the TODO item is pretty bogus as worded.  A closer look
at what's going on is:

regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) ;     ?column?
--------------------1.6666666666666667
(1 row)

and of course if you multiply that by 6 you get

regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) * 6;     ?column?
---------------------10.0000000000000002
(1 row)

However this seems basically insoluble.  The TODO item seems to imagine
that it would be better if the division returned 1.6666666666666666,
but AFAICS that answer is actually *less* accurate:

regression=# select 1.6666666666666666 * 6;     ?column?
--------------------9.9999999999999996
(1 row)

regression=#

The only way to make it more accurate is to return more decimal places,
but you'll never get an exact result, because this is a nonterminating
fraction.

There may be a use for a division operator that rounds the last returned
digit towards minus infinity instead of to nearest, but the TODO entry
is utterly unconvincing as an argument for that.  Does anyone recall
what the original argument was for it?  Perhaps the TODO entry is
just mis-summarizing the discussion.

A separate question is whether the division operator chooses a good
default for the number of digits to return.  You can make it compute
more digits by increasing the scale values of the inputs:

regression=# SELECT (10::numeric(32,30) / 6::numeric(2,0)) ;            ?column?
----------------------------------1.666666666666666666666666666667
(1 row)

but I wouldn't want to defend the details of the rule about how many
fractional digits out given so many fractional digits in.
        regards, tom lane


Re: Question about the TODO, numerics, and division

От
Martijn van Oosterhout
Дата:
On Tue, Mar 20, 2007 at 08:27:46PM -0400, Tom Lane wrote:
> and of course if you multiply that by 6 you get
>
> regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) * 6;
>       ?column?
> ---------------------
>  10.0000000000000002
> (1 row)

The only way to fix that is to shift from fixed-point arithmetic to
something that manipulates rationals, where 10/6 can be represented
exactly.

Arithmatic on rationals is not terribly complicated, the real problem
comes with the fact that as the number of operations increase, your
numerator and denominator are going to tend to infinity in an attempt
to represent your number accuratly. That's probably a solved problem
too, but still...

Have  a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: Question about the TODO, numerics, and division

От
Gregory Stark
Дата:
"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> but I wouldn't want to defend the details of the rule about how many
> fractional digits out given so many fractional digits in.

In particular it seems to me this is poor:

postgres=# select 1::numeric/10;       ?column?        
------------------------0.10000000000000000000
(1 row)


In an ideal world it seems to me that if you're dividing by a number with only
factors of 2 and 5 you should only gain as many digits as necessary to
represent the result exactly.

At the very least if you're dividing by a power of ten we should just move the
decimal place and keep the same overall precision.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com


Re: Question about the TODO, numerics, and division

От
Bruce Momjian
Дата:
Here is the full TODO item:
* Add NUMERIC division operator that doesn't round?  Currently NUMERIC _rounds_ the result to the specified precision.
Thismeans division can return a result that multiplied by the  divisor is greater than the dividend, e.g. this returns
avalue > 10:    SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;  The positive modulus result returned by
NUMERICsmight be considered  inaccurate, in one sense.
 

The reason the TODO item was added is the last sentence, namely that you
can't use division to do a modulus.  This is fine:
test=> SELECT (10::numeric(2,0) / 6::numeric(2,0));      ?column?-------------------- 1.6666666666666667(1 row)

but this is where the rounding causes a problem:
test=> SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6; ?column?----------       12(1 row)

When casting to numeric(2,0), the value is rounded up to '2', not '1'. 
However, integer does the same thing:
test=> SELECT (1.66)::integer * 6; ?column?----------       12(1 row)

floor() works just fine:
test=> SELECT floor(10::numeric(2,0) / 6::numeric(2,0)) * 6; ?column?----------        6(1 row)

and there is a NUMERIC version of floor():
test=> \df floor                      List of functions   Schema   | Name  | Result data type | Argument data
types------------+-------+------------------+---------------------pg_catalog | floor | double precision | double
precisionpg_catalog | floor | numeric          | numeric(2 rows)
 

so I am removing the TODO item.  As you can see from the question mark
on the item, I was dubious of its validity.  It was added on 2005-06-25.

---------------------------------------------------------------------------

Tom Lane wrote:
> Chris Travers <chris@verkiel.metatrontech.com> writes:
> > I have been looking at the TODO and have found something that I find 
> > sort of odd and we should probably reconsider:
> 
> > One of the items under data types is:
> 
> >     * Add NUMERIC division operator that doesn't round?
> 
> >       Currently NUMERIC _rounds_ the result to the specified precision.
> >       This means division can return a result that multiplied by the
> >       divisor is greater than the dividend, e.g. this returns a value > 10:
> >       SELECT (10::numeric(2,0) / 6::numeric(2,0))::numeric(2,0) * 6;
> 
> I agree that the TODO item is pretty bogus as worded.  A closer look
> at what's going on is:
> 
> regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) ;
>       ?column?
> --------------------
>  1.6666666666666667
> (1 row)
> 
> and of course if you multiply that by 6 you get
> 
> regression=# SELECT (10::numeric(2,0) / 6::numeric(2,0)) * 6;
>       ?column?
> ---------------------
>  10.0000000000000002
> (1 row)
> 
> However this seems basically insoluble.  The TODO item seems to imagine
> that it would be better if the division returned 1.6666666666666666,
> but AFAICS that answer is actually *less* accurate:
> 
> regression=# select 1.6666666666666666 * 6;
>       ?column?
> --------------------
>  9.9999999999999996
> (1 row)
> 
> regression=#
> 
> The only way to make it more accurate is to return more decimal places,
> but you'll never get an exact result, because this is a nonterminating
> fraction.
> 
> There may be a use for a division operator that rounds the last returned
> digit towards minus infinity instead of to nearest, but the TODO entry
> is utterly unconvincing as an argument for that.  Does anyone recall
> what the original argument was for it?  Perhaps the TODO entry is
> just mis-summarizing the discussion.
> 
> A separate question is whether the division operator chooses a good
> default for the number of digits to return.  You can make it compute
> more digits by increasing the scale values of the inputs:
> 
> regression=# SELECT (10::numeric(32,30) / 6::numeric(2,0)) ;
>              ?column?
> ----------------------------------
>  1.666666666666666666666666666667
> (1 row)
> 
> but I wouldn't want to defend the details of the rule about how many
> fractional digits out given so many fractional digits in.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +