Обсуждение: 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 #