Thread

1. coalesce and aggregate functions

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2006-12-12T15:22:19Z

   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)
   
   

2. Re: coalesce and aggregate functions

   Heikki Linnakangas <heikki@enterprisedb.com> — 2006-12-12T15:33:04Z

   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
   
   

3. Re: coalesce and aggregate functions

   Gregory Stark <stark@enterprisedb.com> — 2006-12-12T15:35:07Z

   "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
   
   

4. Re: coalesce and aggregate functions

   Kevin Grittner <kevin.grittner@wicourts.gov> — 2006-12-12T15:35:26Z

   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?
   
   
   

5. Re: coalesce and aggregate functions

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2006-12-12T16:45:34Z

   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