Обсуждение: numeric precision when raising one numeric to another.

It appears from checking the output of exponentiation of one numeric to
another, the output is actually in floating point.  Is this normal and /
or expected?

Now, given that

create table test2 (i1 numeric(20,0), i2 numeric(20,0));
insert into test values (123456789012345,123456789012345);
select i1*i2 from test2;
gives:
           ?column?
-------------------------------
 15241578753238669120562399025

it seems odd that

create table test (i1 numeric(20,0), i2 numeric(20,0));
insert into test values (2,55);
select i1^i2 from test;
gives:
      ?column?
---------------------
 3.6028797018964e+16

Now, I can get an exact answer if I'm willing to twiddle with breaking
the exponent down:

select (2^60)::numeric;

Gives:
       numeric
---------------------
 1152921504606850000

While, select (2^30)::numeric*(2^30)::numeric;

Gives:
      ?column?
---------------------
 1152921504606846976

So, numeric can hold the value, but it looks like the exponent math is
converting it to float.

I'm not bothered too much by it, as I don't really work with numbers
that big.  I was mainly wondering if this is kosher is all.

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> It appears from checking the output of exponentiation of one numeric to
> another, the output is actually in floating point.  Is this normal and /
> or expected?

Yes, seeing that the only ^ operator we have is float8.

regression=# \do ^
                                         List of operators
   Schema   | Name |  Left arg type   |  Right arg type  |   Result type    |
  Description
------------+------+------------------+------------------+------------------+----------------------
 pg_catalog | ^    | double precision | double precision | double precision | exponentiation (x^y)
(1 row)

            regards, tom lane

On Wed, 2005-05-18 at 16:42, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > It appears from checking the output of exponentiation of one numeric to
> > another, the output is actually in floating point.  Is this normal and /
> > or expected?
>
> Yes, seeing that the only ^ operator we have is float8.
>
> regression=# \do ^
>                                          List of operators
>    Schema   | Name |  Left arg type   |  Right arg type  |   Result type    |
>   Description
> ------------+------+------------------+------------------+------------------+----------------------
>  pg_catalog | ^    | double precision | double precision | double precision | exponentiation (x^y)
> (1 row)

But is this proper behaviour?

Considering that the SQL spec says the result of multiplication of exact
numeric types is exact numeric types of precision S1+S2, and
exponentiation is nothing more than repeated multiplication, should
postgresql have a numeric capable exponentiation operator?  Since I've
finally got a job where I can actually hack on the clock a bit, it might
be a nice trial balloon.  It'll take a week or two to knock the rust off
my C skills though.  :)

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> Considering that the SQL spec says the result of multiplication of exact
> numeric types is exact numeric types of precision S1+S2, and
> exponentiation is nothing more than repeated multiplication,

... not when the exponent is non-integral.

            regards, tom lane

>> Considering that the SQL spec says the result of multiplication of
>> exact
>> numeric types is exact numeric types of precision S1+S2, and
>> exponentiation is nothing more than repeated multiplication,
>
> ... not when the exponent is non-integral.

For one thing.  For another, I believe the standard C library only has
floating point exponentiation functions, not that there aren't plenty
of numeric libraries with integral ones.  Finally, exponentiated
numbers get real big, real fast, and the floating point types can hold
much larger magnitudes than the integer types, albeit inexactly.  For
example, on the Mac I'm using now, long long ints max out at about
10^19, while long doubles can represent 10^308.

- John Burger
   MITRE

On Wed, May 18, 2005 at 10:46:50PM -0400, John Burger wrote:
> >>Considering that the SQL spec says the result of multiplication of
> >>exact
> >>numeric types is exact numeric types of precision S1+S2, and
> >>exponentiation is nothing more than repeated multiplication,
> >
> >... not when the exponent is non-integral.
>
> For one thing.  For another, I believe the standard C library only has
> floating point exponentiation functions, not that there aren't plenty
> of numeric libraries with integral ones.  Finally, exponentiated
> numbers get real big, real fast, and the floating point types can hold
> much larger magnitudes than the integer types, albeit inexactly.  For
> example, on the Mac I'm using now, long long ints max out at about
> 10^19, while long doubles can represent 10^308.

Well, we already have an interesting library of mathematical functions
for NUMERIC (which is an arbitrary precision type, so it wouldn't matter
how big the result would get).  I think the only reason we don't have a
NUMERIC exponentiation function is that nobody has implemented it.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"People get annoyed when you try to debug them."  (Larry Wall)

On Wed, May 18, 2005 at 11:32:40PM -0400, Alvaro Herrera wrote:
> On Wed, May 18, 2005 at 10:46:50PM -0400, John Burger wrote:
> > For one thing.  For another, I believe the standard C library only has
> > floating point exponentiation functions, not that there aren't plenty
> > of numeric libraries with integral ones.  Finally, exponentiated
> > numbers get real big, real fast, and the floating point types can hold
> > much larger magnitudes than the integer types, albeit inexactly.  For
> > example, on the Mac I'm using now, long long ints max out at about
> > 10^19, while long doubles can represent 10^308.
>
> Well, we already have an interesting library of mathematical functions
> for NUMERIC (which is an arbitrary precision type, so it wouldn't matter
> how big the result would get).  I think the only reason we don't have a
> NUMERIC exponentiation function is that nobody has implemented it.

The prerequisites for such a function would be a log() and exp()
function for numeric. And the real question there would be, what's a
sufficient accuracy? Numbers people actually use rarely have even
rational logarithms, so there is no way to store them 100% accurate.

As long as you're using integral exponents you can get away with
multiplication. BTW, the commandline utility "bc" has arbitrary number
arithmatic, maybe we can see how they do it? It defaults to 20 digits
precision, which is obviously not enough for large exponents.

Hmm, it looks like even they don't support raising to fractional
powers. When calculating 2^100, you need a precision of at least 35
decimal places to get in the ballpark of the correct figure using
log/exp, 30 isn't enough. Maybe do exact for integer exponents and
approx for non-integer?

kleptog@vali:~$ bc -l
bc 1.06
Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc.
> 2^100
1267650600228229401496703205376
> 2^100.1
Runtime warning (func=(main), adr=11): non-zero scale in exponent
1267650600228229401496703205376
> e(l(2)*100)
1267650600228229400579922894637.90158245154400629512
> scale=30
> e(l(2)*100)
1267650600228229401496703205353.617337311111135194699059124092
> scale=35
> e(l(2)*100)
1267650600228229401496703205375.99897630874075350752485091801369515

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

PostgreSQL has a numeric exp() function and a numeric ln() function, so
a numeric pow() function is trivial.

pow(A,z) = exp(z*ln(A))

Probably, it could be made a bit more efficient if specially tuned so as
to not require these functions.

Newton's method (or something of that nature) could obviously be used to
write a more generic version.  The double C function can provide the
starting estimate.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Alvaro Herrera
> Sent: Wednesday, May 18, 2005 8:33 PM
> To: John Burger
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] numeric precision when raising one numeric to
> another.
>
> On Wed, May 18, 2005 at 10:46:50PM -0400, John Burger wrote:
> > >>Considering that the SQL spec says the result of multiplication of
> > >>exact
> > >>numeric types is exact numeric types of precision S1+S2, and
> > >>exponentiation is nothing more than repeated multiplication,
> > >
> > >... not when the exponent is non-integral.
> >
> > For one thing.  For another, I believe the standard C library only
has
> > floating point exponentiation functions, not that there aren't
plenty
> > of numeric libraries with integral ones.  Finally, exponentiated
> > numbers get real big, real fast, and the floating point types can
hold
> > much larger magnitudes than the integer types, albeit inexactly.
For
> > example, on the Mac I'm using now, long long ints max out at about
> > 10^19, while long doubles can represent 10^308.
>
> Well, we already have an interesting library of mathematical functions
> for NUMERIC (which is an arbitrary precision type, so it wouldn't
matter
> how big the result would get).  I think the only reason we don't have
a
> NUMERIC exponentiation function is that nobody has implemented it.
>
> --
> Alvaro Herrera (<alvherre[a]surnet.cl>)
> "People get annoyed when you try to debug them."  (Larry Wall)
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org

We use Moshier's excellent qfloat numbers.
http://www.moshier.net/qlib.zip
Documentation:
http://www.moshier.net/qlibdoc.html

So, if you do the following query using CONNX:
select convert(pow(9.5,5.9), varchar)
You will get:
586906.97548405202106027547827738573075504470845684721318303336760202394
5916438064873363100477233500417619

select pow(9.5,5.9)
will return
586906.975484052
Since we bind to double by default.

Correct answer is (1000+ digits correct):
586906.97548405202106027547827738573075504470845684721318303336760202394
591643806487336310047723350041762446340060298807517843626920535883745120
986264188881010308125070048988991029963307831015812131852033741567043945
026243178422915290830477381800527219457732229115168020868495354958648414
971711685840852684310130094029132142016389076807514261122763703528030232
527888410105794936941873557344173381053429729906642653004811669321631656
412265025095200907690509153627646726650174318576911125609483654656735531
730688699016039020145753010069585349923506043259767525488453544723589880
427675085429230106535405724821481118286775763085905255396545439080913364
233329975992733986721408870779427889446166143315004295671202526112889352
043403059958082573333911277403826735005243749050919501832287479909523379
145261282152034011112442260653013983173651648948479379642961647792197822
118268619926636309476522424825736766449170308662847527591516245860159270
335785812239686778074630519049627528571047048724459826189283691382474184
22032503387712889

It might seem like overkill, but (for instance) we have customers who
measure every toll on toll roads for large states in the eastern US.

If they want to calculate 5 years of interest on the current balance,
accurate to the penny, at small interest rates, such precision is very
helpful.

His (Moshier's) math stuff is really top-notch.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Martijn van Oosterhout
> Sent: Thursday, May 19, 2005 2:14 AM
> To: Alvaro Herrera
> Cc: John Burger; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] numeric precision when raising one numeric to
> another.
>
> On Wed, May 18, 2005 at 11:32:40PM -0400, Alvaro Herrera wrote:
> > On Wed, May 18, 2005 at 10:46:50PM -0400, John Burger wrote:
> > > For one thing.  For another, I believe the standard C library only
has
> > > floating point exponentiation functions, not that there aren't
plenty
> > > of numeric libraries with integral ones.  Finally, exponentiated
> > > numbers get real big, real fast, and the floating point types can
hold
> > > much larger magnitudes than the integer types, albeit inexactly.
For
> > > example, on the Mac I'm using now, long long ints max out at about
> > > 10^19, while long doubles can represent 10^308.
> >
> > Well, we already have an interesting library of mathematical
functions
> > for NUMERIC (which is an arbitrary precision type, so it wouldn't
matter
> > how big the result would get).  I think the only reason we don't
have a
> > NUMERIC exponentiation function is that nobody has implemented it.
>
> The prerequisites for such a function would be a log() and exp()
> function for numeric. And the real question there would be, what's a
> sufficient accuracy? Numbers people actually use rarely have even
> rational logarithms, so there is no way to store them 100% accurate.
>
> As long as you're using integral exponents you can get away with
> multiplication. BTW, the commandline utility "bc" has arbitrary number
> arithmatic, maybe we can see how they do it? It defaults to 20 digits
> precision, which is obviously not enough for large exponents.
>
> Hmm, it looks like even they don't support raising to fractional
> powers. When calculating 2^100, you need a precision of at least 35
> decimal places to get in the ballpark of the correct figure using
> log/exp, 30 isn't enough. Maybe do exact for integer exponents and
> approx for non-integer?
>
> kleptog@vali:~$ bc -l
> bc 1.06
> Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc.
> > 2^100
> 1267650600228229401496703205376
> > 2^100.1
> Runtime warning (func=(main), adr=11): non-zero scale in exponent
> 1267650600228229401496703205376
> > e(l(2)*100)
> 1267650600228229400579922894637.90158245154400629512
> > scale=30
> > e(l(2)*100)
> 1267650600228229401496703205353.617337311111135194699059124092
> > scale=35
> > e(l(2)*100)
> 1267650600228229401496703205375.99897630874075350752485091801369515
>
> Hope this helps,
> --
> Martijn van Oosterhout   <kleptog@svana.org>
http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent
is a
> > tool for doing 5% of the work and then sitting around waiting for
> someone
> > else to do the other 95% so you can sue them.

On Thu, May 19, 2005 at 10:41:51AM -0700, Dann Corbit wrote:
> We use Moshier's excellent qfloat numbers.
> http://www.moshier.net/qlib.zip
> Documentation:
> http://www.moshier.net/qlibdoc.html
>
> So, if you do the following query using CONNX:
> select convert(pow(9.5,5.9), varchar)
> You will get:
> 586906.97548405202106027547827738573075504470845684721318303336760202394
> 5916438064873363100477233500417619

But it's not accurate enough with the default settings. For example
2^100:

# select exp( ln(2::numeric) * 100 );
                       exp
--------------------------------------------------
 1267650600228229400579922894637.9015824515440063
(1 row)

The answer should be:
 1267650600228229401496703205376

So it's wrong from the 14th digit onwards. If that's the case you may
as well stick to using floating point. It does however appear you can
influence the precision, See:

# select exp( ln(2::numeric(50,30)) * 100 );
                              exp
----------------------------------------------------------------
 1267650600228229401496703205375.991370405139384131115870698781
(1 row)

Using numeric(50,25) gets you only 28 correct digits. So, if you know
how big your result is going to be you can adjust the types to match
and get whatever precision you want. Given that you can estimate the
number of digits easily enough (it's linear with the value before the
exp()) maybe you can get it to automatically choose the right
precision?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

If you want to create a pow() function for numeric using existing
numeric functions, it [the new function] should be aware of the
precision of the inputs, and the precision of the output should be their
product.

So, if you do pow(numeric(10,5), numeric(10,5)) then the result column
should be numeric(100,25) if you want to retain full precision.

> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Sent: Thursday, May 19, 2005 2:02 PM
> To: Dann Corbit
> Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] numeric precision when raising one numeric to
> another.
>
> On Thu, May 19, 2005 at 10:41:51AM -0700, Dann Corbit wrote:
> > We use Moshier's excellent qfloat numbers.
> > http://www.moshier.net/qlib.zip
> > Documentation:
> > http://www.moshier.net/qlibdoc.html
> >
> > So, if you do the following query using CONNX:
> > select convert(pow(9.5,5.9), varchar)
> > You will get:
> >
586906.97548405202106027547827738573075504470845684721318303336760202394
> > 5916438064873363100477233500417619
>
> But it's not accurate enough with the default settings. For example
> 2^100:
>
> # select exp( ln(2::numeric) * 100 );
>                        exp
> --------------------------------------------------
>  1267650600228229400579922894637.9015824515440063
> (1 row)
>
> The answer should be:
>  1267650600228229401496703205376
>
> So it's wrong from the 14th digit onwards. If that's the case you may
> as well stick to using floating point. It does however appear you can
> influence the precision, See:
>
> # select exp( ln(2::numeric(50,30)) * 100 );
>                               exp
> ----------------------------------------------------------------
>  1267650600228229401496703205375.991370405139384131115870698781
> (1 row)
>
> Using numeric(50,25) gets you only 28 correct digits. So, if you know
> how big your result is going to be you can adjust the types to match
> and get whatever precision you want. Given that you can estimate the
> number of digits easily enough (it's linear with the value before the
> exp()) maybe you can get it to automatically choose the right
> precision?
> --
> Martijn van Oosterhout   <kleptog@svana.org>
http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent
is a
> > tool for doing 5% of the work and then sitting around waiting for
> someone
> > else to do the other 95% so you can sue them.

Hmmm....
I underestimated.

pow(99999.99999,99999.99999) =
   9.998748785736894607828527462269893046126336085
91664915498635306081273911645075964079222720857427
35641018572673827935330501923067157794798212338823
24997145234949798725508071849154834025252682619864
09675931105114160107573542813573334036043627693673
32584230414090115274301822704676399594689777183090
95124350838052746795283582659784697437868624515447
84308955024802754764364277858847454870139679632204
93566098207186651878539285222697852739872657689082
77740528466769263852694444704577829403518386946691
11157539964528436618742040945886361696712501785143
49612003446329175703756667138162553151705912580792
12331560317684418171064195077598932031644579554853
98595138860229023469055949001949521877405516916475
97554564462253024119778312344592336542732038212175
43130812948451126588746192211036266786198594583755
89036373827433475892132965189682874790600247279436
07120265912512012429492123644988164587146533255393
93335345599658088256314460922495519381049143246081
37075434256493449284197921246089978660147299071527
8174795070535064342859550611e499999

So the precision calculation would be much more complicated.

> -----Original Message-----
> From: Dann Corbit
> Sent: Thursday, May 19, 2005 2:20 PM
> To: 'Martijn van Oosterhout'
> Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org
> Subject: RE: [GENERAL] numeric precision when raising one numeric to
> another.
>
> If you want to create a pow() function for numeric using existing
numeric
> functions, it [the new function] should be aware of the precision of
the
> inputs, and the precision of the output should be their product.
>
> So, if you do pow(numeric(10,5), numeric(10,5)) then the result column
> should be numeric(100,25) if you want to retain full precision.
>
> > -----Original Message-----
> > From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> > Sent: Thursday, May 19, 2005 2:02 PM
> > To: Dann Corbit
> > Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] numeric precision when raising one numeric to
> > another.
> >
> > On Thu, May 19, 2005 at 10:41:51AM -0700, Dann Corbit wrote:
> > > We use Moshier's excellent qfloat numbers.
> > > http://www.moshier.net/qlib.zip
> > > Documentation:
> > > http://www.moshier.net/qlibdoc.html
> > >
> > > So, if you do the following query using CONNX:
> > > select convert(pow(9.5,5.9), varchar)
> > > You will get:
> > >
>
586906.97548405202106027547827738573075504470845684721318303336760202394
> > > 5916438064873363100477233500417619
> >
> > But it's not accurate enough with the default settings. For example
> > 2^100:
> >
> > # select exp( ln(2::numeric) * 100 );
> >                        exp
> > --------------------------------------------------
> >  1267650600228229400579922894637.9015824515440063
> > (1 row)
> >
> > The answer should be:
> >  1267650600228229401496703205376
> >
> > So it's wrong from the 14th digit onwards. If that's the case you
may
> > as well stick to using floating point. It does however appear you
can
> > influence the precision, See:
> >
> > # select exp( ln(2::numeric(50,30)) * 100 );
> >                               exp
> > ----------------------------------------------------------------
> >  1267650600228229401496703205375.991370405139384131115870698781
> > (1 row)
> >
> > Using numeric(50,25) gets you only 28 correct digits. So, if you
know
> > how big your result is going to be you can adjust the types to match
> > and get whatever precision you want. Given that you can estimate the
> > number of digits easily enough (it's linear with the value before
the
> > exp()) maybe you can get it to automatically choose the right
> > precision?
> > --
> > Martijn van Oosterhout   <kleptog@svana.org>
http://svana.org/kleptog/
> > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent
is
> a
> > > tool for doing 5% of the work and then sitting around waiting for
> > someone
> > > else to do the other 95% so you can sue them.

On Thu, May 19, 2005 at 02:25:58PM -0700, Dann Corbit wrote:
> Hmmm....
> I underestimated.
>
> pow(99999.99999,99999.99999) =

Yeah, a number with x digits raised to the power with something y digits
long could have a length approximating:

x * (10^y) digits

So two numbers both 4 digits long can have a result of upto 40,000
digits. You're only going to be able to them represent exactly for
cases where y is small and integer.

What's a meaningful limit? Do we simply say, you get upto 100 digits
and that's it? Or an extra parameter so you can specify directly?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Probably, the important meaningful cases are ones that have small
exponents (HOPEFULLY less than 25) used in interest calculations.

Million digit numbers are really only interesting in the field of pure
mathematics, since the number of elementary particles in the universe is
well under a googol (10^100).

But if someone has a billion dollars (and some do, of course -- even
potentially trillions if it is a government) and they want to do a long
term interest calculation accurate to the penny, then we should be
careful to get that answer right.

The calculation pow(huge,huge) will result in a big pile of fascinating
digits that won't really have much physical meaning.

> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Sent: Thursday, May 19, 2005 2:48 PM
> To: Dann Corbit
> Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] numeric precision when raising one numeric to
> another.
>
> On Thu, May 19, 2005 at 02:25:58PM -0700, Dann Corbit wrote:
> > Hmmm....
> > I underestimated.
> >
> > pow(99999.99999,99999.99999) =
>
> Yeah, a number with x digits raised to the power with something y
digits
> long could have a length approximating:
>
> x * (10^y) digits
>
> So two numbers both 4 digits long can have a result of upto 40,000
> digits. You're only going to be able to them represent exactly for
> cases where y is small and integer.
>
> What's a meaningful limit? Do we simply say, you get upto 100 digits
> and that's it? Or an extra parameter so you can specify directly?
> --
> Martijn van Oosterhout   <kleptog@svana.org>
http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent
is a
> > tool for doing 5% of the work and then sitting around waiting for
> someone
> > else to do the other 95% so you can sue them.

At CONNX, we just do 100 digits using qfloat (about 104 actually).
Internally, all math is done using this type.  Then we convert to the
smaller types [or character types] as requested.

I don't think that there is any business need for more than that.

A package like Maple might need to worry about it, or a theoretical
mathematician looking for patterns in digits or something like that.

But you can't please everybody.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Martijn van Oosterhout
> Sent: Thursday, May 19, 2005 2:48 PM
> To: Dann Corbit
> Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] numeric precision when raising one numeric to
> another.
>
> On Thu, May 19, 2005 at 02:25:58PM -0700, Dann Corbit wrote:
> > Hmmm....
> > I underestimated.
> >
> > pow(99999.99999,99999.99999) =
>
> Yeah, a number with x digits raised to the power with something y
digits
> long could have a length approximating:
>
> x * (10^y) digits
>
> So two numbers both 4 digits long can have a result of upto 40,000
> digits. You're only going to be able to them represent exactly for
> cases where y is small and integer.
>
> What's a meaningful limit? Do we simply say, you get upto 100 digits
> and that's it? Or an extra parameter so you can specify directly?
> --
> Martijn van Oosterhout   <kleptog@svana.org>
http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent
is a
> > tool for doing 5% of the work and then sitting around waiting for
> someone
> > else to do the other 95% so you can sue them.

"Dann Corbit" <DCorbit@connx.com> writes:

> Probably, the important meaningful cases are ones that have small
> exponents (HOPEFULLY less than 25) used in interest calculations.

No, even in interest calculation floating point arithmetic is perfectly fine.
You do your floating point arithmetic to calculate the factor to use when
multiplying your fixed precision exact dollar amounts. You then store the
result again in exact form and do your account balancing in fixed precision
arithmetic to be sure you don't lose a penny here or there.

In fact the exponent can be much larger than 25 (think of monthly compounded
25 year mortgages, or worse, daily compounded savings accounts). But in those
cases the base will be very close to 1.

There's really no use case for NUMERIC^NUMERIC except in the case of an
integral power which is useful for number theory and cryptography.


--
greg

I find all these statements about the near-uselessness of
NUMERIC^NUMERIC to be pretty amazing.  It's fine to say, "no one seems
to be asking for this, so we haven't implemented it yet", but, c'mon,
folks, Postgres gets used for more than "business cases".

- John D. Burger
   MITRE

On Fri, May 20, 2005 at 08:19:58 -0400,
  "John D. Burger" <john@mitre.org> wrote:
> I find all these statements about the near-uselessness of
> NUMERIC^NUMERIC to be pretty amazing.  It's fine to say, "no one seems
> to be asking for this, so we haven't implemented it yet", but, c'mon,
> folks, Postgres gets used for more than "business cases".

It is pretty useless. If you are doing exact math, fractional exponents
don't fit. If you are using integer exponents, you can store usable
exponents in an int (arguably an an int2).

People may be interested in NUMERIC^NUMERIC MOD N, but if so they aren't
going to do the exponentation first and then the mod operation.

On Fri, 20 May 2005, John D. Burger wrote:

> I find all these statements about the near-uselessness of
> NUMERIC^NUMERIC to be pretty amazing.  It's fine to say, "no one seems
> to be asking for this, so we haven't implemented it yet", but, c'mon,
> folks, Postgres gets used for more than "business cases".

If people don't see the use of a function they aren't going to implement
it.  In addition, there is a small, but non-zero cost to adding a
function/operator to the system (in the cost to maintain it at the very
least) and if the general belief is that the function or operator is
useless or nearly useless then it simply may not be worth adding.

On Fri, 2005-05-20 at 09:06, Stephan Szabo wrote:
> On Fri, 20 May 2005, John D. Burger wrote:
>
> > I find all these statements about the near-uselessness of
> > NUMERIC^NUMERIC to be pretty amazing.  It's fine to say, "no one seems
> > to be asking for this, so we haven't implemented it yet", but, c'mon,
> > folks, Postgres gets used for more than "business cases".
>
> If people don't see the use of a function they aren't going to implement
> it.  In addition, there is a small, but non-zero cost to adding a
> function/operator to the system (in the cost to maintain it at the very
> least) and if the general belief is that the function or operator is
> useless or nearly useless then it simply may not be worth adding.

A couple of points.

1:  How much time has been expended in the last 5 or so years
"maintaining" the floating point exponentiation operator?  Seriously.  I
doubt any work has gone into maintaining it.  I don't mean bug fixes.  I
mean touching its code because something else changed, and therefore the
fp exponent code was affected.  If someone has had to do something to
maintain it, I'd certainly welcome hearing from them.  My guess is that
the total amount of time that's gone into maintaining the FP version of
this operator is zero, or nearly so, and, if implemented, the amount of
time that will go into maintaining will be the same, zero, or, very
nearly so.

I could be wrong, and would be unoffended to be proven so, but I don't
think I am.  I think that argument is just hand waving.

2:  How many people who DO work with large exponents and need arbitrary
precision have looked at postgresql, typed in "select 3^100" got back
5.15377520732011e+47, and simply went to another piece of software and
never looked back?  We don't know.  And the attitude that it seems
useless to me so it must be useless to everybody else isn't going to
help attract people who do things that seem esoteric and strange to you,
but are important to them.

3: Is this worth submitting a patch for?  I don't want to spend x hours
making a patch and 10x hours arguing over getting it accepted... :)

Has anyone bothered to actually look into the code?

regression=# select power(2::numeric,1000);
                                                                              power

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

10715086071862673209484250490600018105614048117055336074437503883703510511249361224931983788156958581275946729175531468251871452856923140435984577574698574803934567774824230985421074605062371141877954182153046474983581941267398767559165543946077062914571196477686542167660429831652624386837205668069376.0000000000000000
(1 row)

AFAICT the only thing missing is a pg_operator entry linked to the
function.

            regards, tom lane

Scott Marlowe wrote:
> I could be wrong, and would be unoffended to be proven so, but I don't
> think I am.  I think that argument is just hand waving.
>
> 2:  How many people who DO work with large exponents and need arbitrary
> precision have looked at postgresql, typed in "select 3^100" got back
> 5.15377520732011e+47, and simply went to another piece of software and
> never looked back?  We don't know.  And the attitude that it seems
> useless to me so it must be useless to everybody else isn't going to
> help attract people who do things that seem esoteric and strange to you,
> but are important to them.
>
> 3: Is this worth submitting a patch for?  I don't want to spend x hours
> making a patch and 10x hours arguing over getting it accepted... :)

Seems we could create a NUMERIC^NUMERIC function that does integral
exponents accurately and non-integrals using floats.  Is the problem
that the function can only return NUMERIC or float?

--
  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

On Fri, 20 May 2005, Scott Marlowe wrote:

> On Fri, 2005-05-20 at 09:06, Stephan Szabo wrote:
> > On Fri, 20 May 2005, John D. Burger wrote:
> >
> > > I find all these statements about the near-uselessness of
> > > NUMERIC^NUMERIC to be pretty amazing.  It's fine to say, "no one seems
> > > to be asking for this, so we haven't implemented it yet", but, c'mon,
> > > folks, Postgres gets used for more than "business cases".
> >
> > If people don't see the use of a function they aren't going to implement
> > it.  In addition, there is a small, but non-zero cost to adding a
> > function/operator to the system (in the cost to maintain it at the very
> > least) and if the general belief is that the function or operator is
> > useless or nearly useless then it simply may not be worth adding.
>
> A couple of points.

> 1:  How much time has been expended in the last 5 or so years
> "maintaining" the floating point exponentiation operator?  Seriously.  I

Probably pretty little or none, but wasn't there a binary incompatible
change in numeric in that time?

> I could be wrong, and would be unoffended to be proven so, but I don't
> think I am.  I think that argument is just hand waving.

It simply means that the value necessary to overcome it is very small. I'd
even argue that in this case, the value is probably higher than the cost.

> 2:  How many people who DO work with large exponents and need arbitrary
> precision have looked at postgresql, typed in "select 3^100" got back
> 5.15377520732011e+47, and simply went to another piece of software and
> never looked back?  We don't know.  And the attitude that it seems
> useless to me so it must be useless to everybody else isn't going to
> help attract people who do things that seem esoteric and strange to you,
> but are important to them.

As a note, I don't think it's useless.  I simply think the argument that
anything that can be included should is invalid.  I could make
equivalent arguments for a whole lot of things and that's when the cost
argument starts making more sense.

On Fri, 2005-05-20 at 10:37, Bruce Momjian wrote:
> Scott Marlowe wrote:
> > I could be wrong, and would be unoffended to be proven so, but I don't
> > think I am.  I think that argument is just hand waving.
> >
> > 2:  How many people who DO work with large exponents and need arbitrary
> > precision have looked at postgresql, typed in "select 3^100" got back
> > 5.15377520732011e+47, and simply went to another piece of software and
> > never looked back?  We don't know.  And the attitude that it seems
> > useless to me so it must be useless to everybody else isn't going to
> > help attract people who do things that seem esoteric and strange to you,
> > but are important to them.
> >
> > 3: Is this worth submitting a patch for?  I don't want to spend x hours
> > making a patch and 10x hours arguing over getting it accepted... :)
>
> Seems we could create a NUMERIC^NUMERIC function that does integral
> exponents accurately and non-integrals using floats.  Is the problem
> that the function can only return NUMERIC or float?

Is there an underlying lib that can do better, but won't be used by this
method?  The scientific calculator included with fedora core 2 does
better than this method.  Consider something like:

select 10000000000000000::numeric^1.04::numeric;
       ?column?
----------------------
4.36515832240167e+16
or
43651583224016700 if we represent that is as numeric

The answer from my calculator is:

43651583224016596.7463835.

I have to admit I find it discouraging that the calculator in my fedora
core installation is better at math than my database.

On Fri, 20 May 2005, Tom Lane wrote:

> Has anyone bothered to actually look into the code?
>
> regression=# select power(2::numeric,1000);
>                                                                               power
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
10715086071862673209484250490600018105614048117055336074437503883703510511249361224931983788156958581275946729175531468251871452856923140435984577574698574803934567774824230985421074605062371141877954182153046474983581941267398767559165543946077062914571196477686542167660429831652624386837205668069376.0000000000000000
> (1 row)
>
> AFAICT the only thing missing is a pg_operator entry linked to the
> function.

It appears fairly limited however given that you rapidly run into the
numeric maximum length for exp.

It also doesn't seem to work terribly well:

sszabo=# select power(0.1::numeric, 15);
       power
--------------------
 0.0000000000000010
(1 row)

sszabo=# select power(0.1::numeric, 16);
       power
--------------------
 0.0000000000000001
(1 row)

sszabo=# select power(0.1::numeric, 17);
       power
--------------------
 0.0000000000000000
(1 row)

sszabo=# select power(0.1::numeric, 17)*100;
      ?column?
--------------------
 0.0000000000000000
(1 row)

On Fri, 2005-05-20 at 11:16, Stephan Szabo wrote:
> On Fri, 20 May 2005, Scott Marlowe wrote:

>
> > 2:  How many people who DO work with large exponents and need arbitrary
> > precision have looked at postgresql, typed in "select 3^100" got back
> > 5.15377520732011e+47, and simply went to another piece of software and
> > never looked back?  We don't know.  And the attitude that it seems
> > useless to me so it must be useless to everybody else isn't going to
> > help attract people who do things that seem esoteric and strange to you,
> > but are important to them.
>
> As a note, I don't think it's useless.  I simply think the argument that
> anything that can be included should is invalid.  I could make
> equivalent arguments for a whole lot of things and that's when the cost
> argument starts making more sense.

Agreed.  However, I think that if PostgreSQL has support for numerics of
1000 characters, it might make sense for it to have the operators to
ensure that operations exist for most if not all common mathmatical
operations, especially since many esoteric math functions could make use
of such accuracy.

I wonder what Joe Conway's take on all this would be, since he's the guy
that made PL/R a reality.

I don't think we should include anything that could be added either.  U
just don't like surprises, which is what I consider it when I raise one
numeric to another numeric and get a floating point answer.

That's because numerics default to 16 or something similar. If you want
more precision just explicitly cast it:

decibel=# select power(0.1::numeric(20,20),17);
 0.00000000000000001000

On Fri, May 20, 2005 at 09:30:16AM -0700, Stephan Szabo wrote:
>
> On Fri, 20 May 2005, Tom Lane wrote:
>
> > Has anyone bothered to actually look into the code?
> >
> > regression=# select power(2::numeric,1000);
> >                                                                               power
> >
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
10715086071862673209484250490600018105614048117055336074437503883703510511249361224931983788156958581275946729175531468251871452856923140435984577574698574803934567774824230985421074605062371141877954182153046474983581941267398767559165543946077062914571196477686542167660429831652624386837205668069376.0000000000000000
> > (1 row)
> >
> > AFAICT the only thing missing is a pg_operator entry linked to the
> > function.
>
> It appears fairly limited however given that you rapidly run into the
> numeric maximum length for exp.
>
> It also doesn't seem to work terribly well:
>
> sszabo=# select power(0.1::numeric, 15);
>        power
> --------------------
>  0.0000000000000010
> (1 row)
>
> sszabo=# select power(0.1::numeric, 16);
>        power
> --------------------
>  0.0000000000000001
> (1 row)
>
> sszabo=# select power(0.1::numeric, 17);
>        power
> --------------------
>  0.0000000000000000
> (1 row)
>
> sszabo=# select power(0.1::numeric, 17)*100;
>       ?column?
> --------------------
>  0.0000000000000000
> (1 row)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> It also doesn't seem to work terribly well:

It's not terribly bright about figuring out how many significant digits
it should try to calculate, nor about how many it's actually got in the
result.  Feel free to fix that ;-)  I believe the numeric exp() and ln()
functions have similar issues.

            regards, tom lane

Why are we allowing implicit casts from numeric to floating point?
Doesn't that violate the principle of not doing any implicit casts that
would potentially drop precision? It seems that about half the arguments
here are related to getting unexpected or inaccurate results, presumably
from the implicit cast; if it was removed at least then people would
know their results might be imprecise, where as now there's no
indication of that at all.

On Wed, May 18, 2005 at 05:42:28PM -0400, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > It appears from checking the output of exponentiation of one numeric to
> > another, the output is actually in floating point.  Is this normal and /
> > or expected?
>
> Yes, seeing that the only ^ operator we have is float8.
>
> regression=# \do ^
>                                          List of operators
>    Schema   | Name |  Left arg type   |  Right arg type  |   Result type    |
>   Description
> ------------+------+------------------+------------------+------------------+----------------------
>  pg_catalog | ^    | double precision | double precision | double precision | exponentiation (x^y)
> (1 row)
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <decibel@decibel.org> writes:
> Why are we allowing implicit casts from numeric to floating point?

Because the SQL spec requires it.

         2) If the data type of either operand of a dyadic arithmetic op-
            erator is approximate numeric, then the data type of the re-
            sult is approximate numeric.

It doesn't say to throw an error for mixed-type arithmetic.

Now it also says

         1) If the data type of both operands of a dyadic arithmetic opera-
            tor is exact numeric, then the data type of the result is exact
            numeric, ...

which you could take as requiring us to provide numeric equivalents of
every floating-point operator, but I don't find that argument very
convincing for operations that are inherently not going to give exact
results.  The spec demands exact results from addition, subtraction,
and multiplication, but as soon as you get to division they punt; let
alone transcendental functions.

But having said that, I don't have a problem with putting in a
pg_operator entry for numeric_power.  And if someone wants to improve
the scale factor calculations therein, go for it.  But so far there's
been an extremely low signal-to-noise ratio in this thread ...

            regards, tom lane

17:28, venerdì 20 maggio 2005 - Tom Lane scrive:
|>   Has anyone bothered to actually look into the code?
|>
|>   regression=# select power(2::numeric,1000);
|>
|>      power
|> --------------------------------------------------------------------------
|>---------------------------------------------------------------------------
|>---------------------------------------------------------------------------
|>---------------------------------------------------------------------------
|>----------------------
|> 10715086071862673209484250490600018105614048117055336074437503883703510511
|>249361224931983788156958581275946729175531468251871452856923140435984577574
|>698574803934567774824230985421074605062371141877954182153046474983581941267
|>398767559165543946077062914571196477686542167660429831652624386837205668069
|>376.0000000000000000 (1 row)

Just for curiosity.

With PostgreSQL 7.4.7 on Linux/Debian platform I had a different result so I
made the subtraction of your figure:

                                   
psql -h s1 -d rapp-test -c "select power(2::numeric,1000) -
10715086071862673209484250490600018105614048117055336074437503883703510511
249361224931983788156958581275946729175531468251871452856923140435984577574
698574803934567774824230985421074605062371141877954182153046474983581941267
398767559165543946077062914571196477686542167660429831652624386837205668069
376.0000000000000000"

the difference seems to be more than some decimals:

                                                       ?column?
                                                                          
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------
 -12930966888749171763979711252782126059745074561323490485649902084788364060
8509196350026606450797474300013235449638255271591657519292725441403643666323
4303290336621701615570133781468903988395214925377190448567739467132002913627
731843776737159303888471.0573553374557744
(1 row)

Why I did not get some error message? And how can I be aware of exeeding the
system capacity?

(what am I wrong about?)

Claudio

--
Claudio Succa
PERTEL - Torino - Italy
+39-011-437.4141
http://www.pertel.it
http://www.uniassist.it
http://www.progettocapolinea36.it

On Fri, May 20, 2005 at 01:03:08PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel@decibel.org> writes:
> > Why are we allowing implicit casts from numeric to floating point?
>
> Because the SQL spec requires it.
>
>          2) If the data type of either operand of a dyadic arithmetic op-
>             erator is approximate numeric, then the data type of the re-
>             sult is approximate numeric.
>
> It doesn't say to throw an error for mixed-type arithmetic.
>
> Now it also says
>
>          1) If the data type of both operands of a dyadic arithmetic opera-
>             tor is exact numeric, then the data type of the result is exact
>             numeric, ...

But isn't NUMERIC exact numeric and not approximate?

> which you could take as requiring us to provide numeric equivalents of
> every floating-point operator, but I don't find that argument very
> convincing for operations that are inherently not going to give exact
> results.  The spec demands exact results from addition, subtraction,
> and multiplication, but as soon as you get to division they punt; let
> alone transcendental functions.

ISTM what's more important than be exact is respecting precision. If I'm
remembering this correctly from high school, multiplying two numbers
each having 10 significant digits means you then have 20 significant
digits, so we should at least respect that. Which means
numeric(500)^numeric(500) should give an exact numeric(1000), which I
don't think is a given when casting to a double.

I'm not sure how this changes if you're using a fractional exponent. But
it seems like a pretty serious issue if you're doing financial
calculations and those are sometimes done in floating point under the
covers.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Martijn van Oosterhout wrote:
> On Thu, May 19, 2005 at 02:25:58PM -0700, Dann Corbit wrote:
>
>>Hmmm....
>>I underestimated.
>>
>>pow(99999.99999,99999.99999) =
>
>
> Yeah, a number with x digits raised to the power with something y digits
> long could have a length approximating:
>
> x * (10^y) digits
>
> So two numbers both 4 digits long can have a result of upto 40,000
> digits. You're only going to be able to them represent exactly for
> cases where y is small and integer.
>
> What's a meaningful limit? Do we simply say, you get upto 100 digits
> and that's it? Or an extra parameter so you can specify directly?
I believe that even this limit is wrong. Consider sqrt(2), which is
2^(1/2).
2 has 1 digit, 1/2 has 2 digits, but the result is irrational, and
therefor cannot be represented with a finit amount of digits.

I believe that there is no mathematically correct way (i.e. a way which
guarantees a 100% correct result) to define pow(numeric, numeric) - at
least in the general case.

Stephan Szabo wrote:
> On Fri, 20 May 2005, John D. Burger wrote:
>
>
>>I find all these statements about the near-uselessness of
>>NUMERIC^NUMERIC to be pretty amazing.  It's fine to say, "no one seems
>>to be asking for this, so we haven't implemented it yet", but, c'mon,
>>folks, Postgres gets used for more than "business cases".
>
> If people don't see the use of a function they aren't going to implement
> it.  In addition, there is a small, but non-zero cost to adding a
> function/operator to the system (in the cost to maintain it at the very
> least) and if the general belief is that the function or operator is
> useless or nearly useless then it simply may not be worth adding.

It's not only useless, it's dangerous. As fas as I know, numeric
_guarantees_ the result of a operation to be correct to the last digit.
This is _impossible_ to archive in the general case (thing 2^(1/2)) -
and therefor, there should be no pow(numeric, numeric). There should be
a pow(numeric, int), and maybe a pow(numeric, float) - and certainly
there should be (and is) an pow(float, float) - but pow(numeric,
numeric) defeats the whole purpose of the numeric type.

greetings, Florian Pflug

On Fri, 2005-05-20 at 12:03, Tom Lane wrote:
> "Jim C. Nasby" <decibel@decibel.org> writes:
> > Why are we allowing implicit casts from numeric to floating point?
>
> Because the SQL spec requires it.
>
>          2) If the data type of either operand of a dyadic arithmetic op-
>             erator is approximate numeric, then the data type of the re-
>             sult is approximate numeric.
>
> It doesn't say to throw an error for mixed-type arithmetic.
>
> Now it also says
>
>          1) If the data type of both operands of a dyadic arithmetic opera-
>             tor is exact numeric, then the data type of the result is exact
>             numeric, ...
>
> which you could take as requiring us to provide numeric equivalents of
> every floating-point operator, but I don't find that argument very
> convincing for operations that are inherently not going to give exact
> results.

Are you saying that the exponent operator will return inexact results?
OR talking about other operators

>  The spec demands exact results from addition, subtraction,
> and multiplication, but as soon as you get to division they punt; let
> alone transcendental functions.

If you're quoting the 92 spec, it seems to say that multiplication
precision is also implementation specific.

> But having said that, I don't have a problem with putting in a
> pg_operator entry for numeric_power.  And if someone wants to improve
> the scale factor calculations therein, go for it.

OK, I'm gonna look at it this weekend.  I might have some questions
before I really get anything working, this being my first real adventure
hacking pgsql.

> But so far there's
> been an extremely low signal-to-noise ratio in this thread ...

Really, I've found it quite informative.  I see no reason to insult the
people who've contributed to it.

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> Are you saying that the exponent operator will return inexact results?

For a fractional exponent, it generally has to, because there is no
finite exact result.

> If you're quoting the 92 spec, it seems to say that multiplication
> precision is also implementation specific.

You're misreading it: the scale part is what's important.  Precision
means the implementation gets to set a limit on the total number of
digits it will store.  The scale rules effectively say that for add/sub/mul
you must either deliver an exact result or report overflow.  But that
requirement is not placed on division, and (by implication) not on other
operations that are incapable of delivering exact results every time.

numeric_power can in theory deliver an exact answer when the exponent is
a positive integer.  Division can deliver an exact answer in some cases
too --- but the spec doesn't say it must do so when possible.  So I
would say that there is no spec requirement for special behavior for
integral exponents.

We could try to deliver an exact answer for an integral exponent by
selecting output scale = input scale times exponent.  But that doesn't
work for any but very small exponents --- as the exponent gets bigger
you really have to drop fractional precision, or you're going to hit
overflow, which is not an improvement.  (We do set a limit on total
number of digits...)  So it's a question of tradeoffs, not black and
white.

            regards, tom lane

On Fri, 2005-05-20 at 12:27, Florian G. Pflug wrote:
> Stephan Szabo wrote:
> > On Fri, 20 May 2005, John D. Burger wrote:
> >
> >
> >>I find all these statements about the near-uselessness of
> >>NUMERIC^NUMERIC to be pretty amazing.  It's fine to say, "no one seems
> >>to be asking for this, so we haven't implemented it yet", but, c'mon,
> >>folks, Postgres gets used for more than "business cases".
> >
> > If people don't see the use of a function they aren't going to implement
> > it.  In addition, there is a small, but non-zero cost to adding a
> > function/operator to the system (in the cost to maintain it at the very
> > least) and if the general belief is that the function or operator is
> > useless or nearly useless then it simply may not be worth adding.
>
> It's not only useless, it's dangerous. As far as I know, numeric
> _guarantees_ the result of an operation to be correct to the last digit.

Actually, not so according to the spec.  The spec makes it clear that
the precision of multiplication is implementation defined, and the scale
is S1+S2.  For division both are implementation defined.

Further, it is more dangerous, to me, to coerce a power(numeric,numeric)
function to float with no warning or error that precision HAS been lost,
than to output it as numeric with as much precision as can be had, or as
the user wants / specifies.

> This is _impossible_ to achieve in the general case (think 2^(1/2)) -
> and therefor, there should be no pow(numeric, numeric).

I would just say to limit it to whatever the proper scale should be.
Now, what the scale should be, there's the real issue.

But just chopping off all the

"Florian G. Pflug" <fgp@phlo.org> writes:
> It's not only useless, it's dangerous. As fas as I know, numeric
> _guarantees_ the result of a operation to be correct to the last digit.

Nonsense ... see division.  By your argument we should not implement
numeric / numeric.

            regards, tom lane

Claudio Succa <claudio.succa.ml@pertel.it> writes:
> With PostgreSQL 7.4.7 on Linux/Debian platform I had a different result so I
> made the subtraction of your figure:
> psql -h s1 -d rapp-test -c "select power(2::numeric,1000) -

I don't know what you're getting there, but there is no power() function
at all in a standard 7.4 installation.  Possibly you have a homebrew
function that uses the floating-point dpow() code?

            regards, tom lane

On Fri, May 20, 2005 at 12:22:33PM -0500, Jim C. Nasby wrote:
> > which you could take as requiring us to provide numeric equivalents of
> > every floating-point operator, but I don't find that argument very
> > convincing for operations that are inherently not going to give exact
> > results.  The spec demands exact results from addition, subtraction,
> > and multiplication, but as soon as you get to division they punt; let
> > alone transcendental functions.
>
> ISTM what's more important than be exact is respecting precision. If I'm
> remembering this correctly from high school, multiplying two numbers
> each having 10 significant digits means you then have 20 significant
> digits, so we should at least respect that. Which means
> numeric(500)^numeric(500) should give an exact numeric(1000), which I
> don't think is a given when casting to a double.

Wrong.

numeric(500) * numeric(500) = numeric(1000)
numeric(500) ^ numeric(500) = numeric(10 ^ 503) >> googleplex

You do not have enough memory to store the exact result. There are not
enough atoms in the universe to store this result. That's one reason
why you can't guarentee an exact result. Even numeric(20) ^ numeric(20)
= numeric( 10 ^ 22 )

> I'm not sure how this changes if you're using a fractional exponent. But
> it seems like a pretty serious issue if you're doing financial
> calculations and those are sometimes done in floating point under the
> covers.

Financial calculations are a red herring. They don't deal with less
than hundredths of a cent or more than trillions of dollars so 20
significant digits is easily enough. I would say to place an upper
limit at say 100 digits. It you want better, go get a real math
package.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Fri, May 20, 2005 at 01:03:08PM -0400, Tom Lane wrote:

> But having said that, I don't have a problem with putting in a
> pg_operator entry for numeric_power.  And if someone wants to improve
> the scale factor calculations therein, go for it.

Oh, and while at it, it would be nice to solve the modulo bug that still
lurks there:

alvherre=# select 12345678901234567890 % 123;
 ?column?
----------
      -45
(1 fila)

alvherre=# select 12345678901234567890 % 123::numeric(4,1);
 ?column?
----------
     78.0
(1 fila)

alvherre=# select 12345678901234567890 % 123::numeric(3,0);
 ?column?
----------
      -45
(1 fila)

alvherre=# select version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6 (Debian 1:3.3.6-4)
(1 fila)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)

Tom Lane wrote:
> Has anyone bothered to actually look into the code?
>
> regression=# select power(2::numeric,1000);
>                                                                               power
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
10715086071862673209484250490600018105614048117055336074437503883703510511249361224931983788156958581275946729175531468251871452856923140435984577574698574803934567774824230985421074605062371141877954182153046474983581941267398767559165543946077062914571196477686542167660429831652624386837205668069376.0000000000000000
> (1 row)
>
> AFAICT the only thing missing is a pg_operator entry linked to the
> function.

Patch to add NUMERIC ^ NUMERIC operator added and applied.  Catalog
version bumped.  This will be in 8.1.  FYI, this already does the right
thing (no rounding) if the second argument is an integal value, see
power_var().

--
  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/include/catalog/catversion.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/catversion.h,v
retrieving revision 1.271
diff -c -c -r1.271 catversion.h
*** src/include/catalog/catversion.h    30 May 2005 06:52:38 -0000    1.271
--- src/include/catalog/catversion.h    30 May 2005 20:54:25 -0000
***************
*** 53,58 ****
   */

  /*                            yyyymmddN */
! #define CATALOG_VERSION_NO    200505301

  #endif
--- 53,58 ----
   */

  /*                            yyyymmddN */
! #define CATALOG_VERSION_NO    200505302

  #endif
Index: src/include/catalog/pg_operator.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_operator.h,v
retrieving revision 1.131
diff -c -c -r1.131 pg_operator.h
*** src/include/catalog/pg_operator.h    14 Apr 2005 01:38:20 -0000    1.131
--- src/include/catalog/pg_operator.h    30 May 2005 20:54:26 -0000
***************
*** 695,700 ****
--- 695,701 ----
  DATA(insert OID = 1760 (  "*"       PGNSP PGUID b f 1700 1700 1700 1760    0 0 0 0 0 numeric_mul - - ));
  DATA(insert OID = 1761 (  "/"       PGNSP PGUID b f 1700 1700 1700     0    0 0 0 0 0 numeric_div - - ));
  DATA(insert OID = 1762 (  "%"       PGNSP PGUID b f 1700 1700 1700     0    0 0 0 0 0 numeric_mod - - ));
+ DATA(insert OID = 1038 (  "^"       PGNSP PGUID b f 1700 1700 1700     0    0 0 0 0 0 numeric_power - - ));
  DATA(insert OID = 1763 (  "@"       PGNSP PGUID l f    0 1700 1700    0    0 0 0 0 0 numeric_abs - - ));

  DATA(insert OID = 1784 (  "="      PGNSP PGUID b f 1560 1560 16 1784 1785 1786 1786 1786 1787 biteq eqsel eqjoinsel
));

Alvaro Herrera wrote:
> On Fri, May 20, 2005 at 01:03:08PM -0400, Tom Lane wrote:
>
> Oh, and while at it, it would be nice to solve the modulo bug that still
> lurks there:
>
> alvherre=# select 12345678901234567890 % 123;
>  ?column?
> ----------
>       -45
> (1 fila)
>
> alvherre=# select 12345678901234567890 % 123::numeric(4,1);
>  ?column?
> ----------
>      78.0
> (1 fila)
>
> alvherre=# select 12345678901234567890 % 123::numeric(3,0);
>  ?column?
> ----------
>       -45
> (1 fila)
>
> alvherre=# select version();
>                                            version
> ----------------------------------------------------------------------------------------------
>  PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6 (Debian 1:3.3.6-4)
> (1 fila)

I poked around on this one and found this in the comments in
numeric::mod_var():

    /* ---------
     * We do this using the equation
     *      mod(x,y) = x - trunc(x/y)*y
     * We set rscale the same way numeric_div and numeric_mul do
     * to get the right answer from the equation.  The final result,
     * however, need not be displayed to more precision than the inputs.
     * ----------
     */

so I tried it:

    test=> select 12345678901234567890 % 123;
     ?column?
    ----------
          -45
    (1 row)

    test=> select 12345678901234567890 / 123;
          ?column?
    --------------------
     100371373180768845
    (1 row)

    test=> select 100371373180768845::numeric * 123::numeric;
           ?column?
    ----------------------
     12345678901234567935
    (1 row)

    test=> select 12345678901234567890 - 12345678901234567935;
     ?column?
    ----------
          -45
    (1 row)

and I was quite surprised at the result.  Basically, it looks like the
division is rounding _up_ the next integer on the /123 division, and
that is causing the modulo error.  In fact, should the /123 round up
with numeric?  I think there is an assumption in our code that div_var()
will not round up, but in fact it does in this case.

Here is 'calc' showing the same calculation:

    > 12345678901234567890 % 123
            78
    > 12345678901234567890 / 123
            ~100371373180768844.63414634146341463414
    > 100371373180768845 * 123

          ^^^^^^^^^^^^^^^^^^ rounded up by me

            12345678901234567935
    > 12345678901234567890 - 12345678901234567935
            -45

and here is 'bc' doing integer division:

    12345678901234567890 / 123
    100371373180768844
    100371373180768844 * 123
    12345678901234567812
    12345678901234567890 - 12345678901234567812
    78

This is why 123::numeric(4,1) fixes it because the division returns on
digit that is truncated, rather than rounding up to the next whole
number.

I am not sure how to fix this.  Adding extra scale to the division would
help, but if the division returned .999 and we had a scale of 2, it
would still round up and the truncate would not see it.

--
  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

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?

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Industry suffers from the managerial dogma that for the sake of stability
and continuity, the company should be independent of the competence of
individual employees."                                      (E. Dijkstra)

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.

--
  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

On 5/20/2005 2:26 PM, Tom Lane wrote:

> numeric_power can in theory deliver an exact answer when the exponent is
> a positive integer.  Division can deliver an exact answer in some cases
> too --- but the spec doesn't say it must do so when possible.  So I
> would say that there is no spec requirement for special behavior for
> integral exponents.

There are cases where a numeric_power could in theory deliver an exact
answer for a fractional exponent. That is when the exponent is a natural
fraction because the result is the m'th root of x^n (for n/m). As an
example 4^1.5 = 8. Of course does the m'th root need to produce a finite
result, which I think is not guaranteed for arbitrary numbers.

I'm not advocating to do that, just saying it is theoretically possible
for a subset of possible inputs.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #