Обсуждение: coalesce and aggregate functions
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)
Patrick Welche wrote: > 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.. Coalesce returns the first non-null argument. In your example, 0 is always the first non-null argument. You should be doing this instead: select coalesce(sum(b),0) from test where a=2; to get the desired effect. BTW: This type of questions really belong to pgsql-general or pgsql-novice, this list is for discussing development of PostgreSQL itself. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
"Patrick Welche" <prlw1@newn.cam.ac.uk> writes: > Is this a bug, or don't I understand coalesce()? > > 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.. Coalesce will return the first argument if it's not null. You may be thinking about the arguments in reverse order? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
COALESCE returns the leftmost non-null value. Perhaps what you wanted was sum(coalesce(b,0)) instead of coalesce(0,sum(b)) >>> On Tue, Dec 12, 2006 at 9:22 AM, in message <20061212152219.GC290@quartz.itdept.newn.cam.ac.uk>, Patrick Welche <prlw1@newn.cam.ac.uk> wrote: > 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?
On Tue, Dec 12, 2006 at 03:33:04PM +0000, Heikki Linnakangas wrote: > BTW: This type of questions really belong to pgsql-general or > pgsql-novice, this list is for discussing development of PostgreSQL itself. ^^^^^^ Indeed - I am truly feeling like a novice now... Cheers, Patrick