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(at)prolexia(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461