Is this a bug, or don't I understand coalesce()?
create table test (a int, b int);
insert into test values (1,null);
insert into test values (2,1);
insert into test values (2,2);
select * from test; -- returns:
select sum(b) from test where a=1; -- null
select sum(b) from test where a=2; -- 3
select coalesce(0,sum(b)) from test where a=1; -- 0
select coalesce(0,sum(b)) from test where a=2; -- 0
delete from test where a=1;
select coalesce(0,sum(b)) from test where a=2; -- 0 !
So when I use coalesce() with sum(), I always get the constant. I would
have expected it only in the case where sum() returns null..
What am I missing?
Cheers,
Patrick
(PostgreSQL 8.2devel of 21st November 2006)