Thread

Re: [HACKERS] No: implied sort with group by

Andrew Martin <martin@biochemistry.ucl.ac.uk> — 1998-01-29T10:43:34Z

> > > postgres=> select b,c,sum(a) from t1 group by b,c;
> > > b|c|sum
> > > -+-+---
> > >  |x|  5
> > >  |z|  3
> > >  |x|  0
> > > (3 rows)
> > >
> > > postgres=> select * from t1;
> > > a|b|c
> > > -+-+-
> > > 1| |x
> > > 2| |x
> > > 2| |x
> > > 3| |z
> > > 0| |x
> > > (5 rows)
> > >
> > > I just inserted a single out-of-order row at the end of the table which, since the
> > > integer value is zero, should have not affected the result. Sorry I didn't understand
> > > the nature of the test case.
> 
> > Hmmm...is this a grouping problem or an aggregate problem?  Or both?  The first
> > query above should have the data sorted before aggregating, shouldn't it, or I
> > am still missing a piece of this puzzle?
> 
> fwiw, I see the same incorrect behavior in v6.2.1p5.
> 
>                                                 - Tom
> 
> 
And in v6.1. If b is a space (rather than a NULL), then the behaviour is correct
so it must be a problem in grouping NULLs.


Andrew


----------------------------------------------------------------------------
Dr. Andrew C.R. Martin                             University College London
EMAIL: (Work) martin@biochem.ucl.ac.uk    (Home) andrew@stagleys.demon.co.uk
URL:   http://www.biochem.ucl.ac.uk/~martin
Tel:   (Work) +44(0)171 419 3890                    (Home) +44(0)1372 275775