Thread

1. Re: [HACKERS] Group By, NULL values and inconsistent behaviour.

   Darren King <darrenk@insightdist.com> — 1998-01-26T15:36:04Z

   > 
   > The following is Informix behavior:
   > 
   > informix@zeus:/usr/informix72> dbaccess - -
   > > create database nulltest in datadbs;
   > Database created.
   > 
   > > create table t1 (a int, b char(2), c char(2));
   > Table created.
   > 
   > > insert into t1 (a,c) values (1,'x');
   > 1 row(s) inserted.
   > > insert into t1 (a,c) values (2,'x');
   > 1 row(s) inserted.
   > > insert into t1 (a,c) values (3,'z');
   > 1 row(s) inserted.
   > > insert into t1 (a,c) values (2,'x');
   > 1 row(s) inserted.
   > > select * from t1;
   >           a b  c
   >           1    x
   >           2    x
   >           3    z
   >           2    x
   > 
   > 4 row(s) retrieved.
   > > select b,c,sum(a) from t1 group by b,c;
   > b  c             (sum)
   > 
   >    x                 5
   >    z                 3
   > 
   > 2 row(s) retrieved.
   
   Here is where postgres seems to differ.  Seems postgres is missing
   an implicit sort so that the grouping is done properly.
   
   Postgres will return _three_ rows...
   
   b  c      (sum)
      x          3
      z          3
      x          2
   
   
   > > select b,c,sum(a) from t1 group by b,c order by c;
   > b  c             (sum)
   > 
   >    x                 5
   >    z                 3
   > 
   > 2 row(s) retrieved.
   
   Even with the order by, postgres still returns _three_ rows...
   
   b  c       (sum)
      x           3
      x           2
      z           3
      
   For now, ignore the patch I sent.  Appears from Andreas demo that the
   current postgres code will follow the Informix style with regard to
   grouping columns with NULL values. Now that I really think about it,
   it does make more sense.
   
   But there is still a problem.
   
   Does the SQL standard say anything about an implied sort when
   grouping or is it up to the user to include an ORDER BY clause?
   
   darrenk
   
   

2. Re: [HACKERS] Group By, NULL values and inconsistent behaviour.

   Michael J. Maravillo <mmj@philonline.com> — 1998-01-26T16:23:04Z

   On Mon, 26 Jan 1998, Darren King wrote:
   
   >> The following is Informix behavior:
   >> 
   >> > select b,c,sum(a) from t1 group by b,c;
   >> b  c             (sum)
   >> 
   >>    x                 5
   >>    z                 3
   >> 
   >> 2 row(s) retrieved.
   >
   >Here is where postgres seems to differ.  Seems postgres is missing
   >an implicit sort so that the grouping is done properly.
   >
   >Postgres will return _three_ rows...
   >
   >b  c      (sum)
   >   x          3
   >   z          3
   >   x          2
   
   I'm running the current cvs and it gives me this.
   
   select b,c,sum(a) from t1 group by b,c;
   b|c |sum
   -+--+---
    |x |  1
    |x |  2
    |z |  3
    |x |  2
   (4 rows)
   
   >> > select b,c,sum(a) from t1 group by b,c order by c;
   >> b  c             (sum)
   >> 
   >>    x                 5
   >>    z                 3
   >> 
   >> 2 row(s) retrieved.
   >
   >Even with the order by, postgres still returns _three_ rows...
   >
   >b  c       (sum)
   >   x           3
   >   x           2
   >   z           3
   
   select b,c,sum(a) from t1 group by b,c order by c;
   b|c |sum
   -+--+---
    |x |  1
    |x |  2
    |x |  2
    |z |  3
   (4 rows) 
   
   >For now, ignore the patch I sent.  Appears from Andreas demo that the
   >current postgres code will follow the Informix style with regard to
   >grouping columns with NULL values. Now that I really think about it,
   >it does make more sense.
   
   I think I saw the patch committed this morning...?
   
   
   Mike
   [ Michael J. Maravillo                                 Philippines Online ]
   [ System Administrator     PGP KeyID: 470AED9D     InfoDyne, Incorporated ]
   [ http://www.philonline.com/~mmj/                          (632) 890-0204 ]
   
   
   

3. Re: [HACKERS] Group By, NULL values and inconsistent behaviour.

   Marc G. Fournier <scrappy@hub.org> — 1998-01-26T16:30:46Z

   On Tue, 27 Jan 1998, Michael J. Maravillo wrote:
   
   > >For now, ignore the patch I sent.  Appears from Andreas demo that the
   > >current postgres code will follow the Informix style with regard to
   > >grouping columns with NULL values. Now that I really think about it,
   > >it does make more sense.
   > 
   > I think I saw the patch committed this morning...?
   
   	Yesterday evening, actually...should we back it out, or leave it
   as is?  Is the old way more corrrect the the new?