Re: BUG #2477: Aggregate Integer divisors incorrectly yield integer-type quotient

On Sun, Jun 11, 2006 at 09:08:00PM +0000, John Rylander wrote:
>
> The following bug has been logged online:
>
> Bug reference:      2477
> Logged by:          John Rylander
> Email address:      rylander@prolexia.com
> PostgreSQL version: 8.1.4
> Operating system:   Windows XP :(
> Description:        Aggregate Integer divisors incorrectly yield
> integer-type quotient
> Details:
>
> When for some reason I did a sum/count instead of an avg, the result was
> always an integer (either 0 or 1):
>
> Sum(
>     Case when Sign(Delta)=Sign(AvgDelta) then
>        1
>     else
>        0
>    ) / Count(*) as HitRate;
>
> If I use "/ Count(*)::float4", or use Avg instead of Sum/Count, it works
> fine.

Do you have a test case for this?

> Yet another maddening PostgreSQL bug/idiosyncrasy.  Time for this boy to go
> to Oracle (not because of this, but because of the function->transaction
> problem, such that a function that has nested loops that create myriad
> temporary analytical tables necessarily runs out of disk space; if it
> weren't for the mandatory and completely counterproductive implicit
> transaction, it'd take very little space and a lot less time).  :(

Huh? What implicit transaction? Do you have an example that illustrates
this problem?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461