pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: secret <secret@kearneydev.com>
To: Jackson, DeJuan <djackson@cpsgroup.com>
Cc: PG-SQL <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Oddities with NULL and GROUP BY
Date: Mon, 17 May 1999 09:14:50 -0400
Message-ID: <3740164A.E604F6E5@kearneydev.com> (raw)
References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail>

"Jackson, DeJuan" wrote:

> The behavior is valid, if you define NULL as meaning undefined.
> In other words when you define something as NULL you're saying, "I don't
> know what it is. It could be equal or not."
>         -DEJ
>
> > -----Original Message-----
> > From: secret [SMTP:secret@kearneydev.com]
> > Sent: Friday, May 14, 1999 11:58 AM
> > To:   PG-SQL
> > Subject:      [SQL] Oddities with NULL and GROUP BY
> >
> >     Maybe there is something I don't know about how GROUP BY should
> > work, but if I have a table like:
> > a,b,c
> > 1,1,1
> > 1,1,2
> > 1,1,3
> > 1,2,1
> > 1,3,1
> >
> > And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
> > 1,1,6
> > 1,2,1
> > 1,3,1
> >
> > So whenever a or b changes we get a new summed row, well if I have rows
> > where a or b are null, this doesn't happen, infact I seem to get all
> > those rows individually... Like if:
> > 1,1,1
> > 1,1,3
> > 1,NULL,10
> > 1,NULL,20
> > 1,2,3
> >
> > I get:
> > 1,1,4
> > 1,NULL,10
> > 1,NULL,20
> > 1,2,3
> >
> > Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like any other
> > value?  Or is there some bit of information I'm missing?  I can set
> > everything from NULL to 0 if need be, but I'd rather not...
> >
> > David Secret
> > MIS Director
> > Kearney Development Co., Inc.
> >

    IBM's DB/2 Disagrees, so does Oracle8!


Here is a cut & paste from Oracle SQL+:

SQL> select * from z;

        A         B
--------- ---------
        1         1
        1         2
                  5
                 10

SQL> select a,sum(b) from z group by a;

        A    SUM(B)
--------- ---------
        1         3
                 15

SQL>

    I'm going to report this as a bug now that I've verified 2 major database
vendors perform the task as I would expect them to, and PostgreSQL does it
very differently.  The question is really is NULL=NULL, which I would say it
should be.




view thread (15+ messages)  latest in thread

Message-ID: <3740164A.E604F6E5@kearneydev.com>
Permalink:  ../3740164A.E604F6E5@kearneydev.com/
Also on:    postgresql.org/message-id/3740164A.E604F6E5@kearneydev.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: secret@kearneydev.com, djackson@cpsgroup.com
  Subject: Re: [SQL] Oddities with NULL and GROUP BY
  In-Reply-To: <3740164A.E604F6E5@kearneydev.com>
  References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail> <3740164A.E604F6E5@kearneydev.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox