Problem With Case Statement and Aggregate Functions

From: Andrew Shea <andrew(at)octahedron(dot)com(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem With Case Statement and Aggregate Functions
Date: 2007-05-11 04:47:04
Message-ID: 4643F548.80500@octahedron.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following works as expected:

select (SELECT CASE WHEN (1=2) THEN 0 ELSE sum(count) END) from (
select 1 as count union select 2 union select 3
) as "temp";

The result is "6".

The following also works as expected:

select count(*) from (
select 1 as count union select 2 union select 3
) as "temp";

The results is "3".

However the following code doesn't work even though it is very similar
to the first query (that is, and aggregate function within a case
statement):

select (SELECT CASE WHEN (1=2) THEN 0 ELSE COUNT(*) END) from (
select 1 as count union select 2 union select 3
) as "temp";

The result is three rows of "1".

So why does the "count" aggregate function within a case statement
execute on a per row basis whereas the "sum" aggregate within a case
statement will first group the rows?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Shyam Sunder Rai 2007-05-11 08:14:37 BUG #3267: Relfilenode
Previous Message Peter Koczan 2007-05-10 20:14:39 BUG #3266: SSL broken pipes kill the machine and fill the disk