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?
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 |