Thread: Bug #534: factorial function

Bug #534: factorial function

From
pgsql-bugs@postgresql.org
Date:
Janko Richter (j.richter@wallstreet-develop.de) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
factorial function

Long Description
1. The factorial function returns 0 when the argument is 0. The factorial Funtion is defined as:  factorial(0)=1

2. It would be better to define a factorial function returning double precision. The factorial function returning
bigintproduces false values  with arguments larger then 20. 

Notice: I'm using the factorial function for statistical functions (for example poisson distribution)

Thank you.


Sample Code
test=# select factorial(0);
 factorial
-----------
         0
(1 row)

janko=# select factorial(21::bigint);
      factorial
----------------------
 -4249290049419214848
(1 row)




No file was uploaded with this report

Re: Bug #534: factorial function

From
Bruce Momjian
Date:
Yep, 0! sure looks like a bug.  We will fix it in 7.3.  Not sure about
the double precision.  Comments?

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

> Janko Richter (j.richter@wallstreet-develop.de) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> factorial function
>
> Long Description
> 1. The factorial function returns 0 when the argument is 0. The factorial Funtion is defined as:  factorial(0)=1
>
> 2. It would be better to define a factorial function returning double precision. The factorial function returning
bigintproduces false values  with arguments larger then 20. 
>
> Notice: I'm using the factorial function for statistical functions (for example poisson distribution)
>
> Thank you.
>
>
> Sample Code
> test=# select factorial(0);
>  factorial
> -----------
>          0
> (1 row)
>
> janko=# select factorial(21::bigint);
>       factorial
> ----------------------
>  -4249290049419214848
> (1 row)
>
>
>
>
> No file was uploaded with this report
>
>
> ---------------------------(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
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Bug #534: factorial function

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yep, 0! sure looks like a bug.  We will fix it in 7.3.  Not sure about
> the double precision.  Comments?

It looks like we have three versions of factorial, for int2 int4 and
int8.  The version taking int2 is just plain wasted code space (perhaps
it predates the availability of automatic type conversions?)  The int4
and int8 versions both have a serious problem with lack of overflow
detection.  I'd be sorely tempted to replace all three by a single
function that takes integer and returns numeric.

            regards, tom lane

Re: Bug #534: factorial function

From
Thomas Lockhart
Date:
> ... I'd be sorely tempted to replace all three by a single
> function that takes integer and returns numeric.

Yikes. Although numeric is theoretically nice, it is hundreds of times
slower than native doubles. We've already moved it in to some of the
other aggregate math functions without much discussion of the
performance hit.

                    - Thomas

Re: Bug #534: factorial function

From
Tom Lane
Date:
Thomas Lockhart <lockhart@fourpalms.org> writes:
>> ... I'd be sorely tempted to replace all three by a single
>> function that takes integer and returns numeric.

> Yikes. Although numeric is theoretically nice, it is hundreds of times
> slower than native doubles.

(a) As a wise man once said, "I can make it arbitrarily fast, if it
doesn't have to give the right answer".  (b) The factorial function
doesn't strike me as a performance bottleneck.  (c) I have no objection
to offering a double-precision-based gamma function alongside the
integer factorial function.  But I think factorial should give an exact
answer as far as is possible before it overflows.

            regards, tom lane

Re: Bug #534: factorial function

From
"Janko F. Richter"
Date:
-------- Original Message --------
Subject: Re: [BUGS] Bug #534:  factorial function
Date: Mon, 10 Dec 2001 13:28:15 +0100
From: "Janko F. Richter" <j.richter@wallstreet-develop.de>
To: Bruce Momjian <pgman@candle.pha.pa.us>
References: <200112101121.fBABLgI23940@candle.pha.pa.us>

Hallo Bruce,

Bruce Momjian wrote:

 > Yep, 0! sure looks like a bug.  We will fix it in 7.3.  Not sure about
 > the double precision.  Comments?



Perhaps a good idea is to implement the Gamma function additional
to factorial function. The gamma function as general function of
factorial accepts float/double values as argument and returns double.


Maybe a good idea too is to implement a set of standard statistical
functions for instance normal, poisson, binomial, etc to get less range
overflow while using statistical functions.

Perhaps when I'll have time, I will try to write a set of this functions
as contrib next year.

Thank you and regards ,

Janko

Re: Bug #534: factorial function

From
Bruce Momjian
Date:
> Thomas Lockhart <lockhart@fourpalms.org> writes:
> >> ... I'd be sorely tempted to replace all three by a single
> >> function that takes integer and returns numeric.
>
> > Yikes. Although numeric is theoretically nice, it is hundreds of times
> > slower than native doubles.
>
> (a) As a wise man once said, "I can make it arbitrarily fast, if it
> doesn't have to give the right answer".  (b) The factorial function
> doesn't strike me as a performance bottleneck.  (c) I have no objection
> to offering a double-precision-based gamma function alongside the
> integer factorial function.  But I think factorial should give an exact
> answer as far as is possible before it overflows.

float8 is the wrong way to go with factorial.  It is imprecise and only
goes to 69!.  Numeric seems like the way to go with this function.

Added to TODO:

    * Change factorial to return a numeric

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Bug #534: factorial function

From
Bruce Momjian
Date:
pgsql-bugs@postgresql.org wrote:
> Janko Richter (j.richter@wallstreet-develop.de) reports a bug
> with a severity of 3 The lower the number the more severe it
> is.
>
> Short Description factorial function
>
> Long Description 1. The factorial function returns 0 when the
> argument is 0. The factorial Funtion is defined as:  factorial(0)=1

I have fixed factorial(0)=1 in 7.3.

> 2. It would be better to define a factorial function returning
> double precision. The factorial function returning bigint produces
> false values  with arguments larger then 20.
>
> Notice: I'm using the factorial function for statistical functions
> (for example poisson distribution)
>
> Thank you.
>
>
> Sample Code test=# select factorial(0);
>  factorial -----------
>     0
> (1 row)
>
> janko=# select factorial(21::bigint);
>  factorial ----------------------
>  -4249290049419214848 (1 row)

The problem here is that we don't have a bigint version of factorial,
just an int8 version.  This is now on the TODO list:

    * Change factorial to return a numeric

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026